ORACLE management-view users with DBA roles

ORACLE management-view users with DBA roles

1. view All users: select * from dba_users; select * from all_users; select * from user_users; 2. view system permissions of users or roles (system permissions assigned to users or roles): select * from dba_sys_privs; select * from user_sys_privs; SQL> select * from user_sys_privs; username privilege adm ---------------------------- permission --- zszq unlimited tablespace NO3. view the permissions of a role (only view roles owned by login users) SQL> select * from role_sys_privs; 4. view user objects Permission: 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 a user or role: select * from dba_role_privs; select * from user_role_privs; -- query the user 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. check which users have sysdba or sysoper system permissions (the corresponding permissions are required during query) select * from V $ PWFILE_USERS. For example, I want to view the roles owned by user wzsb: SQL> select * from dba_sys_privs where grantee = 'zszq '; grantee privilege adm privileges --- zszq unlimited tablespace no. View All permissions of a user and select privilege from dba_sys_privs where grantee = 'zszq' unionselect privilege from dba_sys_pri Vs 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 zookeeper --- zszq unlimited tablespace NO8. view the permissions that the RESOURCE has. 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 cluster noresource create operator noresource create indextype noresource create table no eight rows have been selected. SQL> select * from role_sys_privs t1 where t1.role = 'resource '; role privilege adm zookeeper --- resource create sequence noresource create trigger noresource create cluster noresource create procedure noresource create type noresource create operator noresource create table noresource create indextype no eight rows have been selected. 9. view scott user's default tablespace, temporary tablespace select username, default_tablespace, temporary_tablespace from dba_users where username = 'Scott '; 10. view scott's system permissions: 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 --------------------------------------------------------------- ------- --- Scott create view noscott unlimited tablespace NO11. VIEW the select grantee, owner, table_name, t. grantor, t. privilege, t. grantable, t. hierarchyfrom dba_tab_privs twhere t. grantee = 'Scott '; 12. view 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. defa Ult_role 2 from dba_role_privs t 3 where t. grantee = 'Scott '; GRANTEE GRANTED_ROLE adm def -------------------------------- ---------------------------- --- scott resource no yesscott connect no yessql> select * from user_role_privs t; USERNAME GRANTED_ROLE adm def OS _ ------------------------------ ---------------------------- --- ZSZQ CONNECT NO YES NOZSZQ DBA NO YES NOZSZQ EXP_FULL_DATA Base no yes nozszq IMP_FULL_DATABASE no yes nozszq resource no yes NO13. check the table space selected by scott. table_name, t. tablespace_namefrom dba_all_tables twhere t. owner = 'Scott '; 14. view the permissions of the current user, select t. privilege from session_privs t; SQL> select t. privilege from session_privs t; PRIVILEGE --------------------------------------- -CREATE SESSIONUNLIMITED TABLESPACECREATE TABLECREATE CLUSTERCREATE VIEWCREATE S Equencecreate procedurecreate triggercreate typecreate operatorcreate indextype has selected 11 rows. 14. view role permissions granted to a role (DBA) select * from role_role_privs t where t. role = 'dba '; view the permissions granted to the role (DBA). 15. select * from role_tab_privs t1 where t1.role = 'dba'