Thanks for the question, Christine.

Asked: July 05, 2018 - 6:45 pm UTC

Last updated: October 31, 2018 - 2:31 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

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?

and Connor said...

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

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

dictionary to find "INHERIT PRIVILEGES"

Rajeshwaran, Jeyabal, July 10, 2018 - 2:28 pm UTC

Team,

was looking at the data dictionary where i could find the information related to "INHERIT PRIVILEGES" granted to PUBLIC schema by default?

looked into dba_role_privs - but dont see there, is there is any other dictionary which i can look into?

demo@ORA12C> select *
  2  from dba_role_privs
  3  where granted_role like 'INHER%';

no rows selected

July 12, 2018 - 9:42 am UTC

It is not a role...it is a privilege

select  * from dba_SYS_privs
where privilege like '%INHERIT%'

dictionary to find "INHERIT PRIVILEGES"

Rajeshwaran, Jeyabal, July 12, 2018 - 10:41 am UTC

but as said, that is not granted to public by default.
in the below listing, we dont see the PUBLIC schema.

demo@ORA12C> select  * from dba_sys_privs
  2  where privilege like '%INHERIT%'
  3  /

GRANTEE              PRIVILEGE                      ADM COM INH
-------------------- ------------------------------ --- --- ---
APEX_050100          INHERIT ANY PRIVILEGES         NO  NO  NO
DBSNMP               INHERIT ANY PRIVILEGES         NO  YES YES
ORDPLUGINS           INHERIT ANY PRIVILEGES         NO  YES YES
XDB                  INHERIT ANY PRIVILEGES         NO  YES YES
MDSYS                INHERIT ANY PRIVILEGES         NO  YES YES
SYS                  INHERIT ANY PRIVILEGES         NO  YES YES
GSMADMIN_INTERNAL    INHERIT ANY PRIVILEGES         NO  YES YES
WMSYS                INHERIT ANY PRIVILEGES         NO  YES YES
CTXSYS               INHERIT ANY PRIVILEGES         NO  YES YES
ORACLE_OCM           INHERIT ANY PRIVILEGES         NO  YES YES
ORDSYS               INHERIT ANY PRIVILEGES         NO  YES YES

11 rows selected.

demo@ORA12C>

October 31, 2018 - 2:31 am UTC

Interesting...I found it in DBA_TAB_PRIVS

SQL> select grantee, table_name, privilege
  2  from dba_tab_privs
  3  where privilege = 'INHERIT PRIVILEGES';

GRANTEE                        TABLE_NAME                     PRIVILEGE
------------------------------ ------------------------------ ---------------------
XDB                            SYS                            INHERIT PRIVILEGES
CTXSYS                         SYS                            INHERIT PRIVILEGES
PUBLIC                         PUBLIC                         INHERIT PRIVILEGES
PUBLIC                         XS$NULL                        INHERIT PRIVILEGES
GSMADMIN_INTERNAL              GSMCATUSER                     INHERIT PRIVILEGES
CTXSYS                         XDB                            INHERIT PRIVILEGES
PUBLIC                         HR                             INHERIT PRIVILEGES
PUBLIC                         SCOTT                          INHERIT PRIVILEGES
PUBLIC                         ASKTOM                         INHERIT PRIVILEGES
PUBLIC                         ORDS_METADATA                  INHERIT PRIVILEGES
PUBLIC                         ORDS_PUBLIC_USER               INHERIT PRIVILEGES
PUBLIC                         APEX_LISTENER                  INHERIT PRIVILEGES
PUBLIC                         APEX_REST_PUBLIC_USER          INHERIT PRIVILEGES

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

dictionary to find "INHERIT PRIVILEGES"

dictionary to find "INHERIT PRIVILEGES"

More to Explore

PL/SQL demos

PL/SQL docs

Inherit Privileges privilege to PUBLIC

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

dictionary to find "INHERIT PRIVILEGES"

dictionary to find "INHERIT PRIVILEGES"

More to Explore

PL/SQL demos

PL/SQL docs