Oracle常用用户权限视图

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:Oracle常用用户权限视图

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

DBA_SYS_PRIVS 用户所拥有的系统权限

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 16 13:26:09 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user xff identified by xifenfei;

User created.

SQL> grant connect to xff;

Grant succeeded.

SQL> select * from DBA_SYS_PRIVS where grantee='XFF';

no rows selected

SQL> REVOKE CONNECT FROM XFF;

Revoke succeeded.

SQL> grant create session to xff;

Grant succeeded.

SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO

SQL> grant select  on chf.t_1 to xff;

Grant succeeded.

SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO
--说明只能查询系统权限,不能查询角色,不能查询用户权限

DBA_SYS_PRIVS 用户所拥有的角色

SQL> grant resource to xff;

授权成功。

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='XFF';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
XFF                            RESOURCE                       NO  YES

ROLE_SYS_PRIVS 角色所拥有的系统权限

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='CONNECT';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';

no rows selected

SQL> SELECT * FROM SESSION_ROLES;

ROLE
------------------------------
CONNECT

SQL> CONN / AS SYSDBA
Connected.
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

8 rows selected.
--很多时候只能使用sysdba(或者具体特定权限)才能够查询角色有哪些系统权限

ROLE_ROLE_PRIVS: 角色被赋予的角色

SQL> SELECT *FROM ROLE_ROLE_PRIVS WHERE ROLE='DBA';

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            OLAP_DBA                       NO
DBA                            SCHEDULER_ADMIN                YES
DBA                            DELETE_CATALOG_ROLE            YES
DBA                            EXECUTE_CATALOG_ROLE           YES
DBA                            WM_ADMIN_ROLE                  NO
DBA                            EXP_FULL_DATABASE              NO
DBA                            SELECT_CATALOG_ROLE            YES
DBA                            JAVA_DEPLOY                    NO
DBA                            GATHER_SYSTEM_STATISTICS       NO
DBA                            JAVA_ADMIN                     NO
DBA                            XDBADMIN                       NO

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            IMP_FULL_DATABASE              NO
DBA                            XDBWEBSERVICES                 NO

13 rows selected.

SESSION_PRIVS 当前用户所拥有的全部权限

SQL> conn xff/xifenfei
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> conn / as sysdba
Connected.
SQL> revoke create session from xff;

Revoke succeeded.

SQL> grant connect to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
--只能查看系统权限或者角色中包含的系统权限,不能查看用户权限 

SESSION_ROLES: 当前用户被激活的角色

SQL> SELECT * from SESSION_ROLES;

no rows selected

SQL> show user;
USER is "SYS"
SQL> conn xff/xifenfei
Connected.
SQL> SELECT *FROM SESSION_ROLES;

ROLE
------------------------------
CONNECT
--sysdba查询无role选项,全部是由系统权限构成

查询某用户的所有系统权限

SQL> SELECT PRIVILEGE, ADMIN_OPTION
  2    FROM DBA_SYS_PRIVS
  3   WHERE GRANTEE = &USERNAME
  4  UNION
  5  --角色转换为权限
  6  SELECT PRIVILEGE, ADMIN_OPTION
  7    FROM ROLE_SYS_PRIVS
  8   WHERE ROLE IN
  9         (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
 10  UNION
 11  --角色的角色转为权限
 12  SELECT PRIVILEGE, ADMIN_OPTION
 13    FROM ROLE_SYS_PRIVS
 14   WHERE ROLE IN (SELECT GRANTED_ROLE
 15                    FROM ROLE_ROLE_PRIVS
 16                   WHERE ROLE IN (SELECT GRANTED_ROLE
 17                                    FROM DBA_ROLE_PRIVS
 18                                   WHERE GRANTEE = &USERNAME));
输入 username 的值:  'XFF'
原值    3:  WHERE GRANTEE = &USERNAME
新值    3:  WHERE GRANTEE = 'XFF'
输入 username 的值:  'XFF'
原值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
新值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'XFF')
输入 username 的值:  'XFF'
原值   18:                                  WHERE GRANTEE = &USERNAME))
新值   18:                                  WHERE GRANTEE = 'XFF'))

PRIVILEGE                                ADM
---------------------------------------- ---
CREATE CLUSTER                           NO
CREATE INDEXTYPE                         NO
CREATE OPERATOR                          NO
CREATE PROCEDURE                         NO
CREATE SEQUENCE                          NO
CREATE SESSION                           NO
CREATE TABLE                             NO
CREATE TRIGGER                           NO
CREATE TYPE                              NO
UNLIMITED TABLESPACE                     NO

已选择10行。

表相关权限视图

SELECT *FROM TABLE_PRIVILEGES;
SELECT * FROM dba_TAB_PRIVS;
SELECT * FROM ROLE_TAB_PRIVS;
此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

Oracle常用用户权限视图》有 2 条评论

  1. 惜分飞 说:

    connect 在不同版本中权限区别
    9i数据库中的connect角色

    SQL> SELECT * FROM V$VERSION;
    
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
    PL/SQL Release 9.2.0.8.0 - Production
    CORE    9.2.0.8.0       Production
    TNS for Linux: Version 9.2.0.8.0 - Production
    NLSRTL Version 9.2.0.8.0 - Production
    
    SQL> select * from role_sys_privs where role='CONNECT';
    
    ROLE                           PRIVILEGE                                ADM
    ------------------------------ ---------------------------------------- ---
    CONNECT                        CREATE VIEW                              NO
    CONNECT                        CREATE TABLE                             NO
    CONNECT                        ALTER SESSION                            NO
    CONNECT                        CREATE CLUSTER                           NO
    CONNECT                        CREATE SESSION                           NO
    CONNECT                        CREATE SYNONYM                           NO
    CONNECT                        CREATE SEQUENCE                          NO
    CONNECT                        CREATE DATABASE LINK                     NO
    

    11g数据库中connect角色权限(10g就开始改变)

    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for Linux: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    SQL> select * from role_sys_privs where role='CONNECT';
    
    ROLE                           PRIVILEGE                                ADM
    ------------------------------ ---------------------------------------- ---
    CONNECT                        CREATE SESSION                           NO
    
  2. 惜 分飞 说:

    查看某用户系统权限

    SELECT PRIVILEGE, ADMIN_OPTION
      FROM DBA_SYS_PRIVS
     WHERE GRANTEE = &USERNAME
    UNION
    --角色转换为权限
    SELECT PRIVILEGE, ADMIN_OPTION
      FROM ROLE_SYS_PRIVS
     WHERE ROLE IN
           (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
    UNION
    --角色的角色转为权限
    SELECT PRIVILEGE, ADMIN_OPTION
      FROM ROLE_SYS_PRIVS
     WHERE ROLE IN (SELECT GRANTED_ROLE
                      FROM ROLE_ROLE_PRIVS
                     WHERE ROLE IN (SELECT GRANTED_ROLE
                                      FROM DBA_ROLE_PRIVS
                                     WHERE GRANTEE = &USERNAME));