view for look the permissions grant to a user

[maswien]

What's the view for me to check all the permissions granted to a user? THanks

 

[SantaMufasa]

...view...to check all the permissions granted to a user?

As you are probably aware, there are two major classes of "permissions": 1) Object privileges and 2) System privileges. Then, you can obtain those privileges in two ways: 1) Direct GRANT 2) Indirect GRANT through membership in a ROLE.


Here, then, are the different views that disclose both SYSTEM and OBJECT privileges and the user's membership in ROLEs and the SYSTEM and OBJECT privileges granted to ROLEs:

USER_SYS_PRIVS
USER_TAB_PRIVS
USER_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS

You can see a listing of all objects relating to privileges by issuing the query:

select object_name from all_objects where owner in ('SYS','SYSTEM','PUBLIC')
and object_name like '%PRIVS%'
order by 1;

Let us know if this information satisfies your request.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[maswien]

Thanks a lot Mufasa !


USER_SYS_PRIVS
USER_TAB_PRIVS
USER_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS

These tables should be very helpful to verify if a user has the proper privilege on objects and system.

I tried query USER_SYS_PRIVS using username but I can't get results. I want to know which sys privileges were granted to this user, it seems it's not the table I should query. Because I can see from the OEM that the user does have the system privileges granted.

 

[carp]

USER_SYS_PRIVS would give you the system privileges granted explicitly to the user you are current logged in as. If you want to see the system privileges explicitly granted to a different user, you would want to consult dba_sys_privs.

But bear in mind that to see all possible privileges, you must also check the roles granted to the user and the privileges granted to those roles.

 

[maswien]

Thanks carp,

How can I check roles granted to a user as well?

 

[carp]

Check under dba_role_privs for the grantee (user) and granted_role (role name).

 

[carp]

I think this should come pretty close to what you are after:

SELECT grantee, 'Explicit System Privilege', null object_name, privilege 
  FROM dba_sys_privs WHERE grantee = 'YOUR_USER_NAME_HERE'
UNION ALL
SELECT grantee, 'Explicit Object Privilege',table_name, privilege 
FROM dba_tab_privs WHERE grantee = 'YOUR_USER_NAME_HERE'
UNION ALL
SELECT rp.grantee, 'System Privilege From Role '||rp.granted_role, null, sp.privilege 
  FROM dba_role_privs rp, dba_sys_privs sp
 WHERE rp.granted_role = sp.grantee AND rp.grantee = 'YOUR_USER_NAME_HERE'
UNION ALL
 SELECT rp.grantee, 'Object Privilege From Role '||rp.granted_role, table_name, op.privilege 
  FROM dba_role_privs rp, dba_tab_privs op
 WHERE rp.granted_role = op.grantee AND rp.grantee = 'YOUR_USER_NAME_HERE';

 

[maswien]

Thanks a lot carp!