How to give read only access to a user to view the tables of different owner in oracle

oraclepermissionsschema

I have 2 users are present in oracle ex User1 and User2, Under user1 some tables are present ex t1,t2,t3 etc.. . what are the minimum permissions to view the tables of user1 by using user2 ( Just want to view the table description not the Data) .

Best Answer

The REFERENCES privilege kind of provides this:

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> grant create session to u1, u2;

Grant succeeded.

SQL> grant create table to u1;

Grant succeeded.

SQL> create table u1.t1 (c1 number);

Table created.

SQL> grant references on u1.t1 to u2;

Grant succeeded.

SQL> conn u2/u2
Connected.
SQL> desc u1.t1
 Name           Null?     Type
 ------------------------------------
 C1                       NUMBER

SQL> select * from u1.t1;
select * from u1.t1
                 *
ERROR at line 1:
ORA-01031: insufficient privileges
Related Question