How To Grant Select On X$ Objects In Oracle

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.