Oracle的用户权限分为哪几类？如何导出用户的权限？
Tags： DDLOracle列权限创建用户对象权限导出用户建表语句权限DDL语句用户用户DDL语句用户权限用户迁移目录权限系统权限表空间DDL语句角色权限
👉 本文共约1560个字，系统预计阅读时间或需6分钟。
权限是指执行特定类型SQL命令或访问其他用户对象的权利，包括系统权限（System Privilege）、对象权限（Object Privilege）、角色权限（Role Privilege）、列权限。
（一）系统权限
系统权限是指执行特定类型SQL命令的权利。它用于控制用户可以执行的一个或是一组数据库操作。通过查询系统表SYSTEM_PRIVILEGE_MAP可以获取所有系统权限，查询视图DBA_SYS_PRIVS可以获取每个用户拥有的系统权限。以下示例显示了SCOTT用户具有的系统权限：
1 2 3 4 | SYS@lhrdb> SELECT * FROM DBA_SYS_PRIVS D WHERE D.GRANTEE='SCOTT'; GRANTEE PRIVILEGE ADMIN_OPTION ----------- ----------------------- --------------- SCOTT UNLIMITED TABLESPACE NO |
一般情况，授予系统权限是由DBA完成的，如果用其他用户来授予系统权限，那么要求该用户必须具有GRANT ANY PRIVILEGE的系统权限。在授予系统权限时，可以带有WITH ADMIN OPTION选项，这样，被授予权限的用户或角色还可以将该系统权限授予其他的用户或角色。
（二）对象权限
对象权限指访问其它用户（SCHEMA）对象的权利，用户可以直接访问自己用户的对象，但是如果要访问别的用户的对象，那么必须具有该对象的相应权限。常用的对象权限有：ALTER、DELETE、SELECT、INSERT、UPDATE等。通过数据字段视图DBA_TAB_PRIVS可以查询用户或角色所具有的对象权限。下列示例列举出了所有的对象权限的种类：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SYS@lhrdb> SELECT DISTINCT PRIVILEGE FROM DBA_TAB_PRIVS; PRIVILEGE ---------------------------------------- EXECUTE FLASHBACK DEQUEUE ON COMMIT REFRESH ALTER DELETE UPDATE DEBUG QUERY REWRITE SELECT READ USE WRITE INSERT INDEX REFERENCES MERGE VIEW 17 rows selected. |
可以单独赋权，也可以多个权限用逗号隔开：
1 2 3 4 | GRANT SELECT ON EMP TO LHR; GRANT UPDATE ON EMP TO LHR; GRANT DELETE ON EMP TO LHR; GRANT UPDATE,DELETE,INSERT ON EMP TO LHR; |
也可以使用ALL来赋权：
1 | GRANT ALL ON EMP TO LHR; |
需要注意的是，系统权限和对象权限不能放在一个GRANT语句中进行授权，分开单独授权即可，否则会报错：
1 2 3 4 5 6 7 8 9 10 11 12 13 | SYS@oradg11g > GRANT CREATE TABLE, SELECT ON oe.Orders TO lhr; GRANT CREATE TABLE, SELECT ON oe.Orders TO lhr * ERROR at line 1: ORA-00990: missing or invalid privilege SYS@oradg11g > GRANT SELECT ON oe.Orders to lhr; Grant succeeded. SYS@oradg11g > GRANT CREATE TABLE to lhr; Grant succeeded. |
（三）列权限
可以基于列进行赋权，只不过只能赋予INSERT、REFERENCES和UPDATE的权限，举例如下：
1 | GRANT UPDATE (ENAME,SAL) ON EMP TO LHR; |
基于列的权限可以查询DBA_COL_PRIVS视图。
（四）角色权限
角色即用户权限的集合，可以对用户直接赋予某一个角色，这样，该用户就拥有了角色的所有权限。如果想查询角色所拥有的权限，那么可以通过视图DBA_SYS_PRIVS来查询；如果想查询某个用户拥有哪些角色，那么可以通过视图DBA_ROLE_PRIVS来查询。
角色权限需要注意默认角色（Default Role）的问题。一个用户一旦被赋予某个角色之后，其默认角色为YES，即角色权限处于激活状态，该角色拥有的权限是生效的；若默认角色为NO，则代表目标用户被赋予了某个角色，但是该角色拥有的权限并没有生效。可以使用如下SQL语句让角色生效：
1 2 3 | ALTER USER LHRTEST DEFAULT ROLE CONNECT;--设置LHRTEST用户的默认角色为CONNECT，让该用户其它角色失效 SET ROLE RESOURCE; --让当前用户的RESOURCE角色生效 ALTER USER LHRTEST DEFAULT ROLE ALL;--所有角色生效 |
（五）如何获取用户的权限
若要获取一个用户的角色、系统权限、对象权限以及列权限，则可以通过以上介绍的数据字典视图来获取也可以通过DBMS_METADATA.GET_GRANTED_DDL来获取。
若使用SYS用户创建了如下的用户LHRSYS并赋予相应的权限：
1 2 3 4 5 6 | 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的所有权限：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | 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列来创建用户并赋予相应的权限。
视图获取
另外，可以创建如下的视图：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | 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 where D.TABLE_NAME not like 'BIN$%' 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 where D.TABLE_NAME not like 'BIN$%'; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
这样就可以直接查询某个用户的权限了：
1 2 3 4 5 6 7 8 9 | 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和DBMS_METADATA.GET_GRANTED_DDL获取
通过系统包DBMS_METADATA.GET_DDL也可以获取用户的权限信息，如下所示：
1 2 3 4 5 6 7 8 9 | set long 9999 set pagesize 0 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
另外还可以通过exp或expdp来获取用户的权限，这里不再演示。
其它
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | ----查看用户的目录权限 column grantee format a10 column grantor format a10 column dir_name format a20 column dir_path format a50 column privilege format a10 break on dir_name select d.directory_name dir_name, d.directory_path dir_path, p.privilege, p.grantee, p.grantor from dba_tab_privs p, dba_directories d where p.table_name = d.directory_name and p.grantee = upper('&user') order by d.directory_name, p.privilege / 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; SELECT * FROM DBA_COL_PRIVS D WHERE D.GRANTEE='LHRSYS'; SELECT * FROM DBA_TAB_PRIVS D WHERE D.GRANTEE='LHRSYS'; SELECT * FROM DBA_SYS_PRIVS D WHERE D.GRANTEE='LHRSYS'; SELECT * FROM DBA_ROLE_PRIVS D WHERE D.GRANTEE='LHRSYS'; 1、给表加注释 COMMENT ON TABLE TABLENAME IS '用户表'; 2、查看表的COMMENT SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME='TABLENAME'; 3、给字段加注释 COMMENT ON COLUMN TABLENAME.COLNAME IS 'OOXX'; 4、查看字段的COMMENT SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME='TABLENAME'; --查看创建表SQL语句： SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM USER_TABLES U; --查看创建索引的SQL语句： SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME) FROM USER_INDEXES U; --查看创建主键的SQL语句： SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL; --查看创建外键的SQL语句： SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL; --查看创建视图（VIEW）的SQL语句： SELECT DBMS_METADATA.GET_DDL('VIEW', 'MY_TABLES','SCOTT') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('VIEW', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'VIEW'; SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME'); --查看创建存储过程（PROCEDURE）的SQL语句： SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'PROCEDURE'; --查看创建触发器（TRIGGER）的SQL语句： SELECT DBMS_METADATA.GET_DDL('TRIGGER', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'TRIGGER'; --查看创建函数（FUNCTION）的SQL语句： SELECT DBMS_METADATA.GET_DDL('FUNCTION', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'FUNCTION'; --查看创建包（PACKAGE）的SQL语句： SELECT DBMS_METADATA.GET_DDL('PACKAGE', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'PACKAGE'; --查看创建序列（SEQUENCE）的SQL语句： SELECT DBMS_METADATA.GET_DDL('SEQUENCE', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'SEQUENCE'; --查看创建同义词（SYNONYM）的SQL语句： SELECT DBMS_METADATA.GET_DDL('SYNONYM', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'SYNONYM'; --查看创建表空间（TABLESPACE）的SQL语句： SELECT DBMS_METADATA.GET_DDL('TABLESPACE', U.TABLESPACE_NAME) FROM USER_TABLESPACES U; --查看创建角色（ROLE）的SQL语句： SELECT DBMS_METADATA.GET_DDL('ROLE', U.ROLE) FROM DBA_ROLES U; --查看创建用户（USER）的SQL语句： SELECT DBMS_METADATA.GET_DDL('USER','SYS') FROM DUAL; ------------------------- 得到表空间DDL语句 SELECT (SELECT b.NAME FROM v$tablespace b WHERE b.TS# = a.TS#) ts_name, a.NAME datafilename, 'create tablespace '||(SELECT b.NAME FROM v$tablespace b WHERE b.TS# = a.TS#) || ' datafile ' || a.NAME ||' size ;' FROM v$datafile a; SELECT TABLESPACE_NAME, substr(create_ts, 1, instr(create_ts, 'EXTENT') - 1) || ';' FROM (SELECT a.TABLESPACE_NAME, replace(to_char(DBMS_METADATA.GET_DDL('TABLESPACE', a.tablespace_name)), chr(10), '') create_ts FROM DBA_TABLESPACES a) v where v.TABLESPACE_NAME not in ('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'TEMP', 'USERS', 'EXAMPLE'); ------------------------- 得到用户及其权限的DDL语句 SELECT DBMS_METADATA.GET_DDL('USER','LHRSYS') DDL_SQL FROM DUAL UNION ALL SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL UNION ALL SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL UNION ALL SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL; |
