Create Synonym & Grant Right - Windows version

*** Create_Grants.sql

DEFINE EOwner='&1'

SET echo off feed off pages 0 verify off
SET lines 130
SPOOL Sqls/Create_Grants_RBPONL_&EOwner..sql

SELECT    'Grant '
       || DECODE (object_type,
                  'TABLE', 'select,insert,delete,update',
                  'SEQUENCE', 'select',
                  'VIEW', 'select,insert,delete,update',
                  'execute'
                 )
       || ' on '
       || UPPER ('&EOwner')
       || '.'
       || object_name
       || ' to &EOwner._USER;'
  FROM dba_objects
 WHERE owner = UPPER ('&EOwner')
   AND object_type IN
          ('TABLE',
           'SEQUENCE',
           'VIEW',
           'PACKAGE',
           'PROCEDURE',
           'FUNCTION',
           'TYPE'
          );

SPOOL off

exit; 

*** Create_Synonyms.sql

DEFINE EOwner='&1'

SET echo off feed off pages 0 verify off
SET lines 200

SPOOL Sqls/Create_Synonyms_RBPONL_&EOwner..sql
SELECT    'Drop synonym '||d1.owner||'.'
       || synonym_name || ';'
  FROM dba_synonyms d1, 
       ( SELECT DISTINCT grantee FROM dba_role_privs
          WHERE granted_role IN (upper('&EOwner._user'),upper('&EOwner._read'))
            AND grantee NOT IN ('SYS')
       ) d2
 WHERE d1.table_owner=UPPER ('&EOwner') 
   AND d1.owner=d2.grantee
   AND (d1.table_owner, d1.table_name) not in
       ( SELECT owner, object_name from dba_objects
          WHERE object_type IN ('FUNCTION', 'PACKAGE BODY', 'PROCEDURE', 'SEQUENCE', 'TABLE', 'TYPE', 'VIEW')
            AND owner = UPPER ('&EOwner'))
UNION ALL
SELECT    'Create synonym '||d2.grantee||'.'
       || d1.object_name
       || ' for &EOwner..'
       || d1.object_name
       || ';'
  FROM dba_objects d1, 
       ( SELECT DISTINCT grantee FROM dba_role_privs
          WHERE granted_role IN (upper('&EOwner._user'),upper('&EOwner._read'))
            AND grantee NOT IN ('SYS')
       ) d2
 WHERE d1.object_type IN ('FUNCTION', 'PACKAGE BODY', 'PROCEDURE', 'SEQUENCE', 'TABLE', 'TYPE', 'VIEW')
   AND d1.owner = UPPER ('&EOwner')
   AND (d2.grantee,d1.object_name) not in
       ( SELECT owner, synonym_name from dba_synonyms
          WHERE table_owner=UPPER ('&EOwner') )
;
SPOOL off

REM **************************************** END OF FILE ************************************************

exit; 

 

*** Run_Grants.sql

DEFINE EOwner='&1'

SET echo off feed off pages 0 verify off
SET lines 130

SET feed on echo on
SPOOL Logs/Create_Grants_RBPONL_&EOwner..log
START Sqls/Create_Grants_RBPONL_&EOwner..sql
SPOOL off

exit; 

 

*** Run_Synonyms.sql

DEFINE EAppUser='&1'

SET echo off feed off pages 0 verify off
SET lines 200

SET echo on feed on
SPOOL Logs/Create_Synonyms_RBPONL_&EAppUser..log
START Sqls/Create_Synonyms_RBPONL_&EAppUser..sql
SPOOL off

REM **************************************** END OF FILE ************************************************

exit; 

 

 调用程序：

cd %~dp0

set ORACLE_HOME=C:\ORACLE\ORACLE_1120\product\11.2.0\client_1
set SCHEMA_NAME=schema_name
set PASSWORD=password
set DB_NAME=db_name

%ORACLE_HOME%/bin/sqlplus %SCHEMA_NAME%/%PASSWORD%@%DB_NAME% @goldstack/Create_Grants.sql %SCHEMA_NAME%
%ORACLE_HOME%/bin/sqlplus %SCHEMA_NAME%/%PASSWORD%@%DB_NAME% @goldstack/Run_Grants.sql %SCHEMA_NAME%

%ORACLE_HOME%/bin/sqlplus %SCHEMA_NAME%/%PASSWORD%@%DB_NAME% @goldstack/Create_Synonyms.sql %SCHEMA_NAME%

findstr "APPUSER" Sqls\Create_Synonyms_RBPONL_%SCHEMA_NAME%.sql > Sqls\Create_Synonyms_RBPONL_%SCHEMA_NAME%_APPUSER.sql

%ORACLE_HOME%/bin/sqlplus %SCHEMA_NAME%_APPUSER/%PASSWORD%@%DB_NAME% @goldstack/Run_Synonyms.sql %SCHEMA_NAME%_APPUSER