oracle查询用户权限和角色_Oracle数据库用户管理之角色管理（二）--角色其他操作...

3.3 其它操作

3.3.1 设置角色生效或失效

1．使角色生效

使角色生效是指使当前会话用户所具有的角色有效。当生效了某个角色后，当前用户会

话将可以执行该角色所具有的所有权限操作。不过，要注意以下两点：

• • 角色生效或失效只对当前会话有效。
• • 使新角色生效会自动禁止其他所有角色。

当设置了用户的默认角色之后，以用户身份登录时会自动激活默认角色。为了执行特定

角色的操作，可以使该角色生效，从而使其它角色临时被收回。生效角色是使用 SET ROLE

命令来完成的，有具有角色的用户自己设置。

如果在建立角色时未指定角色验证方式，那么在生效角色时不需要进行口令验证。

【实例 2-1】使角色 oe_clerk 生效，但不验证的角色

1)以 devp 用户登录 SOL>CONNECT devp/development 已连接。 2)设置角色生效 SOL>SET ROLE oe_clerk; 角色集  当生效了 oe_clerk 角色之后，就可以执行该角色所具有的权限操作了。但因为生效新角色会自动禁止其他所有角色，所以此时只能执行 oe_clerk 角色的权限操作，而不能执行任何  其他角色的权限操作。如下所示：  SOL>CREATE TABLE Tl(acol int);  表已创建。  SQL>delete from scott.salgrade where grade=1;  ERROR 位于第 1 行:  ORA-00942: 表或视图不存在  SET ROLE 是 SQL*Plus 命令，如果要在应用程序生效或失效角色，那么必须使用包  DBMS_SESSION。例如：  SOL>CONNECT devp/development  已连接。  SOL>EXECUTE DBMS_SESSION.SET_ROLE('oe_clerk'); PL/SQL 过程已成功完成。 

如果在建立角色时指定了数据库验证方式，那么在生效角色时必须提供口令。

【实例 2-2】使角色 hr_clerk 生效，验证的角色

1)以 aaron 用户登录 SOL>CONNECT aaron/soccer 已连接。 2)设置角色生效 SOL>SET ROLE hr_clerk IDENTIFIED BY bonus; 角色集 

2．使角色失效

使角色失效是指使当前会话用户所具有的所有角色全部无效。当设置了角色失效之后，

用户将不能执行其所有角色所具有的权限操作。

【实例 2-3】使角色失效

1)以 aaron 用户登录SOL>CONNECT aaron/soccer 已连接。 2)设置角色失效 SOL>SET ROLE NONE; 角色集 

3.3.2 修改角色

修改角色是使用命令 ALTER ROLE 来完成的，修改角色包括修改角色验证方式以及角

色口令两个方面。一般情况下修改角色是由 DBA 来完成的，但如果要以其他用户身份修改

角色信息，那么要求该用户必须具有 ALTER ANY ROLE 系统权限或角色上的 WITH ADMIN

OPTION 选项。

1．修改角色验证方式

在建立角色 oe_clerk 时，未采用任何验证方式，即设置该角色生效时，不需要验证。为

了限制用户对该角色的使用，将其修改为数据库验证方式。

【实例 2-4】修改角色 oe_clerk 为数据库验证方式。

1)以管理员身份登录 SOL>CONNECT / AS SYSDBA 已连接。 2)修改角色 SOL>ALTER ROLE oe_clerk IDENTIFIED BY order; 角色已丢弃。 

【实例 2-5】把角色 hr_manager 修改为不需验证的角色。

1)以管理员身份登录 SOL>CONNECT / AS SYSDBA 已连接。 2)修改角色 SOL>ALTER ROLE hr_manager NOT IDENTIFIED; 角色已丢弃。 

2．修改角色口令

在采用数据库验证方式建立了角色之后，出于安全考虑应该定期修改角色口令。

【实例 2-6】修改角色 oe_clerk 口令。

1)以管理员身份登录

SOL>CONNECT / AS SYSDBA 已连接。 2)修改角色 SOL>ALTER ROLE oe_clerk IDENTIFIED BY order_comm; 角色已丢弃。 

3.3.3 回收角色

角色分配给用户后，用户就拥有了角色的权限，当角色的权限要从用户中收回时，可以

回收用户的角色，就如同前面介绍的回收用户权限一样，也是采用 REVOKE 命令。一般情

况下回收角色是由 DBA 来完成的，但如果要以其他用户身份回收角色，那么要求该用户必

须具有 GRANT ANY ROLE 权限或角色上的 WITH ADMIN OPTION 选项。

【实例2-7】从用户 devp 中回收角色 oe_clerk。

1)以管理员身份登录 SOL>CONNECT / AS SYSDBA 已连接。 2)回收角色 SOL>REVOKE oe_clerk FROM devp; 撤销成功。 

3.3.4 用控制台回收角色

3.3.5 删除角色

当角色不需要时，可以进行删除。删除角色是使用 DROP ROLE 命令由 DBA 来完成的，

如果要以其他用户身份删除角色，则要求该用户必须具有 DROP ANY ROLE 系统权限或者

角色上的 WITH ADMIN OPTION。

【实例2-9】删除角色 admin_role。

1)以管理员身份登录SOL>CONNECT / AS SYSDBA 已连接。 2)删除角色 SOL>DROP ROLE admin_role; 角色已丢弃 

如果角色已经被分配给了用户，那么当删除角色时 Oracle 会自动收回用户所具有的角色。

3.3.6 查询角色信息

Oracle 提供了一些数据字典视图供查询角色的信息，下面介绍几个常用的数据字典视图，以获得角色的信息。

1．查询所有的角色

数据字典视图 DBA_ROLES 中包含了数据库中所有的角色，查询它可以了解数据库中

到底定义了哪些角色，它们采用了什么样的验证方式？

【实例 2-10】查询数据库中所有的角色。

1)以管理员身份登录 SOL>CONNECT / AS SYSDBA 已连接。 2)查询 SOL>SELECT role, password_required FROM dba_roles; ROLE PASSWORD ------------------------------ -------- CONNECT NO RESOURCE NO DBA NO SELECT_CATALOG_ROLE NO EXECUTE_CATALOG_ROLE NO DELETE_CATALOG_ROLE NO EXP_FULL_DATABASE NO IMP_FULL_DATABASE NO RECOVERY_CATALOG_OWNER NO GATHER_SYSTEM_STATISTICS NO LOGSTDBY_ADMINISTRATOR NO …… 

列说明如下：

• ROLE：角色名。
• PASSWORD_REQUIRED：角色的验证方式。

2．显示角色所具有的系统权限

当建立角色时，角色没有任何权限。为了使得角色可以执行特定 SQL 命令，必须为其

授予系统权限，并且在为角色授予系统权限时可以带有 WITH ADMIN OPTION 选项。那么

如何确定角色具有哪些系统权限呢?通过查询数据字典视图 ROLE_SYS_PRIVS，可以显示角

色所具有的系统权限。

【实例 2-11】查询角色 oe_clerk 所具有的系统权限。

1)以管理员身份登录 SOL>CONNECT / AS SYSDBA 已连接。 2)查询 SOL>SELECT * FROM ROLE_SYS_PRIVS WHERE role='OE_CLERK'; ROLE PRIVILEGE ADM ------------------------------------------------------- OE_CLERK CREATE VIEW YES OE_CLERK CREATE SESSION YES OE_CLERK CREATE TABLE YES 

当执行了以上 SELECT 语句之后，会显示角色 oe_clerk 所具有的系统权限以及 WITH

• ADMIN OPTION 选项，列说明如下：
• ROLE：角色名。
• PRIVILEGE：系统权限。
• ADMIN_OPTION：转授系统权限选项。YES 表示可以转授系统权限，而 NO 则表示不能转授系统权限。

3．角色所具有的对象权限

当建立角色时，角色没有任何权限。为了使得角色可以访问特定模式对象，必须为其授

予对象权限。要注意，在为角色授予对象权限时不能带有 WITH GRANT OPTION 选项。如

何确定角色具有哪些对象权限呢?通过查询数据字典 ROLE_TAB_PRIVS，可以显示角色所

具有的对象权限。

【实例 2-12】查询角色 oe_clerk 所具有的对象权限。

1)以管理员身份登录 SOL>CONNECT / AS SYSDBA 已连接。 2)查询SOL>SELECT owner, table_name, privilege, grantable FROM ROLE_TAB_PRIVS WHERE role= 'HR_MANAGER '; OWNER TABLE_NAME PRIVILEGE GRA ----------------------------------------------- SCOTT SALGRADE DELETE NO 

当执行了以上 SELECT 语句之后，会返回角色 hr_manager 所具有的对象权限，其中列

说明如下：

• OWNER：对象所有者，即模式名。
• TABLE_NAME：对象名。
• PRIVILEGE：对象权限。
• GRANTABLE：转授对象权限选项。因为在为角色授予对象权限时不能带有 WITH
• GRANT OPTION 选项，所以该列的值永远为“NO”。

4．显示用户的默认角色

当以用户身份连接到数据库时，Oracle 会自动激活默认角色。那么如何确定用户具有哪

些默认角色呢?通过查询数据字典 dba_role_privs，可以显示用户所具有的角色以及默认角色

等信息。

【实例 2-13】查询用户 devp 的默认角色。

1)以管理员身份登录 SOL>CONNECT / AS SYSDBA 已连接。 2)查询 SOL>SELECT granted_role FROM dba_role_privs WHERE grantee=’DEVP’ AND default_role=’YES’; GRANTED_ ROLE ---------------------------- OE_CLERK 

当执行了上述查询语句之后，会显示用户 devp 的默认角色。其中列说明如下：

• GRANTED_ROLE：被授予的角色。
• GRANTEE：角色所有者，即用户。
• DEFAULT_ROLE：YES 表示默认角色，NO 表示不是默认角色。

5．显示当前用户所具有的所有角色

通过查询数据字典视图 user_role_privs 可以显示当前用户所具有的角色。

【实例2-14】查询当前用户 devp 所具有的角色。

1)以 devp 用户登录 SOL>CONNECT devp/development 已连接。 2)查询 SOL>SELECT username, granted_role FROM user_role_privs WHERE username= 'DEVP '; USERNAME GRANTED_ROLE --------------------------------------------- DEVP OE_CLERK DEVP HR_MANAGER 

6．显示当前会话生效的角色

尽管用户 devp 具有多个角色，但以该用户身份登录之后，只能使用被生效角色所具有

的权限，而不能执行未生效角色所具有的权限操作。那么如何确定当前会话已生效的角色呢?

通过查询数据字典 session_roles，可以显示当前会话所生效的角色，

【实例 2-15】查询当前会话所生效的角色。

1)以 devp 用户登录 SOL>CONNECT devp/development 已连接。 2)设置角色生效 SOL>SET ROLE oe_clerk; 角色集 3)查询生效的角色 SOL>SELECT * FROM session_roles; ROLE ------------ OE_CLERK 

保护角色

默认情况下通常会启用角色，这意味着如果角色已授予用户，用户就可以行使指定给角色的权限。此时用户可以：

• 使角色成为非默认角色。将角色授予用户后，取消选中“DEFAULT(默认)”复选框。现在，用户必须先显式启用角色，才可以行使角色的权限。

• 要求角色有附加验证。默认情况下角色的验证为“无”，但可以要求角色有附加验证，之后才可以设置角色。

• 只通过成功地执行 PL/SQL 过程才支持创建安全的应用程序角色。通过 PL/SQL 过程可以检查用户运行程序的用户网络地址、日期时间或正确保护权限组所需的其它元素。

3.4 小结

角色是一组相关权限的命名集合，使用角色最主要的目的是简化权限管理。Oracle 提供

的一些预定义角色，常用的是 CONNECT 和 RESOURCE。用户也可以创建新的角色，为了

确保角色的安全性，角色也可以进行验证。角色创建后，就可以分配给用户了。将角色分配

给用户时，用户将具有角色的所有系统权限和对象权限。在分配角色时如果使用了 WITH

ADMIN OPTION 选项，用户有权将这个角色再授予其它用户。每个用户可拥有一些默认角

色，默认角色是在用户登录时自动生效的角色。

---

写在最后的话

感谢各位的支持与阅读，后续会继续推送相关知识和交流，欢迎交流、转发和关注，感谢