set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

sqlplus sys/password@dbtest as sysdba @./V_TD_USR_QUERY_PRD_ROLE.sql >1_CREATE_QUERY_PRD_ROLE.log

@echo off

echo.

echo Grant select on all tables and views of production schema to PRODUCTION_QUERY!

Pause

V_TD_USR_QUERY_PRD_ROLE.sql

SET PAGESIZE 0

SET LINESIZE 160

SET DEFINE OFF;

-- Create a new user

CREATE USER PRODUCTION_QUERY

IDENTIFIED BY PRODUCTION_QUERY

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

-- Create new role

CREATE ROLE V_TD_USR_QUERY_PRD_ROLE NOT IDENTIFIED;

-- Grant select on all tables and views of production schema to PRODUCTION_QUERY

SPOOL grant_select_all_tables_views.sql

SELECT 'GRANT SELECT ON PRODUCTION.'||table_name||' TO V_TD_USR_QUERY_PRD_ROLE;'

FROM dba_tables

WHERE owner='PRODUCTION';

SELECT 'GRANT SELECT ON PRODUCTION.'||view_name||' TO V_TD_USR_QUERY_PRD_ROLE;'

FROM dba_views

WHERE owner='PRODUCTION';

SPOOL OFF

@grant_select_all_tables_views.sql

GRANT V_TD_USR_QUERY_PRD_ROLE TO PRODUCTION_QUERY;

ALTER USER PRODUCTION_QUERY DEFAULT ROLE ALL;

GRANT CREATE SESSION TO PRODUCTION_QUERY;

-- Edit SQL : Create synonym for all tables and views of production schema

grant create synonym to production_query;

SPOOL create_synonym_for_tables_views.sql

SELECT 'CREATE SYNONYM '||view_name||' FOR PRODUCTION.'||view_name||';'

FROM dba_views

WHERE owner='PRODUCTION';

SELECT 'CREATE SYNONYM '||table_name||' FOR PRODUCTION.'||table_name||';'

FROM dba_tables

WHERE owner='PRODUCTION';

SPOOL OFF

SET PAGESIZE 14

SET LINESIZE 80

quit

2_CREATE_SYNONYM.cmd

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

sqlplus production_query/production_query@dbtest @./create_synonym_for_tables_views.sql > create_synonym_for_tables_views.log

@echo off

echo.

echo Create synonym for all tables and views of production schema!

pause