[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

總結:
--//許可權這東西就是一把雙刃劍.現在搞得生產系統自己內部的人操作都不方便.