— 查看预定义角色
SELECT role
FROM dba_roles
WHERE role IN (‘CONNECT’, ‘RESOURCE’, ‘DBA’, ‘SELECT_CATALOG_ROLE’, ‘EXECUTE_CATALOG_ROLE’, ‘DELETE_CATALOG_ROLE’);– 查看预定义角色的权限
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘CONNECT’
ORDER BY privilege;SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘RESOURCE’
ORDER BY privilege;– 授予预定义角色给用户
GRANT CONNECT, RESOURCE TO app_user;GRANT SELECT_CATALOG_ROLE TO report_user;GRANT DBA TO admin_user;– 从用户回收预定义角色
REVOKE DBA FROM admin_user;– 查看用户的预定义角色
SELECT grantee, granted_role
FROM dba_role_privs
WHERE grantee = ‘FGAPP_USER’
ORDER BY granted_role;

— 常见预定义角色及其权限
— CONNECT角色：基本的数据库连接权限
— 包含的权限：CREATE SESSION

— RESOURCE角色：应用开发所需的权限
— 包含的权限：CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

— DBA角色：数据库管理员权限
— 包含的权限：几乎所有系统权限

— SELECT_CATALOG_ROLE：查询数据字典的权限
— 包含的权限：SELECT权限访问数据字典视图

— EXECUTE_CATALOG_ROLE：执行数据字典包的权限
— 包含的权限：EXECUTE权限访问数据字典包

— DELETE_CATALOG_ROLE：删除数据字典表的权限
— 包含的权限：DELETE权限访问数据字典表

— AUDIT_ADMIN：审计管理权限
— 包含的权限：审计相关的系统权限

— AUDIT_VIEWER：审计查看权限
— 包含的权限：查看审计数据的权限

— 1. 为不同类型的用户分配不同的预定义角色
— 普通用户
GRANT CONNECT TO app_user;– 应用开发人员
GRANT CONNECT, RESOURCE TO dev_user;– 报表用户
GRANT CONNECT, SELECT_CATALOG_ROLE TO report_user;– 数据库管理员
GRANT CONNECT, DBA TO admin_user;– 2. 结合自定义角色使用
— 创建自定义角色
CREATE ROLE app_developer;– 授予预定义角色
GRANT CONNECT, RESOURCE TO app_developer;– 授予额外的对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_developer;– 将角色授予用户
GRANT app_developer TO dev_user;– 3. 查看预定义角色的详细权限
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘DBA’
ORDER BY privilege;

— 1. 创建不同类型的用户
SQL> CREATE USER app_user IDENTIFIED BY App123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;SQL> CREATE USER dev_user IDENTIFIED BY Dev123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;SQL> CREATE USER report_user IDENTIFIED BY Report123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;SQL> CREATE USER admin_user IDENTIFIED BY Admin123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;– 2. 为用户分配预定义角色
SQL> GRANT CONNECT TO app_user;SQL> GRANT CONNECT, RESOURCE TO dev_user;SQL> GRANT CONNECT, SELECT_CATALOG_ROLE TO report_user;SQL> GRANT CONNECT, DBA TO admin_user;– 3. 验证角色分配
SQL> SELECT grantee, granted_role
FROM dba_role_privs
WHERE grantee IN (‘FGAPP_USER’, ‘DEV_USER’, ‘REPORT_USER’, ‘ADMIN_USER’)
ORDER BY grantee, granted_role;GRANTEE GRANTED_ROLE
———— ————
ADMIN_USER CONNECT
ADMIN_USER DBA
FGAPP_USER CONNECT
DEV_USER CONNECT
DEV_USER RESOURCE
REPORT_USER CONNECT
REPORT_USER SELECT_CATALOG_ROLE

— 1. 查看CONNECT角色的权限
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘CONNECT’
ORDER BY privilege;PRIVILEGE
—————————————-
CREATE SESSION

— 2. 查看RESOURCE角色的权限
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘RESOURCE’
ORDER BY privilege;PRIVILEGE
—————————————-
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE

— 3. 查看SELECT_CATALOG_ROLE的权限
SQL> SELECT privilege, owner, table_name
FROM dba_tab_privs
WHERE grantee = ‘SELECT_CATALOG_ROLE’
ORDER BY owner, table_name, privilege;– 输出结果会包含大量的数据字典视图的SELECT权限

— 1. 创建自定义角色
SQL> CREATE ROLE hr_fgapp_role;– 2. 授予预定义角色
SQL> GRANT CONNECT, RESOURCE TO hr_fgapp_role;– 3. 授予额外的对象权限
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO hr_fgapp_role;SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON hr.departments TO hr_fgapp_role;SQL> GRANT EXECUTE ON hr.get_employee_info TO hr_fgapp_role;– 4. 创建用户并分配角色
SQL> CREATE USER hr_user IDENTIFIED BY Hr123;SQL> GRANT hr_fgapp_role TO hr_user;– 5. 验证角色分配
SQL> SELECT grantee, granted_role
FROM dba_role_privs
WHERE grantee = ‘HR_USER’;GRANTEE GRANTED_ROLE
———— ————
HR_USER HR_APP_ROLE

— 6. 验证角色的权限
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘HR_APP_ROLE’
ORDER BY privilege;PRIVILEGE
—————————————-
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE