— 授予系统权限
GRANT CREATE SESSION TO app_user;GRANT CREATE TABLE TO app_user;GRANT CREATE VIEW TO app_user;– 授予对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_user;GRANT EXECUTE ON hr.get_employee_info TO app_user;– 撤销权限
REVOKE CREATE TABLE FROM app_user;REVOKE DELETE ON hr.employees FROM app_user;– 查看用户权限
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘FGAPP_USER’;SELECT grantee, table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘FGAPP_USER’;

— 创建角色
CREATE ROLE app_developer;– 为角色授予权限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_developer;GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_developer;– 将角色授予用户
GRANT app_developer TO app_user;– 查看角色权限
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘APP_DEVELOPER’;SELECT grantee, table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘APP_DEVELOPER’;

— 系统权限管理
— 1. 为用户授予基本权限
GRANT CREATE SESSION TO app_user;– 2. 根据需要授予其他系统权限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO app_user;– 对象权限管理
— 1. 为用户授予表权限
GRANT SELECT, INSERT, UPDATE ON hr.employees TO app_user;– 2. 为用户授予存储过程执行权限
GRANT EXECUTE ON hr.get_employee_info TO app_user;– 角色管理
— 1. 创建功能角色
CREATE ROLE read_only_role;CREATE ROLE data_entry_role;CREATE ROLE admin_role;– 2. 为角色授予权限
GRANT SELECT ANY TABLE TO read_only_role;GRANT INSERT, UPDATE, DELETE ON hr.employees TO data_entry_role;GRANT ALL PRIVILEGES ON hr.employees TO admin_role;– 3. 将角色授予用户
GRANT read_only_role TO report_user;GRANT data_entry_role TO app_user;GRANT admin_role TO sysadmin;

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

— 2. 授予基本系统权限
SQL> GRANT CREATE SESSION TO myapp;SQL> GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE 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;SQL> GRANT EXECUTE ON hr.get_employee_info TO myapp;– 4. 验证用户权限
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘MYAPP’
ORDER BY privilege;PRIVILEGE
—————————————-
CREATE PROCEDURE
CREATE SESSION
CREATE TABLE
CREATE VIEW

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

— 1. 创建角色
SQL> CREATE ROLE app_read_only;SQL> CREATE ROLE app_data_entry;SQL> CREATE ROLE app_admin;– 2. 为角色授予权限
SQL> GRANT CREATE SESSION TO app_read_only, app_data_entry, app_admin;SQL> GRANT SELECT ON hr.employees TO app_read_only;SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_data_entry;SQL> GRANT ALL PRIVILEGES ON hr.employees TO app_admin;– 3. 创建用户并分配角色
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 app_read_only TO report_user;SQL> GRANT app_data_entry TO data_user;SQL> GRANT app_admin TO admin_user;– 4. 验证角色权限
SQL> SELECT grantee, privilege
FROM dba_role_privs
WHERE grantee IN (‘REPORT_USER’, ‘DATA_USER’, ‘ADMIN_USER’)
ORDER BY grantee, privilege;GRANTEE PRIVILEGE
———— ————
ADMIN_USER APP_ADMIN
DATA_USER APP_DATA_ENTRY
REPORT_USER APP_READ_ONLY

— 1. 查看用户的系统权限
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘FGAPP_USER’
ORDER BY privilege;PRIVILEGE
—————————————-
CREATE PROCEDURE
CREATE SESSION
CREATE TABLE
CREATE VIEW

— 2. 查看用户的对象权限
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

— 3. 回收不必要的权限
SQL> REVOKE CREATE TABLE, CREATE VIEW, CREATE PROCEDURE FROM app_user;SQL> REVOKE DELETE ON hr.employees FROM app_user;SQL> REVOKE DELETE ON hr.departments FROM app_user;– 4. 验证权限回收
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘FGAPP_USER’
ORDER BY privilege;PRIVILEGE
—————————————-
CREATE SESSION

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