通过创建视图及同义词方式实现普通用户查询X$基表的方法

来源：互联网发布：数据库分组查询语句编辑：程序博客网时间：2024/05/21 06:33

看到群里有人问普通用户访问基表的问题，测试下如下：

X$基表可以通过创建视图，再对视图创建同义词方式、授权的方式来实现普通用户可以访问基表。

当然了普通用户访基表也是没道理的，理论上没必要，权限控制上也应该是不允许的。此处不考虑合理性，就此问题进行实验。

1.直接对X$基表创建同义词，其它用户无法实现访问。

SQL> show userUSER is "SYS"SQL> select count(*) from sys.x$kcbwds;  COUNT(*)----------         8SQL> 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 userUSER 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 existSQL> 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 userUSER is "SYS"SQL> select count(*) from x$kcbwds;  COUNT(*)----------         8SQL> create view testa as select * from sys.x$kcbwds;View created.SQL> grant select on sys.testa to bys;Grant succeeded.SQL> conn bys/bysConnected.SQL> show userUSER is "BYS"SQL> select count(*) from sys.testa;  COUNT(*)----------         8SQL> 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 userUSER is "SYS"SQL> conn / as sysdbaConnected.SQL> conn bys/bysConnected.SQL> select count(*) from testb;  COUNT(*)----------         8

0 0