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) .

  • 1
    You just want them to be able to see the table definitions & not the actual data? Why? – Philᵀᴹ Aug 1 '18 at 9:20
  • I wanted to create a report of tables, not with the Data and if i wanted to give access to a user to the Table definition and that user should not see the data of the table. – chellangi sravan Aug 1 '18 at 10:00

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
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
  • Thank you for your answer. In my schema thousands of tables are Present. If i want to execute the above statements for each table it will take lot of time. So is there any way to grant references to all tables in my schema to another user. Thanks in Advance – chellangi sravan Aug 1 '18 at 10:03
  • @chellangisravan begin for t in (select * from dba_tables where owner = 'USER1') loop execute immediate 'grant references on ' || o.owner || '.' || o.table_name || ' to USER2'; end loop; end; / – Balazs Papp Aug 1 '18 at 11:08
  • Thank you very Much, It is working with small modification begin for t in (select * from dba_tables where owner = 'USER1') loop execute immediate 'grant references on ' || t.owner || '.' || t.table_name || ' to USER2'; end loop; end; – chellangi sravan Aug 1 '18 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.