Oracle 12c 无需高权限的V$LOCKED_OBJECT锁表查询替代方案
Oracle 12c 无高权限场景下表锁状态判断方案
现有异常的核心逻辑根因:
- Oracle默认定义者权限模式的存储对象,编译、运行阶段仅校验直接授予用户的权限,不识别任何通过角色(包括
SELECT_CATALOG_ROLE)获得的权限,这是即使会话可访问V$LOCKED_OBJECT、仍需临时授予SELECT_ANY_DICTIONARY才能编译存储对象的本质原因。 - 默认权限域下,存储过程内执行的动态SQL遵循和静态SQL一致的权限校验规则,因此会出现“TOAD会话可正常执行同语句、函数内执行报ORA-00942”的现象。
方案1:调整存储对象权限域为调用者权限
- 在自定义锁检测函数的定义中增加
AUTHID CURRENT_USER子句,将函数的运行权限域和当前会话保持一致,让函数可以继承会话中通过角色获得的权限,不需要单独授予高粒度系统权限。 - 参考函数定义片段:
CREATE OR REPLACE FUNCTION check_table_lock( p_table_name VARCHAR2, v_object_type VARCHAR2 DEFAULT 'TABLE' ) RETURN NUMBER AUTHID CURRENT_USER IS n_count NUMBER; BEGIN SELECT COUNT(*) INTO n_count FROM V$LOCKED_OBJECT a INNER JOIN ALL_OBJECTS o ON a.OBJECT_ID = o.OBJECT_ID WHERE o.OBJECT_TYPE = v_object_type AND o.OBJECT_NAME = p_table_name; RETURN n_count; END; / - 落地效果:
- 编译阶段不会触发字典视图的权限校验,不需要协调临时高权限即可完成对象创建;
- 后续版本部署导致对象失效时,不需要额外权限即可完成重编译;
- 运行时继承现有
SELECT_CATALOG_ROLE的权限即可正常访问V$LOCKED_OBJECT,原有动态SQL的ORA-00942报错同步解决。
方案2:无字典视图依赖的锁探测实现
- 完全绕开动态性能视图的访问需求,通过短事务尝试获取目标表锁的方式判断锁定状态,不需要任何字典访问权限。
- 核心逻辑片段:
BEGIN -- 尝试无等待获取目标表的排他锁 EXECUTE IMMEDIATE 'LOCK TABLE '||p_table_name||' IN EXCLUSIVE MODE NOWAIT'; -- 锁获取成功说明无其他会话持有该表锁 ROLLBACK; -- 执行回滚立即释放锁,不影响业务逻辑 RETURN 0; EXCEPTION WHEN OTHERS THEN -- 捕获资源忙错误,说明表已被其他会话锁定 IF SQLCODE = -54 THEN RETURN 1; ELSE RAISE; END IF; END; - 落地效果:
- 仅需要目标表的常规访问权限(应用账号对自有schema下的表天然具备该权限),不需要任何字典视图、系统级权限;
- 不受角色权限在存储过程中失效的限制,静态、动态SQL均可稳定运行。
方案3:精准低粒度授权替代高权限
- 若不适合调整存储对象的权限模式,可由DBA做一次精准授权,替代高粒度的
SELECT_ANY_DICTIONARY权限,整个授权过程不需要长期开放高权限:-- 仅授予应用账号对锁信息动态视图的直接SELECT权限,粒度远低于系统权限 GRANT SELECT ON SYS.V_$LOCKED_OBJECT TO 应用账号; - 落地效果:
- 授权粒度最小,仅开放锁查询所需的单视图访问权,不开放其他字典表的访问能力;
- 授权完成后,存储对象编译、重编译都可以正常通过,不再需要临时协调权限。
内容的提问来源于stack exchange,提问作者Carl
相关产品推荐
相关产品推荐

