— 步骤1：创建三权分立角色
— 创建系统管理员角色
CREATE ROLE sys_admin_role;

— 创建安全管理员角色
CREATE ROLE security_admin_role;

— 创建审计管理员角色
CREATE ROLE audit_admin_role;

— 步骤2：分配系统管理员权限
GRANT SYSDBA TO sys_admin_role;
— 注意：实际生产环境中应根据需要分配具体权限，避免使用SYSDBA

— 步骤3：分配安全管理员权限
GRANT CREATE USER, ALTER USER, DROP USER TO security_admin_role;
GRANT GRANT ANY PRIVILEGE, GRANT ANY ROLE TO security_admin_role;
GRANT CREATE ROLE, ALTER ROLE, DROP ROLE TO security_admin_role;

— 步骤4：分配审计管理员权限
GRANT AUDIT SYSTEM, AUDIT ANY TO audit_admin_role;
GRANT SELECT ON DBA_AUDIT_TRAIL TO audit_admin_role;
GRANT SELECT ON DBA_FGA_AUDIT_TRAIL TO audit_admin_role;

— 步骤5：创建三权分立用户
— 创建系统管理员用户
CREATE USER sys_admin IDENTIFIED BY “SysAdmin123!”;
GRANT sys_admin_role TO sys_admin;

— 创建安全管理员用户
CREATE USER security_admin IDENTIFIED BY “SecurityAdmin123!”;
GRANT security_admin_role TO security_admin;

— 创建审计管理员用户
CREATE USER audit_admin IDENTIFIED BY “AuditAdmin123!”;
GRANT audit_admin_role TO audit_admin;

— 步骤6：验证用户和角色
SELECT
username,
account_status
FROM dba_users
WHERE username IN (‘SYS_ADMIN’, ‘SECURITY_ADMIN’, ‘AUDIT_ADMIN’);

— 输出结果
USERNAME ACCOUNT_STATUS
————— —————
SYS_ADMIN OPEN
SECURITY_ADMIN OPEN
AUDIT_ADMIN OPEN

— 查看角色权限
SELECT
grantee,
granted_role
FROM dba_role_privs
WHERE grantee IN (‘SYS_ADMIN’, ‘SECURITY_ADMIN’, ‘AUDIT_ADMIN’);

— 步骤1：创建细粒度的系统管理权限
CREATE ROLE system_config_role;
GRANT ALTER SYSTEM, ALTER SESSION TO system_config_role;
GRANT CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE TO system_config_role;
GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE TO system_config_role;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO system_config_role;

— 步骤2：创建细粒度的安全管理权限
CREATE ROLE user_admin_role;
GRANT CREATE USER, ALTER USER, DROP USER TO user_admin_role;
GRANT CREATE ROLE, ALTER ROLE, DROP ROLE TO user_admin_role;
GRANT GRANT ANY PRIVILEGE, GRANT ANY ROLE TO user_admin_role;
GRANT SELECT ON DBA_USERS TO user_admin_role;
GRANT SELECT ON DBA_ROLES TO user_admin_role;

— 步骤3：创建细粒度的审计管理权限
CREATE ROLE audit_config_role;
GRANT AUDIT SYSTEM, AUDIT ANY TO audit_config_role;
GRANT SELECT ON DBA_AUDIT_TRAIL TO audit_config_role;
GRANT SELECT ON DBA_FGA_AUDIT_TRAIL TO audit_config_role;
GRANT SELECT ON DBA_AUDIT_OBJECT TO audit_config_role;

— 步骤4：重新分配权限
REVOKE sys_admin_role FROM sys_admin;
GRANT system_config_role TO sys_admin;

REVOKE security_admin_role FROM security_admin;
GRANT user_admin_role TO security_admin;

REVOKE audit_admin_role FROM audit_admin;
GRANT audit_config_role TO audit_admin;

— 步骤5：启用审计
ALTER SYSTEM SET audit_trail = ‘DB,EXTENDED’ SCOPE=SPFILE;

— 重启数据库使审计设置生效
— SHUTDOWN IMMEDIATE;
— STARTUP;

— 步骤6：配置审计策略
AUDIT ALL BY sys_admin BY ACCESS;
AUDIT ALL BY security_admin BY ACCESS;
AUDIT ALL BY audit_admin BY ACCESS;
AUDIT ALL BY fgedu BY ACCESS;

— 步骤1：创建业务用户角色
CREATE ROLE app_user_role;
GRANT CREATE SESSION TO app_user_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu.sales TO app_user_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu.customers TO app_user_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu.products TO app_user_role;

— 步骤2：创建业务用户
CREATE USER fgedu_app IDENTIFIED BY “FgApp123!”;
GRANT app_user_role TO fgedu_app;

— 步骤3：创建只读用户角色
CREATE ROLE read_only_role;
GRANT CREATE SESSION TO read_only_role;
GRANT SELECT ON fgedu.sales TO read_only_role;
GRANT SELECT ON fgedu.customers TO read_only_role;
GRANT SELECT ON fgedu.products TO read_only_role;

— 步骤4：创建只读用户
CREATE USER fgedu_read IDENTIFIED BY “FgRead123!”;
GRANT read_only_role TO fgedu_read;

— 步骤5：管理角色权限
— 查看角色权限
SELECT
grantee,
privilege
FROM dba_sys_privs
WHERE grantee = ‘APP_USER_ROLE’;

— 查看对象权限
SELECT
grantee,
owner,
table_name,
privilege
FROM dba_tab_privs
WHERE grantee = ‘APP_USER_ROLE’;

— 调整角色权限
REVOKE DELETE ON fgedu.sales FROM app_user_role;
GRANT INSERT, UPDATE ON fgedu.sales TO app_user_role;

— 步骤6：管理用户角色
— 查看用户角色
SELECT
username,
granted_role
FROM dba_role_privs
WHERE username = ‘FGEDU_APP’;

— 调整用户角色
REVOKE app_user_role FROM fgedu_app;
GRANT read_only_role TO fgedu_app;

— 步骤1：审查系统权限
SELECT
grantee,
privilege,
admin_option
FROM dba_sys_privs
WHERE grantee IN (‘SYS_ADMIN’, ‘SECURITY_ADMIN’, ‘AUDIT_ADMIN’, ‘FGEDU_APP’, ‘FGEDU_READ’)
ORDER BY grantee, privilege;

— 步骤2：审查对象权限
SELECT
grantee,
owner,
table_name,
privilege
FROM dba_tab_privs
WHERE grantee IN (‘SYS_ADMIN’, ‘SECURITY_ADMIN’, ‘AUDIT_ADMIN’, ‘FGEDU_APP’, ‘FGEDU_READ’)
ORDER BY grantee, owner, table_name, privilege;

— 步骤3：审查角色权限
SELECT
grantee,
granted_role,
admin_option
FROM dba_role_privs
WHERE grantee IN (‘SYS_ADMIN’, ‘SECURITY_ADMIN’, ‘AUDIT_ADMIN’, ‘FGEDU_APP’, ‘FGEDU_READ’)
ORDER BY grantee, granted_role;

— 步骤4：审查高权限用户
SELECT
username,
account_status,
profile
FROM dba_users
WHERE username IN (
SELECT grantee
FROM dba_sys_privs
WHERE privilege IN (‘SYSDBA’, ‘SYSOPER’, ‘SYSASM’)
);

— 步骤5：生成权限审查报告
— 可以使用SQL生成详细的权限审查报告

— 步骤1：查看审计日志
SELECT
username,
timestamp,
action_name,
object_name,
sql_text
FROM dba_audit_trail
WHERE timestamp > SYSDATE – 7
ORDER BY timestamp DESC;

— 步骤2：查看细粒度审计日志
SELECT
db_user,
object_schema,
object_name,
policy_name,
statement_type,
timestamp,
sql_text
FROM dba_fga_audit_trail
WHERE timestamp > SYSDATE – 7
ORDER BY timestamp DESC;

— 步骤3：监控用户登录
SELECT
username,
timestamp,
action_name,
returncode,
client_identifier
FROM dba_audit_trail
WHERE action_name IN (‘LOGON’, ‘LOGOFF’)
AND timestamp > SYSDATE – 1
ORDER BY timestamp DESC;

— 步骤4：监控权限变更
SELECT
username,
timestamp,
action_name,
object_name,
sql_text
FROM dba_audit_trail
WHERE action_name IN (‘GRANT’, ‘REVOKE’)
AND timestamp > SYSDATE – 7
ORDER BY timestamp DESC;

— 步骤5：监控对象变更
SELECT
username,
timestamp,
action_name,
object_name,
object_type,
sql_text
FROM dba_audit_trail
WHERE action_name IN (‘CREATE’, ‘ALTER’, ‘DROP’)
AND timestamp > SYSDATE – 7
ORDER BY timestamp DESC;

— 步骤1：查看用户状态
SELECT
username,
account_status,
lock_date,
expiry_date
FROM dba_users
ORDER BY account_status;

— 步骤2：查看无效用户
SELECT
username,
account_status
FROM dba_users
WHERE account_status != ‘OPEN’;

— 步骤3：查看密码过期用户
SELECT
username,
expiry_date
FROM dba_users
WHERE expiry_date < SYSDATE; -- 步骤4：查看失败登录尝试 SELECT username, count(*) FROM dba_audit_trail WHERE action_name = 'LOGON' AND returncode != 0 AND timestamp > SYSDATE – 1
GROUP BY username
ORDER BY count(*) DESC;

— 步骤5：查看特权用户
SELECT
username,
granted_role
FROM dba_role_privs
WHERE granted_role IN (‘DBA’, ‘SYSDBA’, ‘SYSOPER’)
ORDER BY username;