I was doing a query on my database against dba_tab_privs where the grantee was PUBLIC. I noticed that for every user I have in my database, PUBLIC has "INHERIT PRIVILEGES" on that user. Should this be something that is allowed? How exactly is this working because I wanted to limit the amount of objects that PUBLIC has access to?
To explain, let me give you a scenario...where I hack your database :-)
First I do this:
SQL> conn scott/tiger
Connected.
SQL>
SQL> drop table t purge;
Table dropped.
SQL> create table t ( x int );
Table created.
SQL> grant select on t to public;
Grant succeeded.
SQL> create or replace
2 procedure MY_PROC authid current_user is
3 v int;
4 begin
5 select 1/count(*) into v from scott.t;
6 end;
7 /
Procedure created.
SQL> exec scott.my_proc
BEGIN scott.my_proc; END;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SCOTT.MY_PROC", line 4
ORA-06512: at line 1
You can see that it fails because the "count(*)" will return zero and I'll get an error
I did this deliberately, because now I get on the phone to you... (the DBA)
"Hi Christine, Man I'm having a bad day. My proc keeps failing - I think its a privileges issue. Could you give it a run and see if it works for you?"
You log in with your DBA account, take a look at the code (which looks harmless) and give it a run:
SQL> conn christine/christine
Connected.
SQL> exec scott.my_proc
BEGIN scott.my_proc; END;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SCOTT.MY_PROC", line 4
ORA-06512: at line 1
and you jump back on the phone to me
"Hey Scott...your table is empty you dufus...there is no problem here with the code".
Now of course I *knew* you would say that...so now I amend the procedure to be this:
SQL> conn scott/tiger
Connected.
SQL>
SQL> create or replace
2 procedure MY_PROC authid current_user is
3 v int;
4 begin
5 execute immediate 'grant dba to scott';
6 select 1/count(*) into v from scott.t;
7 end;
8 /
Procedure created.
Of course, as SCOTT, it doesn't matter what grants I try do - I'll not be allowed to execute them. But of course...I can just get back on the phone :-)
"Hi Christine, Scott again. I thought I'd fixed that but its still not working - can you see if it fails for you too?
and since you only *just looked* at the code, and your sqlplus window is just *sitting there* from a few moments ago...you do this:
SQL> exec scott.my_proc
BEGIN scott.my_proc; END;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SCOTT.MY_PROC", line 5
ORA-06512: at line 1
and reply...
"Hey Scott...yeah, same error"
and THAT .... is how I hacked your database :-)
SQL> conn scott/tiger
Connected.
SQL> select * from session_roles;
ROLE
----------------------------------
CONNECT
RESOURCE
DBA <============= !!!!!!!!!
So back to your question... The above demo is how privs have always worked - but in 12c we give you more tools to control it. The ability for a user to perform an escalated privileges operation (such as granting dba to someone) is now controllable via the INHERIT PRIVILEGES privilege. By default, it is public (as you've seen) because we are keeping the functionality compatible with previous releases of the database.
More details here
https://docs.oracle.com/database/121/DBSEG/dr_ir.htm#DBSEG658