通過建立檢視及同義詞方式實現普通使用者查詢X$基表的方法

看到群裡有人問普通使用者訪問基表的問題，測試下如下：

X$基表可以通過建立檢視，再對檢視建立同義詞方式、授權的方式來實現普通使用者可以訪問基表。

當然了普通使用者訪基表也是沒道理的，理論上沒必要，許可權控制上也應該是不允許的。此處不考慮合理性，就此問題進行實驗。

1.直接對X$基表建立同義詞，其它使用者無法實現訪問。

SQL> show user
USER is "SYS"
SQL> select count(*) from sys.x$kcbwds;

  COUNT(*)
----------
         8

SQL> CREATE PUBLIC SYNONYM kcbwds FOR sys.x$kcbwds;

Synonym created.

SQL> grant select on sys.x$kcbwds to bys;
grant select on sys.x$kcbwds to bys
                    *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
----------
SQL> show user
USER is "BYS"
SQL> select count(*) from sys.x$kcbwds;
select count(*) from sys.x$kcbwds
                         *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from kcbwds;
select count(*) from kcbwds
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

--------------

2.使用對X$基表建立檢視的方法可以實現普通使用者訪問X$：

SQL> show user
USER is "SYS"
SQL> select count(*) from x$kcbwds;

  COUNT(*)
----------
         8
SQL> create view testa as select * from sys.x$kcbwds;

View created.

SQL> grant select on sys.testa to bys;

Grant succeeded.

SQL> conn bys/bys
Connected.
SQL> show user
USER is "BYS"
SQL> select count(*) from sys.testa;

  COUNT(*)
----------
         8
SQL> desc sys.testa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(4)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 SET_ID                                             NUMBER
 POOL_ID                                            NUMBER
 DBWR_NUM                                           NUMBER
 BLK_SIZE                                           NUMBER
後面省略。。。

3.可以通過對檢視再加同義詞方式來實現更簡單的訪問

SQL> CREATE PUBLIC SYNONYM testb FOR sys.testa;

Synonym created.

SQL> show user
USER is "SYS"
SQL> conn / as sysdba
Connected.
SQL> conn bys/bys
Connected.
SQL> select count(*) from testb;

  COUNT(*)
----------
         8