♣
题目部分
在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程序员面试笔试宝典》,作者:李华荣。