CODEWITHSUNDEEP
oracle-databasesecurityoracle10gschemaprivileges
j0hnstew
j0hnstew

Reputation: 709

Oracle 10g Express - Let Another User View Tables from another user

I am a huge noob with Oracle right now. I was asked to import two databases into Oracle. I succeeded...sort of...I think. So these databases were exported with the user and when I imported the databases it created the user and all of the tables were attached to that user. Same thing for the second database. Lets just call the user for the first import USER1 and for the second db import USER2. USER1 has its own tables and USER2 has its own tables.

I want to create a user that can see all of those tables. so I don't have to login to one to access and manipulate its data and the other to do the same. I would like to create a USER3 that can see and manipulate USER1 and USER2's tables associated with each. I have tried a number of ways and just cannot seem to get this to work. Any help would be greatly appreciated.

Thanks

Upvotes: 2

Views: 1838

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

To allow USER3 to query a table owned by USER1:

GRANT SELECT ON USER1.tablename TO USER3;

You must run this for each table individually.

Other grants that you may need are INSERT, UPDATE and DELETE, e.g. to grant full control:

GRANT SELECT, INSERT, UPDATE, DELETE ON USER1.tablename TO USER3;

When you login as USER3, to query the table you normally need to specify the schema, e.g.:

SELECT * FROM USER1.tablename;

If you want to avoid having to specify the schema each time, you can use synonyms, e.g.:

(login as USER3)

CREATE SYNONYM tablename FOR USER1.tablename;

Now you can login as USER3 and run this:

SELECT * FROM tablename;

"I just don't understand why I have to do all that."

Users - or schemas - are the means Oracle uses for organising applications and enforcing governance. In a well-design application it is extremely unlikely that one schema would need to grant every privilege on all its objects to another user. Oracle recommends a policy of granting the minimum necessary set of privileges to other users. Doing this requires us to make choices and write discrete statements to grant specific privileges on particular objects.

Upvotes: 8

Related Questions