Re: Role not found in ROLE_SYS_PRIVS and ROLE_TAB

From: Chip <chip_ling_at_yahoo.ca>
Date: 11 Oct 2006 14:44:26 -0700
Message-ID: <1160603066.682375.284360@k70g2000cwa.googlegroups.com>

Brian Peasland wrote:
> SELECT privilege FROM dba_sys_privs
> WHERE grantee='ROLE_NAME';
> SELECT owner,table_name,privilege FROM dba_tab_privs
> WHERE grantee='ROLE_NAME';
>

Let's start from the beginning.

I got a request to add a new user TOM to the database with the same privilege as MARY.

So I logon to the database as SYSDBA using SYS id, and then issue the following SQLs:

Step 1: Find out what privileges MARY has.

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'MARY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MARY                           CONNECT                        NO  YES
MARY                           APP1_ACCESS_ROLE               NO  YES

2 rows selected.

So far so good, I know user MARY has granted the 2 privileges above.

Step 2: What is APP1_ACCESS_ROLE?

SQL> SELECT NAME, TYPE# FROM USER$ WHERE NAME IN ('MARY','APP1_ACCESS_ROLE');

NAME                                TYPE#
------------------------------ ----------
APP1_ACCESS_ROLE                        0
MARY                                    1

With TYPE# = zero, APP1_ACCESS_ROLE is a role or a privilege object.

Step 3: What system privileges are granted to APP1_ACCESS_ROLE?

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'APP1_ACCESS_ROLE'; no rows selected

It is possible that a role does not have any system privilege.

Step 4: What table(s) privileges are granted to APP1_ACCESS_ROLE?

SQL> SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'APP1_ACCESS_ROLE'; no rows selected

This is not possible because I know MARY has select access to all tables under the schema APP1.

Step 5: Create a test role called GENERAL_USER_ROLE and create a new user SAM with the new role privileges.

SQL> CREATE ROLE GENERAL_USER_ROLE;
SQL> GRANT CREATE SESSION TO GENERAL_USER_ROLE;
SQL> GRANT SELECT ON APP1.TABLE1 TO GENERAL_USER_ROLE;
SQL> GRANT SELECT ON APP1.VIEW1   TO GENERAL_USER_ROLE;
SQL> GRANT EXECUTE ON APP1.PROC1 TO GENERAL_USER_ROLE;

SQL> CREATE USER SAM IDENTIFIED BY SAM DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP; SQL> GRANT GENERAL_USER_ROLE TO SAM; All SQLs finished with positive messages.

Step 6: Logon to the database with another machine using the user SAM.

I can open a session, can select the app1.table1, can select app1.view1, can execute tview.proc1.

Step 7: Repeat Step 1 but with user SAM.

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'SAM';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SAM                            GENERAL_USER_ROLE              NO  YES

Step 8: Repeat Step 2 but with 'SAM' and 'GENERAL_USER_ROLE'

SQL> SELECT NAME, TYPE# FROM USER$ WHERE NAME IN ('SAM','GENERAL_USER_ROLE');

NAME                                TYPE#
------------------------------ ----------
GENERAL_USER_ROLE                       0
SAM                                     1

Step 9: Repeat Step 3 with 'GENERAL_USER_ROLE'

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'GENERAL_USER_ROLE';

ROLE                           PRIVILEGE

ADM

------------------------------ ----------------------------------------
---
GENERAL_USER_ROLE              CREATE SESSION

Step 10: Repeat Step 4 with 'GENERAL_USER_ROLE'

SQL> SELECT OWNER, TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS WHERE ROLE = 'GENERAL_USER_ROLE';

OWNER                          TABLE_NAME                     PRIVILEGE
------------------------------ ------------------------------
---------------------------
APP1                           PROC1                          EXECUTE
APP1                           TABLE1                         SELECT
APP1                           VIEW1                          SELECT

With Brian Peasland's suggestion

Step 11:

SQL> SELECT OWNER, TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'APP1_ACCESS_ROLE'; Result is positive. I find all the privileges definition here.

Thanks very much Brian.

Rgds,
Chip Received on Wed Oct 11 2006 - 16:44:26 CDT

Re: Role not found in ROLE_SYS_PRIVS and ROLE_TAB_PRIVS