HR user does not have access to DBA_TAB_COLUMNS

I am studying Oracle Database and solving review questions. There is a questions and answer about dictionary views that I do not quite understand:

You need to get information about columns in a table you do not own, nor do you have privileges to it. Which view can you query to get this information?

And answer for this questions is: DBA_TAB_COLUMNS.

I thought that correct answer would be Can’t be done.

I have run it with HR user:

SELECT * FROM DBA_TAB_COLUMNS;

I have got table or view does not exist which likely means that HR does not have access to this particular view. Which is in my understanding correct behavior.

Could you please explain why DBA_TAB_COLUMNS is the correct answer if it does not work for all users?

Answer

Well, it is correct if you have access to dba_tab_columns; not everyone does.

Privileged users (such as SYS or SYSTEM) own the database so they see it. If you can connect as such user (doesn’t have to be any of those two; that could be any other user granted required privileges), then you can perform query.

Or, if you can acquire select privileges, that would be enough. For example:

Create brand new user with very limited privileges:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create user mike identified by lion
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> grant create session to mike;

Grant succeeded.

Connect as newly created user, try to select from dba_tab_columns:

SQL> connect mike/lion
Connected.
SQL> select count(*) from dba_tab_columns where table_name = 'DEPT';
select count(*) from dba_tab_columns where table_name = 'DEPT'
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Doesn’t work (as expected). Acquire some privileges and try again:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant select on dba_tab_columns to mike;

Grant succeeded.

SQL> connect  mike/lion
Connected.
SQL> select count(*) from dba_tab_columns where table_name = 'DEPT';

  COUNT(*)
----------
         6

SQL>

Now it works.

---

So … yes, I agree with you – unless you have privileges to access dba_tab_columns, how are you supposed to query it?