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