Grating roles after creating table/views/synonyms in Peoplesoft

When ever there database changes in migrations, Admin need to update the customized grants to the tables /views /synonyms in oracle.

This can be automated with sciprt below :

This script helps to create public synonyms , reanalyze the tables that got modified with data. Also provide the grants to roles with new objects

I just mentioned the broad steps to follow to automate ;

-- Create roles

select 'create role xxxx;' from dual

-- Create public synonyms

select 'create public synonym ' || table_name || ' for ' || owner || '.' || table_name || ';'
from dba_tables a
where owner = 'user1'
and not exists (select 'x'
from dba_synonyms b
where b.owner = 'PUBLIC'
and b.synonym_name = a.table_name
and b.table_owner = a.owner
and b.table_name = a.table_name)
union
select 'create public synonym ' || view_name || ' for ' || owner || '.' || view_name || ';'
from dba_views a
where owner = 'user1'
and not exists (select 'x'
from dba_synonyms b
where b.owner = 'PUBLIC'
and b.synonym_name = a.view_name
and b.table_owner = a.owner
and b.table_name = a.view_name)


-- Analyze tables

-- Grant select to xxxx
select 'grant select on ' || owner || '.' || object_name || ' to xxxx;'
from dba_objects
where owner = 'user1'

PeopleSoft Human Resources Management System Bug Lets Remote Authenticated Users Access and Modify Data

Fix Available: Yes Vendor Confirmed: Yes

Version(s): 8.9, 9.0

Description: A vulnerability was reported in PeopleSoft Human Resources Management System (HRMS). A remote authenticated user can access and modify data on the target system.

No details were provided.

The following researchers reported these and other Oracle vulnerabilities:

Dyon Balding of Secunia Research; Joshua J. Drake of iDefense; Gerhard Eschelbeck of Qualys, Inc.; Esteban Martinez Fayo of Application Security, Inc.; Franz Huell of Red Database Security; Mike Janowski of Neohapsis, Inc.; Joxean Koret; Joxean Koret of TippingPoint (3com); Alexander Kornbrust of Red Database Security; David Litchfield of NGS Software; Tanel Poder; Sven Vetter of Trivadis; and Dennis Yurichev.

Impact: A remote authenticated user can access and modify data on the target system.

Solution: The vendor has issued a fix, described in their April 2009 Critical Patch Update advisory.

The vendor's advisory is available at:

http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpuapr2009.html

Peoplesoft metetable list and discription

Please see the list of meta tables below...

ACCESS GROUPS

ACCESS_GRP_TBL


ACTIVITIES


PSACTIVITYDEFN
PSACTIVITYLANG
PSACTIVIMG
PSACTIVIMGLANG
PSSTEPDEFN
PSEVENTDEFN
PSEVENTROUTE
PSMSGAGTDEFN
PSMAPEXPR
PSMAPFIELD
PSMAPLEVEL
PSMAPRECFIELD
PSMAPROLEBIND
PSMAPROLENAME
PSWORKLIST


ANALYSIS MODELS


ANALYSIS_MODEL
ANL_MOD_DAT_SRC
ANL_MOD_DIM
ANL_MOD_DIM_FLD


APP ENGINE PROGRAMS


PSAEAPPLDEFN
PSAEAPPLLANG
PSAEAPPLSTATE
PSAEAPPLTEMPTBL


APP ENGINE SECTIONS


PSAESECTDEFN
PSAESECTDTLDEFN
PSAESTEPDEFN
PSAESTEPMSGDEFN
PSAESTMTDEFN
PS_AEINSTANCENBR
PS_AELOCKMGR


APP MESSAGING


PSAPMSGARCHPC
PSAPMSGARCHPD
PSAPMSGARCHPH
PSAPMSGARCHSC
PSAPMSGARCHTMP
PSAPMSGPUBATTR
PSAPMSGPUBCERR
PSAPMSGPUBCERRP
PSAPMSGPUBCLOCK
PSAPMSGPUBCON
PSAPMSGPUBCSYNC
PSAPMSGPUBDATA
PSAPMSGPUBERR
PSAPMSGPUBERRP
PSAPMSGPUBHDR
PSAPMSGPUBINST
PSAPMSGPUBLOCK
PSAPMSGPUBSYNC
PSAPMSGSUBCERR
PSAPMSGSUBCERRP
PSAPMSGSUBCLOCK
PSAPMSGSUBCON
PSAPMSGSUBCSYNC
PSAPMSGSUBPRCID
PSAPMSGXTB


APPLICATION PACKAGES


PSPACKAGEDEFN
PSPACKAGELANG
PSAPPCLASSDEFN


APPROVAL RULE SETS


APPR_HDR_LNG
APPR_RULE_AMT
APPR_RULE_DETL
APPR_RULE_FIELD
APPR_RULE_HDR
APPR_RULE_LN
APPR_RULE_QTY
APPR_RULE_ROLE
PSVAITEM
PSVAITEMIMG
PSVAITEMLANG


ARCHIVE TEMPLATES


PS_ARCH_PROJ
PS_ARCH_TBL
PS_ARCH_CTRL
PS_ARCH_OTH_CTRL
PS_ARCH_SQL_LNG


BUSINESS COMPONENTS


PSBCDEFN
PSBCDEFNLANG
PSBCITEM
PSBCITEMLANG


BUSINESS INTERLINKS


PSIODEFN
PSIODEFNLANG
PSIOINPUTS
PSIOOUTPUTS
PSIOSETTINGS


BUSINESS PROCESSES


PSBUSPROCDEFN
PSBUSPROCITEM
PSBUSPROCLANG
PSBUSPROCSEC
PSBUSPRIMG
PSBUSPITEMLANG
PSBUSPRIMGLANG


CHANGE CONTROL


PSCHGCTLHIST
PSCHGCTLLOCK


COLORS


PSCOLORDEFN


CONNECT STRINGS


PSCONN
PSCONNLANG
PSCONNPROP
PSCONNPROPLANG


CUBE TEMPLATES


ANALYSIS_DB
ANALYSIS_DB_DIM
ANL_DB_LANG
ANL_DB_QRY_ESS
CUBE_AGG_DEF
CUBE_AGG_DIM
CUBE_FILTER_ITM


DEFINITION SECURITY


PSOBJGROUP
PSOPROBJ


DELETE USER BYPASS TABLE


PS_BYPASS_TABLE


DICTIONARY


PSSCDICTDEFN


DIMENSIONS


DIMENSION
DIMENSION_LANG
DIM_CTRL_TBL
DIM_DATA_SRC
DIM_INPUT_FLD
DIM_ROLLUP
DIM_ROLLUP_LVL


DIRECTORY


PSDSDIR
PSDSSRVR
DSCONNECTID
PSDSEXT_INSTALL
PSDSSECMAPMAIN
PSDSSECMAPSRVR
DSUSRPRFLMAP
PSDSUSERPRFL
PSDSSECROLERULE
DSSRCH_SBR
DSSRCHATTR
DSSECFILTER
PT_WF_NOT_DSCFG


EDI OBJECTS


PS_ECACTIONCDS
PS_ECACTIONS
PS_ECCVTPROFILE
PS_ECENTITYCDS
PS_ECEXTPARTNER
PS_ECEXTTPLINK
PS_ECGENERAL
PS_ECINMAPFILE
PS_ECINMAPFLD
PS_ECINMAPFLDCVT
PS_ECINMAPREC
PS_ECINMAPRECFLD
PS_ECINTLINK
PS_ECINTPARTNER
PS_ECMAPDEFN
PS_ECMAPPROFILE
PS_ECNAMES
PS_ECOUTMAPCVT
PS_ECOUTMAPFLD
PS_ECOUTMAPREC
PS_ECOUTMAP
PS_ECPRIEVENTCDS
PS_ECPRODFLTS
PS_ECPROMAP
PS_ECPROTRANS
PS_ECSECEVENTCDS
PS_ECTPALIAS
PS_ECTPCVT
PS_ECTPCVT_HDR
PS_ECTPCVT_LN
PS_ECTPPROFILE
PS_ECTRANS
PS_ECTRANSOPTION
PS_ECTRANSOPVAL


EVALUATED NODE TRANSACTION


PSNODETRX
PSNODTRXCONPROP


EVALUATED RELATIONSHIP TRANSACTION


PSRELATIONTRX


FIELD FORMATS


PSFMTDEFN
PSFMTITEM


FIELDS


PSDBFIELD
PSDBFIELDLANG
PSDBFLDLABL
PSDBFLDLABLLANG


FILE LAYOUT DEFINITIONS


PSFLDDEFN
PSFLDFIELDDEFN
PSFLDSEGDEFN


FORGOT EMAIL TEXT


PSPSWDEMAIL
PSPSWDEMAILLANG


FORMAT DEFINITIONS


PSCIREF
PSCIREFLANG
PSCIREFPROPERTY
PSCIREFENUM
PSCISYNCDEFN


HOLIDAY DEFINITIONS


PSHOLIDAYDEFN
-- ICE 569507001
PS_XMLSERVICEINFO


IMAGE AND HTMLCATALOG-- CONTENT


PSCONTDEFN
PSCONTDEFNLANG


LANGUAGES


PSLANGUAGES
PS_STRINGS_LNG_TBL


MAINTENANCE LOG


PS_MAINTENANCE_LOG


MENUS


PSMENUDEFN
PSMENUDEFNLANG
PSMENUITEM
PSMENUITEMLANG
PSXFERITEM


MESSAGE CHANNELS


PSCHNLDEFN
PSCHNLDEFNLANG
PSCHNLNODE
PSSUBCHNL


MESSAGE DEFINITIONS


PSMSGDEFN
PSMSGDEFNLANG
PSMSGFLDOVR
PSMSGREC
PSMSGVER


MESSAGE NODES


PSMSGNODEDEFN
PSNODEDEFNLANG
PSCONNECTSTRING
PSNODEURITEXT
PSNODECONPROP
PSNODCONPRPLANG
PSNODEPROPLANG
PSNODESDOWN
PSTRUSTNODES


MESSAGES


PSMSGSETDEFN
PSMSGSETLANG
PSMSGCATDEFN
PSMSGCATLANG


MOBILE PAGE


PSMPDEFN
PSMPDEFNLANG
PSMPDEFNDEV
PSMPDEFNDEVLANG
PSMPCONTDEV
PSMPCONTDEVLANG
PSSYSTEMID
PSAUTHMP


OBJECT CHANGE (RENAMES)


PSOBJCHNG


PAGES


PSPNLDEFN
PSPNLFIELD
PSPNLTREECTRL
PSPNLHTMLAREA
PSPNLCNTRLDATA
PSPNLBTNDATA
PSPNLDEFNLANG
PSPNLFIELDLANG
PSPNLHTMLLANG
PSPNLBTNLANG


PANEL GROUPS


PSPNLGRPDEFN
PSPNLGDEFNLANG
PSPNLGROUP
PSPNLGROUPLANG


PASSWORD HINTS


PSPSWDHINT
PSPSWDHINT_LANG


PEOPLECODE


PSPCMPROG
PSPCMNAME


PEOPLETOOLS PROJECTS


PSPROJECTDEFN
PSPROJECTITEM
PSPROJDEFNLANG
PSPROJECTDEP
PSPROJECTINC
PSPROJECTMSG
PSPROJECTSEC


PEOPLETOOLS SYSTEM DATA


PSCERTDB
PSKEYDB
PSCHARSETS
PS_OLAP_ATTRIB_NAM
PS_VERTICAL_MARKET
PS_APP_DES_OBJECTS
PS_APP_DES_OBJ_PAR
PSPGEACCESSDESC
PSOBJSECDESC
PSOPTPARMTYPE
PSLOCALEDEFN
PSLOCALELANG
PSLOCALEOPTNDFN
PSCOLLATIONS
PS_CDM_FILE_EXT
PS_AEONLINEINST
PS_WL_TEMPLATE_GEN
PS_WL_TEMPL_GEN_TK
PSGATEWAY
PS_INSTALLATION_IB
PSRF_FLIST_TBL
PSRF_FLIST_LANG
PSRF_FVIEW_TBL
PSRF_FVIEW_LANG
PS_DECIMAL_POS_TBL
PS_WL_TEMPL_GNTKLN
PSACTIVEXLIC
PS_APPDES_OBJ_PERM
PSXMLSIGNINST
PS_TS_REC_KEYFLDS
PS_TS_REC_TXTFLDS
PS_TS_RECORDS
PS_MCF_INSTALL
PS_MCFIMNETWORKS
PSMCFRENPERMS
PSMCFSYSTEM
PS_MCFUQTASKCFG
PS_PINGOPTIONS
PSVERITYLOCALE
PS_PTP_TABLE1
PS_PTP_TABLE2
PSOPRALIASTYPE
PSOPRALIASFIELD
PS_PT_CTI_SWITCH
PS_WF_INSTSTATUS


PERMISSION LISTS


PSCLASSDEFN
PSAUTHBUSCOMP
PSAUTHCHNLMON
PSAUTHCUBE
PSAUTHITEM
PSAUTHOPTN
PSAUTHPRCS
PSAUTHSIGNON
PSPRCSPRFL
PS_MC_OPR_SECURITY
PS_MC_OPRID
PS_SCRTY_ACC_GRP
PS_SCRTY_QUERY


PERSONALIZATIONS


PSUSEROPTNDEFN
PSUSEROPTNLANG
PSOPTNCATGRPLNG
PSOPTNCATGRPTBL
PSOPTNCATTBL
PSOPTNCATLANG


PORTAL


PSPRSMDEFN
PSPRSMDEFNLANG
PSPRSMATTR
PSPRSMATTRLANG
PSPRSMATTRVAL
PSPRSMATTRVALNG
PSPRSMPERM
PSPRSMSYSATTR
PSPRSMSYSATTRVL
PSPRSMHPASGPGLT
PSPRSMSYNC
PSPRDMDEFN
PSPRDMDEFNLANG
PSPRDMCNTPRV


PORTAL REGISTRY USER FAVORITES


PSPRUFDEFN


PORTAL USER REGISTRY HOMEPAGE


PSPRUHDEFN
PSPRUHTAB
PSPRUHTABPGLT


PROBLEM DEFINITIONS


PSOPTPRBTYPE
PSOPTPRBTYPELNG
PSOPTTRN
PSOPTTRNLNG
PSOPTTRNCTLG
PSOPTREC
PSOPTRECLNG
PSOPTFIELD


PROCESS CONTROL


PS_SCHDLDEFN
PS_SCHDLDEFNLANG
PS_SCHDLITEM
PS_SCHDLMESSAGE
PS_SCHDLNODEPARM
PS_SCHDLNOTIFY
PS_SCHDLRPTDIST
PS_SCHDLTEXT


PROCESS DEFINITIONS


PRCSDEFN
PRCSDEFNLANG
PRCSDEFNGRP
PRCSDEFNLANG
PRCSDEFNPNL
PRCSDEFNXFER
PRCSDEFNNOTIFY
PRCSDEFNCNTDIST
PRCSDEFNMESSAGE
PRCSDEFNMETA


PROCESS JOBS


PRCSJOBDEFN
PRCSJOBDEFNLANG
PRCSJOBGRP
PRCSJOBITEM
PRCSJOBPNL
PS_PRCSJOBNOTIFY
PS_PRCSJOBCNTDIST
PS_PRCSJOBMESSAGE
PS_PRCSSEQUENCE
PS_PRCSOUTPUTLIST
PS_PRCSOUTDESTTYPE
PS_PRCSSYSTEM
PS_PRCSPURGELIST


PROCESS RECURRENCE


PRCSRECUR
PRCSRECURLANG
PRCSRECURDATE
PRCSRECUREXEMPT


PROCESS SERVERS


PS_SERVERCLASS
PS_SERVERDEFN
PS_SERVERNOTIFY
PS_SERVERMESSAGE
PS_SERVEROPRTN


PROCESS TYPES


PS_PRCSTYPEDEFN
PS_PRCSTYPEDEFNLNG
PS_PRCSTYPEMETA


QUERIES


PSQRYDEFN
PSQRYDEFNLANG
PSQRYBIND
PSQRYBINDLANG
PSQRYCRITERIA
PSQRYEXPR
PSQRYFIELD
PSQRYFIELDLANG
PSQRYLINK
PSQRYRECORD
PSQRYSELECT


RECORD GROUP


PS_REC_GROUP_REC
PS_REC_GROUP_TBL


RECORDS AND INDEXES


PSRECDEFN
PSRECDEFNLANG
PSRECFIELD
PSINDEXDEFN
PSKEYDEFN
PSDDLMODEL
PSDDLDEFPARMS
PSSPCDDLPARM
PSRECDDLPARM
PSIDXDDLPARM
PSRECFIELDDB
PSRECTBLSPC


RELATIONSHIPS


PSRELATCONTACT
PSRELATIONLANG
PSRELATIONPROP
PSRELATIONSHIP
PSRELATIONTRX
PSRELPROPLANG


ROLES


PSROLEDEFN
PSROLEDEFNLANG
PSROLECANGRANT
PSROLECLASS


SDK DATA


PS_SDK_AMORT_PREF
PS_SDK_BUS_EXP_DTL
PS_SDK_BUS_EXP_PER
PS_SDK_COMPANY_TBL
PS_SDK_COUNTRY_TBL
PS_SDK_CURR_CD_TBL
PS_SDK_DEPT_TBL
PS_SDK_EM_MAILLST
PS_SDK_EM_RCVMSGS
PS_SDK_EM_SNDEMAIL
PS_SDK_FILEUTL_AET
PS_SDK_INSTALL
PS_SDK_INTL_FLG_CD
PS_SDK_JOB
PS_SDK_LOCH_TBL
PS_SDK_PERS_DATA
PS_SDK_POS_DATA
PS_SDK_PSTREENODE
PS_SDK_RP_INPUT
PS_SDK_RP_PO
PS_SDK_RP_POLINE
PS_SDK_RP_QUERYWRK
PS_SDK_RP_RESULT
PS_SDK_RP_SALORDER
PS_SDK_RP_SITE
PS_SDK_RP_SOQRY1
PS_SDK_RP_SOQRY2
PS_SDK_RT_TYPE_TBL
PS_SDK_RUNCNTL
PS_SDK_SCRTY_DEPT
PS_SDK_SM
PS_SDK_SM_CONCATE
PS_SDK_SRCHSPREF
PS_SDK_SRCH_SAVE
PS_SDK_STOCK
PS_SDK_STOCKXCHG
PS_SDK_STOCK_PRF
PS_SDK_UPS_COST
PS_SDK_UPS_CST_RES
PS_SDK_UPS_TIME


SECURITY


PSACCESSPRFL
PSOPRDEFN
PSSTATUS


SECURITY LINKS


PSUSEROTHER
PSUSERSELFOTHER
PSROLEOTHER
PSPERMLISTOTHER


SECURITY OPTIONS


PSSECOPTIONS


SIGNON PEOPLECODE


PSSIGNONPPC


SQL


PSSQLDEFN
PSSQLDESCR
PSSQLLANG
PSSQLTEXTDEFN


STYLE SHEETS


PSSTYLECLASS
PSSTYLECLASSFNT
PSSTYLSHEETDEFN
PSSTYLEDEFNLANG
PSSTYLECLSLANG
PSSTYLEFNTLANG


STYLES


PSSTYLEDEFN
PSSTYLEDEFNLANG


SUBSCRIPTIONS


PSSUBDEFN


SYSTEM
PSOPTIONS
SYSTEM PAUSE TIME


PSSPTDEFN
PSSPTIMES


TOOLBARS


PSTOOLBARDEFN
PSTOOLBARITEM


TRANSLATES


PSXLATDEFN
PSXLATITEM
PSXLATITEMLANG


TREE STRUCTURES


PSTREESTRCT
PSTREESTRCTLANG


TREES


PSTREEDEFN
PSTREEDEFNLANG
PSTREEBRANCH
PSTREELEAF
PSTREELEVEL
PSTREENODE
PSTREEPROMPT
PSTREESELCTL
PSTREESELNUM
PSTREESELECT01
PSTREESELECT02
PSTREESELECT03
PSTREESELECT04
PSTREESELECT05
PSTREESELECT06
PSTREESELECT07
PSTREESELECT08
PSTREESELECT09
PSTREESELECT10
PSTREESELECT11
PSTREESELECT12
PSTREESELECT13
PSTREESELECT14
PSTREESELECT15
PSTREESELECT16
PSTREESELECT17
PSTREESELECT18
PSTREESELECT19
PSTREESELECT20
PSTREESELECT21
PSTREESELECT22
PSTREESELECT23
PSTREESELECT24
PSTREESELECT25
PSTREESELECT26
PSTREESELECT27
PSTREESELECT28
PSTREESELECT29
PSTREESELECT30
PSTREESELNUM


URL DEFINITIONS


PSURLDEFN
PSURLDEFNLANG


USER ID TYPES


PSOPRALIASTYPE
PSOPRALIASFIELD


USERS



Note: PSOPRDEFN exported seperately, see top of script


PSOPRALIAS
PSROLEUSER
PSUSERATTR
PSUSEREMAIL
PSUSERPRSNLOPTN
PS_ROLEXLATOPR
PS_RTE_CNTL_RUSER

Peoplesoft and LDAP intgration- how its works

There are 3 scenarios where LDAP may be used with PeopleSoft

Delivered external authentication:

In this instance the customer chooses an attribute in the user object which will contain the PeopleSoft user ID. The login process is configured to access the LDAP server using the user credentials entered in the challenge screen. Signon PeopleCode connects to the LDAP server, retrieves the user object which matches the value entered by the user as the "UserID", extracts the DN from the user object and attempts to BIND the user object using the entered password. If this sequence is successful, Signon PeopleCode extracts the value in the attribute which has been configured as storing the PeopleSoft user ID, usually "uid" and makes a call to SetAuthenticationResult to cache the user profile and log the user into a PeopleSoft session.

Default or Dynamic Role creation:

This is an extension to the authentication functionality above. If the user successfully authenticates against LDAP but does not have an entry in PSOPRDEFN and a default Role has been configured, the entry will be created in PSOPRDEFN and the user will be logged into that default Role in PeopleSoft. This default Role is usually the Self Service Role, so customer PeopleSoft administrators do not have to create an account for every employee, for instance.

With Dynamic Roles, a user account can be created or modified using attribute values in the user object, queries against the PeopleSoft instance or other custom logic.

PeopleSoft Directory Interface (PDI):

This is a licensable option with HCM and developed/supported as an Enterprise Component.
With this option, the LDAP schema is modified with PeopleSoft specific object classes and attributes to create a structure in LDAP which reflects the organizational structure defined in HCM. Messages are created from Workforce Management events to modify the LDAP structure to reflect changes in the workforce.

LDAP authentication and Role management are described in the Security Administration PeopleBook, http://www.oracle.com/applications/peoplesoft/tools_tech/ent/ptools/peoplebook-security-administration.pdf, which is part of the PeopleTools suite., PDI is described in the Enterprise Components PeopleBook, http://download.oracle.com/docs/cd/B40039_02/psft/acrobat/hrcs9ecq-b1206.pdf, which is part of the HCM suite.

PeopleSoft supports LDAP v3, and delivers 4 pre-built configurations:
- Oracle Internet Directory
- Sun Java System Directory Server
- Novell eDirectory
- Microsoft Active Directory
There is also a custom option to allow any other configuration to be defined.