ORACLE管理-查看擁有DBA角色的使用者，oracledba

ORACLE管理-查看擁有DBA角色的使用者，oracledba

1.查看所有使用者：  select * from dba_users;  select * from all_users;  select * from user_users;2.查看使用者或角色系統許可權(直接賦值給使用者或角色的系統許可權)：select * from dba_sys_privs;  select * from user_sys_privs;SQL> select * from user_sys_privs;USERNAME                       PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---ZSZQ                           UNLIMITED TABLESPACE                     NO3.查看角色(只能查看登陸使用者擁有的角色)所包含的許可權sql>select * from role_sys_privs;4.查看使用者物件許可權：  select * from dba_tab_privs;  select * from all_tab_privs;  select * from user_tab_privs;5.查看所有角色：  select * from dba_roles6.查看使用者或角色所擁有的角色：  select * from dba_role_privs;  select * from user_role_privs;--查詢擁有DBA許可權的使用者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.查看哪些使用者有sysdba或sysoper系統許可權(查詢時需要相應許可權)select * from V$PWFILE_USERS比如我要查看使用者 wzsb的擁有的角色： SQL> select * from dba_sys_privs where grantee='ZSZQ';GRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---ZSZQ                           UNLIMITED TABLESPACE                     NO查看一個使用者所有的許可權及角色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 TABLESPACE                     NO8、查看RESOURCE具有那些許可權 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已選擇8行。SQL> select * from role_sys_privs t1 where t1.role = 'RESOURCE';ROLE                           PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---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已選擇8行。9.查看scott使用者的預設資料表空間、暫存資料表空間select username, default_tablespace, temporary_tablespace  from dba_users where username = 'SCOTT';10.查看scott使用者的系統許可權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.查看賦予scott使用者的對象許可權select grantee,owner, table_name, t.grantor, t.privilege, t.grantable, t.hierarchyfrom dba_tab_privs twhere t.grantee = 'SCOTT'; 12.查看授予了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                          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_DATABASE              NO  YES NOZSZQ                           IMP_FULL_DATABASE              NO  YES NOZSZQ                           RESOURCE                       NO  YES NO13.查看scott使用者使用了哪些資料表空間select t.table_name, t.tablespace_namefrom  dba_all_tables twhere t.owner = 'SCOTT' ;14.查看目前使用者擁有的許可權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行。14.查看角色(DBA)被賦予的角色許可權select * from role_role_privs t where t.role = 'DBA';查看角色(DBA)被賦予的對象許可權15.select * from role_tab_privs t1  where t1.role = 'DBA'

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。