How to create all synonyms for another user under one user

Oracle synonyms literally mean aliases. Similar to the view function, they are a ing relationship. Maintenance of users in Oracle is maintained by permissions. That is to say, if we want to use the database, we must have permissions, but if someone else grants us the permissions, we can also perform operations on the database, but it is the name of the user who wants to authorize the table, such as bsrun.doc tor_info, so this is troublesome. What should we do in this case? Create a synonym! In this way, we can directly Use synonyms to use tables.

1. If we want to create a synonym for all objects of the bsrun user, we can write as follows:

Select 'create synonym' object_name 'For bsrun. 'object _ name ';'
From user_objects where object_type in ('table', 'view', 'Procedure ', 'trigger', 'function', 'package ');
 

Execute the result set obtained by the preceding statement in the PL/SQL tool. The above is a private synonym, that is, only the creator can access the result set.

The following statement creates a public Synonym that can be accessed by all authorized users:

Select 'create public synonym' object_name 'For bsrun. 'object _ name ';'
From user_objects where object_type in ('table', 'view', 'Procedure ', 'trigger', 'function', 'package ');

2. Delete Synonyms:

Drop synonym EMP; -- delete a private synonym named EMP
Drop public synonym public_emp; -- delete a public synonym named public_emp.

 

Select 'create public synonym' | object_name | 'For jtsjk.' | object_name | ';'
From user_objects where object_type in ('table', 'view ');

How to create all synonyms for another user under one user