Oracle Management-View users with DBA roles

1. View All Users: SELECT * from Dba_users;  SELECT * from All_users;  SELECT * from user_users;2. To view user or role system permissions (System permissions that are directly assigned to a user or role): SELECT * from Dba_sys_privs; SELECT * from User_sys_privs; Sql> select * from User_sys_privs; USERNAME PRIVILEGE ADM---------------------------------------------- ---------------------------zszq UNLIMITED tablespace NO3. View roles (view only roles owned by logged-in users)  ) contains permissions Sql>select * from role_sys_privs;4. View User Object permissions: SELECT * from Dba_tab_privs;  SELECT * from All_tab_privs;  SELECT * from user_tab_privs;5. View all roles: SELECT * from Dba_roles6. View the roles owned by the user or role: SELECT * from Dba_role_privs; SELECT * FROM user_role_privs;--queries a user with DBA authority sql> SELECT * from Dba_role_privs where granted_role= ' DBA '; GRANTEE granted_role ADM DEF---------------------------------------------------- --------------SYS DBA yes YesSYSTEM dba YES Yeszszq dba NO YESKSWORK7. See which users have SYSDBA or Sysoper system permissions (requires appropriate permissions when querying) SELECT * from V$pwfile_users for example I want to see the user Wzsb's own role: Sql> Selec T * from Dba_sys_privs where grantee= ' zszq '; GRANTEE PRIVILEGE ADM----------------------------------------------  ---------------------------zszq UNLIMITED tablespace no View all permissions and roles for a user select             Privilege from Dba_sys_privs where grantee = ' zszq ' unionselect privilege from Dba_sys_privs where grantee in (select Granted_role from dba_role_privs where grantee = ' zszq '); Sql> SELECT * from Dba_sys_privs where grantee= ' zszq '; GRANTEE PRIVILEGE ADM----------------------------------------------                     ---------------------------zszq UNLIMITED tablespaceNO8, view RESOURCE with those permissions sql> SELECT * from Dba_sys_privs WHERE grantee= ' RESOURCE '; GRANTEE PRIVILEGE ADM----------------------------------------------                       ---------------------------RESOURCE CREATE TRIGGER noresource                              Create SEQUENCE Noresource Create TYPE Noresource Create PROCEDURE Noresource Create Clus                       TER Noresource CREATE OPERATOR Noresource                             Create Indextype Noresource Create TABLE No has selected 8 rows. Sql> SELECT * from Role_sys_privs t1 where t1.role = ' RESOURCE '; ROLE PRIVILEGE ADM-------------------------------------------------------------------------RESOURCE CREATE SEQUENCE NOR                           Esource Create TRIGGER Noresource Create CLUSTER                       Noresource CREATE PROCEDURE Noresource                          Create TYPE Noresource Create OPERATOR Noresource Create TABLE Noresource Create Indexty PE No has selected 8 rows. 9. View the default tablespace for Scott users, temporal tablespace select username, Default_tablespace, temporary_tablespace from dba_users where username = ' Scott '; 10. View system permissions for Scott users select Username,privilege,admin_option from user_sys_privs where username = ' SCOTT '; Sql> Select Username,privilege,admin_option 2 from User_sys_privs 3 where username = ' SCOTT '; USERNAME PRivilege ADM-------------------------------------------------------------------------SCOT                     T CREATE VIEW Noscott UNLIMITED tablespace NO11. Viewing object permissions given to Scott users select Grantee,owner, table_name, T.grantor, T.privilege, t.grantable, T.hierarch Yfrom dba_tab_privs twhere t.grantee = ' SCOTT '; 12. View the role permissions granted to Scott Select T.grantee, T.granted_role, T.admin_option, t.default_role from Dba_role_privs t where T.grantee = ' SCOTT '; Sql> Select T.grantee, T.granted_role, T.admin_option, T.default_role 2 from Dba_role_privs T 3 where t.grantee = ' SCOTT '; GRANTEE granted_role ADM DEF---------------------------------------------------- --------------SCOTT RESOURCE NO yesscott Connec T yessql> select * from User_role_privs t; UsernaME granted_role ADM DEF os_------------------------------------------------------ ---------------zszq CONNECT NO YES nozszq D                           BA No Yes nozszq exp_full_database No yes nozszq                       Imp_full_database NO YES nozszq RESOURCE NO YES NO13. See which tablespaces the SCOTT user uses select T.table_name, T.tablespace_namefrom dba_all_tables twhere t.owner = ' SCOTT '; 14. View Current user-owned permissions select T.privilege from Session_privs t; Sql> Select T.privilege from Session_privs t; PRIVILEGE----------------------------------------CREATE sessionunlimited tablespacecreate tablecreate Clustercreate viewcreate sequencecreate procedurecreate triggercreate typecreate OPERATORCREATE indextype 11 rows have been selected. 14. View role (DBA) Given role permissions select * from Role_role_privs t where T.role = ' DBA '; View role (DBA) Assigned object permissions 15.select * from Role_tab_privs t1 where t1.role = ' DBA ' 

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Management-View users with DBA roles