Oracle用户权限管理 #

-- 创建用户基本语法
CREATE USER username
IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE temp_tablespace]
[QUOTA size ON tablespace_name]
[PROFILE profile_name]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK];

-- 创建简单用户
CREATE USER app_user IDENTIFIED BY password123;

-- 创建完整配置用户
CREATE USER app_user
IDENTIFIED BY password123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
QUOTA 50M ON indx
PROFILE default
PASSWORD EXPIRE
ACCOUNT UNLOCK;

-- 修改密码
ALTER USER app_user IDENTIFIED BY new_password;

-- 修改默认表空间
ALTER USER app_user DEFAULT TABLESPACE app_data;

-- 修改配额
ALTER USER app_user QUOTA UNLIMITED ON users;

-- 锁定/解锁用户
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;

-- 密码过期
ALTER USER app_user PASSWORD EXPIRE;

-- 删除用户（无对象）
DROP USER app_user;

-- 删除用户及其所有对象
DROP USER app_user CASCADE;

-- 查看所有用户
SELECT username, account_status, created, default_tablespace
FROM dba_users;

-- 查看当前用户
SELECT * FROM user_users;

-- 查看用户配额
SELECT * FROM dba_ts_quotas WHERE username = 'APP_USER';

-- 常用系统权限
-- CREATE SESSION：连接数据库
-- CREATE TABLE：创建表
-- CREATE VIEW：创建视图
-- CREATE PROCEDURE：创建过程
-- CREATE SEQUENCE：创建序列
-- CREATE TRIGGER：创建触发器
-- CREATE ANY TABLE：创建任意用户的表
-- ALTER ANY TABLE：修改任意用户的表
-- DROP ANY TABLE：删除任意用户的表
-- SELECT ANY TABLE：查询任意表
-- INSERT ANY TABLE：插入任意表
-- UPDATE ANY TABLE：更新任意表
-- DELETE ANY TABLE：删除任意表

-- 授予系统权限
GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO app_user;

-- 授予所有权限
GRANT ALL PRIVILEGES TO app_user;

-- 回收系统权限
REVOKE CREATE TABLE FROM app_user;

-- 对象权限类型
-- SELECT：查询
-- INSERT：插入
-- UPDATE：更新
-- DELETE：删除
-- EXECUTE：执行
-- ALTER：修改
-- INDEX：创建索引
-- REFERENCES：创建外键

-- 授予对象权限
GRANT SELECT ON employees TO app_user;
GRANT SELECT, INSERT, UPDATE ON employees TO app_user;
GRANT ALL ON employees TO app_user;

-- 授予特定列权限
GRANT UPDATE (salary) ON employees TO app_user;

-- 带GRANT OPTION（允许转授）
GRANT SELECT ON employees TO app_user WITH GRANT OPTION;

-- 回收对象权限
REVOKE SELECT ON employees FROM app_user;

-- 查看系统权限
SELECT * FROM dba_sys_privs WHERE grantee = 'APP_USER';
SELECT * FROM user_sys_privs;

-- 查看对象权限
SELECT * FROM dba_tab_privs WHERE grantee = 'APP_USER';
SELECT * FROM user_tab_privs;

-- 查看列权限
SELECT * FROM dba_col_privs WHERE grantee = 'APP_USER';

-- 常用预定义角色
-- CONNECT：基本连接权限
-- RESOURCE：开发者权限
-- DBA：数据库管理员权限
-- EXP_FULL_DATABASE：导出数据库
-- IMP_FULL_DATABASE：导入数据库

-- 查看预定义角色
SELECT role FROM dba_roles;

-- 查看角色权限
SELECT * FROM dba_sys_privs WHERE grantee = 'CONNECT';
SELECT * FROM dba_sys_privs WHERE grantee = 'RESOURCE';

-- 创建角色
CREATE ROLE app_role;

-- 创建带密码的角色
CREATE ROLE admin_role IDENTIFIED BY password123;

-- 授予角色权限
GRANT CREATE SESSION TO app_role;
GRANT SELECT ON employees TO app_role;
GRANT SELECT ON departments TO app_role;

-- 授予用户角色
GRANT app_role TO app_user;
GRANT app_role TO app_user WITH ADMIN OPTION;

-- 启用角色
SET ROLE app_role;

-- 启用所有角色
SET ROLE ALL;

-- 启用带密码的角色
SET ROLE admin_role IDENTIFIED BY password123;

-- 修改角色密码
ALTER ROLE admin_role IDENTIFIED BY new_password;

-- 删除角色密码
ALTER ROLE admin_role NOT IDENTIFIED;

-- 删除角色
DROP ROLE app_role;

-- 查看用户角色
SELECT * FROM dba_role_privs WHERE grantee = 'APP_USER';

-- 查看角色权限
SELECT * FROM role_sys_privs WHERE role = 'APP_ROLE';
SELECT * FROM role_tab_privs WHERE role = 'APP_ROLE';

-- 创建Profile
CREATE PROFILE app_profile LIMIT
    SESSIONS_PER_USER 5
    CPU_PER_SESSION UNLIMITED
    CPU_PER_CALL 30000
    CONNECT_TIME 480
    IDLE_TIME 60
    LOGICAL_READS_PER_SESSION DEFAULT
    LOGICAL_READS_PER_CALL 1000
    PRIVATE_SGA 10M
    COMPOSITE_LIMIT 5000000
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_TIME 30
    PASSWORD_REUSE_MAX 5
    PASSWORD_LOCK_TIME 1
    PASSWORD_GRACE_TIME 7
    PASSWORD_VERIFY_FUNCTION verify_function;

-- 分配Profile
ALTER USER app_user PROFILE app_profile;

-- 密码策略参数
-- FAILED_LOGIN_ATTEMPTS：登录失败次数
-- PASSWORD_LIFE_TIME：密码有效期（天）
-- PASSWORD_REUSE_TIME：密码重用间隔（天）
-- PASSWORD_REUSE_MAX：密码重用次数
-- PASSWORD_LOCK_TIME：锁定时间（天）
-- PASSWORD_GRACE_TIME：宽限期（天）
-- PASSWORD_VERIFY_FUNCTION：密码验证函数

-- 修改密码策略
ALTER PROFILE default LIMIT
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LIFE_TIME 180
    PASSWORD_LOCK_TIME 1;

-- 查看审计状态
SHOW PARAMETER audit;

-- 启用审计
ALTER SYSTEM SET audit_trail = db SCOPE = spfile;
-- 需要重启数据库

-- 审计类型
-- NONE：禁用审计
-- DB：审计记录到数据库
-- OS：审计记录到操作系统文件
-- DB,EXTENDED：详细审计记录

-- 审计语句
AUDIT CREATE TABLE BY app_user;
AUDIT INSERT, UPDATE ON employees BY ACCESS;
AUDIT SELECT ON employees WHENEVER SUCCESSFUL;

-- 审计权限
AUDIT CREATE ANY TABLE;

-- 审计对象
AUDIT ALL ON employees;

-- 取消审计
NOAUDIT CREATE TABLE BY app_user;
NOAUDIT INSERT, UPDATE ON employees;

-- 查看审计记录
SELECT * FROM dba_audit_trail
ORDER BY timestamp DESC;

-- 查看审计配置
SELECT * FROM dba_stmt_audit_opts;
SELECT * FROM dba_obj_audit_opts;