— 授予系统权限
GRANT CREATE SESSION TO app_user;GRANT CREATE TABLE TO app_user;GRANT CREATE VIEW TO app_user;GRANT CREATE PROCEDURE TO app_user;– 授予带有WITH ADMIN OPTION的权限
GRANT CREATE USER TO admin_user WITH ADMIN OPTION;– 撤销系统权限
REVOKE CREATE TABLE FROM app_user;REVOKE CREATE VIEW FROM app_user;– 查看用户的系统权限
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee = ‘FGAPP_USER’;– 查看所有系统权限
SELECT privilege
FROM system_privilege_map
ORDER BY privilege;

— 常见系统权限及其用途
— 数据库连接权限
GRANT CREATE SESSION TO user_name;– 对象创建权限
GRANT CREATE TABLE TO user_name;GRANT CREATE VIEW TO user_name;GRANT CREATE PROCEDURE TO user_name;GRANT CREATE FUNCTION TO user_name;GRANT CREATE PACKAGE TO user_name;– 数据操作权限
GRANT SELECT ANY TABLE TO user_name;GRANT INSERT ANY TABLE TO user_name;GRANT UPDATE ANY TABLE TO user_name;GRANT DELETE ANY TABLE TO user_name;– 管理权限
GRANT CREATE USER TO user_name;GRANT ALTER USER TO user_name;GRANT DROP USER TO user_name;GRANT CREATE TABLESPACE TO user_name;GRANT ALTER TABLESPACE TO user_name;GRANT DROP TABLESPACE TO user_name;

— 1. 创建角色管理系统权限
CREATE ROLE db_developer;CREATE ROLE db_admin;– 2. 为角色授予系统权限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO db_developer;GRANT CREATE USER, ALTER USER, DROP USER, CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE TO db_admin;– 3. 将角色授予用户
GRANT db_developer TO app_user;GRANT db_admin TO admin_user;– 4. 查看角色的系统权限
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘DB_DEVELOPER’
ORDER BY privilege;SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘DB_ADMIN’
ORDER BY privilege;

— 1. 创建开发人员用户
SQL> CREATE USER dev_user IDENTIFIED BY Dev123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;User created.

— 2. 授予基本系统权限
SQL> GRANT CREATE SESSION TO dev_user;SQL> GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION, CREATE PACKAGE TO dev_user;SQL> GRANT CREATE SYNONYM, CREATE TRIGGER, CREATE TYPE TO dev_user;– 3. 授予数据操作权限
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO dev_user;SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON hr.departments TO dev_user;– 4. 验证用户权限
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘DEV_USER’
ORDER BY privilege;PRIVILEGE
—————————————-
CREATE FUNCTION
CREATE PACKAGE
CREATE PROCEDURE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW

— 1. 创建管理员用户
SQL> CREATE USER db_admin IDENTIFIED BY Admin123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;User created.

— 2. 授予管理权限
SQL> GRANT CREATE USER, ALTER USER, DROP USER TO db_admin;SQL> GRANT CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE TO db_admin;SQL> GRANT CREATE ROLE, ALTER ROLE, DROP ROLE TO db_admin;SQL> GRANT GRANT ANY PRIVILEGE, GRANT ANY ROLE TO db_admin;– 3. 授予数据管理权限
SQL> GRANT SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO db_admin;SQL> GRANT EXECUTE ANY PROCEDURE, EXECUTE ANY FUNCTION TO db_admin;– 4. 验证用户权限
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘DB_ADMIN’
ORDER BY privilege;PRIVILEGE
—————————————-
ALTER ROLE
ALTER TABLESPACE
ALTER USER
CREATE ROLE
CREATE TABLESPACE
CREATE USER
DELETE ANY TABLE
DROP ROLE
DROP TABLESPACE
DROP USER
EXECUTE ANY FUNCTION
EXECUTE ANY PROCEDURE
GRANT ANY PRIVILEGE
GRANT ANY ROLE
INSERT ANY TABLE
SELECT ANY TABLE
UPDATE ANY TABLE

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

— 2. 回收不必要的系统权限
SQL> REVOKE CREATE TRIGGER, CREATE TYPE FROM dev_user;– 3. 验证权限回收
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘DEV_USER’
ORDER BY privilege;PRIVILEGE
—————————————-
CREATE FUNCTION
CREATE PACKAGE
CREATE PROCEDURE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE VIEW

— 4. 查看角色的系统权限
SQL> SELECT privilege
FROM dba_sys_privs
WHERE grantee = ‘DB_DEVELOPER’
ORDER BY privilege;PRIVILEGE
—————————————-
CREATE PROCEDURE
CREATE SESSION
CREATE TABLE
CREATE VIEW