How to Check if Table is Granted to You

Is Table Granted?

Sometimes, you may be not sure that if tables owned by others have ever been granted to you. Actually, there are several ways to know it.

1. Quick Way

The fastest way to know it is to use desc to describe the definition of the table. It's my favorite. Suppose we would like SH to use HR.EMPLOYEES.

Before Granted

SQL> conn sh/sh
Connected.
SQL> desc hr.employees;
ERROR:
ORA-04043: object hr.employees does not exist

We got ORA-04043 for describing unusable object.

After Granted

SQL> desc hr.employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

You can see the layout of the table. It implies that you are granted to see the table.

2. Formal Way

The most formal way is to query the dictionary view ALL_TABLES to know the details. For example, user SH would like to know the status of table HR.EMPLOYEES:

Before Granted

SQL> select owner, table_name from all_tables where owner = 'HR' and table_name = 'EMPLOYEES';

no rows selected

After Granted

SQL> select owner, table_name from all_tables where owner = 'HR' and table_name = 'EMPLOYEES';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
HR                             EMPLOYEES

Yes, the table owned by HR can be viewed by SH. If you don't know what ALL_TABLES is about, you should learn about different scope views of tables.

Further Reading - How to Grant All Tables Owned by Others

3. Direct Way

You can also query this table directly with limiting the returned rows to none:

Before Granted

SQL> select * from hr.employees where rownum = 0;
select * from hr.employees where rownum = 0
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

Further Reading - How to Resolve ORA-00942: table or view does not exist

After Granted

SQL> select * from hr.employees where rownum = 0;

no rows selected

Even though we have no rows selected, we know the table has been granted to SH. This is because we refused any row returned on purpose by adding the filter rownum = 0.

4. Smart Way

A neat and smart result can be derived by selecting a system function instead of retrieving the actual data, for example, we use SYSDATE() to check the system time on the table:

Before Granted

SQL> select distinct sysdate from hr.employees;
select distinct sysdate from hr.employees
                                *
ERROR at line 1:
ORA-00942: table or view does not exist

You can see the error ORA-00942 is returned for selecting from ungranted tables.

After Granted

SQL> select distinct sysdate from hr.employees;

SYSDATE
---------
21-JUL-13

There's more information about how to mange object privileges in an Oracle database.