【DB笔试面试545】在Oracle中，如何获取用户的权限？

本文主要是介绍【DB笔试面试545】在Oracle中，如何获取用户的权限？，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

在Oracle中，如何获取用户的权限？

若要获取一个用户的角色、系统权限、对象权限以及列权限，则可以通过以上介绍的数据字典视图来获取也可以通过DBMS_METADATA.GET_GRANTED_DDL来获取。

若使用SYS用户创建了如下的用户LHRSYS并赋予相应的权限：

1CREATE USER LHRSYS IDENTIFIED BY LHRSYS;
2GRANT  UPDATE (ENAME,SAL) ON  SCOTT.EMP  TO  LHRSYS;
3GRANT  UPDATE  (ENAME)  ON  SCOTT.EMP  TO  LHRSYS;
4GRANT SELECT ON SCOTT.EMP TO LHRSYS;
5GRANT CONNECT TO LHRSYS;
6GRANT CREATE JOB TO LHRSYS; 

若通过数据字典来获取权限则可以通过如下的程序来获取LHRSYS的所有权限：

 1DROP TABLE T_TMP_USER_LHR;
 2CREATE TABLE   T_TMP_USER_LHR( ID NUMBER, USERNAME VARCHAR2(50), EXEC_SQL VARCHAR2(4000),CREATE_TYPE VARCHAR2(20) );  
 3DROP  SEQUENCE   S_T_TMP_USER_LHR;
 4CREATE SEQUENCE S_T_TMP_USER_LHR; 
 5BEGIN
 FOR CUR IN (SELECT D.USERNAME,
                    D.DEFAULT_TABLESPACE,
                    D.ACCOUNT_STATUS,
                    'create user ' || D.USERNAME || ' identified by ' ||
                    D.USERNAME || ' default tablespace ' ||
                    D.DEFAULT_TABLESPACE || '  TEMPORARY TABLESPACE  ' ||
                    D.TEMPORARY_TABLESPACE || ';' CREATE_USER,
                    REPLACE(TO_CHAR(DBMS_METADATA.GET_DDL('USER',D.USERNAME)),CHR(10),'') CREATE_USER1
               FROM DBA_USERS D
              WHERE D.USERNAME NOT IN  ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','DMSYS','DVSYS','EXFSYS','FLOWS_FILES','HR','IX','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','REMOTE_SCHEDULER_AGENT','SCOTT','SH','SI_INFORMATN_SCHEMA','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','XS$NULL')) LOOP
   --create user
   INSERT INTO T_TMP_USER_LHR
     (ID, USERNAME, EXEC_SQL, CREATE_TYPE)
   VALUES
     (S_T_TMP_USER_LHR.NEXTVAL, CUR.USERNAME, CUR.CREATE_USER, 'USER');
   ---system privilege 
   INSERT INTO T_TMP_USER_LHR
     (ID, USERNAME, EXEC_SQL, CREATE_TYPE)
     SELECT S_T_TMP_USER_LHR.NEXTVAL,
            CUR.USERNAME,
            CASE
              WHEN D.ADMIN_OPTION = 'YES' THEN
               'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE ||
               ' WITH GRANT OPTION ;'
              ELSE
               'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE || ';'
            END PRIV,
            'DBA_SYS_PRIVS'
       FROM DBA_SYS_PRIVS D
      WHERE D.GRANTEE = CUR.USERNAME;
   ---role privilege 
   INSERT INTO T_TMP_USER_LHR
     (ID, USERNAME, EXEC_SQL, CREATE_TYPE)
     SELECT S_T_TMP_USER_LHR.NEXTVAL,
            CUR.USERNAME,
            CASE
              WHEN D.ADMIN_OPTION = 'YES' THEN
               'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE ||
               ' WITH GRANT OPTION;'
              ELSE
               'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE || ';'
            END PRIV,
            'DBA_ROLE_PRIVS'
       FROM DBA_ROLE_PRIVS D
      WHERE D.GRANTEE = CUR.USERNAME;
   ---objects privilege 
   INSERT INTO T_TMP_USER_LHR
     (ID, USERNAME, EXEC_SQL, CREATE_TYPE)
     SELECT S_T_TMP_USER_LHR.NEXTVAL,
            CUR.USERNAME,
            CASE
              WHEN D.GRANTABLE = 'YES' THEN
               'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' ||
               D.TABLE_NAME || ' TO ' || D.GRANTEE ||
               '  WITH GRANT OPTION ;'
              ELSE
               'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' ||
               D.TABLE_NAME || ' TO ' || D.GRANTEE || ';'
            END PRIV,
            'DBA_TAB_PRIVS'
       FROM DBA_TAB_PRIVS D
      WHERE D.GRANTEE = CUR.USERNAME;
   ---column privilege 
   INSERT INTO T_TMP_USER_LHR
     (ID, USERNAME, EXEC_SQL, CREATE_TYPE)
     SELECT S_T_TMP_USER_LHR.NEXTVAL,
            CUR.USERNAME,
            CASE
              WHEN D.GRANTABLE = 'YES' THEN
               'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' ||
               D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE ||
               '  WITH GRANT OPTION ;'
              ELSE
               'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' ||
               D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ';'
            END PRIV,
            'DBA_COL_PRIVS'
       FROM DBA_COL_PRIVS D
      WHERE D.GRANTEE = CUR.USERNAME ;
 END LOOP;
 COMMIT;
92END;
93/
94SELECT * FROM T_TMP_USER_LHR;

可以直接运行EXEC_SQL列来创建用户并赋予相应的权限。另外，可以创建如下的视图：

 1CREATE OR REPLACE VIEW VW_USER_PRIVS_LHR AS
 2SELECT D.GRANTEE,
      CASE
        WHEN D.ADMIN_OPTION = 'YES' THEN
         'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE ||
         ' WITH GRANT OPTION ;'
        ELSE
         'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE || ';'
      END PRIV,
      'SYSTEM_GRANT' TYPE,
      'DBA_SYS_PRIVS' FROM_VIEW
 FROM DBA_SYS_PRIVS D
13UNION ALL
14SELECT D.GRANTEE,
      CASE
        WHEN D.ADMIN_OPTION = 'YES' THEN
         'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE ||
         ' WITH GRANT OPTION;'
        ELSE
         'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE || ';'
      END PRIV,
      'SYSTEM_GRANT' TYPE,
      'DBA_SYS_PRIVS' FROM_VIEW
 FROM DBA_ROLE_PRIVS D
25UNION ALL
26SELECT D.GRANTEE,
      CASE
        WHEN D.GRANTABLE = 'YES' THEN
         'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' ||
         D.TABLE_NAME || ' TO ' || D.GRANTEE || '  WITH GRANT OPTION ;'
        ELSE
         'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' ||
         D.TABLE_NAME || ' TO ' || D.GRANTEE || ';'
      END PRIV,
      'SYSTEM_GRANT' TYPE,
      'DBA_SYS_PRIVS' FROM_VIEW
 FROM DBA_TAB_PRIVS D
38UNION ALL
39SELECT D.GRANTEE,
      CASE
        WHEN D.GRANTABLE = 'YES' THEN
         'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' ||
         D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE ||
         '  WITH GRANT OPTION ;'
        ELSE
         'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' ||
         D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ';'
      END PRIV,
      'COL_GRANT' TYPE,
      'DBA_COL_PRIVS' FROM_VIEW
 FROM DBA_COL_PRIVS D;

这样就可以直接查询某个用户的权限了：

1SYS@lhrdb> set line 9999
2SYS@lhrdb> SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = 'LHRSYS';
3GRANTEE    PRIV                                          TYPE          FROM_VIEW
4---------- --------------------------------------------- ------------ -------------
5LHRSYS     GRANT CREATE JOB TO LHRSYS;                   SYSTEM_GRANT DBA_SYS_PRIVS
6LHRSYS     GRANT CONNECT TO LHRSYS;                      SYSTEM_GRANT DBA_SYS_PRIVS
7LHRSYS     GRANT SELECT ON SCOTT.EMP TO LHRSYS;          SYSTEM_GRANT DBA_SYS_PRIVS
8LHRSYS     GRANT UPDATE (ENAME) ON SCOTT.EMP TO LHRSYS;  COL_GRANT    DBA_COL_PRIVS
9LHRSYS     GRANT UPDATE (SAL) ON SCOTT.EMP TO LHRSYS;    COL_GRANT    DBA_COL_PRIVS

通过系统包DBMS_METADATA.GET_DDL也可以获取用户的权限信息，如下所示：

1SELECT DBMS_METADATA.GET_DDL('USER', 'LHR') DDL_SQL FROM DUAL
2UNION ALL
3SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHR') FROM DUAL
4UNION ALL
5SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHR') FROM DUAL
6UNION ALL
7SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHR') FROM DUAL;

将结果拷贝出来简单的用文本编辑工具编辑后即可运行。

另外还可以通过exp或expdp来获取用户的权限，这里不再演示。

& 说明：

有关权限的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140775/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

---------------优质麦课------------

 详细内容可以添加麦老师微信或QQ私聊。

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● QQ：646634621  QQ群：618766405

● 提供OCP、OCM和高可用部分最实用的技能培训

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

DBA宝典

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。

本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

这篇关于【DB笔试面试545】在Oracle中，如何获取用户的权限？的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！