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

AiDBA宝典

发布于 2019-09-29 15:21:31

1.6K0

发布于 2019-09-29 15:21:31

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

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

♣

答案部分

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

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

CREATE USER LHRSYS IDENTIFIED BY LHRSYS;
GRANT  UPDATE (ENAME,SAL) ON  SCOTT.EMP  TO  LHRSYS;
GRANT  UPDATE  (ENAME)  ON  SCOTT.EMP  TO  LHRSYS;
GRANT SELECT ON SCOTT.EMP TO LHRSYS;
GRANT CONNECT TO LHRSYS;
GRANT CREATE JOB TO LHRSYS;

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

DROP TABLE T_TMP_USER_LHR;
CREATE TABLE   T_TMP_USER_LHR( ID NUMBER, USERNAME VARCHAR2(50), EXEC_SQL VARCHAR2(4000),CREATE_TYPE VARCHAR2(20) );  
DROP  SEQUENCE   S_T_TMP_USER_LHR;
CREATE SEQUENCE S_T_TMP_USER_LHR; 
BEGIN
  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;
END;
/
SELECT * FROM T_TMP_USER_LHR;

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

CREATE OR REPLACE VIEW VW_USER_PRIVS_LHR AS
SELECT 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
UNION ALL
SELECT 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
UNION ALL
SELECT 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
UNION ALL
SELECT 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;

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

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

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

SELECT DBMS_METADATA.GET_DDL('USER', 'LHR') DDL_SQL FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHR') FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHR') FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHR') FROM DUAL;

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

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

& 说明：

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

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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-04-18，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle

http

本文分享自 DB宝微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

oracle

http

登录后参与评论

0 条评论

热度

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

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐