Saturday, December 15, 2012

0 Oracle Privilege

  • “SELECT ANY TABLE” can query any tables except SYS and data dictionary.
  • “SELECT ANY DICTIONARY” can query SYS and data dictionary but not other table
  • Many system privileges looks similar, create table and create ANY table. The difference is the ANY privilege can across schema.
  • Public Role: all users are part of Public Role, if the privilege grant to public means all user has that privilege.

 

Related tables:

  • SESSION_PRIVS
  • DBA_SYS_PRIVS
  • DBA_TAB_PRIVS

Whether ANY privilege include SYS schema?

It is define by “O7_DICTIONARY_ACCESSIBILITY”, the default value is FALSE from 9i.

image

To grant the dictionary permission, we can use SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY.

Check current system privilege own by current user

select * from session_privs;

image

 

Demo: System privilege

Create 2 users, PO2 and PO3.

--- Create 2 users
create user PO2
identified by oracle
default tablespace POTS1
temporary tablespace tempts
quota 10M ON POTS1
profile default;
grant create session,create table to PO2;
 
create user PO3
identified by oracle
default tablespace POTS1
temporary tablespace tempts
quota 10M ON POTS1
profile default;
grant create session,create table to PO3;
 
--- This will work
Connect PO2/oracle
create table t1( a number);
insert into t1 values( 1);
select * from PO2.t1;
commit;

 

image

Try to create the table on the PO3 schema

--- This will fail
--- because PO2 only have "create table" not "create any table"
create table PO3.t1( a number);

image

Grant PO2 “CREATE ANY TABLE” privilege.

image 

select * from session_privs;
create table PO3.t1( a number);
insert into PO3.t1 values( 1);
select * from PO3.t1;

 

Create PO3.t1 would work because PO2 has “Create ANY table” privilege now.
However, insert and update would fail because PO2 does not have "insert any table" ,"select any table" or the object level privilege.

image

To fix this, we can grant the "insert any table" ,"select any table" to PO2 or grant insert,select on PO3.t1 to PO2.

--- Grant System privilege
grant select any table to PO2;
grant insert any table to PO2;
-- OR Grant object level privilege
grant select on PO3.t1 to PO2;
grant insert on PO3.t1 to PO2;

Grant Privilege options:

  • System privilege can be grant with admin option: If the user being grant with admin option, the user can manage this privilege and can revoke and grant to other users.
  • Object privilege can be grant with grant option: If the user being grant with grant option, the user can grant this object privilege to other user.

image

image

DBA_SYS_PRIVS

Show all the user has the system privilege and whether they have admin permission.

image

DBA_TAB_PRIVS

Show the table permission.

col grantee format a10
col table_name format a10
col privilege format a10 
col grantor format a10 
col grantable format a10 
select grantee,table_name,privilege,grantor,grantable from dba_tab_privs where owner='PO3';
 
revoke select on PO3.t1 from PO2;
select grantee,table_name,privilege,grantor,grantable from dba_tab_privs where owner='PO3';
image

Revoke Privilege

revoke select any table from PO2;
revoke select on PO3.t1 from PO2;

 

CASCADE REVOKE

Below example, PO3 grant the t1 to PO2 with grant option. PO2 grant the PO3.t1 to PO.

connect PO3/oracle
show user
grant select on PO3.t1 to PO2 with grant option;
connect PO2/oracle
grant select on PO3.t1 to PO;
 
col grantee format a10
col table_name format a10
col privilege format a10 
col grantor format a10 
col grantable format a10 
select grantee,table_name,privilege,grantor,grantable from dba_tab_privs where owner='PO3';

image

revoke select on PO3.t1 from PO;

When we try to revoke PO’s permission, we would get the error “ORA-01927: cannot REVOKE privileges you did not grant

image

In this case, if we revoke the PO2’s privilege, all the privilege PO2 has grant to will be revoke too,

image

Reference

Oracle system privilege: http://www.adp-gmbh.ch/ora/admin/system_privileges.html

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates