— 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_user;GRANT ALTER, INDEX ON hr.employees TO admin_user;– 授予视图权限
GRANT SELECT, INSERT, UPDATE ON hr.employee_view TO app_user;– 授予存储过程权限
GRANT EXECUTE ON hr.get_employee_info TO app_user;– 授予函数权限
GRANT EXECUTE ON hr.calculate_salary TO app_user;– 授予包权限
GRANT EXECUTE ON hr.employee_pkg TO app_user;– 撤销对象权限
REVOKE DELETE ON hr.employees FROM app_user;REVOKE EXECUTE ON hr.get_employee_info FROM app_user;– 查看用户的对象权限
SELECT grantee, table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘FGAPP_USER’;

— 常见对象权限及其用途
— 表权限
GRANT SELECT ON table_name TO user_name; — 允许查询表
GRANT INSERT ON table_name TO user_name; — 允许插入数据
GRANT UPDATE ON table_name TO user_name; — 允许更新数据
GRANT DELETE ON table_name TO user_name; — 允许删除数据
GRANT ALTER ON table_name TO user_name; — 允许修改表结构
GRANT INDEX ON table_name TO user_name; — 允许创建索引
GRANT ALL PRIVILEGES ON table_name TO user_name; — 授予所有表权限

— 视图权限
GRANT SELECT ON view_name TO user_name; — 允许查询视图
GRANT INSERT ON view_name TO user_name; — 允许插入数据
GRANT UPDATE ON view_name TO user_name; — 允许更新数据
GRANT DELETE ON view_name TO user_name; — 允许删除数据

— 存储过程、函数、包权限
GRANT EXECUTE ON procedure_name TO user_name; — 允许执行存储过程
GRANT EXECUTE ON function_name TO user_name; — 允许执行函数
GRANT EXECUTE ON package_name TO user_name; — 允许执行包

— 序列权限
GRANT SELECT ON sequence_name TO user_name; — 允许查询序列
GRANT ALTER ON sequence_name TO user_name; — 允许修改序列

— 1. 创建角色管理对象权限
CREATE ROLE read_only_role;CREATE ROLE data_entry_role;CREATE ROLE object_admin_role;– 2. 为角色授予对象权限
GRANT SELECT ON hr.employees TO read_only_role;GRANT SELECT ON hr.departments TO read_only_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 ALL PRIVILEGES ON hr.employees TO object_admin_role;GRANT ALL PRIVILEGES ON hr.departments TO object_admin_role;– 3. 将角色授予用户
GRANT read_only_role TO report_user;GRANT data_entry_role TO app_user;GRANT object_admin_role TO admin_user;– 4. 查看角色的对象权限
SELECT grantee, table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘READ_ONLY_ROLE’
ORDER BY table_name, privilege;SELECT grantee, table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘DATA_ENTRY_ROLE’
ORDER BY table_name, privilege;

— 1. 创建应用用户
SQL> CREATE USER myapp IDENTIFIED BY MyApp123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;User created.

— 2. 授予基本系统权限
SQL> GRANT CREATE SESSION TO myapp;– 3. 授予表权限
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO myapp;SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON hr.departments TO myapp;– 4. 授予存储过程权限
SQL> GRANT EXECUTE ON hr.get_employee_info TO myapp;SQL> GRANT EXECUTE ON hr.update_employee TO myapp;– 5. 验证用户权限
SQL> SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘MYAPP’
ORDER BY table_name, privilege;TABLE_NAME PRIVILEGE
———— ———
DEPARTMENTS DELETE
DEPARTMENTS INSERT
DEPARTMENTS SELECT
DEPARTMENTS UPDATE
EMPLOYEES DELETE
EMPLOYEES INSERT
EMPLOYEES SELECT
EMPLOYEES UPDATE
GET_EMPLOYEE_INFO EXECUTE
UPDATE_EMPLOYEE EXECUTE

— 1. 创建报表用户
SQL> CREATE USER report_user IDENTIFIED BY Report123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;User created.

— 2. 授予基本系统权限
SQL> GRANT CREATE SESSION TO report_user;– 3. 授予只读对象权限
SQL> GRANT SELECT ON hr.employees TO report_user;SQL> GRANT SELECT ON hr.departments TO report_user;SQL> GRANT SELECT ON hr.employee_view TO report_user;SQL> GRANT EXECUTE ON hr.get_employee_report TO report_user;– 4. 验证用户权限
SQL> SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘REPORT_USER’
ORDER BY table_name, privilege;TABLE_NAME PRIVILEGE
———— ———
DEPARTMENTS SELECT
EMPLOYEES SELECT
EMPLOYEE_VIEW SELECT
GET_EMPLOYEE_REPORT EXECUTE

— 1. 查看用户的对象权限
SQL> SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘FGAPP_USER’
ORDER BY table_name, privilege;TABLE_NAME PRIVILEGE
———— ———
DEPARTMENTS DELETE
DEPARTMENTS INSERT
DEPARTMENTS SELECT
DEPARTMENTS UPDATE
EMPLOYEES DELETE
EMPLOYEES INSERT
EMPLOYEES SELECT
EMPLOYEES UPDATE
GET_EMPLOYEE_INFO EXECUTE
UPDATE_EMPLOYEE EXECUTE

— 2. 回收不必要的对象权限
SQL> REVOKE DELETE ON hr.employees FROM app_user;SQL> REVOKE DELETE ON hr.departments FROM app_user;– 3. 验证权限回收
SQL> SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘FGAPP_USER’
ORDER BY table_name, privilege;TABLE_NAME PRIVILEGE
———— ———
DEPARTMENTS INSERT
DEPARTMENTS SELECT
DEPARTMENTS UPDATE
EMPLOYEES INSERT
EMPLOYEES SELECT
EMPLOYEES UPDATE
GET_EMPLOYEE_INFO EXECUTE
UPDATE_EMPLOYEE EXECUTE

— 4. 查看角色的对象权限
SQL> SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘DATA_ENTRY_ROLE’
ORDER BY table_name, privilege;TABLE_NAME PRIVILEGE
———— ———
DEPARTMENTS DELETE
DEPARTMENTS INSERT
DEPARTMENTS SELECT
DEPARTMENTS UPDATE
EMPLOYEES DELETE
EMPLOYEES INSERT
EMPLOYEES SELECT
EMPLOYEES UPDATE