How To Give Grant Select On X$ Objects In Oracle 10g?

Metalink Note:453076.1

1) 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$bh to scott;
grant select on sys.x$bh to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


2) Alternatively, you can create a view and then grant a select privilege on that new view to
another user as follow:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"
SQL> create user scott identified by tiger default tablespace users temporary tablespace temp;

User created.

SQL> grant resource to scott;

Grant succeeded.

SQL> grant connect to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> show user
USER is "SCOTT"

SQL> select * from sys.x$bh;
select * from sys.x$bh
*
ERROR at line 1:
ORA-00942: table or view does not exist

==================================================================

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


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.

SQL> connect scott/tiger
Connected.
SQL> select * from sys.vw_x$bh; <(=== This works