Oracle只讀用戶角色的建立

授予某模式下對象讀權限給角色,就可以建立Oracle只讀用戶角色,下文對該方法的實現步驟作了詳細的介紹,供您參考學習。

下面為您介紹的是Oracle只讀用戶角色的建立方法,該方法供您參考,如果您在Oracle只讀用戶角色方面遇到過問題,不妨一看。

Oracle只讀用戶角色的建立的腳本:
1.授予某模式下對象讀權限給角色。

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.為模式權限對象創建同意詞。

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