Implementation of batch Oracle authorization

Oracle batch authorization can implement some specific functions: two users, one of which has no data structure. User A can access all data of user B through user A, and only has the permission to read data, the data structure of user B is invisible and can only be queried.

1. Create read-only user:

 
 

  
  
-- Create the user  
  
  
create user A  
  
  
identified by ""  
  
  
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 A;  
  
  
grant create synonym to A; 
 
 

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 A;'  
  
  
FROM TAB WHERE TABTYPE <>'VIEW'  
  
  
UNION ALL  
  
  
SELECT 'grant select,insert,update,deleteon ' || OBJECT_NAME ||  
  
  
 ' to A;'  
  
  
FROM USER_OBJECTS  
  
  
 WHERE OBJECT_TYPE = 'VIEW' 
  
  
UNION ALL  
  
  
SELECT 'grant EXECUTEon ' || OBJECT_NAME || ' to A;'  
  
  
FROM USER_OBJECTS  
  
  
 WHERE OBJECT_TYPE = 'PROCEDURE' 
  
  
UNION ALL  
  
  
SELECT 'grant EXECUTEon ' || OBJECT_NAME || ' to A;'  
  
  
FROM USER_OBJECTS  
  
  
 WHERE OBJECT_TYPE = 'FUNCTION' 
  
  
UNION ALL  
  
  
SELECT 'grant selecton ' || OBJECT_NAME || ' to A;'  
  
  
FROM USER_OBJECTS  
  
  
 WHERE OBJECT_TYPE = 'SEQUENCE';  
  
  
 
  
  
 SPOOL OFF;  
 
 

6. execute scripts

 
 

  
  
@c:\grantA.SQL; 
 
 

Three oracle authorizations

Implementation of oracle tree Query

Implementation of oracle query current time

Provides you with an in-depth understanding of Oracle temporary tables

How to store images in oracle