7/31/2019 Read Only Schema

1/3

Read Only Schema in Oracle APPS 11i |Print|E-mail

WrittenbyAnilPassi

InthisarticleIhavediscussedhowtocreateandmaintainareadonlyschemaforAPPSinOracleeBusinessSuite.

WhilstinthepastIhaveknownclientstoimplementthisusingsynonyms.Howevertheapproachdiscussedbelowisdesigned

withouttheneedofhavingtocreateasinglesynonyminAPPS_QUERYschema.

Createtheread-onlyschema,inthiscaseletscallitAPPS_QUERY.

Surely,theschemacreatedinaboveStep1willbegivenreadonlygrantstoobjectsinapps.Therewillbecaseswherethegrantcommandmightfail.Tomonitorsuchfailurescreateatableasbelowconnxx_g4g/&2;--ForAPPS_QUERY.ThistablewillcapturetheexceptionsduringGrantsPROMPTcreatetableXX_GRANTS_FAIL_APPS_QUERYcreatetableXX_GRANTS_FAIL_APPS_QUERY(object_nameVARCHAR2(100),sqlerrmvarchar2(2000),creation_dateDATE);grantallonXX_GRANTS_FAIL_APPS_QUERYtoappswithgrantoption;grantselectonXX_GRANTS_FAIL_APPS_QUERYtoapps_query;

Inthisstepwegrantselectonalltheexistingviewsandsynonymsinappsschematoapps_query.connapps/&1;PROMPTThiscantakeupto15-30minutesPROMPTGrantingSELECTonAllsynonymsandviewstoapps_queryDECLARE--Oneoffscripttoexecutegrantstoapps_queryv_errorVARCHAR2(2000);BEGINFORp_recIN(SELECT*FROMall_objectsWHEREowner='APPS'ANDobject_typeIN('SYNONYM','VIEW')ANDobject_nameNOTLIKE'%_S')LOOPBEGINEXECUTEIMMEDIATE'grantselecton'||p_rec.object_name||'toapps_query';EXCEPTIONWHENOTHERSTHENv_error:=substr(SQLERRM,1,2000);INSERTINTObes.XX_GRANTS_FAIL_apps_query(object_name,SQLERRM,creation_date)

VALUES(p_rec.object_name,v_error,sysdate);END;ENDLOOP;COMMIT;END;/

http://oracle.anilpassi.com/index2.php?option=com_content&task=view&id=23&pop=1&page=0&Itemid=37http://oracle.anilpassi.com/index2.php?option=com_content&task=emailform&id=23&itemid=37http://oracle.anilpassi.com/index2.php?option=com_content&task=emailform&id=23&itemid=37http://oracle.anilpassi.com/index2.php?option=com_content&task=emailform&id=23&itemid=37http://oracle.anilpassi.com/index2.php?option=com_content&task=emailform&id=23&itemid=37http://oracle.anilpassi.com/index2.php?option=com_content&task=emailform&id=23&itemid=37http://oracle.anilpassi.com/index2.php?option=com_content&task=emailform&id=23&itemid=37http://oracle.anilpassi.com/index2.php?option=com_content&task=view&id=23&pop=1&page=0&Itemid=37

7/31/2019 Read Only Schema

2/3

Writeaafterlogontriggeronapps_queryschema.Themainpurposeofthistriggeristoalterthesessiontoappsschema,suchthattheCurrentSchemawillbesettoappsforthesession(whilstretainingapps_queryrestrictions).Indoingsoyourlogonwillretainthepermissionsofapps_queryschema(read_only).Howerveritwillbeabletoreferencetheappsobjectswithexactlythesamenameasdoesadirectconnectiontoappsschema.connapps/&1;PROMPTCREATEORREPLACETRIGGERxx_apps_query_logon_trg

CREATEORREPLACETRIGGERxx_apps_query_logon_trg--16Jun2006ByAnilPassi--Triggertotoggleschematoapps,butyetretainingapps_queryresitrictions--Alsosetstheorg_idAFTERlogonONapps_query.SCHEMADECLAREBEGINEXECUTEIMMEDIATE'declarebegin'||'dbms_application_info.set_client_info(101);end;';EXECUTEIMMEDIATE'ALTERSESSIONSETCURRENT_SCHEMA=APPS';END;/

CreateaTriggerontheappsschematoissueselectonlygrantsforallnewviewsandsynonyms.PleasenotethatIamexcludinggrantsforsequences.SELECTgrantsforviewsandsynonymswillbeprovidedtoapps_queryasandwhensuchobjectsarecreated

inAPPS.Pleasenotethat,alltheAPPSobjects(viewsandsynonyms)thatexistedinAPPSschemapriortotheimplementationofthisdesign,wouldhavebeengrantedread-onlyaccesstoapps_queryinStep2.connapps/&1;PROMPTCREATEORREPLACETRIGGERxx_grant_apps_queryCREATEORREPLACETRIGGERxx_grant_apps_query--16Jun2006ByAnilPassi--AFTERCREATEONAPPS.SCHEMADECLAREl_strVARCHAR2(255);l_jobNUMBER;BEGINIF(ora_dict_obj_typeIN('SYNONYM','VIEW'))AND(ora_dict_obj_nameNOTLIKE'%_S')THENl_str:='executeimmediate"grantselecton'||ora_dict_obj_name||

'toapps_query";';dbms_job.submit(l_job,REPLACE(l_str,'"',''''));ENDIF;END;/

YouneedtoensurethattheschemacreatedinStep1hasverylimitedpermissions.MostimportantlyitmustnotbegivengrantforEXECUTE/CREATEANYPROCEDURE.YouwillneedtoagreewithyourDBAsupfrontforthepermissions,

Onlyviewsandsynonymswillbegrantedaccess.Objectsinyourxx_g4g(bespoke)schemashouldhavetheirsynonymsinappsalreadyinplace.

Ifyoursitehasmultiorgenabled,youwillthenhavetosettheorgI'dafterloggiongontoappsqueryschema.IncaseyouhaveonlyonesingleORG_ID,thenwouldhavebeensetasinStep4above.

ALTERSESSIONSETCURRENT_SCHEMA=APPSThisfacilitatesuserstoruntheirqueriesasiftheywereconnectedtoappsschema.However,theirpreviligeswillberestrictedtothoseofapps_query

ItisassumedthatALTERSESSIONprivilegewillexistforAPPS_QUERYschema.

7/31/2019 Read Only Schema

3/3