Oracle read-only user role creation

The following describes how to create an Oracle read-only user role. This method is for your reference. If you have encountered any problems with the Oracle read-only user role, take a look.

Script created for Oracle read-only user role:
1. Grant the object read permission to the role in a certain mode.

 
 

  
  
SET PAGESIZE 0  
  
  
SET FEEDBACK OFF  
  
  
SET VERIFY OFF  
  
  
SPOOL s.sql  
  
  
SELECT 'GRANT SELECT ON "' || u.object_name || '" TO &1;'  
  
  
FROM user_objects u  
  
  
WHERE   u.object_type IN ('TABLE','VIEW','SEQUENCE')  
  
  
AND NOT EXISTS (SELECT '1'  
  
  
               FROM all_tab_privs a  
  
  
               WHERE   a.grantee = UPPER('&1')  
  
  
               AND a.privilege   = 'SELECT' 
  
  
               AND a.table_name = u.object_name);  
  
  
SPOOL OFF  
  
  
-- Comment out following line to prevent immediate run @s.sql  
  
  
SET PAGESIZE 14  
  
  
SET FEEDBACK ON  
  
  
SET VERIFY ON 
 
 

2. Create an authorization word for the mode permission object.

 
 

  
  
SET PAGESIZE 0  
  
  
SET FEEDBACK OFF  
  
  
SET VERIFY OFF  
  
  
SPOOL temp.sql  
  
  
SELECT 'CREATE SYNONYM "' || a.table_name || '" FOR "' || a.owner || '"."' || a.table_name || '";'  
  
  
FROM all_tables a  
  
  
WHERE   NOT EXISTS (SELECT '1'  
  
  
               FROM user_synonyms u  
  
  
               WHERE   u.synonym_name = a.table_name  
  
  
               AND u.table_owner   = UPPER('&1'))  
  
  
AND a.owner = UPPER('&1');  
  
  
 
  
  
SPOOL OFF  
  
  
-- Comment out following line to prevent immediate run  
  
  
@temp.sql  
  
  
SET PAGESIZE 14  
  
  
SET FEEDBACK ON  
  
  
SET VERIFY ON  
 
 

The preceding section describes how to create an Oracle read-only user role.

View oracle user permissions

Implementation of batch Oracle authorization

How to store images in oracle

Implementation of oracle tree Query

Implementation of oracle query current time