Purpose
To create public synonyms for custom tables for accessing them without the schema name reference in the query. The level of access to these records in different environments should be discussed before we proceed further. In this document we have a few scripts provided as solution to avoid the public synonyms issue.
Problem
Developers are unable to access custom objects created in application designer using an external SQL tool like SQL Developer or SQL Plus. This external access is required by the developer to write/update/test. Example as below.
Table name – ps_dr_cerdn_fsa_bk
Solution
Public Synonyms and appropriate read/write privileges to these objects will allow the developers to access the objects without a schema reference
Additional points to be considered
1) We need to run the script for all the non production environments after database refresh.(script 1)
2) Synonyms and grants need to be done when a new custom object is created.
3) Psreadonly and psreadwrite are the roles in non-production environment.
4) The pre requisite is to check if Psreadonly and psreadwrite group is present or else please suggest one.
5) Psreadonly role should be used for production environment.
Script to create public synonyms-1
This script will create public synonyms for all the tables.
The disadvantage is, we will be creating public synonym for all the records and it will generate a huge script.
SPOOL C:\TEMP\Synonym_script.sql;
SET LINESIZE 200;
SET PAGESIZE 0;
SET ECHO OFF;
SELECT distinct ‘CREATE OR REPLACE PUBLIC SYNONYM ‘ || OBJECT_NAME || ‘ FOR SYSADM||’.’ || OBJECT_NAME || ‘;’
FROM all_objects where
object_type not in (‘TABLE PARTITION’,'LOB’,'INDEX’ )
and object_name not like ‘%$%’ and owner=’SYSADM’
and owner=’SYSADM’ ;
SELECT ‘GRANT SELECT ON SYSADM.’||OBJECT_NAME||’ TO PSREADONLY;’ FROM all_objects
Where object_type not in (‘TABLE PARTITION’,'LOB’,'INDEX’ ) and
object_name not like ‘%$%’ and owner=’SYSADM’
and owner=’SYSADM’ ;
SELECT ‘GRANT SELECT, INSERT, UPDATE, DELETE ON SYSADM.’||OBJECT_NAME||’ TO PSREADWRITE;’
FROM all_objects where
object_type not in (‘TABLE PARTITION’,'LOB’,'INDEX’ ) and
object_name not like ‘%$%’ and owner=’SYSADM’
and owner=’SYSADM’ ;
SPOOL OFF;
Script for a grant read-2
This will grant read only access to the entire users who have the role psreadonly.
GRANT SELECT ON SYSADM. ps_dr_cerdn_fsa_bk TO psreadonly;
Script for a grant read\ write-3
This will grant read\write access to the entire users who have the role psreadwrite.
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM. ps_dr_cerdn_fsa_bk TO psreadwrite;
Script for a new custom project-4
After completing each project which includes creation of new tables the developer will run the script below to avoid the synonyms issue. This can be done only for the future projects. Example of a project which had 10 newly created records.
SPOOL C:\TEMP\CIS_FIN3249_PRJ_Synonym_script.txt;
SET LINESIZE 200;
SET PAGESIZE 5000;
SET ECHO ON;
SELECT * FROM PSDBOWNER;
SELECT TO_CHAR(SYSDATE, ‘DD-MON-YY HH:MM AM’) “RUN DATE/TIME” FROM DUAL;
–**********************************************************************
–***********************************************
–For <Project Name> – ******** <Description>.
–***********************************************
CREATE PUBLIC SYNONYM PS_CIS_NTS_CHARGE FOR SYSADM.PS_CIS_NTS_CHARGE;
CREATE PUBLIC SYNONYM PS_CIS_NTS_MONHDR FOR YSADM.PS_CIS_NTS_MONHDR;
CREATE PUBLIC SYNONYM PS_CIS_RUN_SIPX_LD FOR SYSADM.PS_CIS_RUN_SIPX_LD;
CREATE PUBLIC SYNONYM PS_CIS_SIP_OLD_QTY FOR SYSADM.PS_CIS_SIP_OLD_QTY;
CREATE PUBLIC SYNONYM PS_CIS_SIP_STG_DTL FOR SYSADM.PS_CIS_SIP_STG_DTL;
CREATE PUBLIC SYNONYM S_CIS_SIP_STG_ERR FOR SYSADM.PS_CIS_SIP_STG_ERR;
CREATE PUBLIC SYNONYM PS_CIS_SIP_STG_HDR FOR SYSADM.PS_CIS_SIP_STG_HDR;
CREATE PUBLIC SYNONYM PS_NTS_TIERS_HDR FOR SYSADM.PS_NTS_TIERS_HDR;
CREATE PUBLIC SYNONYM PS_CIS_NTS_CUSTPRC FOR SADM.PS_CIS_NTS_CUSTPRC;
CREATE PUBLIC SYNONYM PS_NTS_TIERS FOR SYSADM.PS_NTS_TIERS;
GRANT SELECT ON SYSADM.PS_CIS_NTS_CHARGE TO psreadonly;
GRANT SELECT ON SYSADM.PS_CIS_NTS_MONHDR TO psreadonly;
GRANT SELECT ON SYSADM.PS_CIS_RUN_SIPX_LD TO psreadonly;
GRANT SELECT ON SYSADM.PS_CIS_SIP_OLD_QTY TO psreadonly;
GRANT SELECT ON SYSADM.PS_CIS_SIP_STG_DTL TO psreadonly;
GRANT SELECT ON SYSADM.PS_CIS_SIP_STG_ERR TO psreadonly;
GRANT SELECT ON SYSADM.PS_CIS_SIP_STG_HDR TO psreadonly;
GRANT SELECT ON SYSADM.PS_NTS_TIERS_HDR TO psreadonly;
GRANT SELECT ON SYSADM.PS_CIS_NTS_CUSTPRC TO psreadonly;
GRANT SELECT ON SYSADM.PS_NTS_TIERS TO psreadonly;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_CIS_NTS_CHARGE TO psreadwrite;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_CIS_NTS_MONHDR TO psreadwrite;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_CIS_RUN_SIPX_LD TO psreadwrite;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_CIS_SIP_OLD_QTY TO psreadwrite;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_CIS_SIP_STG_DTL TO psreadwrite;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_CIS_SIP_STG_ERR TO psreadwrite;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_CIS_SIP_STG_HDR TO psreadwrite;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_NTS_TIERS_HDR TO psreadwrite;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_CIS_NTS_CUSTPRC TO psreadwrite;
GRANT SELECT,INSERT,UPDATE,DELETE ON SYSADM.PS_NTS_TIERS TO psreadwrite;
Commit;
SPOOL OFF;