Using synonyms to share objects

 

Using Oracle synonyms for a user to view / use objects in a schema that belongs to another user. 
It's very simple and really useful. All you have to do is create a synonym for each object you want to 'share', then assign permissions to the schema of interest they want to access the object.

- Creating a synonym 
CREATE PUBLIC synonym "MY_TABLE" 
FOR "ME". "MY_TABLE";

We use a public synonym for different share schemes. The allocation of permits them to be specific for each scheme have to access the object

- Assigning permissions for the user 
GRANT SELECT ON "MEI". "MY_TABLE" TO "THE"; 

GRANT UPDATE ON "ME". MY_TABLE"TO" THE "; 
GRANT INSERT ON "ME". MY_TABLE"TO" THE "; 
GRANT DELETE ON "ME". MY_TABLE"TO" THE ";

- If you want to query only access to the same table to another user, it would suffice to make 
GRANT SELECT ON "ME". "MY_TABLE" TO "SHE";

Now "THE" and "HER" and can work on "MY_TABLE" each with the permissions that the owner of the board has decided

 

Average: 5 (1 vote)