Whose permissions are checked for a procedure with SQL SECURITY DEFINER and client-side reads?

[VolkerBarth]

Say, I have a v12.0.1 procedure (or function) with the default SQL SECURITY DEFINER that does a client-read within.

When calling that procedure (owned by UserA) by a different user UserB, whose permissions and options are checked to do a successful client-side read - those by UserA or UserB?

I'm asking since UserA has DBA authority and has option "allow_read_client_file" = 'On' whereas UserB is a normal user and does not have that option set, and the call of the procedure fails with SQLCODE -121 because of missing privileges as option "allow_read_client_file" prevents that.

With DEFINER, I would have expected that the definer's option would be used, and the caller's settings would be irelevant...

---

Aside: In case the option is set to 'On' for that user by a DBA, this seems only to become effective once the user's connection is closed and re-opened. Is that expected behaviour for that option?

[Former Member]

This response does not apply to versions newer than 12.0.1 since the introduction of Roles in version 16 changes many aspects of this discussion so such questions would need to be re-evaluated when upgrading.

For Version 12.0.x the question seems to be one of a difference of 'scope'.

• The SQL SECURITY mechanism operates at the level of SQL language parsing, annotation, group membership and object-level permissions (inherited or assigned directly).
• In contrast the READCLIENTFILE authority is not an object-level permission but a database level authority.

While this difference of scope may seem to be a semantic difference (since an authority can sometimes confer priviledges on database objects to the user) they do operate in a fundamentally different way. {If it helps to clarify any, I tend to think of object-level permissions as operating at the DML level, but authorities operating more at a DCL level or meta-security level.} The fact that the Read_Client_File( ) function appears to bridde both of those scopes, the authority is still checked at the database connection level; see allow_read_client_file.

... more of an explanation than an answer or a workaround since you already know how it is behaving ... hopefully that info helps in some useful way ...

[Breck_Carter]

> the introduction of Roles

[VolkerBarth]

Nick, thanks for the explanation. Still I have a few more questions:

• I'm not dealing with the READCLIENTFILE authority but with the "allow_read_client_file" option. In my case the authority itself has been granted to the owner ("UserA") which is a group, and that way the "normal" UserB as memmber of the group inherits that authority. But I generally had to explicitly set UserB's option "allow_read_client_file" to 'On' to enable client-side reads.
• As asked in the question: Is it expected behaviour for that option to only become effective once the user's connection is closed and re-opened?
• As permissions can be granted to groups, here I get the impression it would be handy to have "group-level" database options, as well...

[VolkerBarth]

Given Nick's explanation, here's a kind of workaround to allow client-reads for the caller of a procedure with SQL SECURITY DEFINER just within that call...

The caller's name is dynamically asked via current user, and therefore execute immediate is needed to build the SET OPTION statement. (v17's "indirect identifiers" feature would be handy here - but apparently that one's RBAC is a different beast altogether...)

Note: To do the SET OPTION for another user, DBA authority is required for the procedure's owner.

create procedure ...
   ...
   declare bFlagAllowClientReadsTemporarily bit = 0;

   -- eventually set that option temporarily
   if connection_property('allow_read_client_file') = 'Off' then
      execute immediate with result set off
         'set temporary option "' || current user || '".allow_read_client_file = ''On''';
      if connection_property('allow_read_client_file') = 'On' then
         set bFlagAllowClientReadsTemporarily = 1;
         message 'Option allow_read_client_file has been enabled temporarily' to client;
      end if;
   end if;

   -- do the read_client_file() calls

   -- eventually reset that option temporarily
   if bFlagAllowClientReadsTemporarily = 1 then
      execute immediate with result set off
         'set temporary option "' || current user || '".allow_read_client_file = ''Off''';
   end if;
   ...