Oracle – Why Doesn’t It Show My Privileges?

oraclepermissionsusers

I have a user that is able to drop, alter, create tables and indexes. But when I list the privileges for my Oracle user it does not show any CREATE TABLE or other such privileges. How am I able to do these operations without the privileges or why doesn't Oracle show them?

Running following queries returns nothing:

SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM all_tab_privs_recd WHERE grantee = 'user_kshitiz';

Running SELECT * FROM USER_SYS_PRIVS returns:

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
user_kshitiz                   UNLIMITED TABLESPACE                     NO 
user_kshitiz                   CREATE SEQUENCE                          NO

Running SELECT * FROM USER_ROLE_PRIVS returns:

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
user_kshitiz                   CONNECT                        NO  YES NO 
user_kshitiz                   RESOURCE                       NO  YES NO 
user_kshitiz                   SCHEMA_1_RW_ROLE               NO  YES NO

Checking privileges on each of those roles returns nothing.

SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'CONNECT';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'RESOURCE';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'SCHEMA_1_RW_ROLE';

So where does the power come from?

Best Answer

As you can see you have RESOURCE role granted to the user user_kshitz. And you have to query the ROLE_SYS_PRIVS.

SQL> select * from role_sys_privs where role='RESOURCE';

ROLE                   PRIVILEGE                ADM
------------------------------ ---------------------------------------- ---
RESOURCE               CREATE TRIGGER               NO
RESOURCE               CREATE SEQUENCE              NO
RESOURCE               CREATE TYPE              NO
RESOURCE               CREATE PROCEDURE             NO
RESOURCE               CREATE CLUSTER               NO
RESOURCE               CREATE OPERATOR              NO
RESOURCE               CREATE INDEXTYPE             NO
RESOURCE               CREATE TABLE             NO

8 rows selected.

Also you can query the SESSION_PRIVS to find all the privileges granted to the user.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

9 rows selected.
Related Question