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.
Only SYS can query the X$ tables, they cannot be granted. However, you can create view on them and grant the view.
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 scott;
grant select on sys.x$tables to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
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 qobesa identified by pass ;
User created.
SQL> grant resource to qobesa ;
Grant succeeded.
SQL> grant connect to qobesa ;
Grant succeeded.
Connect database as my user qobesa and try to select any of X$ tables:
SQL> connect qobesa/pass
Connected.
SQL> show user
USER is "qobesa"
SQL> select * from sys.x$ksppcv;
select * from sys.x$ksppcv
*
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 qobesa:
SQL> show user
USER is "SYS"
SQL> grant select on sys.x$ksppcv to qobesa;
grant select on sys.x$ksppcv to qobesa
*
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$ksppcv as select * from sys.x$ksppcv;
View created.
SQL> grant select on sys.vw_x$ksppcv to qobesa;
Grant succeeded.
Now Let us try to select any x$table such as:
SQL> select * from sys.vw_x$ksppcv;
That's all.
