Oracle 用户管理与权限分配 —— 语法详解与实战案例
一、环境准备：Oracle 安装简要说明（以 Oracle Database 21c Express Edition 为例）
注意：本章节重点为用户与权限管理，安装过程仅作必要引导。
1. 安装 Oracle Database 21c XE（Windows/Linux）
Windows 安装步骤简述：
- 下载 Oracle Database 21c XE 安装包（官网链接）
- 以管理员身份运行
setup.exe - 选择安装类型：Desktop Class（单机开发）或Server Class
- 设置数据库口令（用于 SYS、SYSTEM 等内置账户）
- 完成安装后，默认监听端口为 1521，服务名为
XE
Linux（Ubuntu/CentOS）安装简述：
# 示例：Ubuntu 22.04wgethttps://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpmsudodnfinstalloracle-database-xe-21c-1.0-1.ol8.x86_64.rpmsudo/etc/init.d/oracle-xe-21c configure配置过程中设置 SYS/SYSTEM 密码。
安装完成后，使用 SQL*Plus 或 SQL Developer 连接数据库：
sqlplus sys/your_password@localhost:1521/XE as sysdba二、核心语法知识点详解与案例
1. 用户与模式（Schema）的关系
- 用户（User）：数据库中的账户，用于身份验证。
- 模式（Schema）：用户拥有的数据库对象（表、视图、索引等）的集合。
- 关系：每个用户自动拥有一个同名的模式。创建用户即创建同名模式。
✅ 用户 ≠ 模式，但默认一一对应。
2. 创建用户
语法：
CREATEUSERusername IDENTIFIEDBYpassword[DEFAULTTABLESPACEtablespace_name][TEMPORARYTABLESPACEtemp_tablespace][PROFILE profile_name][ACCOUNT {LOCK|UNLOCK}];案例：
-- 创建用户 scott，密码为 tiger-- 默认表空间为 USERS，临时表空间为 TEMPCREATEUSERscott IDENTIFIEDBYtigerDEFAULTTABLESPACEusersTEMPORARYTABLESPACEtempACCOUNTUNLOCK;🔒 注意：普通用户无权创建用户，需具有
CREATE USER系统权限（通常由 DBA 执行）。
3. 修改用户
语法：
ALTERUSERusername[IDENTIFIEDBYnew_password][DEFAULTTABLESPACEtablespace_name][TEMPORARYTABLESPACEtemp_tablespace][PROFILE profile_name][ACCOUNT {LOCK|UNLOCK}];案例：
-- 修改 scott 密码并锁定账户ALTERUSERscott IDENTIFIEDBYnewpass ACCOUNTLOCK;-- 解锁账户ALTERUSERscott ACCOUNTUNLOCK;4. 删除用户
语法：
DROPUSERusername[CASCADE];CASCADE：级联删除该用户拥有的所有对象（如表、视图等）。- 若用户拥有对象但未加
CASCADE，则删除失败。
案例：
-- 删除用户及其所有对象DROPUSERscottCASCADE;5. 权限简介
- 系统权限（System Privileges）：对整个数据库的操作权限，如
CREATE SESSION,CREATE TABLE。 - 对象权限（Object Privileges）：对特定对象的操作权限，如
SELECT ON emp,UPDATE ON dept。
6. 授权操作（GRANT）
6.1 系统权限授权
GRANTprivilege[,privilege...]TOuser_or_role;案例：
-- 授予 scott 登录和建表权限GRANTCREATESESSION,CREATETABLETOscott;6.2 对象权限授权
GRANTobject_privilege[(column_list)]ON[schema.]objectTOuser_or_role[WITHGRANTOPTION];案例：
-- 假设 hr 用户有 employees 表-- 授予 scott 查询 hr.employees 的权限，并允许其转授GRANTSELECTONhr.employeesTOscottWITHGRANTOPTION;⚠️
WITH GRANT OPTION允许被授权者将权限再授予他人。
7. 撤销权限（REVOKE）
7.1 撤销系统权限
REVOKEprivilegeFROMuser_or_role;案例：
REVOKECREATETABLEFROMscott;7.2 撤销对象权限
REVOKEobject_privilegeON[schema.]objectFROMuser_or_role;案例：
REVOKESELECTONhr.employeesFROMscott;🔁 撤销权限会级联生效（即使通过
WITH GRANT OPTION转授的也会失效）。
8. 查询用户与权限
常用数据字典视图：
| 视图 | 说明 |
|---|---|
DBA_USERS | 所有用户信息（需 DBA 权限） |
USER_USERS | 当前用户信息 |
DBA_SYS_PRIVS | 系统权限分配 |
DBA_TAB_PRIVS | 对象权限分配 |
USER_SYS_PRIVS | 当前用户的系统权限 |
USER_TAB_PRIVS | 当前用户的对象权限 |
案例查询：
-- 查看 scott 用户的系统权限SELECT*FROMDBA_SYS_PRIVSWHEREGRANTEE='SCOTT';-- 查看谁可以访问 hr.employees 表SELECTGRANTEE,PRIVILEGEFROMDBA_TAB_PRIVSWHEREOWNER='HR'ANDTABLE_NAME='EMPLOYEES';9. 角色管理
9.1 角色简介
- 角色是一组权限的集合，可简化权限管理。
- 用户可被授予多个角色，角色也可被授予其他角色（避免循环）。
9.2 预定义角色
CONNECT：基本连接权限（含CREATE SESSION）RESOURCE：开发常用权限（含CREATE TABLE,CREATE SEQUENCE等）DBA：全部系统权限（谨慎使用！）
💡 Oracle 12c+ 中，
CONNECT和RESOURCE已精简，建议显式授权。
9.3 创建角色与授权
语法：
CREATEROLE role_name[NOTIDENTIFIED|IDENTIFIEDBYpassword];案例：
-- 创建开发角色CREATEROLE dev_role;-- 授予角色权限GRANTCREATESESSION,CREATETABLE,CREATEVIEWTOdev_role;-- 将角色授予用户GRANTdev_roleTOscott;🔐
IDENTIFIED BY password表示角色需密码激活（较少用）。
9.4 管理角色
-- 启用/禁用角色（会话级）SETROLE dev_role;-- 启用SETROLE NONE;-- 禁用所有角色（除 DEFAULT）-- 设置默认角色（登录自动启用）ALTERUSERscottDEFAULTROLE dev_role;-- 删除角色DROPROLE dev_role;9.5 查询角色与权限
-- 用户拥有的角色SELECT*FROMDBA_ROLE_PRIVSWHEREGRANTEE='SCOTT';-- 角色包含的系统权限SELECT*FROMROLE_SYS_PRIVSWHEREROLE='DEV_ROLE';-- 角色包含的对象权限SELECT*FROMROLE_TAB_PRIVSWHEREROLE='DEV_ROLE';10. 资源配置 PROFILE
10.1 PROFILE 简介
- 用于限制用户资源使用和密码策略。
- 每个用户只能关联一个 PROFILE。
10.2 使用 PROFILE 管理密码
常见参数：
FAILED_LOGIN_ATTEMPTS：失败登录次数限制PASSWORD_LOCK_TIME：锁定时间（天）PASSWORD_LIFE_TIME：密码有效期（天）PASSWORD_REUSE_TIME：密码重用间隔
案例：创建安全 PROFILE
CREATEPROFILE secure_profileLIMITFAILED_LOGIN_ATTEMPTS3PASSWORD_LOCK_TIME1PASSWORD_LIFE_TIME90PASSWORD_REUSE_TIME365;10.3 使用 PROFILE 管理资源
常见参数：
SESSIONS_PER_USER：并发会话数CPU_PER_SESSION：CPU 秒数/会话CONNECT_TIME：连接总时长（分钟）IDLE_TIME：空闲超时（分钟）
案例：
ALTERPROFILE secure_profileLIMITSESSIONS_PER_USER2CPU_PER_SESSION3600IDLE_TIME30;10.4 维护 PROFILE
-- 将 PROFILE 分配给用户ALTERUSERscott PROFILE secure_profile;-- 修改现有 PROFILEALTERPROFILEdefaultLIMITFAILED_LOGIN_ATTEMPTS5;-- 删除 PROFILE（不能删除正在使用的）DROPPROFILE secure_profile;10.5 显示 PROFILE 信息
-- 查看所有 PROFILE 限制SELECT*FROMDBA_PROFILESWHEREPROFILE='SECURE_PROFILE';-- 查看用户使用的 PROFILESELECTUSERNAME,PROFILEFROMDBA_USERSWHEREUSERNAME='SCOTT';三、综合性实战案例
场景：为一家公司搭建开发与测试环境
目标：
- 创建开发人员用户
dev1、dev2 - 创建测试人员用户
tester1 - 设置角色：
developer_role、tester_role - 应用安全 PROFILE
- 授权访问 HR 模式下的表
步骤代码（以 SYSDBA 身份执行）：
-- 1. 创建安全 PROFILECREATEPROFILE app_user_profileLIMITFAILED_LOGIN_ATTEMPTS3PASSWORD_LOCK_TIME1PASSWORD_LIFE_TIME60SESSIONS_PER_USER2IDLE_TIME20;-- 2. 创建角色CREATEROLE developer_role;CREATEROLE tester_role;-- 3. 授予角色权限-- 开发者：可建表、查 HR 数据、建视图GRANTCREATESESSION,CREATETABLE,CREATEVIEWTOdeveloper_role;GRANTSELECT,INSERT,UPDATE,DELETEONhr.employeesTOdeveloper_role;GRANTSELECTONhr.departmentsTOdeveloper_role;-- 测试者：只读 HR 数据GRANTCREATESESSIONTOtester_role;GRANTSELECTONhr.employeesTOtester_role;GRANTSELECTONhr.departmentsTOtester_role;-- 4. 创建用户CREATEUSERdev1 IDENTIFIEDBYDevPass123 PROFILE app_user_profile;CREATEUSERdev2 IDENTIFIEDBYDevPass456 PROFILE app_user_profile;CREATEUSERtester1 IDENTIFIEDBYTestPass789 PROFILE app_user_profile;-- 5. 授予角色GRANTdeveloper_roleTOdev1,dev2;GRANTtester_roleTOtester1;-- 6. 设置默认角色（登录自动激活）ALTERUSERdev1DEFAULTROLE developer_role;ALTERUSERdev2DEFAULTROLE developer_role;ALTERUSERtester1DEFAULTROLE tester_role;-- 7. 验证SELECTGRANTEE,GRANTED_ROLEFROMDBA_ROLE_PRIVSWHEREGRANTEEIN('DEV1','DEV2','TESTER1');用户登录后操作示例（以 dev1 为例）：
-- 连接数据库-- sqlplus dev1/DevPass123@localhost:1521/XE-- 创建自己的表CREATETABLEmy_projects(id NUMBER,name VARCHAR2(100));-- 查询 HR 数据SELECT*FROMhr.employeesWHEREdepartment_id=50;-- 插入数据（因有 INSERT 权限）INSERTINTOhr.employees(...)VALUES(...);四、注意事项与最佳实践
- 最小权限原则：只授予必要权限。
- 避免直接授权给用户：优先使用角色管理权限。
- 定期审计权限：使用
DBA_SYS_PRIVS、DBA_TAB_PRIVS检查异常授权。 - PROFILE 是安全基线：强制密码策略和资源限制。
- 不要滥用 DBA 角色：开发/测试环境也应模拟生产权限模型。
✅ 本章内容覆盖 Oracle 用户、权限、角色、PROFILE 全生命周期管理，适用于 Oracle 12c/19c/21c。
如需进一步自动化脚本或与应用集成（如 JDBC 连接字符串配置），可继续扩展。
