GRANT ANY OBJECT PRIVILEGE (GAOP) not working in 12.2

Hallo,

some days ago one of my costomers got ORA-01031 when granting sys object privileges to another db user.

He used an user with DBA role to do this. The same grant works in 12.1 but not in 12.2.

Some analysis later i thought it’s a bug.

The GRANT ANY OBJECT PRIVILEGE (GAOP) was implemented in Oracle 9.2 or 10.1 (doesn’t know currently). It works in 12.1 too but doesn’t work in 12.2.

I raised a service request and got the answers now.  –> It’s not a bug it’s a feature 🙂

Here are the facts:

  • GRANT ANY OBJECT PRIVILEGE (GAOP) does not apply for SYS objecs because of dictionary protection feature (db parameter O7_DICTIONARY_ACCESSIBILITY=false)
  • New READ privilege provided in 12.1 ( READ = SELECT without LOCK TABLE permission)
  • Up to version 12.1SELECT on sys.all_synonymes granted with GRANT OPTION to PUBLIC (anybody can grant SELECT to this object)
  • Starting from 12.2READ on sys.all_synonymes granted with GRANT OPTION to PUBLIC (nobody can grant SELECT to this object except SYS or other user with SELECT .. WITH GRANT OPTION permission)

This is a new feature or change in behavior. This will get the database more secure in default configuration!

That does it mean?

Nobody (except SYS) can grant SYS object privileges to other database users!

 

Starting from 12.2 …

Nobody (except SYS and from SYS with GRANT OPTION privileged users) can SELECT or GRANT SELECT on SYS objects eg. ALL_SYNONYMS.

Anybody can READ SYS objects eg. ALL_SYNONYMES

 

Mind the READ permission change in 12.2 if you get ORA-01031 when you SELECT or GRANT SELECT on SYS objects.

Workarounds:

  • connect sys as sysdba; grant select on <object> with grant option to <user | role>
  • connect sys as sysdba; grant select on <object> with grant option to public;    (Oracle 12.1 behavior)
  • disable dictionary protection with O7_DICTIONARY_ACCESSIBILITY=true     (Oracle 7 behavior)

 

And here is my test case:

Oracle 12.1.0.2 Oracle 12.2.0.1
O7_DICTIONARY_ACCESSIBILITY  = FALSE O7_DICTIONARY_ACCESSIBILITY  = FALSE
connect system/oracle connect system/oracle
select * from session_privs where privilege like ‚GRANT%‘;

PRIVILEGE

—————————————-

GRANT ANY OBJECT PRIVILEGE

GRANT ANY PRIVILEGE

GRANT ANY ROLE

–>  SYSTEM has GRANT ANY OBJECT PRIVILEGE but this doesn’t work for SYS objects because of enabled dictionary protection (O7_DICTIONARY_ACCESSIBILITY  = FALSE)

select * from session_privs where privilege like ‚GRANT%‘;

PRIVILEGE

—————————————-

GRANT ANY OBJECT PRIVILEGE

GRANT ANY PRIVILEGE

GRANT ANY ROLE

select grantee, privilege, grantable from dba_tab_privs where table_name=’ALL_SEQUENCES‘;

GRANTEE PRIVILEGE GRA

—————————— —————————————- —

PUBLIC SELECT YES

–>  SYSTEM (and all other users) are privileged to grant SELECT to other users because of PUBLIC was granted the SELECT on sys.all_sequences with GRANT OPTION.

select grantee, privilege, grantable from dba_tab_privs where table_name=’ALL_SEQUENCES‘;

GRANTEE PRIVILEGE GRA

—————————— —————————————- —

PUBLIC READ YES

–>  SYSTEM (and all other suers) are privileged for READ only because of PUBLIC was granted the READ on sys.all_sequences with GRANT OPTION.

grant SELECT on all_synonyms to hugo with grant option;

–> It works!

grant SELECT on all_synonyms to hugo with grant option;

–>  ORA-01031

And some Oracle 12.2 version informations:

 

Schreibe einen Kommentar