Is it possible to query Oracle system tables (DBA_USERS,dba_role_privs,dba_tab_privs) Using Db_link?

Question

Is it possible to query Oracle system tables (DBA_USERS,dba_role_privs,dba_tab_privs) Using Db_link?

I am getting ORA-00942: table or view does not exist when tried to query the same.

Answer 1

Sure it is, but only if database link points to user who is capable of accessing those objects.

Connected as an ordinary user (Scott), I'll create database link to user with DBA privileges:

SQL> connect scott/tiger
Connected.

SQL> create database link dev_old_db1
  2    connect to mydba
  3    identified by mypwd
  4    using 'orcl';

Database link created.

Selecting rows from dba_users over database link:

SQL> select count(*) from dba_users@dev_old_db1;

  COUNT(*)
----------
        83

Is that really so? Let's connect as mydba in remote database:

SQL> connect mydba/mypwd@orcl
Connected.

SQL> select count(*) from dba_users;

  COUNT(*)
----------
        83

SQL>

Looks like that's it.

---

If, on the other hand, I point database link to a non-privileged user, it won't work (and I'll get the same error as you did):

SQL> show user
USER is "SCOTT"
SQL> drop database link dev_old_db1;

Database link dropped.

SQL> create database link dev_old_db1
  2    connect to mike
  3    identified by lion
  4    using 'orcl';

Database link created.

SQL> select count(*) from dba_users@dev_old_db1;
select count(*) from dba_users@dev_old_db1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist               --> here it is!
ORA-02063: preceding line from DEV_OLD_DB1


SQL>

---

Therefore, make sure that user - which is behind that database link - has required privileges.