【异常处理】Oracle 添加索引造成其他用户对此表的查询权限丢失处理

问题描述

客户生产环境出现权限丢失的现象,沟通后得知,情况如下,用户B对用户A的tab1表有查询权限,某天,开发人员对用户A的tab1表添加索引IDX_XXX后,索引添加成功,同时用户B对用户A的tab1表查询权限也被收回,开发人员很确定,只操作了添加索引命令,并没有操作收回权限命令,类似情况出现2次,问题十分诡异,需要介入排查。
环境信息:
主机环境:Oracle Linux Server release 6.10
数据库:Oracle 11.2.0.4.180717 RAC

问题分析

与开发人员沟通,了解操作的具体步骤,以及操作表的详细信息,提取关键信息,如下:
1、表tab1上有主键,且新建索引IDX_XXX有3个字段,主键字段包含在内
2、开发人员通过PL/SQL Developer三方客户端工具,在表上通过编辑表,图形化操作添加主键
对沟通结果进行分析,可能出现的原因:
1、Oracle 自身BUG造成,需要进行验证
2、开发人员可能进行误操作,概率比较小,因出现过2次
3、PL/SQL Developer三方客户端工具BUG,操作了预期之外的命令
针对以上问题需要逐个验证。

信息追溯

通过和开发人员沟通,了解了问题的表象,作为一名数据库管理员,需要对信息进行再次认证,通过对数据库信息的搜集,得到2个有效信息。

表对象和索引修改时间

在11点13分32秒确实有创建索引操作,但是在11点13分38秒,表也有相关DDL操作,通过DDL时间可以得知。
注:表的LAST_DDL_TIME可以通过很多途径更新,比如添加/删除字段、添加字段comment,授权/收回权限等。
2.png

归档日志解析

数据库归档日志记录着几乎所有数据库的更新操作,通过解析归档日志,得到了更有效的信息,在创建索引之后,数据库随机进行了收回权限的操作,证实了,收回权限是命令触发的,至于命令是数据库自己生成,还是其他原因造成需要进行验证。
1.png

实践验证

既然无法确认是那种情况造成,那就亲自实践。

使用sqlplus进行验证

通过Oracle 原生的sqlplus并没有复现问题

1,创建测试用用户,并赋予权限。
root:
create user zsdba1 identified by 123456;
grant dba to zsdba1;
create user zsdba2 identified by 123456;
grant connect to zsdba2;

2,创建测试用表
conn zsdba1:
create table test001 (id number,name varchar2(20),age number);
alter table TEST001  add constraint PK1 primary key (ID);

3,目前由于没有赋予select权限,zsdba2无法访问zsdba1的test001.
conn zsdba2/123456:
select * from zsdba1.test001;
*
ERROR at line 1:
ORA-00942: table or view does not exist

4. 赋予zsdba2对zsdba1表的访问权限。
conn SYS:
GRANT SELECT ON zsdba1.test001 TO zsdba2;

5. 可以访问。
conn zsdba2/123456:
SQL> select * from zsdba1.test001;

no rows selected

6. 添加索引
conn zsdba1:
SQL> create index idx_test001 on test001(id,age);
Index created.

7. 访问没有问题,问题不再现
conn zsdba2/123456:
SQL> select * from zsdba1.test001;

no rows selected

使用PL/SQL Developer进行验证

1.创建测试表T_OBJECT1033
2.添加主键
alter table T_OBJECT1033add constraint PK333 primary key (OBJECT_ID);
3.授权
grant select on T_OBJECT1033 to zsdba2;
4.接下来一步比较重要
通过PL/SQL Developer进行一下操作

4.png
5.png
3.png
此时发现,PL/SQL Developer竟然生成一条收回权限的操作,经过几轮的测试,问题均会复现,至此,权限丢失的问题原因已经找到。
而且经过测试发现,每个表这种情况只会出现一次,就是说,第二次添加索引就不会有这种现象。

结论

通过以上的分析,实践验证,最终问题定位于PL/SQL Developer工具问题(可能BUG),与开发人员沟通,调整操作习惯,统一使用命令行添加索引。


数据运维技术 » 【异常处理】Oracle 添加索引造成其他用户对此表的查询权限丢失处理