How To Give 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. 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;
Reklam


“How To Give Grant Select On X$ Objects In Oracle” için 2 cevap

  1. Thank you for this article

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Connecting to %s

%d blogcu bunu beğendi: