— 创建新用户
CREATE USER test_user IDENTIFIED BY “TestPassword123”;User created.

— 修改用户密码
ALTER USER test_user IDENTIFIED BY “NewPassword456”;User altered.

— 锁定用户
ALTER USER test_user ACCOUNT LOCK;User altered.

— 解锁用户
ALTER USER test_user ACCOUNT UNLOCK;User altered.

— 删除用户
DROP USER test_user CASCADE;User dropped.

— 查看用户信息
SELECT username, account_status, created, lock_date
FROM dba_users
WHERE username = ‘TEST_USER’;USERNAME ACCOUNT_STATUS CREATED LOCK_DATE
———- ——————————– ———– ———–
TEST_USER OPEN 26-JAN-26

— 创建自定义角色
CREATE ROLE sales_role;Role created.

— 为角色授予权限
GRANT SELECT, INSERT, UPDATE ON employees TO sales_role;GRANT SELECT ON departments TO sales_role;Grant succeeded.
Grant succeeded.

— 将角色授予用户
GRANT sales_role TO test_user;Grant succeeded.

— 查看角色权限
SELECT grantee, table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘SALES_ROLE’;GRANTEE TABLE_NAME PRIVILEGE
———- —————————— ———-
SALES_ROLE DEPARTMENTS SELECT
SALES_ROLE EMPLOYEES INSERT
SALES_ROLE EMPLOYEES SELECT
SALES_ROLE EMPLOYEES UPDATE

— 查看用户拥有的角色
SELECT grantee, granted_role, admin_option
FROM dba_role_privs
WHERE grantee = ‘TEST_USER’;GRANTEE GRANTED_ROLE ADM
———- —————————— —
TEST_USER SALES_ROLE NO

— 删除角色
DROP ROLE sales_role;Role dropped.

— 授予系统权限
GRANT CREATE SESSION TO test_user;GRANT CREATE TABLE TO test_user;Grant succeeded.
Grant succeeded.

— 授予对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO test_user;Grant succeeded.

— 查看用户的系统权限
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘TEST_USER’;GRANTEE PRIVILEGE
———- ——————–
TEST_USER CREATE TABLE
TEST_USER CREATE SESSION

— 查看用户的对象权限
SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = ‘TEST_USER’;GRANTEE OWNER TABLE_NAME PRIVILEGE
———- ———- —————————— ———-
TEST_USER HR EMPLOYEES DELETE
TEST_USER HR EMPLOYEES INSERT
TEST_USER HR EMPLOYEES SELECT
TEST_USER HR EMPLOYEES UPDATE

— 撤销权限
REVOKE DELETE ON employees FROM test_user;Revoke succeeded.

— 启用审计
ALTER SYSTEM SET audit_trail = ‘DB’ SCOPE=SPFILE;System altered.

— 重启数据库使审计设置生效
SHUTDOWN IMMEDIATE;STARTUP;– 审计用户登录和注销
AUDIT SESSION;Audit succeeded.

— 审计表的访问
AUDIT SELECT, INSERT, UPDATE, DELETE ON employees;Audit succeeded.

— 查看审计记录
SELECT username, action_name, obj_name, timestamp
FROM dba_audit_trail
WHERE obj_name = ‘EMPLOYEES’
ORDER BY timestamp DESC;USERNAME ACTION_NAME OBJ_NAME TIMESTAMP
———- ——————– —————————- ———–
TEST_USER SELECT EMPLOYEES 26-JAN-26
TEST_USER INSERT EMPLOYEES 26-JAN-26

— 停止审计
NOAUDIT SESSION;NOAUDIT SELECT, INSERT, UPDATE, DELETE ON employees;Noaudit succeeded.
Noaudit succeeded.

— 查看当前密码策略
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_type = ‘PASSWORD’;PROFILE RESOURCE_NAME LIMIT
—————————— ——————————– ——————–
DEFAULT PASSWORD_LIFE_TIME UNLIMITED
DEFAULT PASSWORD_GRACE_TIME 7
DEFAULT PASSWORD_REUSE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_MAX UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
DEFAULT PASSWORD_LOCK_TIME 1
DEFAULT PASSWORD_FAILED_LOGIN_ATTEMPTS 10

— 创建密码验证函数
CREATE OR REPLACE FUNCTION verify_password(
username VARCHAR2,
password VARCHAR2,
old_password VARCHAR2
) RETURN BOOLEAN AS
len NUMBER;digits NUMBER;letters NUMBER;specials NUMBER;BEGIN
— 密码长度至少8位
len := LENGTH(password);IF len < 8 THEN RETURN FALSE;END IF;-- 至少包含一个数字 digits := REGEXP_COUNT(password, '[0-9]');IF digits < 1 THEN RETURN FALSE;END IF;-- 至少包含一个字母 letters := REGEXP_COUNT(password, '[A-Za-z]');IF letters < 1 THEN RETURN FALSE;END IF;-- 至少包含一个特殊字符 specials := REGEXP_COUNT(password, '[^A-Za-z0-9]');IF specials < 1 THEN RETURN FALSE;END IF;RETURN TRUE;END;/Function created. -- 修改密码策略 ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 7 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 5 PASSWORD_VERIFY_FUNCTION verify_password PASSWORD_LOCK_TIME 1 PASSWORD_FAILED_LOGIN_ATTEMPTS 5;Profile altered. -- 测试密码策略 ALTER USER test_user IDENTIFIED BY "weak";-- 会报错：ORA-28003: password verification for the specified password failed ALTER USER test_user IDENTIFIED BY "StrongPassword123!";-- 成功 User altered.

— 启用透明数据加密(TDE)
— 1. 创建加密钱包
ALTER SYSTEM SET encryption_key = ‘password’ SCOPE=SPFILE;System altered.

— 2. 重启数据库
SHUTDOWN IMMEDIATE;STARTUP;– 3. 创建加密表空间
CREATE TABLESPACE encrypted_ts
DATAFILE ‘/u01/app/oracle/oradata/ORCL/encrypted_ts01.dbf’
SIZE 100M
ENCRYPTION USING ‘AES256’ DEFAULT STORAGE (ENCRYPT);Tablespace created.

— 4. 在加密表空间中创建表
CREATE TABLE sensitive_data (
id NUMBER(6),
name VARCHAR2(50),
credit_card VARCHAR2(16)
)
TABLESPACE encrypted_ts;Table created.

— 5. 插入数据
INSERT INTO sensitive_data VALUES (1, ‘John Doe’, ‘1234567890123456’);COMMIT;– 6. 查看数据（数据在存储时已加密，但查询时自动解密）
SELECT * FROM sensitive_data;ID NAME CREDIT_CARD
———- ———- —————-
1 John Doe 1234567890123456

— 启用网络加密
ALTER SYSTEM SET sqlnet.encryption_client = ‘required’ SCOPE=SPFILE;ALTER SYSTEM SET sqlnet.encryption_types_client = ‘(AES256)’ SCOPE=SPFILE;ALTER SYSTEM SET sqlnet.crypto_checksum_client = ‘required’ SCOPE=SPFILE;System altered.
System altered.
System altered.

— 重启数据库
SHUTDOWN IMMEDIATE;STARTUP;