AUTHID_CURRENT_USER can be misused by developers. The new object privilege INHERIT PRIVILEGES ON was introduced in 12c to prevent that. I start with a demo to explain the problem that the new features solves. The playground:
[oracle@uhesse ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 7 10:36:28 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> grant dba to adam identified by adam;
Grant succeeded.
SQL> grant create session,create procedure to devel identified by devel;
Grant succeeded.
SQL> connect adam/adam
Connected.
SQL> create table important_table as select * from dual;
Table created.
SQL> connect devel/devel
Connected.
SQL> drop table adam.important_table;
drop table adam.important_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
DEVEL has no privileges that allow him to do much harm, apparently. But he may trick ADAM into doing that for him:
SQL> create or replace procedure trustmeplease
authid current_user
as
begin
execute immediate 'drop table adam.important_table';
end;
/
SQL> Procedure created.
SQL> exec trustmeplease
BEGIN trustmeplease; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "DEVEL.TRUSTMEPLEASE", line 5
ORA-06512: at line 1
Without AUTHID_CURRENT_USER, the procedure runs with creators rights. With it, it runs with callers rights. No difference if DEVEL calls it. But maybe he can convince ADAM to call it:
SQL> grant execute on trustmeplease to adam;
Grant succeeded.
SQL> connect adam/adam
Connected.
SQL> exec devel.trustmeplease
PL/SQL procedure successfully completed.
SQL> select * from important_table;
select * from important_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
ADAM got tricked into dropping that table! Assume that the DROP TABLE was hidden within some apparently useful code. So this is the problem why INHERIT PRIVILEGES was introduced. As a default, the behavior of the 12c Database is the same regarding AUTHID_CURRENT_USER as in previous releases. But there is a change under the covers. Whenever a new user gets created, we grant INHERIT PRIVILEGES to PUBLIC:
SQL> col privilege for a20 SQL> select grantee,privilege,grantor from user_tab_privs_made; GRANTEE PRIVILEGE GRANTOR ---------- -------------------- ---------- PUBLIC INHERIT PRIVILEGES ADAM
You saw me creating that user and I did not grant anything about ADAM myself. In order to safeguard, this privilege needs to be revoked from public:
SQL> revoke inherit privileges on user adam from public;
Revoke succeeded.
SQL> flashback table important_table to before drop;
Flashback complete.
SQL> exec devel.trustmeplease
BEGIN devel.trustmeplease; END;
*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "DEVEL.TRUSTMEPLEASE", line 1
ORA-06512: at line 1
The error means that the procedure I call wants to exercise one of my privileges that the creator of the procedure lacks. After an investigation of that procedure, I may grant that:
SQL> grant inherit privileges on user adam to devel; Grant succeeded. SQL> exec devel.trustmeplease PL/SQL procedure successfully completed. SQL> flashback table important_table to before drop; Flashback complete.
It is always a good idea to be aware of privileges that have been granted to PUBLIC. In this case, we keep up the old behavior towards AUTHID_CURRENT_USER that way. Because developers are not necessarily malicious and procedures would otherwise fail with errors that did run successful in the past.
My advice is to revoke INHERIT PRIVILEGES on all users from PUBLIC on a test system and then check thoroughly the procedures that fail now with ORA-06598. Afterwards – if the check turns out okay – grant it on the caller to the developer of these procedures and not to PUBLIC. Because, you know:
Don’t believe it, test it! 🙂
Pickleball spielen 