做了这么多年数据库运维我越来越觉得权限管理是整套 Oracle 体系里最“磨人”的一块。很多人以为用户建出来、grant 一下就算完事结果线上跑着跑着要么权限给太多出事要么业务突然报错缺权限一头扎进数据字典里查半天。前阵子我刚好在 19c 环境里把账号体系从头到尾捋了一遍趁热打铁把这套东西整理出来算是我这个入门教程系列里的第 13 讲主题锁定在用户管理与权限分配——语法、原理、实战一次讲透。这篇文章适合三类人刚接触 Oracle 想系统学账号体系的新手 DBA需要自己维护库的后端开发以及正在做数据库安全加固的运维同学。文章里所有语法我都基于 Oracle 19c 实测过案例可以直接抄但我会连“为什么这么做”一起讲清楚——因为只背语法是走不远的你得知道每个参数背后在防什么事。1. 先把概念理清楚用户、Schema、权限到底是什么关系1.1 用户不等于账号用户与 Schema 的对应关系很多初学者会把“用户”和“数据库账号”划等号这在 Oracle 里是不够准确的。一个 Oracle 用户User在创建之后系统会自动给他配套一个同名 Schema。Schema 是什么呢简单说就是用户所拥有的数据库对象的集合包括表、视图、存储过程、序列、索引等等。你可以把 Schema 理解为用户的一间专属仓库用户往里面放的每一张表、每一个视图都算这间仓库里的资产。这个对应关系带来一个非常实用的推论访问别人的表本质上是在访问别的 Schema 下的对象。默认情况下用户访问自己的对象不需要任何额外授权但如果一个用户想读另一个用户的表光有数据库连接权限是不够的必须由对象的所有者或 DBA 显式授予对象权限。这个机制贯穿整个 Oracle 权限体系后面我在实战部分会反复用到。需要特别注意的是Oracle 里没有 MySQL 中那种“数据库Database”与“用户”完全分离的概念。很多人从 MySQL 转过来习惯性地问“这个用户属于哪个库”在 Oracle 里这个问题的答案就是用户就是库的边界一个用户及其 Schema 就构成了一整套独立的数据空间。实例启动后所有用户共享同一个物理数据库但逻辑上彼此隔离靠权限来划定边界。1.2 权限的两大分类系统权限与对象权限Oracle 的权限大体分两类这个分类一定要烂熟于心。系统权限System Privilege指的是操作数据库系统级能力的权限比如能不能连接数据库CREATE SESSION、能不能在自己的 Schema 里建表CREATE TABLE、能不能建视图CREATE VIEW、能不能建存储过程CREATE PROCEDURE。这类权限控制的是“你有没有资格做某个动作”跟具体的表、视图无关。Oracle 19c 里系统权限有 170 多种大多数我们根本用不上日常见到的核心权限不超过二十种。对象权限Object Privilege指的是针对某一个具体对象的操作权限比如能不能查某张表SELECT ON scott.emp、能不能往某张表插数据INSERT ON scott.emp、能不能执行某个存储过程EXECUTE ON pkg_xxx。这类权限控制的是“你能对某个具体的东西做什么”。用生活场景类比系统权限像是公司门禁卡决定你能进哪些办公区域对象权限像是保险柜的钥匙决定你能打开哪个抽屉。门禁卡给你再多没有对应抽屉的钥匙你也拿不到里面的文件钥匙给你再多连公司大门都不让你进白搭。所以实际工作中两个维度永远要配合着看。1.3 角色权限的“套餐包”如果每个用户都要一条条去授权那 DBA 的工作量会非常可怕。比如一个项目组有 20 个开发每个人都得给 SELECT、INSERT、UPDATE、DELETE 一堆表权限万一中间某个表要加权限你难道要改 20 次Oracle 提供了一种机制来解决这个问题——角色Role。角色本质上就是一个权限集合的命名容器。你可以把一组权限打包成一个角色再把角色授予多个用户。后续只需要修改角色的权限所有拥有该角色的用户就自动跟着变不用逐个调整。这套机制跟 Linux 里用户组的概念非常像组里有权限用户加入组就继承了权限改组的权限所有人同步生效。角色还支持嵌套一个角色里可以包含另一个角色这为我们后面做分层权限设计提供了很大便利。2. 用户管理实战从创建到删除的全流程语法详解2.1 创建用户 CREATE USER每个参数背后都有讲究用户管理里最基础的操作就是创建用户语法看起来不复杂但实际生产环境里每个参数都值得认真对待。Core 语法如下CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE tbs_name TEMPORARY TABLESPACE temp_tbs QUOTA { UNLIMITED | size_clause } ON tbs_name PROFILE profile_name PASSWORD EXPIRE ACCOUNT { LOCK | UNLOCK };我按参数拆开说明。IDENTIFIED BY password指定登录密码。这里有一个 19c 的细节数据库默认开启了密码复杂度校验由ORA_STIG_PROFILE或默认的DEFAULT配置项控制太简单的密码会被直接拒绝。我用新库测试时尝试过123456直接被提示违反密码复杂度。生产环境请务必保留这个特性密码长度、大小写、特殊字符组合是最基础的安全防线。DEFAULT TABLESPACE指定用户创建对象时默认使用的表空间。如果不指定Oracle 在 19c 里会默认指向数据库的默认表空间通常是 USERS 或 SYSTEM。绝大多数情况下你应该显式指定让不同业务的数据落在不同表空间里既便于容量管理也能避免把 SYSTEM 表空间塞满。TEMPORARY TABLESPACE指定用户的临时表空间用于排序、分组、去重等操作产生的临时数据。19c 默认是 TEMP一般不用动但如果某个业务有特别大的排序需求可以考虑单独建一个大一些的临时表空间给它。QUOTA配额也就是允许用户在某个表空间里最多使用多少空间。这个参数太关键了很多线上事故都是配额的锅。不给配额用户即使有 CREATE TABLE 权限也建不了表会报 ORA-01950给 UNLIMITED 又等于放弃了空间管控。我给应用账号分配配额的标准做法是预估半年到一年的数据增长量再乘 1.5 的缓冲系数后期按需调整。PASSWORD EXPIRE让用户首次登录时必须修改密码。这个选项在创建一次性初始化账号时特别好用避免里面写死一个长期不换的密码。ACCOUNT LOCK/UNLOCK创建后立即锁定或解锁账户。默认是 UNLOCK但如果你创建的是备用账号或者需要审批后才能使用的账号可以先 LOCK 住等审批通过再解锁。下面给一个实际创建用户的案例包含业务场景。CREATE USER app_erp IDENTIFIED BY Erp#2024_Strong DEFAULT TABLESPACE tbs_erp TEMPORARY TABLESPACE temp QUOTA 1024M ON tbs_erp PROFILE default PASSWORD EXPIRE ACCOUNT UNLOCK;这个案例里我做了几件事指定了独立表空间 tbs_erp给了 1GB 配额强制首次登录改密码。这样一个应用账号的雏形就出来了。2.2 修改用户 ALTER USER改密码、锁账户、调配额用户建好之后修改是常态。ALTER USER 的语法和 CREATE USER 高度相似但有几个独立操作必须单独记。修改密码ALTER USER app_erp IDENTIFIED BY New_Pass_2024;注意 19c 里如果开了密码复杂度校验新密码同样要满足复杂度要求。另外提醒一句任何通过 ALTER USER 改密码的操作都会影响正在使用的长连接如果应用使用的是连接池旧的连接可能还会保持旧密码会话直到连接池重建。所以生产环境改密码一定要选择维护窗口并且改完通知应用团队重启应用或刷新连接池。锁定与解锁账户-- 锁定账户 ALTER USER app_erp ACCOUNT LOCK; -- 解锁账户 ALTER USER app_erp ACCOUNT UNLOCK;这个操作在应对异常登录、员工离职、应用下线时非常常用。我经历过一个案例一个外包同事离职后他的账号还在应用服务器配置里应用一直用他的账号连数据库后来我们安全巡检发现异常登录直接用 LOCK 锁掉应用立即报警才暴露了这个隐藏账号。所以定期排查账号状态是必须的。调整配额ALTER USER app_erp QUOTA 2048M ON tbs_erp;配额的调整一般不需要重启应用新会话立即生效。但要注意如果你把配额调小用户已经占用的空间不会强制回收只是超过新配额后无法再分配新空间。这个逻辑跟 Linux 磁盘配额是类似的。2.3 删除用户 DROP USERCASCADE 什么时候必须用删除用户有两种情况用户名下没有任何对象和用户名下有一堆表、视图、存储过程。前者直接 DROP 即可DROP USER app_erp;但绝大多数情况下我们要删的用户名下都有对象这时必须带上 CASCADEDROP USER app_erp CASCADE;CASCADE 的作用是连带删除该用户 Schema 下的所有对象。这里要极其谨慎一旦执行这个用户的所有表数据都会被永久删除没有任何回收站机制可以兜底。虽然 Oracle 有回收站Recycle Bin但 DROP USER CASCADE 不是简单的 DROP TABLE我实测时发现回收站里并不会完整保留所有对象恢复难度极高。所以永远要在执行前做好两件事第一用数据泵expdp完整备份该用户的 schema第二确认没有其他用户引用了这个 Schema 下的对象否则删完之后别人会报 ORA-00942。另外还有一个高频报错ORA-01940提示无法删除当前已连接的用户。这是因为该用户有活跃会话你需要先杀掉会话或让应用下线再执行 DROP。清理会话的经典做法是-- 查看该用户的会话 SELECT sid, serial# FROM v$session WHERE username APP_ERP; -- 杀掉会话请谨慎操作 ALTER SYSTEM KILL SESSION sid,serial# IMMEDIATE;2.4 用户信息查询DBA_USERS 等常用视图刚入门的人经常不知道该查哪个视图我直接给出一张非常实用的速查表视图名称主要用途关键字段DBA_USERS查询所有用户的基础信息USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE, ACCOUNT_STATUS, EXPIRY_DATEALL_USERS查询所有可见用户名不含密码等敏感信息USERNAME, CREATEDUSER_USERS查询当前登录用户自身的信息USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACEDBA_TS_QUOTAS查询用户在表空间上的配额使用情况USERNAME, TABLESPACE_NAME, BYTES, MAX_BYTES我最常用的排查语句是查账户状态和配额-- 查看账户状态、过期时间、默认表空间 SELECT username, account_status, expiry_date, default_tablespace, temporary_tablespace, profile FROM dba_users WHERE username APP_ERP; -- 查看用户在表空间上的配额和实际使用量 SELECT tablespace_name, bytes/1024/1024 used_mb, max_bytes/1024/1024 quota_mb FROM dba_ts_quotas WHERE username APP_ERP;这里ACCOUNT_STATUS字段的值非常值得注意常见的有OPEN正常LOCKED被手动锁定EXPIRED密码过期EXPIRED LOCKED密码过期且账户被锁LOCKED(TIMED)因连续登录失败被自动锁定由 FAILED_LOGIN_ATTEMPTS 配置项控制19c 默认的 Profile 对连续失败密码尝试做了限制连续输错多次会自动锁账户这能有效防止暴力破解。但反过来如果应用里配置的密码被改掉了应用连接池就会一直重试最终把账号打成 LOCKED(TIMED)。所以一旦发现应用登录大面积失败先别急着改密码先看 account_status大概率是被自动锁定了。3. 权限分配核心语法GRANT 与 REVOKE 的完整体系3.1 系统权限授权GRANT 的两种写法和 WITH ADMIN OPTION 的坑系统权限的授予语法非常简单GRANT privilege_name TO user_or_role;一次性授予多个权限GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_erp;这里有一个经常被忽略的选项WITH ADMIN OPTION。GRANT CREATE TABLE TO app_erp WITH ADMIN OPTION;它的含义是被授权者不但自己拥有了 CREATE TABLE 权限还可以把 CREATE TABLE 这个权限再转授给其他用户。这个选项在日常授权时我建议不轻易使用因为它把授权能力也交出去了。一旦某个拥有 ADMIN OPTION 的账号被攻破攻击者就可以给自己或者任意新用户授予高权限整个权限体系就失去控制了。更重要的一点是系统权限的回收是“断链不追溯”的。意思是DBA 把 CREATE TABLE 授给 A带 ADMIN OPTIONA 又把它授给 B。此时 DBA 从 A 身上回收了 CREATE TABLEB 的权限不受影响B 依然持有 CREATE TABLE。这跟对象权限的级联回收机制完全不同很多人在这里栽过跟头。所以我会刻意避免给普通账号 ADMIN OPTION从源头上杜绝这种“权限链失控”的可能。特权用户常用的一句授权是GRANT UNLIMITED TABLESPACE TO app_erp;UNLIMITED TABLESPACE 是一个特殊的系统权限它让用户可以无限制使用任何表空间。给了这个权限之前设的 QUOTA 配额就形同虚设了。我的原则除非这个账号确实需要管理表空间比如 DBA 运维账号否则一律不给 UNLIMITED TABLESPACE空间大小全交给 QUOTA 来控制。3.2 对象权限授权细粒度的 SELECT / INSERT / UPDATE / DELETE对象权限是日常开发中接触最多的一类授权典型语法-- 只授权查询 GRANT SELECT ON scott.emp TO app_erp; -- 授权增删改查 GRANT SELECT, INSERT, UPDATE, DELETE ON scott.emp TO app_erp; -- 授权执行存储过程 GRANT EXECUTE ON pkg_erp_utils TO app_erp;对象权限一共有 8 类最常用的就是 SELECT、INSERT、UPDATE、DELETE、EXECUTE还有 ALTER改表结构、INDEX在表上建索引、REFERENCES建外键引用表。需要特别说明的是对象权限的“所有者”概念让授权链变得复杂。对表scott.emp授权必须由表的属主 scott 来执行或者由拥有GRANT ANY OBJECT PRIVILEGE系统权限的 DBA 来执行。如果 scott 把 SELECT 权限授给 A带 WITH GRANT OPTIONA 就能把 SELECT ON scott.emp 再授给别人。对象权限的回收是级联的。同样场景DBA 或 scott 回收 A 的 SELECT 权限时A 转授出去的权限会一并被回收这就是对象权限与系统权限最大不同的地方。做个图记在脑子里系统权限回收是“斩草不除根”对象权限回收是“连根拔起”。再看一个非常实用的场景视图授权。我们经常不希望把整张表的字段暴露给下游应用做法是先创建一个视图只包含需要的字段和行然后授权视图而不是底层表。-- 创建视图屏蔽敏感列 salary CREATE VIEW scott.emp_public AS SELECT empno, ename, job, hiredate, deptno FROM scott.emp; -- 只授视图的查询权限 GRANT SELECT ON scott.emp_public TO app_report;这样做的好处是应用只能看到视图暴露的字段底层新增字段也影响不到它。我在数据安全要求比较高的项目里对客户信息、薪资信息一律采用这个方案比直接授权表要稳得多。3.3 列级权限只让看该看的字段有些场景连视图都不想建只想实现在某一张表上只允许用户看其中几列。Oracle 支持列级别授权GRANT SELECT (empno, ename, job, deptno) ON scott.emp TO app_report; GRANT UPDATE (job, deptno) ON scott.emp TO hr_admin;这个语法比较容易被忽略但它是细粒度控制里非常锐利的工具。授权后用户app_report可以查询 empno、ename、job、deptno 这几列但查 salary 列会直接报错。这种方式省去了建视图的开销权限逻辑也更直观缺点是列权限的组合查起来稍微麻烦一些需要查DBA_COL_PRIVS视图。需要注意列级授权和对象级授权是两套授权记录。如果先给了对象级 SELECT又给了列级 SELECT两者会同时生效如果你想用列级权限来“缩小”已有权限范围这是做不到的——授权只会增加能力不会削减能力。要缩小范围必须先回收对象级权限重新做列级授权。这个优先级关系很多人会搞混实际场景里我建议是先规划清楚再统一执行。3.4 回收权限 REVOKE 的使用注意事项回收权限的语法和授权对应-- 回收系统权限 REVOKE CREATE TABLE FROM app_erp; -- 回收对象权限 REVOKE SELECT ON scott.emp FROM app_report;回收对象权限时还有一个细节如果你只授权了 SELECT回收也只用 REVOKE SELECT 即可不需要把 INSERT、UPDATE、DELETE 一起列出来。但如果你的本意是收回全部对象权限可以直接写REVOKE ALL ON scott.emp FROM app_report;这里 ALL 仅限于本次授权过的对象权限不会越权去回收 DBA 层面的权限。另外要记住REVOKE 回收的是授权记录不是一个“状态开关”。比如某张表的 SELECT 权限是从角色那儿继承来的你直接执行REVOKE SELECT ON scott.emp FROM app_report往往会报 ORA-01952因为 app_report 并没有被直接授予过这个对象权限——它是通过角色拿到的。这时候要回收应该从角色身上处理而不是用户身上。这个区别在排障时极其重要。我经办过一个事故应用账号一直能查一张表运维以为是安全漏洞反复执行 REVOKE 怎么都成功不了后来查 DBA_TAB_PRIVS 才发现权限根本不是直接授予的而是来自 CONNECT 之外的自定义角色。所以排障第一步永远是先查权限链路看清楚授权路径是大盘底下的哪一层。4. 角色管理把权限分配变成搭积木4.1 为什么说角色是权限管理的“必杀技”如果你管理的用户不超过五个直接给用户授权也没问题。但生产环境动辄几十个账号、上百张表逐条授权的管理成本会彻底失控。角色能带来的三个直接好处批量一致性一个角色的权限变更对所有拥有该角色的用户同时生效。责任隔离不同岗位、不同应用使用不同角色出问题时根据角色就能快速定位权限来源。收放自如临时调整时把一个角色加进去或移除比逐条去改用户权限快一个量级。我在团队里定义角色时会遵守一个习惯角色的命名要能直接说明用途比如ROLE_ERP_APP、ROLE_ERP_READONLY、ROLE_HR_ADMIN。不要在角色名里用ROLE1、ROLE2这种毫无信息量的命名三个月后你自己都会忘。4.2 创建角色与授权实战角色从创建到投入使用完整流程是三步第一步创建角色CREATE ROLE ROLE_ERP_READONLY;如果希望这个角色本身也需要密码才能使用相当于一个受保护的权限组可以加上 IDENTIFIED BY但日常我很少这样用因为会让应用连接变得复杂。第二步给角色装箱权限-- 系统权限 GRANT CREATE SESSION TO ROLE_ERP_READONLY; -- 对象权限 GRANT SELECT ON scott.emp TO ROLE_ERP_READONLY;第三步把角色授予用户GRANT ROLE_ERP_READONLY TO app_report;完成之后app_report 一登录就拥有了 ROLE_ERP_READONLY 包内的全部权限。后续如果业务部门要求 app_report 也能查另一张新表你只需要加一行GRANT SELECT ON scott.dept TO ROLE_ERP_READONLY;所有拥有这个角色的用户立即生效不用再挨个去授权。这就是角色的核心价值。4.3 系统预定义角色该怎么用Oracle 安装完成后系统自带了一批预定义角色最常听到的就是 CONNECT、RESOURCE、DBA。很多教程图省事直接GRANT DBA TO 新用户这在我看来是权限管理里最坏的习惯之一。先看清这三个角色的本质CONNECT在 19c 里只剩 CREATE SESSION 这一个权限等于只允许登录非常干净。RESOURCE包含 CREATE TABLE、CREATE SEQUENCE、CREATE VIEW、CREATE PROCEDURE、CREATE TRIGGER 等基本上是给开发人员在自己 Schema 里建对象的权限集合。DBA包含大量系统权限近似于数据库管理员的全权账号。我的建议是普通应用账号绝对不要直接授予 DBA。哪怕是内部开发库给 DBA 也等于把整个库交出去了。正确思路是让应用账号拥有 CREATE SESSION登录 自定义角色业务所需的对象权限再根据是否需要建表等开发需求决定是否给 RESOURCE。这里补充一个 19c 的细节Oracle 12c 以后引入了 PDB可插拔数据库架构在 PDB 里你看到的用户和角色是 PDB 内的全局用户则要加C##前缀。这篇文章里的所有语法在 PDB 内同样适用只要你当前连接的是对应 PDB。如果你是在 CDB 里操作创建公共用户需要CREATE USER C##APP_ERP IDENTIFIED BY Erp#2024_Strong;4.4 默认角色与 SET ROLE控制会话内的权限生效范围一个用户可以被授予多个角色但默认情况下他登录时所有被授予的角色都会激活。有些角色我们可能希望“平时默认不激活只有在特定会话里临时启用”该怎么做呢给用户授权时使用状态控制-- 授予角色但设为非默认角色 GRANT ROLE_ERP_ADMIN TO app_erp; ALTER USER app_erp DEFAULT ROLE ALL EXCEPT ROLE_ERP_ADMIN;这样 app_erp 登录后ROLE_ERP_ADMIN 默认不会激活它的高权限不会随会话自动生效。等到确实要做管理员操作时自己在会话里启用SET ROLE ROLE_ERP_ADMIN;启用后当前会话才拥有这个角色的权限。这种做法特别适合“平时只能日常操作、需要时才提权”的运维账号相当于给高权限角色加了一道“手动开关”。注意使用 SET ROLE 需要该用户被直接授予对应角色且角色没有被设置密码保护否则会失败。5. 实战案例一套可落地的应用账号权限方案5.1 需求分析与方案设计理论讲完我拿一个实际需求把整套流程串一遍。需求背景一个 ERP 系统要接入数据库团队分为三类账号应用账号 app_erp应用服务器连接使用需要读写 ERP 相关的十几张表能执行两个存储过程不需要建表。报表账号 rpt_erp报表系统使用只需要只读查询且只能看部分列不能看到成本、薪资类敏感字段。开发账号 dev_erp开发人员做日常迭代使用需要在自己的 Schema 里建表、建视图、写过程同时需要读测试环境的核心表。这个需求非常典型我的方案设计遵循三个原则最小权限每个账号只给完成业务必需的最小权限集合不做任何提前预支。职责分离读写、只读、开发三类角色完全分开不允许互相覆盖。可审计所有授权路径都做成角色后续通过数据字典能快速追踪权限来源。5.2 实施 SQL 全流程第一步准备表空间CREATE TABLESPACE tbs_erp DATAFILE /u01/app/oracle/oradata/ORCL/tbs_erp01.dbf SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 20G;第二步创建账号并设置配额CREATE USER app_erp IDENTIFIED BY Erp#2024_Strong DEFAULT TABLESPACE tbs_erp TEMPORARY TABLESPACE temp QUOTA 2048M ON tbs_erp; CREATE USER rpt_erp IDENTIFIED BY Rpt#2024_Strong DEFAULT TABLESPACE tbs_erp TEMPORARY TABLESPACE temp QUOTA 512M ON tbs_erp; CREATE USER dev_erp IDENTIFIED BY Dev#2024_Strong DEFAULT TABLESPACE tbs_erp TEMPORARY TABLESPACE temp QUOTA 1024M ON tbs_erp;第三步定义角色CREATE ROLE ROLE_ERP_APP; CREATE ROLE ROLE_ERP_REPORT; CREATE ROLE ROLE_ERP_DEV;第四步给角色分配权限应用角色读写 执行存储过程GRANT CREATE SESSION TO ROLE_ERP_APP; GRANT SELECT, INSERT, UPDATE, DELETE ON scott.orders TO ROLE_ERP_APP; GRANT SELECT, INSERT, UPDATE, DELETE ON scott.order_items TO ROLE_ERP_APP; GRANT SELECT, UPDATE ON scott.products TO ROLE_ERP_APP; GRANT EXECUTE ON pkg_erp_order TO ROLE_ERP_APP;报表角色只读且只能看部分列。这里我采用视图加细粒度列的方案-- 先创建只读视图隐藏敏感列 CREATE OR REPLACE VIEW scott.v_order_summary AS SELECT order_id, customer_name, order_date, order_status, total_amount FROM scott.orders; GRANT CREATE SESSION TO ROLE_ERP_REPORT; GRANT SELECT ON scott.v_order_summary TO ROLE_ERP_REPORT; GRANT SELECT (order_id, customer_name, order_date, order_status, total_amount) ON scott.orders TO ROLE_ERP_REPORT;开发角色允许在自己的 Schema 建对象并授予对核心表的只读访问GRANT CREATE SESSION TO ROLE_ERP_DEV; GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO ROLE_ERP_DEV; GRANT SELECT ON scott.products TO ROLE_ERP_DEV; GRANT SELECT ON scott.categories TO ROLE_ERP_DEV;第五步把角色授予用户GRANT ROLE_ERP_APP TO app_erp; GRANT ROLE_ERP_REPORT TO rpt_erp; GRANT ROLE_ERP_DEV TO dev_erp;到这里三个账号的权限边界已经清晰账号登录建表读写业务表只读报表执行存储过程app_erp是否是否是rpt_erp是否否是否dev_erp是是仅查询部分表否否5.3 验证权限是否生效授权完成后不能光看回显成功就以为完事我习惯逐项验证。先用 app_erp 登录确认基础权限-- 确认当前会话激活的角色和权限 SELECT * FROM session_roles; SELECT * FROM session_privs;再用 rpt_erp 验证只读权限是否“真的只读”-- 应该报错 ORA-01031: insufficient privileges INSERT INTO scott.v_order_summary (order_id) VALUES (99999);用 dev_erp 验证建表能力CREATE TABLE dev_erp.test_tmp(id NUMBER);这种逐项验证能提前发现授权遗漏。我自己踩过最典型的坑就是“给了 SELECT但视图的创建者没有把底层表权限传出来”结果应用一查视图就报 ORA-01720这种案例等第 6 节详细展开。6. 常用查询与故障排查速查6.1 权限与用户的常用 SQL 汇总日常运维中有一批查询 SQL 可以做成“肌肉记忆”遇到权限问题直接搬出来用-- 1. 查询用户拥有的系统权限 SELECT * FROM dba_sys_privs WHERE grantee APP_ERP; -- 2. 查询用户拥有的对象权限 SELECT owner, table_name, privilege, grantable FROM dba_tab_privs WHERE grantee APP_ERP; -- 3. 查询用户拥有的角色以及角色下的权限 SELECT granted_role, admin_option FROM dba_role_privs WHERE grantee APP_ERP; SELECT privilege FROM role_sys_privs WHERE role ROLE_ERP_APP; SELECT owner, table_name, privilege FROM role_tab_privs WHERE role ROLE_ERP_APP; -- 4. 查询列级权限 SELECT * FROM dba_col_privs WHERE grantee RPT_ERP; -- 5. 查看某张表的所有授权情况排查“谁能看到这张表” SELECT grantee, privilege, grantable FROM dba_tab_privs WHERE ownerSCOTT AND table_nameEMP; -- 6. 查看当前会话实际生效的权限 SELECT * FROM session_privs;这里我额外分享一个排查技巧有时候用户反馈“我明明查这张表没权限”但你检查DBA_TAB_PRIVS又有记录导致口径冲突。这时候很可能用户连接的是另一个用户或者处于不同的 PDB/CDB 上下文。先确认当前会话身份SELECT SYS_CONTEXT(USERENV, CURRENT_USER) AS current_user, SYS_CONTEXT(USERENV, CON_NAME) AS container_name, SYS_CONTEXT(USERENV, DB_NAME) AS db_name FROM dual;6.2 高频错误码解析用户管理和权限分配相关的报错日常遇到最多的就是下面几个我把典型场景和标准解法列成一张表错误码含义与典型场景标准解法ORA-01017用户名或密码错误登录被拒绝确认密码或用 ALTER USER 重置检查账号是否锁定ORA-01950对表空间无权限常见于建表时配额不足给用户在对应表空间增加 QUOTA或授予 UNLIMITED TABLESPACE但首选前者ORA-00942表或视图不存在可能真的不存在也可能只是没有权限用 DBA_TAB_PRIVS 查授权很多情况下是授权漏了ORA-01031权限不足执行操作时缺少系统权限或对象权限逐项检查 session_privs 和 dba_tab_privs补齐最小权限ORA-01920用户名与角色名冲突改名或者先 DROP 冲突的角色/用户后再创建ORA-01940无法删除当前正在连接的用户先 kill 会话再执行 DROP USERORA-01720创建视图时对底层对象授权不足影响视图创建确认视图创建者对底层对象具有所需权限并考虑是否需要 WITH GRANT OPTIONORA-01952回收权限对象不存在常见于尝试回收经由角色获得的权限不要直接对用户 RECOVOKE改为处理角色本身的权限每个错误码背后都有一些“衍生场景”。比如 ORA-01950 不只是新手会遇到我见过一个应用跑得好好的一年突然开始报这个错排查半天发现是业务表空间的数据文件自动扩展达到上限DBA 给用户加的是“无限 QUOTA”但实际上表空间本身满了。所以遇到 ORA-01950除了查用户配额还要同时查表空间使用率SELECT tablespace_name, bytes/1024/1024 MB FROM dba_data_files WHERE tablespace_name TBS_ERP;6.3 实操心得与避坑清单最后这部分我分享几个真实做事过程中沉淀下来的心得每一条都是花过代价换来的。第一养成“先查询、后授权”的习惯。不管是新用户上线还是权限变更先把当前环境的权限快照查出来再把变更 SQL 放到测试库执行最后在生产库操作。尤其建议在最后加上验证 SQL把实际操作和验证放在同一个脚本文件里留存变更记录。这样在出问题时能够回溯每一步。第二警惕权限授权脚本的隐性依赖。你授权了一个角色但这个角色内部可能又依赖另一个角色或者另一个对象。比如ROLE_ERP_APP里包含了GRANT EXECUTE ON pkg_erp_order而pkg_erp_order内部又访问了scott.orders此时如果scott没有给pkg_erp_order的所有者授予足够的底层表权限应用调用存储过程时会报 ORA-01031而不是创建角色时报错。这种“层层依赖导致的权限问题”最难排查我的方法是定期用递归查询把角色-权限树整个拉出来审计。第三把密码策略和生命周期管理纳入自动化脚本。别只盯着建用户、授权密码过期策略、连续失败锁定、空闲会话超时这些往往比授权的优先级更高。我在 19c 里通常会把default的密码有效期设成 90 天锁定阈值设成 5 次ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME 90 FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION ora12c_verify_function;PASSWORD_VERIFY_FUNCTION这行用于启用 Oracle 自带的密码复杂度校验Oracle 19c 里默认的验证函数就是ora12c_verify_function。是不是所有库都能启用我在测试环境试过有些环境需要手动运行$ORACLE_HOME/rdbms/admin/utlpwdmg.sql脚本来创建验证函数如果你启用时报“函数不存在”先去跑一下这个脚本再试。第四权限变更在变更窗口做。权限回收REVOKE尤其要谨慎。对象权限的级联回收会让下游一堆账号权限消失这是一个“水波效应”。我曾经在非窗口时间调整过一个角色的对象权限结果所有通过这个角色拿下游权限的账号全部掉线业务线瞬间一片红。后来我给自己定了一个死规矩所有涉及 REVOKE 或角色改动的操作一律走变更审批流程并在维护窗口执行。宁可慢一点不要拿生产稳定性开玩笑。回到我自己的日常用户管理和权限分配这件事说难不难说简单也不简单。很多人觉得写几条 grant 就是懂权限管理但数据库安全的底线恰恰藏在这些不带任何效果的细节里配额给多少、ADMIN OPTION 要不要勾、角色怎么分层、密码策略怎么配、回收时会不会级联。希望这篇把语法、原理和案例揉在一起的整理能让你在 19c 里少踩几个坑。最后再分享一个小技巧如果你在一个环境里反复审批账号权限可以把第 5 节那套“建表空间、建用户、建角色、装箱、绑人、验证”的流程写成一套带变量的 SQL 模板每次改一下用户名和授权列表就行。我用这套模板之后新项目开账号的时间从半小时压缩到了五分钟而且出错的概率低了很多——因为每一步都是验证过的固定动作。模板的第一行一定是注释把当前变更单号、需求方、执行时间写清楚这是你将来翻旧账时最可靠的线索。