The X$ tables are owned by the SYS database user and are read-only, which is why they are referred to as fixed tables and the V$ views are referred to as fixed views. This statement might be a juicy invitation for you to try to verify this read-only property. Any attempt to alter these tables with a DDL or DML statement is met with an ORA-02030 error.
Only SYS can query the X$ tables, they cannot be granted. However, you can create view on them and grant the view.
Here is the demo:
1) As I mention; You cannot grant a select privilege on X$* tables to another user due to they are internally protected.
– If you try grant select on x$ objects then you will receive the following error:
SQL> grant select on sys.x$tables to user; grant select on sys.x$tables to user * ERROR at line 1: ORA-02030: can only select from fixed tables/views
2) Alternatively, We can create a view and then grant a select privilege on that new view to another user as follow:
– Now create my own user and gives related grants to my user:
SQL> create user helios identified by yyyy ; User created. SQL> grant resource to helios ; Grant succeeded. SQL> grant connect to helios ; Grant succeeded.
– Connect database as my user HELIOS and try to select any of X$ tables:
SQL> connect helios/yyyy Connected. SQL> show user USER is "HELIOS" SQL> select * from sys.x$bh; select * from sys.x$bh * ERROR at line 1: ORA-00942: table or view does not exist
- As you can see We are hitting ORA-00942. Let us try to give Select grant to our new user HELIOS: SQL> show user USER is "SYS" SQL> grant select on sys.x$bh to scott; grant select on sys.x$bh to scott * ERROR at line 1: ORA-02030: can only select from fixed tables/views
As you can see We can not give select grant and hitting ORA-02030 error.
–Now Let us create view and grant select to our new view.
SQL> create view vw_x$bh as select * from sys.x$bh; <(=== This works View created. SQL> grant select on sys.vw_x$bh to scott; <(=== This works Grant succeeded.
-Now Let us try to select any x$table such as:
SQL> select * from sys.vw_x$bh;
Bir Cevap Yazın