通过创建视图及同义词方式实现普通用户查询X$基表的方法
程序员文章站
2022-06-17 14:50:37
...
看到群里有人问普通用户访问基表的问题,测试下如下: 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