sysauth$基表的使用者許可權的一點分析

源於itpub的一篇帖子http://www.itpub.net/thread-1581904-1-1.html說到sysauth$表被頻繁訪問。

select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

如上的sql語句頻繁執行，其實對於遞迴sql對於自己初始oracle才一年的菜鳥一般是略去不看的，eygle前輩們有時別太相信oracle內部的sql，他們很可能會成為系統效能的瓶頸，記得eygle的一個案例說到某個版本的oracle上開啟oem很可能導致後臺一個遞迴sql反覆執行而影響系統執行，也建議自己和大家多去嘗試看一些oracle內部的東西。

還是先檢視下基表sysauth$的定義吧

這裡可以參考下oracle_home/rdbms/admin/sql.bsq的指令碼，其中有關於定義oracle基表具體表結構和column的comment

create table sysauth$ /* system authorization table */

( grantee# number not null, /* grantee number (user# or role#) */

privilege# number not null, /* role or privilege # */

sequence# number not null, /* unique grant sequence */

option$ number) /* null = none, 1 = admin option */

還存在一個組合索引

create unique index i_sysauth1 on sysauth$(grantee#, privilege#)

看來確實是關於使用者許可權的基表資訊。

下面用10046來追蹤對於使用者許可權的操作，以下實踐資料庫是oracle 10.2.0.0的win7伺服器上

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> grant resource,connect to test;

Grant succeeded

SQL> alter session set events '10046 trace name context off';

Session altered

擷取的部分trace檔案資訊

select max(nvl(option$,0)) from sysauth$ where privilege#=:1 connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:2 or grantee#=1) and privilege#>0 group by privilege#

insert into sysauth$ (grantee#,privilege#,option$,sequence#) values (:1,:2,decode(:3,0,null,:3),system_grant.nextval)

這裡可以看出對於test的resource，connect的授權最終是對於sysauth$基表的更新，

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> revoke resource,connect from test;

Revoke succeeded

SQL> alter session set events '10046 trace name context off';

Session altered

部分的trace檔案資訊

delete from sysauth$ where grantee#=:1 and privilege#=:2

delete from defrole$ where user#=:1 and role#=:2 and not exists (select null from sysauth$ where grantee#=1 and privilege#=:2)

看出revoke使用者許可權時實則也是對sysauth$基表的更新，期間還更新刪除了了defrole$基表的資料資訊。

可能是對於系統使用者授權較為頻繁導致AWR表報中對於sysauth$訪問較頻繁，由於沒有實際的環境但是從這點倒是可以讓我們更好的瞭解oracle使用者授權機制。

[@more@]