Oracle batch authorization and batch creation of synonyms for other users

Function: two users, one of which has no data structure. User A can access all data of user B through user A. Only User A has the permission to read data and cannot see user B's data structure. User A can only query data.

1. Create read-only user:

-- Create the user
Create user
Identified ""
Default tablespace LMIS
Temporary tablespace LMIS
Profile DEFAULT;

2. grant corresponding permissions to Aas (executed under user B with DBA permission)
 
Grant create session to;
Grant create synonym to;

3. Execute the following statement (commit window) as owner B)
Set heading OFF;
Set feedback OFF;
SPOOL c:/temp/A. SQL; -- path to the file
SELECT 'create synonym' | tname | 'for B.' | tname | ';' FROM TAB;
Spool off;

4. Change to the identity of authorized user A and execute

@ C:/temp/A. SQL

5. Grant user A permissions (executed in the COMMIT window of user B)

Set heading OFF;
Set feedback OFF;
SPOOL c:/grantA. SQL;
SELECT 'Grant select, insert, update, delete, REFERENCES, INDEX on' |
TNAME | 'to ;'
From tab where tabtype <> 'view'
UNION ALL
SELECT 'Grant select, insert, update, delete on' | OBJECT_NAME |
'To ;'
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'view'
UNION ALL
SELECT 'Grant EXECUTE on '| OBJECT_NAME |' to ;'
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'processed'
Union all
Select 'Grant execute on '| object_name |' To ;'
From user_objects
Where object_type = 'function'
Union all
Select 'Grant select on' | object_name | 'To ;'
From user_objects
Where object_type = 'sequence ';

Spool off;

6. execute scripts

@ C:/granta. SQL

 

 

 

In addition, you can use the toad query window to save as text and delete the first line of text. Place other statements in the PL/delvepor command window for execution.

 

Select 'create synonym' | table_name | 'for Ba.' | table_name | ';' from user_tables where table_name like 't_market %'
Select 'create synonym' | table_name | 'for Ba.' | table_name | ';' from user_tables where table_name like 't_user %'
Select 'create synonym' | table_name | 'for Ba.' | table_name | ';' from user_tables where table_name like 't_vip %'
Select 'create synonym' | view_name | 'for Ba.' | view_name | ';' from user_views

Create synonym p_vip_sales_detail for Ba. p_vip_sales_detail;
Create synonym p_vip_pagination for Ba. p_vip_pagination;
Create synonym p_vip_pagination

Select 'Grant select, insert, update, delete, references, index on' |
Tname | 'to baweb ;'
From tab where tabtype <> 'view' and tname like't _ MARKET % 'or tname like't _ USER %' or tname like't _ VIP %'
UNION ALL
SELECT 'Grant select, insert, update, delete on' | OBJECT_NAME |
'To baweb ;'
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'view'
UNION ALL
SELECT 'Grant EXECUTE on '| OBJECT_NAME |' to baweb ;'
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'processed' AND OBJECT_NAME IN ('P _ VIP_PAGINATION ', 'P _ VIP_SALES_DETAIL ')