[20180417]使用10046事件需要什么权限.txt-优快云博客

本文记录了一次尝试在Oracle数据库中启用10046事件进行存储过程调试的经历。面对权限不足的问题，通过逐步排查最终确认仅需授予用户'ALTER SESSION'权限即可解决问题。

[20180417]使用10046事件需要什么权限.txt

--//今天想调试一个存储过程,发现生产系统的用户居然不能执行:
TEST@book> @ &r/10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
ERROR:
ORA-01031: insufficient privileges

--//看看执行这个需要什么权限.

1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//我建立的test用户如下:

CREATE USER TEST
IDENTIFIED BY <password>
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for TEST
GRANT CONNECT TO TEST;
GRANT RESOURCE TO TEST;
ALTER USER TEST DEFAULT ROLE ALL;
-- 1 System Privilege for TEST
GRANT UNLIMITED TABLESPACE TO TEST;

2.这样设置限制多多.

TEST@book> show sga
ORA-00942: table or view does not exist

TEST@book> show parameter cursor
ORA-00942: table or view does not exist

TEST@book> show parameter cursor_sharing
ORA-00942: table or view does not exist

TEST@book> @ &r/10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
ERROR:
ORA-01031: insufficient privileges

--//很奇怪修改用户参数没有影响.
TEST@book> alter session set cursor_sharing=force ;
Session altered.

TEST@book> show parameter cursor_sharing
ORA-00942: table or view does not exist

3.仅仅授权给用户alter session就ok了:

SCOTT@book> grant alter session to test;
Grant succeeded.

TEST@book> alter session set cursor_sharing=force ;
Session altered.

TEST@book> @ &r/10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.

TEST@book> show parameter cursor_sharing
ORA-00942: table or view does not exist

总结:
--//权限这东西就是一把双刃剑.现在搞得生产系统自己内部的人操作都不方便.