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

[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

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