Re: Role not found in ROLE_SYS_PRIVS and ROLE_TAB_PRIVS

Chip wrote:
> 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
> NO
>
>
> 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

I don't see any place where you answered Brian's question reqarding DBA_SYS_PRIVS. You keep focusing on roles, but haven't addressed if this user has any system or object privileges granted directly to the user rather than through a role. Received on Thu Oct 12 2006 - 07:45:46 CDT