|
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? |
|
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'.
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 ... > the introduction of Roles
(06 Jul '16, 14:18)
Breck Carter
Nick, thanks for the explanation. Still I have a few more questions:
(07 Jul '16, 03:32)
Volker Barth
|
|
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;
...
|