Generating Grants & Synonyms

GENERATING PRIVATE SYNONYMS


--###################################################################
--#
--# Title:         gen_cr_priv_syns.sql
--# Description:   Creates private synonyms for 'Application User'
--#                which point to objects owned by 'Object Owner'.
--#
--# Usage Note:    Should be run as '/ as sysdba'
--#                Should be run from a directory that is writable.
--#
--# Copyright :   ABCdba.com 2011 
--#
--###################################################################
 
ACCEPT objowner CHAR PROMPT 'Enter Object Owner user name: '
ACCEPT appuser  CHAR PROMPT 'Enter Application user name:  '
 
SET PAGES 0
SET LINES 2000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET TERMOUT OFF
SET VERIFY OFF
 
SPOOL cr_priv_syns.sql
 
SELECT
   'CREATE OR REPLACE SYNONYM '||
   LOWER( '&appuser' )||
   '.'||
   LOWER( object_name )||
   ' FOR '||
   LOWER( '&objowner' )||
   '.'||
   LOWER( object_name )||
   ';'
FROM
   dba_objects
WHERE
   object_type IN
   ('FUNCTION','LIBRARY','PACKAGE','PROCEDURE','SEQUENCE','TABLE','VIEW')
AND
   owner = UPPER('&objowner')
ORDER BY
   object_type, object_name
/
 
SPOOL OFF
 
SET PAGES 20
SET LINES 80
SET TRIMSPOOL OFF
SET FEEDBACK ON
SET TERMOUT ON
SET VERIFY ON
 
SET ECHO ON
 
@cr_priv_syns.sql
 
SET ECHO OFF

GENERATING PUBLIC SYNONYMS


--###################################################################
--#
--# Title:         gen_cr_pub_syns.sql
--# Description:   Creates public synonyms which point to objects
--#                owned by 'Object Owner'.
--#
--# Usage Note:    Should be run as '/ as sysdba'
--#                Should be run from a directory that is writable.
--#
--# Copyright :   ABCdba.com 2011 
--#
--###################################################################
 
ACCEPT appuser  CHAR PROMPT 'Enter Application user name:  '
 
SET PAGES 0
SET LINES 2000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET TERMOUT OFF
SET VERIFY OFF
 
SPOOL cr_pub_syns.sql
 
SELECT
   'CREATE OR REPLACE PUBLIC SYNONYM '||
   LOWER( object_name )||
   ' FOR '||
   LOWER( '&objowner' )||
   '.'||
   LOWER( object_name )||
   ';'
FROM
   dba_objects
WHERE
   object_type IN
   ('FUNCTION','LIBRARY','PACKAGE','PROCEDURE','SEQUENCE','TABLE','VIEW')
AND
   owner = UPPER('&objowner')
ORDER BY
   object_type, object_name
/
 
SPOOL OFF
 
SET PAGES 20
SET LINES 80
SET TRIMSPOOL OFF
SET FEEDBACK ON
SET TERMOUT ON
SET VERIFY ON
 
SET ECHO ON
 
@cr_pub_syns.sql
 
SET ECHO OFF

GENERATING GRANTS


--###################################################################
--#
--# Title:         gen_grants.sql
--# Description:   Grants SUID or execute privileges as appropriate
--#                to the 'User', 'Role' or 'PUBLIC' for the objects
--#                owned by 'Object Owner'.
--#
--# Usage Note:    Should be run as '/ as sysdba'
--#                Should be run from a directory that is writable.
--#
--# Copyright :   ABCdba.com 2011 
--#
--###################################################################
 
ACCEPT appl_role CHAR PROMPT 'Enter User Name, Role Name or PUBLIC: '
ACCEPT objowner  CHAR PROMPT 'Enter Object Owner user name:         '
ACCEPT objpasswd CHAR PROMPT 'Enter Object Owner password:          '
 
CONNECT &objowner/&objpasswd
 
SET PAGES 0
SET LINES 2000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET TERMOUT OFF
SET VERIFY OFF
 
SPOOL grants.sql
 
SELECT
   'GRANT '||
   DECODE( object_type,
   'FUNCTION', 'EXECUTE',
   'LIBRARY', 'EXECUTE',
   'PACKAGE', 'EXECUTE',
   'PROCEDURE', 'EXECUTE',
   'SEQUENCE', 'SELECT',
   'TABLE', 'SELECT,INSERT,UPDATE,DELETE',
   'VIEW', 'SELECT,INSERT,UPDATE,DELETE'
   )||
   ' ON '||
   LOWER( object_name )||
   ' TO &appl_role;'
FROM
   user_objects
WHERE
   object_type IN
   ('FUNCTION','LIBRARY','PACKAGE','PROCEDURE','SEQUENCE','TABLE','VIEW')
ORDER BY
   object_type, object_name
/
 
SPOOL OFF
 
SET PAGES 20
SET LINES 80
SET TRIMSPOOL OFF
SET FEEDBACK ON
SET TERMOUT ON
SET VERIFY ON
 
SET ECHO ON
 
@grants.sql
 
SET ECHO OFF
 
CONNECT / as sysdba

© copyright 2001-2014 ABCdba.com | all rights reserved