足以摧毁整个应用的数据库设计–技术人生系列第二十八期-我和数据中心的故事

一个貌似简单却足以摧毁你整个应用的问题

绝对不是危言耸听，直接点开大图来看看，如果你看完觉得这个问题并无所谓而轻易放过，那么问题背后隐藏的巨大的风险就这样被你忽略了；这个问题就是这样，一个不经意的小环节，能使PLSQL Devloper出现问题，同样，也足够摧毁你整个应用！

01

背景介绍

这是在某客户的生产数据中心遇到的问题，该数据中心有专门的操作控制中心，备有大量的操作终端，用于统一维护数据中心的系统。

某天，客户DBA提问：应用维护团队使用操作终端上的PLSQL developer连入他们的某个用户查询数据时会报错（图1），报错后点击ok按钮PLSQL Developer就死掉了，但是如果使用sqlplus的方式登录上去做操作并不受影响，而且如果连入的是其他用户则不会出现这样的问题；

作为应用维护团队，经常会要查一些生产数据，甚至生成一些报表，那么各终端上的PLSQL Developer是他们的唯一能直连数据库的工具了，PLSQL Developer不能用，对他们来说影响还是蛮大的；从问题本身看，数据库的正常运行显然没有受到影响，但是对于应用维护来说，该库内单独某一用户无法使用PLSQL Dev来操作，即认为是数据库方面的问题。

有客户不能解决的问题，我们当然义不容辞！披上战袍，上场！！

看到这样的问题，我不禁记起前段时间出现的互联网上下载的PLSQL Dev使用后导致ORACLE数据库异常，以及最近出现的互联网上下载的ORACLE软件出现的数据库在运行一段时间后出现异常的情况，这里提醒各位读者，尽量从官方网站，正规渠道下载相关软件安装介质，避免给不法分子可乘之机。当然，我们这里肯定不是遇到了这个问题。

02

问题摆在眼前，这是哪一方面的问题呢？

首先需要总结一些基本的现象：

拿到iptXXX用户的密码，我们很轻易地重现问题，可以知道，在“SQL窗口”中我们发现不管是查询表/视图都会出现上述问题；

再使用其他用户，则不存在这方面的问题；

查询数据库整体，无明显的失效对象；

从报错”XXX must be declared”结合经验来看，基本定位为权限问题；

另外，我们还需要考虑这里问题的直接导火索，直接使用sqlplus登录做查询没有问题，而使用PLSQL Developer存在问题，那么PLSQL Developer在查询的时候做了什么额外的操作呢？要定位这个倒是很简单，通过相关会话的抓取其执行过的SQL我们就会看到下面语句：

begin

if :enable = 0 then

sys.dbms_output.disable;

else

sys.dbms_output.enable(:size);

end if;

end;

接下来，我们就试着在sqlplus命令窗口中执行相关过程：

而且，以上述方式执行其他的sys用户下各种包的其他存储过程，也存在同样问题；


对比之下，登录其他用户执行上述存储过程，则完全无问题。

对！没错！问题就是在调用sys用户的包上面！

03

诡异的权限

毕竟iptXXX用户是普通用户，sys用户的包岂能说调用就调用，先来看权限：

SQL>select privilege from dba_sys_privs where grantee=’IPTXXX’;

PRIVILEGE

—————————————-

CREATE VIEW

SELECTANY DICTIONARY

CREATE SYNONYM

UNLIMITED TABLESPACE

SQL> select GRANTED_ROLE from dba_role_privs wheregrantee=’IPTXXX’;

GRANTED_ROLE

——————————

RESOURCE

CONNECT

SQL> select role, privilege from role_sys_privs where privilege like ‘%EXECUTE%PROCEDURE%’;

ROLE PRIVILEGE

—————————— —————————————-

DBA EXECUTE ANY PROCEDURE

IMP_FULL_DATABASE EXECUTE ANY PROCEDURE

EXP_FULL_DATABASE EXECUTE ANY PROCEDURE

可以看到iptXXX用户的权限看上去没有什么异常，甚至比普通用户的权限还要多那么一些些，还给了一个额外的查看数据字典的权限，查询也没有问题；

而同时，我们简单的使用DESC命令去查看dbms_output包的所有过程/函数，是没有问题的！


另外，以个人操作习惯，我们通常在调用sys用户的包时，习惯上似乎很少加”sys.”前缀的，比如我们在普通用户收集自己表的统计信息，通常执行的是exec dbms_stats.gather_table_stats，那这里难道连统计信息收集都做不了了吗？

这样，就又多了一分诡异了，从结果上看，


带不带sys.前缀还有所不同了！




sys.dbms_output和dbms_output是什么关系呢？以前我们经常会调用各种sys用户的包，倒是并没有考虑过这个问题，不过这里我们遇到的问题，那就顺手了解了解吧；

SQL>selectobject_name,object_type,owner from dba_objects where object_name=’DBMS_OUTPUT’;

DBMS_OUTPUT PACKAGE SYS

DBMS_OUTPUT PACKAGE BODY SYS

DBMS_OUTPUT SYNONYM PUBLIC

SQL> select table_owner,table_name,owner fromdba_SYNONYMs where SYNONYM_NAME=’DBMS_OUTPUT’;

SYS DBMS_OUTPUT PUBLIC

原来，对于sys用户的包来说，在public下都有对应的同义词；

这样，问题就又奇怪了，DBMS_OUTPUT本是sys下的一个对象，使用PUBLIC的同义词可以访问，而在加了“sys.”前缀后却无法访问，这个意味着什么呢？看起来好像不是简单的权限问题了，难道是这个系统配置了什么特殊的安全策略，不允许该普通用户直接调用sys用户的包，而只能通过public的同义词么？

不解！！

04

分析的过程中，客户DBA又提示到，你看PLSQL Developer”命令窗口”做查询怎么就没事呢？

对此，我先是随口说了一句，那显然是因为它没有调用sys用户的包做操作啊，然而话说出来总觉说服不了自己，于是简单跟踪核查了一下，还真不是，在”命令窗口”也是有调用的；调用的语句如下：

begin :id :=sys.dbms_transaction.local_transaction_id; end;

显然，这里是调用成功了的！这是什么情况，难道dbms_transaction包是例外？并没有，我们再验证一下：

那这里我们来考虑一下区别在哪？显然，dbms_transaction.commit是一个procedure，而

dbms_transaction.

local_transaction_id是一个function；如此，推而广之再验证，确实发现sys用户的函数是可以被调用的，而procedure无法被带前缀的调用；

似乎又进一步验证了相关安全策略的问题，难道安全策略中还允许只能调用function，不能调用procedure？

脑子里一团浆糊！

05

各位看官似乎也要看乱了，问题到了这里，我们不妨来归纳总结一些，现在



我们到底遇到了什么问题：

1.desc dbms_output/desc sys.dbms_output都是没有问题的

2.exec dbms_output.disable是没有问题的（不带sys.前缀）

3.exec sys.dbms_output.disable是有问题的（procedure，带sys.前缀）

4.调用sys.dbms_transaction.local_transaction是没有问题的（function，带sys.前缀）

5.iptXXX用户权限没有看到明显的漏洞（对于一个package来说，权限还会分procedure和functioin 么？）

6.怀疑存在针对sys用户包的存储过程调用的特殊策略

原本以为很简单的问题，把所有现象列出来之后，我们才发现，问题似乎比我们想象的要复杂，最关键的是，找不到头绪？而且，生产环境的问题，容不得大家随意测试！各位看官，到这里，如果是你，你会如何判断，或者如何走向下一步呢？不如我们来思考思考。。。

06

方法总会有的

问题似乎查不下去了，那我们就需要反思一番，是否我们一开始把问题定位的太简单，就是权限问题，导致其影响到我们的判断，最终把我们带到了死胡同？我们何不抽离出来，换一种思考方式，摆脱我们的定式思维！

首先，PLS-00302/ORA-6500，就是一个报错而已，虽然我们觉得就是权限的错误，我们暂不考虑；对于一个错误来说，那我们不妨来先对即产生错误的进程做个errorstack跟踪：

SQL>oradebug setospid 3162340

Oraclepid: 55, Unix process pid: 3162340, image: oracle@XXXX(TNS V1-V3)

SQL>oradebug event 6550 trace name errorstack forever,level 12

Statement processed.

然后，拿到errorstack的trace文件大致是这样的：

然而，事情总没有我们想象的那么顺利，给你一个trace文件，能一眼看到问题所在吗？并不能！errorstack的事件大家似乎都会设置，那能不能读就是另外一回事了，像上面的errorstack的设置，显然，只是在报错的时候做一个processdump而已，dump出来的内容非常之多，要从中精确提取我们所需要的信息，很难！特别是在问题方向都没有的情况下；

errorstack的trace就先放着吧，也许一会儿用的上。

再换一个思路，执行的是sys用户下的包，终归是要调用sql语句免费云主机域名的吧，那我们再来看看到底执行了什么SQL呢？我们不妨来做个10046事件进行跟踪！

SQL>oradebug setospid 4657324

Oracle pid:67, Unix process pid: 4657324, image: oracle@XXXX(TNS V1-V3)

SQL>oradebug event 10046 trace name context forever,level 12

Statement processed.

设置事件后，在相应的会话上调用sys.dbms_output.disable;抓出来的trace文件是这样的：

没错，就这么多，一点也不多，其中有一个等待事件”library cache lock”，通过核查与该等待相关的对象是dbms_standard包，这个包同样是sys用户的，也并无特别之处，同样在iptXXX用户下被调用会存在问题；

为什么10046抓取出来的trace会这么少呢，那看来，一执行就报错了，压根就没有执行下去的机会？

这样查下来，似乎想要跳出权限的死胡同，然而，却也并没有找到问题的方向！

还有什么好方法呢？

。

。

。

。

07

再换个思路

问题到这里，似乎是怎么也查不下去了，MOS上的搜索可能是因为问题都不明确，只能使用PLS-00302/ORA-6500等字眼去搜索，并没有能提供帮助的文章/bug；期间各种生产环境范围内允许的尝试也做了，似乎还是没有什么进展；

看来，找不到别的更好的方法，那么，我们需要反思：

我们刚刚的方法存在问题吗？

1.errorstack抓取的trace到底能不能提供帮助呢？

答：显然应该是有的，但是确实读起来困难，如果我们并不那么熟悉其trace结构，那么此处不宜久留。

2.10046对于这样的语句是否能抓到其调用的SQL呢？

答：不应该，在我的印象中10046是能抓到递归调用的sql的，而这里却没有抓到一条SQL，看起来不合理。

看起来，回归到10046事件上是一个不错的选择，而且，我们确认，使用10046跟踪sys.dbms_transaction.local_transaction时，是有许多递归SQL的调用的，将其中的sql逐一阅读，似乎也没有得到想要的信息；但是，这里，却给了我一个小小的启发：

对比！

没错，前面核查问题，一直因为在生产环境，没有可以随手对比测试的环境，导致将目光死死的盯在了问题本身上，缺乏对比；开拓新思路，我们如何将对比进行下去呢？

对于其他用户而言，调用sys用户的存储过程是没有任何问题的，那我们不妨来看看正确的调用过程中都做了些什么？总不至于也一条SQL都不执行吧！再次针对其他用户（假设叫TEST用户）做10046跟踪，然后成功调用sys用户包下的存储过程，果然能抓到一些SQL，但还好不算多，而且映入眼帘的首先是这样一条SQL：

我们不妨来试着一条一条读下来，逐一对比试试看；这里这个语句，有三个参数，49，‘SYS’，1；在生产环境里核查：

SQL>select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=49

and name=’SYS’ and namespace=1 and remoteowner is null and linkname is null and subname is null;

no rows selected

那么，怎么对比呢？显然，49是我调用存储过程的用户ID（TEST用户），这里如果是iptXXX用户执行呢？

SQL>select user_id from dba_users where username=’IPTXXX’;

USER_ID

———-

34

SQL>select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2from obj$ where owner#=34 and name=’SYS’and namespace=1 and remoteowner is null and linkname is null and subname isnull;

OBJ#TYPE# CTIME MTIME STIME STATUS

——————– ———— ———— ———— ———-

DATAOBJ#FLAGS OID$ SPARE1 SPARE2

——————– ——————————– ———- ———-

77574 2 19-JUN-09 07-JUL-1719-JUN-09 1

77574 06 1

其实，如果是有经验的DBA，应该就能看到问题的所在了；再加一步，所有人都会豁然开朗了：

SQL> Select object_name,object_type,owner from dba_objects where object_id=77574 ;

OBJECT_NAME OBJECT_TYPE OWNER

——————————————————————–

SYS TABLE IPTXXX

没错，事情就是这样的，查了大半天的问题查到这里，我感觉我的智商受到了一万点的侮辱！！我就不知道有一句“XXX”不知当讲不当讲了，不禁感慨，这都什么样的应用设计，什么样的数据库设计啊？原来，


在IPTXXX用户下，居然有一个名字叫SYS的表


！！！原来真相简单到令人发指！！！

08

反思！反思！反思！

问题查出来之后再看往往简单，如果只看到这个结果，大家也许并不会有什么太多收获；对此，我在查完这个问题之后，花了将近一天的时间对这个问题的处理过程、思路以及中间遇到的问题进行了深刻的反思，我才意识到这绝对是值得分享的一个案例。

反思一：

我们来看整个问题的过程；用户IPTXXX上存在SYS对象，导致其调用SYS用户下的包的存储过程时，出现类似权限不足的错误；现在，对我们造成的影响是PLSQL Developer无法正常执行查询；

那么，在其他更复杂的业务环境中，比如，某些黑盒子应用模块中也存在着类似PLSQL Developer的这种调用，而在开发过程中基于这些黑盒子模块进行二次开发，然后在SCHEMA里增加了SYS表，那么这个时候是足以使整个应用无法正常运行的

；通常，作为DBA我们会认为，ORACLE会很好的保留关键字避免被错误使用，然而，从这个CASE来看并没有（

ORACLE的保留关键字在V$RESERVED_WORDS中可以找到

）；

那么我们这里就要给广大应用设计/开发人员一个提醒，在对象的设计过程中，避免使用类似SYS的关键字！同时，对于应用维护/DBA来说，我们就需要对这种情况进行上线前的检查；对于各种SQL审核/应用审核的工具开发厂商来说，我们就需要将这种检查加到我们的工具里；

反思二：

对于这个问题的处理过程而言，真的有那么复杂吗？在权限问题上纠结了那么久：

我们显然可以看到，对于DBMS_LOCK包，我们没有调用权限时，数据库抛出的错是PLS-00201，而我们遇到的错实际上是PLS-00302，其实在面对不熟悉的报错，如果要确认其错误代码的意义，可以先通过模拟报错的形式来对比确认；

反思三



：

如果，我们仔细核查各个函数的意义，我们可以看到kkxsem意义函数的实际上为KKX SEMantic phase，即语义错误；确认了这个函数之后，我们即可确认该问题并非权限问题，避免走入误区！对于callstack，我们还可以给予更多的关注，对于处理问题会有很大的帮助。

反思四：

10046事件真的不能跟踪所有的SQL语句吗？

不是这样的，10046事件是能跟踪到所有执行的SQL语句的！但是对于这里而言，执行的exec sys.dbms_output.disable()语句本身语义错误，我们试图跟踪的是该语句执行过程中产生的递归SQL；这里我们测试可以得出，在初次解析”

exec sys.dbms_output.disable()”语句的过程中，我们会需要load一些数据到library cache中，这个过程是需要执行递归SQL来实现，而在多次执行后，

“exec sys.dbms_output.disable()”的相关cursor已经缓存到library cache lock中，将不会再执行递归SQL，所以我们没有能追踪到；

反思五：这算不算一个bug呢？

是不是可以认为ORACLE忘记将SYS作为保留字而把这个问题当成bug？这里我们不做结论。

09

总结

对于这个CASE，如果还没有了解到其结论的巨大危害性，请再仔细阅读“反思一”部分，反正老K反思完，第一件事就是给我们的客户提建议检查所有的系统是否存在这样类似的表设计。

从整个CASE看，最后得出的结论似乎很简单，但是我们能从中理解到，一个糟糕的应用设计，足以将整个数据库上的应用变得不可用；所以，一个好的设计才是一套应用系统正常运行的根本；

另外，对于任何一个CASE而言，我们收获的不应该只是简单的分析结果，对分析过程的反思与剖析往往比单纯的结果更有意义；这也正是我们对于这个问题的分享所在！

相关推荐: 什么叫archive log expired?

A status of “expired” means that the backup piece or backup set is not found in the backup destination. With tape management syste…