— 创建角色
CREATE ROLE app_developer;CREATE ROLE app_read_only;CREATE ROLE app_admin;– 为角色授予系统权限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_developer;GRANT SELECT ANY TABLE TO app_read_only;GRANT CREATE USER, ALTER USER, DROP USER TO app_admin;– 为角色授予对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_developer;GRANT SELECT ON hr.employees TO app_read_only;GRANT ALL PRIVILEGES ON hr.employees TO app_admin;– 将角色授予用户
GRANT app_developer TO dev_user;GRANT app_read_only TO report_user;GRANT app_admin TO admin_user;– 将角色授予其他角色
GRANT app_read_only TO app_developer;– 撤销角色的权限
REVOKE CREATE TABLE FROM app_developer;REVOKE DELETE ON hr.employees FROM app_developer;– 从用户回收角色
REVOKE app_developer FROM dev_user;– 删除角色
DROP ROLE app_read_only;– 查看角色信息
SELECT role, password_required, authentication_type
FROM dba_roles
WHERE role IN (‘APP_DEVELOPER’, ‘APP_READ_ONLY’, ‘APP_ADMIN’);– 查看角色的权限
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘APP_DEVELOPER’;SELECT grantee, table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘APP_DEVELOPER’;

— 角色管理的高级操作
— 创建带密码的角色
CREATE ROLE app_manager IDENTIFIED BY manager123;– 创建外部认证角色
CREATE ROLE external_role IDENTIFIED EXTERNALLY;– 创建全局角色
CREATE ROLE global_role IDENTIFIED GLOBALLY;– 修改角色密码
ALTER ROLE app_manager IDENTIFIED BY new_password123;– 启用/禁用角色
— 在会话中启用角色
SET ROLE app_developer;– 在会话中启用所有角色
SET ROLE ALL;– 在会话中禁用所有角色
SET ROLE NONE;

— 1. 创建功能角色
CREATE ROLE read_only_role;CREATE ROLE data_entry_role;CREATE ROLE database_admin_role;CREATE ROLE application_admin_role;– 2. 为角色授予权限
— 只读角色
GRANT CREATE SESSION TO read_only_role;GRANT SELECT ON hr.employees TO read_only_role;GRANT SELECT ON hr.departments TO read_only_role;– 数据录入角色
GRANT CREATE SESSION TO data_entry_role;GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO data_entry_role;GRANT SELECT, INSERT, UPDATE, DELETE ON hr.departments TO data_entry_role;– 数据库管理员角色
GRANT CREATE SESSION TO database_admin_role;GRANT CREATE USER, ALTER USER, DROP USER TO database_admin_role;GRANT CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE TO database_admin_role;– 应用管理员角色
GRANT CREATE SESSION TO application_admin_role;GRANT ALL PRIVILEGES ON hr.employees TO application_admin_role;GRANT ALL PRIVILEGES ON hr.departments TO application_admin_role;– 3. 建立角色层次结构
GRANT read_only_role TO data_entry_role;GRANT data_entry_role TO application_admin_role;– 4. 将角色授予用户
GRANT read_only_role TO report_user;GRANT data_entry_role TO app_user;GRANT database_admin_role TO db_admin;GRANT application_admin_role TO app_admin;

— 1. 创建应用角色
SQL> CREATE ROLE myapp_read;SQL> CREATE ROLE myapp_write;SQL> CREATE ROLE myapp_admin;– 2. 为角色授予权限
SQL> GRANT CREATE SESSION TO myapp_read, myapp_write, myapp_admin;– 只读角色权限
SQL> GRANT SELECT ON myapp.employees TO myapp_read;SQL> GRANT SELECT ON myapp.departments TO myapp_read;– 读写角色权限
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.employees TO myapp_write;SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.departments TO myapp_write;– 管理员角色权限
SQL> GRANT ALL PRIVILEGES ON myapp.employees TO myapp_admin;SQL> GRANT ALL PRIVILEGES ON myapp.departments TO myapp_admin;– 3. 建立角色层次结构
SQL> GRANT myapp_read TO myapp_write;SQL> GRANT myapp_write TO myapp_admin;– 4. 创建用户并分配角色
SQL> CREATE USER report_user IDENTIFIED BY Report123;SQL> CREATE USER data_user IDENTIFIED BY Data123;SQL> CREATE USER admin_user IDENTIFIED BY Admin123;SQL> GRANT myapp_read TO report_user;SQL> GRANT myapp_write TO data_user;SQL> GRANT myapp_admin TO admin_user;– 5. 验证角色分配
SQL> SELECT grantee, granted_role
FROM dba_role_privs
WHERE grantee IN (‘REPORT_USER’, ‘DATA_USER’, ‘ADMIN_USER’)
ORDER BY grantee, granted_role;GRANTEE GRANTED_ROLE
———— ————
ADMIN_USER MYAPP_ADMIN
DATA_USER MYAPP_WRITE
REPORT_USER MYAPP_READ

— 1. 查看角色的系统权限
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘MYAPP_ADMIN’
ORDER BY privilege;– 2. 查看角色的对象权限
SQL> SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘MYAPP_ADMIN’
ORDER BY table_name, privilege;– 3. 调整角色权限
SQL> REVOKE DELETE ON myapp.employees FROM myapp_write;SQL> REVOKE DELETE ON myapp.departments FROM myapp_write;– 4. 验证权限调整
SQL> SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘MYAPP_WRITE’
ORDER BY table_name, privilege;TABLE_NAME PRIVILEGE
———— ———
DEPARTMENTS INSERT
DEPARTMENTS SELECT
DEPARTMENTS UPDATE
EMPLOYEES INSERT
EMPLOYEES SELECT
EMPLOYEES UPDATE

— 5. 查看角色层次结构
SQL> SELECT granted_role, grantee
FROM dba_role_privs
WHERE grantee IN (‘MYAPP_WRITE’, ‘MYAPP_ADMIN’)
ORDER BY grantee, granted_role;GRANTED_ROLE GRANTEE
———— ————
MYAPP_READ MYAPP_WRITE
MYAPP_WRITE MYAPP_ADMIN

— 1. 创建带密码的角色
SQL> CREATE ROLE sensitive_data_role IDENTIFIED BY sensitive123;– 2. 为角色授予权限
SQL> GRANT SELECT ON hr.salary_info TO sensitive_data_role;SQL> GRANT SELECT ON hr.bonus_info TO sensitive_data_role;– 3. 将角色授予用户
SQL> GRANT sensitive_data_role TO manager_user;– 4. 在会话中启用角色
SQL> CONN manager_user/Manager123
SQL> SET ROLE sensitive_data_role IDENTIFIED BY sensitive123;– 5. 验证角色是否启用
SQL> SELECT * FROM session_roles;ROLE
——————————
CONNECT
SENSITIVE_DATA_ROLE

— 6. 查询敏感数据
SQL> SELECT * FROM hr.salary_info WHERE employee_id = 100;EMPLOYEE_ID SALARY
———– ———-
100 24000