Sample Script

insert into PS.PSDBOWNER values(upper('COMB'),upper('COMB'))
/
grant select on PS.PSDBOWNER to COMB;
/
grant select on PSADM.PSOPRDEFN      to COMB;
grant select on PSADM.PSACCESSPRFL   to COMB;
grant select on PSADM.PSSTATUS       to COMB WITH GRANT OPTION;
grant select on PSADM.PSAUTHITEM     to COMB;
grant select on PSADM.PSPNLGROUP     to COMB;
grant select on PSADM.PSMENUITEM     to COMB;
grant select on PSADM.PSPNLGROUP     to COMB;
grant select on PSADM.PS_PRCSDEFNPNL to COMB;
grant select on PSADM.PSPNLGRPDEFN   to COMB WITH GRANT OPTION;
grant update on PSADM.PSLOCK         to COMB;
grant update on PSADM.psversion      to COMB;
grant update on PSADM.psserverstat   to COMB;
grant insert on PSADM.ps_serveractvty to COMB;
grant insert,update on PSADM.PSAcceSSLOG to COMB;
/
grant insert,update,delete on PSADM.PS_AERUNCONTROL to COMB;
grant insert,update,delete on PSADM.PS_AETEMPTBLMGR to COMB;
grant update,insert,delete on PSADM.PSTREESELECT10  to COMB; 
/
grant update,insert,delete on PSADM.PSBATCHAUTH to COMB;
grant insert,update,delete on PSADM.PS_AERUNCONTROLPC to COMB;
grant insert,update,delete on PSADM.PS_QUERY_RUN_PARM to COMB;
grant insert,update,delete on PSADM.PS_PRCSRUNCNTLDIST to COMB;
/
grant select on PSADM.PS_SCRTY_QUERY     to COMB;
/
grant update,insert,delete on PSADM.PSIBSUBSLAVE    to COMB;
grant update,insert,delete on PSADM.PSIBBRKSLAVE    to COMB;
grant update,insert,delete on PSADM.PSAPMSGDOMSTAT  to COMB;
grant update,insert,delete on PSADM.PSWEBPROFHIST   to COMB;
grant update,insert,delete on PSADM.PSAPMSGQUEUESET to COMB;
grant update,insert,delete on PSADM.psrenclus_owner to COMB;
grant update,insert,delete on PSADM.psmcfrenurlid   to COMB;
grant update,insert,delete on PSADM.psrencluster    to COMB;
grant update,insert,delete on PSADM.PSAPMSGDSPSTAT  to COMB;
grant update,insert,delete on PSADM.PSIBPUBSLAVE    to COMB;
grant update,insert,delete on PSADM.PSAPMSGSUBCON   to COMB;
grant update,insert,delete on PSADM.PSIBFOLOCK      to COMB;
grant update,insert,delete on PSADM.PSAPMSGPUBHDR   to COMB;
grant delete on PSADM.PSANALYTICREG                 to COMB;
/
drop synonym COMB.PSSTATUS;
create or replace view COMB.PSSTATUS
as
select 
    VERSION,
    'COMB' OWNERID,
    TOOLSREL,
    LASTREFRESHDTTM,
    LASTCHANGEDTTM,
    OWNERACCT,
    UNICODE_ENABLED,
    DBID,
    DATABASE_OPTIONS   --- Newly added field in 9.1
from PSADM.PSSTATUS;
/
SELECT * FROM PSADM.PSACCESSPRFL
/

drop synonym COMB.PSACCESSPRFL;
CREATE TABLE COMB.PSACCESSPRFL LIKE PSADM.PSACCESSPRFL 
create unique index COMB.PS_PSACCESSPRFL on COMB.PSACCESSPRFL (symbolicid);
INSERT INTO COMB.PSACCESSPRFL (SYMBOLICID,VERSION,ACCESSID,ACCESSPSWD,ENCRYPTED) values ('psadm1',0,'COMB=','COMB=',0)
--update COMB.PSACCESSPRFL set ACCESSID = 'COMB', ACCESSPSWD = 'COMB', VERSION = 0, ENCRYPTED = 0;
/

grant select on COMB.PSSTATUS to people;
grant select on COMB.PSACCESSPRFL to people;
/
drop synonym COMB.PSAUTHITEM;
/

create or replace view COMB.PSAUTHITEM
as
select 
    a1.CLASSID,
    a1.MENUNAME,
    a1.BARNAME,
    a1.BARITEMNAME,
    a1.PNLITEMNAME,
    1 DISPLAYONLY,
    a1.AUTHORIZEDACTIONS 
   from PSADM.PSAUTHITEM a1
   WHERE a1.BARITEMNAME NOT IN ('PROCESSMONITOR','QUERY_MANAGER','QUERYVIEWER',
   'PO_INQUIRY',  -- This Allows the user to click on buttons in PO Inquiry
	'DRILLDOWNREGISTER','IC_REPORTBOOK','IC_RUN_DRILL','NVS_REPORT_REQUEST','NVS_SCOPE', -- This allows NVISION access
	'PYMNT_INQ_SRCH',  -- This alllows Payment Inquiry access
	'CONTENT_LIST',  -- This allows REPORT MANAGER access
	'SCHEDQUERY2',  -- This allows Scheduling a Query
	'SCHEDQUERY',  -- This allows Scheduling a Query
	'VOUCHER_ACCOUNTING_ENTRIES',  -- This allows Voucher Accounting Entries Inquiry page
	'PYCYCL_DATA_INQ',  -- This allows Voucher Payment Inquiry Detail page
	'PYCYCL_DATA_SUM',  -- This allows Voucher Payment Inquiry Summary page
	'JOURNAL_STATUS',  -- This allows Jounrnal Status Inquiry Page - bpisack 06/16/2008
	'INQ_LED_CMP_PNL',  -- This allows the Ledger Inqury Page - bpisack 06/16/2008
	'PV_MANAGE',  -- This allows the Manager Requisition Page - bpisack 06/16/2008
	'AP_VCHR_INQ',  -- This allows AP Voucher Inquiry page - bpisack 06/16/2008
	'GL_CASH_VOIDS',
	'HCR_HCRRP026_PGRP',
	'HCR_HIST_RPT',
	'HCR_JRNL_ENTRIES',
	'HCR_LEGAL_SERV',
	'HCR_MONTHLYCASH',
	'HCR_NETPAYROLL',
	'HCR_RC_REIMB',
	'HCR_RUN_PAY002H',
	'HCR_RUN_TAX910AU',
	'HCR_RUNCTL_REIM',
	'HCR_TAX001H_REPORT',
	'HCR_VAC_SICK_HOL',
	'HCR_WAGE_PASS',
	'HCRRB026_PG',
	'HCRRP001',
	'HCRRP002',
	'HCRRP013',
	'HCRRP015_PNLGRP',
	'SMSPCONTRIBUTIONS',		-- wholowac 30/07/2008
	'HCR_401KCONTRIB_PG',		-- wholowac 30/07/2008
	'HCRRPTX2')		-- wholowac 30/07/2008
   and a1.BARNAME NOT IN ('REPORT','RPT_N-Z','RPT_A-M')
union
select 
    a2.CLASSID,
    a2.MENUNAME,
    a2.BARNAME,
    a2.BARITEMNAME,
    a2.PNLITEMNAME,
    0 DISPLAYONLY,
    a2.AUTHORIZEDACTIONS 
   from PSADM.PSAUTHITEM a2
   WHERE a2.BARITEMNAME IN ('PROCESSMONITOR','QUERY_MANAGER','QUERYVIEWER',
      'PO_INQUIRY',  -- This Allows the user to click on buttons in PO Inquiry
	'DRILLDOWNREGISTER','IC_REPORTBOOK','IC_RUN_DRILL','NVS_REPORT_REQUEST','NVS_SCOPE', -- This allows NVISION access
	'PYMNT_INQ_SRCH',  -- This alllows Payment Inquiry access
	'CONTENT_LIST',  -- This allows REPORT MANAGER access
	'SCHEDQUERY2',  -- This allows Scheduling a Query
	'SCHEDQUERY',  -- This allows Scheduling a Query
	'VOUCHER_ACCOUNTING_ENTRIES',  -- This allows Voucher Accounting Entries Inquiry page
	'PYCYCL_DATA_INQ',  -- This allows Voucher Payment Inquiry Detail page
	'PYCYCL_DATA_SUM',  -- This allows Voucher Payment Inquiry Summary page
	'JOURNAL_STATUS',  -- This allows Jounrnal Status Inquiry Page - bpisack 06/16/2008
	'INQ_LED_CMP_PNL',  -- This allows the Ledger Inqury Page - bpisack 06/16/2008
	'PV_MANAGE',  -- This allows the Manager Requisition Page - bpisack 06/16/2008
	'AP_VCHR_INQ',  -- This allows AP Voucher Inquiry page - bpisack 06/16/2008
	'GL_CASH_VOIDS',
	'HCR_HCRRP026_PGRP',
	'HCR_HIST_RPT',
	'HCR_JRNL_ENTRIES',
	'HCR_LEGAL_SERV',
	'HCR_MONTHLYCASH',
	'HCR_NETPAYROLL',
	'HCR_RC_REIMB',
	'HCR_RUN_PAY002H',
	'HCR_RUN_TAX910AU',
	'HCR_RUNCTL_REIM',
	'HCR_TAX001H_REPORT',
	'HCR_VAC_SICK_HOL',
	'HCR_WAGE_PASS',
	'HCRRB026_PG',
	'HCRRP001',
	'HCRRP002',
	'HCRRP013',
	'HCRRP015_PNLGRP',
	'SMSPCONTRIBUTIONS',		-- wholowac 30/07/2008
	'HCR_401KCONTRIB_PG',		-- wholowac 30/07/2008
	'HCRRPTX2')		-- wholowac 30/07/2008
   and a2.BARNAME NOT IN ('REPORT','RPT_N-Z','RPT_A-M')
union
select                                                    
    a4.CLASSID,                                           
    a4.MENUNAME,                                           
    a4.BARNAME,                                          
    a4.BARITEMNAME,                                     
    a4.PNLITEMNAME,                                     
    0 DISPLAYONLY,                                        
    a4.AUTHORIZEDACTIONS                                    
   from PSADM.PSAUTHITEM a4                          
   WHERE a4.BARNAME IN ('REPORT','RPT_N-Z','RPT_A-M')
union
select                                                    
    a5.CLASSID,                                           
    a5.MENUNAME,                                           
    a5.BARNAME,                                          
    a5.BARITEMNAME,                                     
    a5.PNLITEMNAME,                                     
    0 DISPLAYONLY,                                        
    a5.AUTHORIZEDACTIONS                                    
   from PSADM.PSAUTHITEM a5                         
   WHERE a5.MENUNAME IN ('QUERY_MANAGER')
union
select                                                    
    a6.CLASSID,                                           
    a6.MENUNAME,                                           
    a6.BARNAME,                                          
    a6.BARITEMNAME,                                     
    a6.PNLITEMNAME,                                     
    0 DISPLAYONLY,                                        
    a6.AUTHORIZEDACTIONS                                    
   from PSADM.PSAUTHITEM a6                          
   WHERE a6.BARNAME = 'INQUIRE' 
   and a6.MENUNAME = 'CREATE_PAYMENTS'; 
 /
 drop synonym COMB.PS_SCRTY_QUERY;
create or replace view COMB.PS_SCRTY_QUERY
as
select 
   CLASSID,
   VERSION,
--  'N' QRY_RUN_ONLY,      Use this line to allow edit of queries and comment out field below
   'Y' QRY_RUN_ONLY,
   QRY_CREATE_PUBLIC,
   QRY_CREATE_WFLOW,
   QRY_MAX_FETCH,
   QRY_MAX_RUN,
   QRY_ADV_DISTINCT,
   QRY_ADV_ANY_JOIN,
   QRY_ADV_SUBQUERY,
   QRY_ADV_UNION,
   QRY_ADV_EXPR,
   QRY_MAX_JOINS,
   QRY_MAX_IN_TREE,
 --  'N' QRY_OUT_LISTBOX,  Use this line to turn off Query to HTML and Comment out field below
   QRY_OUT_LISTBOX,
 --  'N' QRY_OUT_NVISION,  Use this line to turn off Query to Excel and Comment out field below
   QRY_OUT_NVISION,
   QRY_OUT_CRYSTAL,
   QRY_ADM_AUTOPUBLIC,
   QRY_ADM_AUTOPRIV,
   QRY_ADM_LIMUNAPPRV,
   QRY_ADM_UNAPP_ROWS 
   from PSADM.PS_SCRTY_QUERY;
   
 /
 DROP SYNONYM COMB.PSPNLGRPDEFN;
/
CREATE OR REPLACE VIEW COMB.PSPNLGRPDEFN
  (PNLGRPNAME, MARKET, VERSION, ACTIONS, DESCR, 
   ADDSRCHRECNAME, SEARCHRECNAME, SEARCHPNLNAME, LOADLOC, SAVELOC, 
   DISABLESAVE, OBJECTOWNERID, LASTUPDDTTM, LASTUPDOPRID, PRIMARYACTION, 
   DFLTACTION, DFLTSRCHTYPE, DEFERPROC, EXPENTRYPROC, REQSECURESSL, 
   INCLNAVIGATION, FORCESEARCH, ALLOWACTMODESEL, PNLNAVFLAGS, TBARBTNS, 
   SHOWTBAR, ADDLINKMSGSET,  ADDLINKMSGNUM, SRCHLINKMSGSET,SRCHLINKMSGNUM, 
   SRCHTEXTMSGSET, SRCHTEXTMSGNUM, WSRPCOMPLIANT, DESCRLONG)
AS 
SELECT PNLGRPNAME,MARKET,VERSION,ACTIONS - MOD(ACTIONS, 2) ,DESCR,ADDSRCHRECNAME,SEARCHRECNAME,SEARCHPNLNAME,LOADLOC,SAVELOC
,DISABLESAVE,OBJECTOWNERID,LASTUPDDTTM,LASTUPDOPRID,PRIMARYACTION,DFLTACTION,DFLTSRCHTYPE,DEFERPROC,EXPENTRYPROC,REQSECURESSL
,INCLNAVIGATION,FORCESEARCH,ALLOWACTMODESEL,PNLNAVFLAGS,TBARBTNS,SHOWTBAR,ADDLINKMSGSET,ADDLINKMSGNUM,SRCHLINKMSGSET,SRCHLINKMSGNUM
,SRCHTEXTMSGSET,SRCHTEXTMSGNUM,WSRPCOMPLIANT,DESCRLONG FROM PSADM.PSPNLGRPDEFN;