【SQL_TRACE】解决普通用户无法执行SQL_TRACE跟踪其他会话问题

1.为了跟踪一个异常的会话，偶然在sec用户下执行了如下的SQL_TRACE语句对532会话进行跟踪，不过报错无法完成。故troubleshooting一下，记录在此
sec@ora10g> exec dbms_system.set_sql_trace_in_session(532,2204,true);
BEGIN dbms_system.set_sql_trace_in_session(532,2204,true); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

2.从报错的表面现象上分析，提示信息提到找不到这个包，首先应该能想到，sec这个普通用户没有这个包的定义。这是显然的
sec@ora10g> desc DBMS_SYSTEM
ERROR:
ORA-04043: object DBMS_SYSTEM does not exist

3.这个DBMS_SYSTEM包的主人是谁呢？这里给出两种查看的方法
1）第一种方法是，我们使用dbms_metadata.get_ddl方法查看一下
sys@ora10g> select dbms_metadata.get_ddl('PACKAGE','DBMS_SYSTEM') from dual;

DBMS_METADATA.GET_DDL('PACKAGE','DBMS_SYSTEM')
---------------------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYS"."DBMS_SYSTEM" wrapped
... 省略其他的内容 ...

2）第二种方法是通过dba_objects视图得到这个对象的主人
sys@ora10g> col OWNER for a30
sys@ora10g> col OBJECT_NAME for a30
sys@ora10g> col OBJECT_TYPE for a30
sys@ora10g> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where object_name = 'DBMS_SYSTEM';

OWNER                          OBJECT_NAME            OBJECT_TYPE
------------------------------ ---------------------- ----------------
SYS                            DBMS_SYSTEM            PACKAGE
SYS                            DBMS_SYSTEM            PACKAGE BODY

4.确认一下SYS用户下是否存在这个包，答案是肯定的
sys@ora10g> desc DBMS_SYSTEM
PROCEDURE DIST_TXN_SYNC
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 INST_NUM                       NUMBER                  IN
PROCEDURE GET_ENV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAR                            VARCHAR2                IN
 VAL                            VARCHAR2                OUT
PROCEDURE KCFRMS
PROCEDURE KSDDDT
PROCEDURE KSDFLS
PROCEDURE KSDIND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LVL                            BINARY_INTEGER          IN
PROCEDURE KSDWRT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST                           BINARY_INTEGER          IN
 TST                            VARCHAR2                IN
PROCEDURE READ_EV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 IEV                            BINARY_INTEGER          IN
 OEV                            BINARY_INTEGER          OUT
PROCEDURE SET_BOOL_PARAM_IN_SESSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID                            NUMBER                  IN
 SERIAL#                        NUMBER                  IN
 PARNAM                         VARCHAR2                IN
 BVAL                           BOOLEAN                 IN
PROCEDURE SET_EV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SI                             BINARY_INTEGER          IN
 SE                             BINARY_INTEGER          IN
 EV                             BINARY_INTEGER          IN
 LE                             BINARY_INTEGER          IN
 NM                             VARCHAR2                IN
PROCEDURE SET_INT_PARAM_IN_SESSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID                            NUMBER                  IN
 SERIAL#                        NUMBER                  IN
 PARNAM                         VARCHAR2                IN
 INTVAL                         BINARY_INTEGER          IN
PROCEDURE SET_SQL_TRACE_IN_SESSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID                            NUMBER                  IN
 SERIAL#                        NUMBER                  IN
 SQL_TRACE                      BOOLEAN                 IN
PROCEDURE WAIT_FOR_EVENT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EVENT                          VARCHAR2                IN
 EXTENDED_ID                    BINARY_INTEGER          IN
 TIMEOUT                        BINARY_INTEGER          IN


5.于是，顺理成章的我们加上用户信息（DBMS_SYSTEM是在SYS用户中定义的）后再尝试一次
sec@ora10g> exec sys.dbms_system.set_sql_trace_in_session(532,2204,true);
BEGIN sys.dbms_system.set_sql_trace_in_session(532,2204,true); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

6.还是不能执行，这是为什么呢？思前想后，难道是权限的问题？
sec@ora10g> select * from session_roles;

ROLE
------------------------------
PLUSTRACE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE

11 rows selected.

上面可以看到，我的这个sec用户是一个包含DBA角色在内的超级权限用户。

7.这里就不得不提一个需要重点注意的问题，也是导致上面问题的原因：即使用户具有了DBA角色也同样需要单独去对特定包授权“对象权限”。到此，问题原因已经浮出水面
我们单独对sec用户授予执行dbms_system的权限
sys@ora10g> grant execute on dbms_system to sec;

Grant succeeded.

8.最后，我们再尝试一次，可见：喜讯已到
sec@ora10g> exec sys.dbms_system.set_sql_trace_in_session(532,2204,true);

PL/SQL procedure successfully completed.

sec@ora10g> exec sys.dbms_system.set_sql_trace_in_session(532,2204,false);

PL/SQL procedure successfully completed.

9.如果想彻底的放开对sys.dbms_system包的访问权限，可以直接将它的执行权限授权给PUBLIC
方法：
sys@ora10g> grant execute on dbms_system to public;

Grant succeeded.

10.小结
通过这个案例，我们应该充分的认识到Oracle的权限控制的复杂性，不能想当然的认为一个用户具有了DBA的权限就可以为所欲为，Oracle的权限控制策略还是很严格的，尤其是针对具体的“对象权限”。在很多种场景下，都需要单独的“显示的”授予对象权限。

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-616481/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-616481/