【DB笔试面试544】在Oracle中，用户的权限分为哪几类？
- 2019 年 10 月 10 日
- 筆記
♣
题目部分
在Oracle中，用户的权限分为哪几类？
♣
答案部分
权限是指执行特定类型SQL命令或访问其他用户对象的权利，包括系统权限（System Privilege）、对象权限（Object Privilege）、角色权限（Role Privilege）、列权限。
（一）系统权限
系统权限是指执行特定类型SQL命令的权利。它用于控制用户可以执行的一个或是一组数据库操作。通过查询系统表SYSTEM_PRIVILEGE_MAP可以获取所有系统权限，查询视图DBA_SYS_PRIVS可以获取每个用户拥有的系统权限。以下示例显示了SCOTT用户具有的系统权限：
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可以查询用户或角色所具有的对象权限。下列示例列举出了所有的对象权限的种类：
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.
可以单独赋权，也可以多个权限用逗号隔开：
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来赋权： GRANT ALL ON EMP TO LHR; 需要注意的是，系统权限和对象权限不能放在一个GRANT语句中进行授权，分开单独授权即可，否则会报错： 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的权限，举例如下：
GRANT UPDATE (ENAME,SAL) ON EMP TO LHR;
基于列的权限可以查询DBA_COL_PRIVS视图。
（四）角色权限
角色即用户权限的集合，可以对用户直接赋予某一个角色，这样，该用户就拥有了角色的所有权限。如果想查询角色所拥有的权限，那么可以通过视图DBA_SYS_PRIVS来查询；如果想查询某个用户拥有哪些角色，那么可以通过视图DBA_ROLE_PRIVS来查询。Oracle预定义角色请参考：【3.1.2 Oracle有哪些预定义角色？】。
角色权限需要注意默认角色（Default Role）的问题。一个用户一旦被赋予某个角色之后，其默认角色为YES，即角色权限处于激活状态，该角色拥有的权限是生效的；若默认角色为NO，则代表目标用户被赋予了某个角色，但是该角色拥有的权限并没有生效。可以使用如下SQL语句让角色生效：
ALTER USER LHRTEST DEFAULT ROLE CONNECT;--设置LHRTEST用户的默认角色为CONNECT，让该用户其它角色失效 SET ROLE RESOURCE; --让当前用户的RESOURCE角色生效 ALTER USER LHRTEST DEFAULT ROLE ALL;--所有角色生效
有关权限角色的更多内容可以参考：http://blog.itpub.net/26736162/viewspace-2140769/。
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
