【DB笔试面试545】在Oracle中，如何获取用户的权限？
- 2019 年 10 月 10 日
- 笔记
♣
题目部分
在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程序员面试笔试宝典》，作者：李华荣。
