【DB笔试面试545】在Oracle中，如何获取用户的权限？
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【DB笔试面试545】在Oracle中，如何获取用户的权限？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8633字，纯文字阅读大概需要13分钟。
内容图文
在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
6??FOR?CUR?IN?(SELECT?D.USERNAME,
7?????????????????????D.DEFAULT_TABLESPACE,
8?????????????????????D.ACCOUNT_STATUS,
9?????????????????????'create?user?'?||?D.USERNAME?||?'?identified?by?'?||
10?????????????????????D.USERNAME?||?'?default?tablespace?'?||
11?????????????????????D.DEFAULT_TABLESPACE?||?'??TEMPORARY?TABLESPACE??'?||
12?????????????????????D.TEMPORARY_TABLESPACE?||?';'?CREATE_USER,
13?????????????????????REPLACE(TO_CHAR(DBMS_METADATA.GET_DDL('USER',D.USERNAME)),CHR(10),'')?CREATE_USER1
14????????????????FROM?DBA_USERS?D
15???????????????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
16
17????--create?user
18????INSERT?INTO?T_TMP_USER_LHR
19??????(ID,?USERNAME,?EXEC_SQL,?CREATE_TYPE)
20????VALUES
21??????(S_T_TMP_USER_LHR.NEXTVAL,?CUR.USERNAME,?CUR.CREATE_USER,?'USER');
22
23????---system?privilege?
24????INSERT?INTO?T_TMP_USER_LHR
25??????(ID,?USERNAME,?EXEC_SQL,?CREATE_TYPE)
26??????SELECT?S_T_TMP_USER_LHR.NEXTVAL,
27?????????????CUR.USERNAME,
28?????????????CASE
29???????????????WHEN?D.ADMIN_OPTION?=?'YES'?THEN
30????????????????'GRANT?'?||?D.PRIVILEGE?||?'?TO?'?||?D.GRANTEE?||
31????????????????'?WITH?GRANT?OPTION?;'
32???????????????ELSE
33????????????????'GRANT?'?||?D.PRIVILEGE?||?'?TO?'?||?D.GRANTEE?||?';'
34?????????????END?PRIV,
35?????????????'DBA_SYS_PRIVS'
36????????FROM?DBA_SYS_PRIVS?D
37???????WHERE?D.GRANTEE?=?CUR.USERNAME;
38
39????---role?privilege?
40????INSERT?INTO?T_TMP_USER_LHR
41??????(ID,?USERNAME,?EXEC_SQL,?CREATE_TYPE)
42??????SELECT?S_T_TMP_USER_LHR.NEXTVAL,
43?????????????CUR.USERNAME,
44?????????????CASE
45???????????????WHEN?D.ADMIN_OPTION?=?'YES'?THEN
46????????????????'GRANT?'?||?D.GRANTED_ROLE?||?'?TO?'?||?D.GRANTEE?||
47????????????????'?WITH?GRANT?OPTION;'
48???????????????ELSE
49????????????????'GRANT?'?||?D.GRANTED_ROLE?||?'?TO?'?||?D.GRANTEE?||?';'
50?????????????END?PRIV,
51?????????????'DBA_ROLE_PRIVS'
52????????FROM?DBA_ROLE_PRIVS?D
53???????WHERE?D.GRANTEE?=?CUR.USERNAME;
54
55????---objects?privilege?
56????INSERT?INTO?T_TMP_USER_LHR
57??????(ID,?USERNAME,?EXEC_SQL,?CREATE_TYPE)
58??????SELECT?S_T_TMP_USER_LHR.NEXTVAL,
59?????????????CUR.USERNAME,
60?????????????CASE
61???????????????WHEN?D.GRANTABLE?=?'YES'?THEN
62????????????????'GRANT?'?||?D.PRIVILEGE?||?'?ON?'?||?D.OWNER?||?'.'?||
63????????????????D.TABLE_NAME?||?'?TO?'?||?D.GRANTEE?||
64????????????????'??WITH?GRANT?OPTION?;'
65???????????????ELSE
66????????????????'GRANT?'?||?D.PRIVILEGE?||?'?ON?'?||?D.OWNER?||?'.'?||
67????????????????D.TABLE_NAME?||?'?TO?'?||?D.GRANTEE?||?';'
68?????????????END?PRIV,
69?????????????'DBA_TAB_PRIVS'
70????????FROM?DBA_TAB_PRIVS?D
71???????WHERE?D.GRANTEE?=?CUR.USERNAME;
72
73????---column?privilege?
74????INSERT?INTO?T_TMP_USER_LHR
75??????(ID,?USERNAME,?EXEC_SQL,?CREATE_TYPE)
76??????SELECT?S_T_TMP_USER_LHR.NEXTVAL,
77?????????????CUR.USERNAME,
78?????????????CASE
79???????????????WHEN?D.GRANTABLE?=?'YES'?THEN
80????????????????'GRANT?'?||?D.PRIVILEGE?||?'?('?||?D.COLUMN_NAME?||?')?ON?'?||
81????????????????D.OWNER?||?'.'?||?D.TABLE_NAME?||?'?TO?'?||?D.GRANTEE?||
82????????????????'??WITH?GRANT?OPTION?;'
83???????????????ELSE
84????????????????'GRANT?'?||?D.PRIVILEGE?||?'?('?||?D.COLUMN_NAME?||?')?ON?'?||
85????????????????D.OWNER?||?'.'?||?D.TABLE_NAME?||?'?TO?'?||?D.GRANTEE?||?';'
86?????????????END?PRIV,
87?????????????'DBA_COL_PRIVS'
88????????FROM?DBA_COL_PRIVS?D
89???????WHERE?D.GRANTEE?=?CUR.USERNAME?;
90??END?LOOP;
91??COMMIT;
92END;
93/
94SELECT?*?FROM?T_TMP_USER_LHR;
可以直接运行EXEC_SQL列来创建用户并赋予相应的权限。另外，可以创建如下的视图：
1CREATE?OR?REPLACE?VIEW?VW_USER_PRIVS_LHR?AS
2SELECT?D.GRANTEE,
3???????CASE
4?????????WHEN?D.ADMIN_OPTION?=?'YES'?THEN
5??????????'GRANT?'?||?D.PRIVILEGE?||?'?TO?'?||?D.GRANTEE?||
6??????????'?WITH?GRANT?OPTION?;'
7?????????ELSE
8??????????'GRANT?'?||?D.PRIVILEGE?||?'?TO?'?||?D.GRANTEE?||?';'
9???????END?PRIV,
10???????'SYSTEM_GRANT'?TYPE,
11???????'DBA_SYS_PRIVS'?FROM_VIEW
12??FROM?DBA_SYS_PRIVS?D
13UNION?ALL
14SELECT?D.GRANTEE,
15???????CASE
16?????????WHEN?D.ADMIN_OPTION?=?'YES'?THEN
17??????????'GRANT?'?||?D.GRANTED_ROLE?||?'?TO?'?||?D.GRANTEE?||
18??????????'?WITH?GRANT?OPTION;'
19?????????ELSE
20??????????'GRANT?'?||?D.GRANTED_ROLE?||?'?TO?'?||?D.GRANTEE?||?';'
21???????END?PRIV,
22???????'SYSTEM_GRANT'?TYPE,
23???????'DBA_SYS_PRIVS'?FROM_VIEW
24??FROM?DBA_ROLE_PRIVS?D
25UNION?ALL
26SELECT?D.GRANTEE,
27???????CASE
28?????????WHEN?D.GRANTABLE?=?'YES'?THEN
29??????????'GRANT?'?||?D.PRIVILEGE?||?'?ON?'?||?D.OWNER?||?'.'?||
30??????????D.TABLE_NAME?||?'?TO?'?||?D.GRANTEE?||?'??WITH?GRANT?OPTION?;'
31?????????ELSE
32??????????'GRANT?'?||?D.PRIVILEGE?||?'?ON?'?||?D.OWNER?||?'.'?||
33??????????D.TABLE_NAME?||?'?TO?'?||?D.GRANTEE?||?';'
34???????END?PRIV,
35???????'SYSTEM_GRANT'?TYPE,
36???????'DBA_SYS_PRIVS'?FROM_VIEW
37??FROM?DBA_TAB_PRIVS?D
38UNION?ALL
39SELECT?D.GRANTEE,
40???????CASE
41?????????WHEN?D.GRANTABLE?=?'YES'?THEN
42??????????'GRANT?'?||?D.PRIVILEGE?||?'?('?||?D.COLUMN_NAME?||?')?ON?'?||
43??????????D.OWNER?||?'.'?||?D.TABLE_NAME?||?'?TO?'?||?D.GRANTEE?||
44??????????'??WITH?GRANT?OPTION?;'
45?????????ELSE
46??????????'GRANT?'?||?D.PRIVILEGE?||?'?('?||?D.COLUMN_NAME?||?')?ON?'?||
47??????????D.OWNER?||?'.'?||?D.TABLE_NAME?||?'?TO?'?||?D.GRANTEE?||?';'
48???????END?PRIV,
49???????'COL_GRANT'?TYPE,
50???????'DBA_COL_PRIVS'?FROM_VIEW
51??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和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧 本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
内容总结
以上是互联网集市为您收集整理的【DB笔试面试545】在Oracle中，如何获取用户的权限？全部内容，希望文章能够帮你解决【DB笔试面试545】在Oracle中，如何获取用户的权限？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】