SQL SECURITY DEFINER has inconsistent behaviour if the object owner is non-privileged

[dyemanov]

Usually, SQL SECURITY DEFINER is used to raise the current set of privileges for some particular task. However, if security definer is non-privileged, I'd expect the current set of privileges to be reduced accordingly to the owner. This is not really so though, see below.

1. Usage of MON$ tables

When effective user is privileged, data from all attachments are seen, as expected. However, when effective user is not privileged and thus it should see only its own attachments, in fact it sees only attachments established by the session user. In other words, user A calls procedure with definer B which (ooops) sees attachments by user A. This looks unexpected and may also be considered as a security risk.

2. System privileges

We check them as "session-user-allowed OR definer-user-allowed" (see Attachment::locksmith()). So if privileged user A calls procedure with non-privileged definer B, it still can perform every DBA action inside that procedure, like if it was executed under A's permissions. I'm not sure whether it's intended, but it looks inconsistent with other (SQL level) permissions that are checked for effective user B only (accordingly to the SQL spec, AFAIK). As for me, this looks like a bug.

Both cases are unlikely to be seen in real world databases, but IMO they deserve attention anyway.

[mrotteveel]

For context, 10.4 <routine invocation> in SQL:216 seems relevant:

If the SQL security characteristic of R is DEFINER, then the top cell of the authorization
stack of RSC is set to contain only the routine authorization identifier of R.

(general rule 5.l.ii)

[romansimakov]

Yes. SQL standard is more strict in this part. My original idea was if invoker or definer (effective user) has a permission (is locksmith particulary) it must get an access. Now I tend to agree to make it more consistent with SQL Standard. Let's consider an example. SYSDBA connects to a database and run a procedure P defined by UU (unprivileged user) with SQL SECURITY DEFINER. UU has no access to an object O. Now SYSDBA is able to perform P and get the access to O. According to SQL Standard it must not. It's correct because:

• UU defining P must think about the access to O.
• SYSDBA is able to use other ways to access O.
• P can be desined especially to prevent such access.
  Any case I consider this as correct and agree not to use involker privileges inside sql definer routins.

[aafemt]

If UU has no access to O then P also has no access to O and referencing of O in P is pointless. What kind of P it must be to provide SYSDBA access to O?

[romansimakov]

If UU has no access to O then P also has no access to O and referencing of O in P is pointless. What kind of P it must be to provide SYSDBA access to O?

It is really pointless. But it's the only possible case I could make up and that proves that following SQL Standard is OK.

[pavel-zotov]

Could not reproduce wrong behavior described in "2. System privileges", sent script to dimitr, 31.08.2021 00:12; waiting for reply.