Working with DDL Model in PeopleSoft
May 25, 2011 Leave a comment
PeopleSoft clients that use Oracle as their database, by popular practice, create a database user called SYSADM and nominate SYSADM as the owner of all PeopleSoft objects.
As shown in the picture, when a end-user accesses the PIA, Application server connects to the database using SYSADM is. Likewise, regardless of how a developer is connected to App Designer, either in 3-tier or 2-tier mode, the database connection is always established using SYSADM id.
Most clients do not share the SYSADM credentials to anyone except administrators and they do rightly so as a best practice. Instead, developers are given their own database user ids.
In such environments, for a developer to be able to define a record definition in App Designer and access the table in SQL client, these three steps have to be followed:
- Define the record in Application Designer (Developer)
- Build the record (Administrator)
- Grant to developer the access to insert/update/delete/select from the table (Administrator)
Granting access is important, without which, the developer cannot log on to database through a SQL client, (SQL Plus, SQL Developer etc.), and access the table they just created. On describing the table, they would receive a “Table or View does not exist” error.
Administrators follow two methods to grant access:
Method 1. Hand-write the SQL statement to grant access and execute the statement along with the build script.
This method, however, requires a lot of manual effort. Imagine the plight of an administrator when a 100 tables are built.
Method 2. Some others, schedule a script to automatically grant access to all new tables. This method is very efficient, but not necessarily real-time. If the script is scheduled to run every 30 minutes, a developer might have to wait 29 minutes before gaining access to the table.
There’s another method which is both real-time and automatic. The solution lies in the power of DDL Model statements. DDL Model statements are defined at PeopleTools > Utilities > Administration > DDL Model Defaults.
Application designer, when asked to build the script for a table, refers to the DDL Model statement defined for that particular database type – Oracle for the purpose of this blog.
CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;
[TBNAME] is replaced with the record being built.
[TBSPCNAME] is replaced with the tablespace assigned to the record etc.
To automatically, grant access whenever a table is created, I appended
GRANT SELECT, INSERT, UPDATE, DELETE ON [TBNAME] to PS_DEVELOPER;
PS_DEVELOPER is the database role assigned to all PeopleSoft developers.
With the grant statement included in your DDL, Application designer automatically grants developers with access to any new tables created.
Some known loopholes with this method are:
1. This method doesn’t work when altering tables. Grants will be provided to the temporary table PSYJOB, for example.
2. Grants cannot be provided to different roles. The role is always hard-coded in the DDL Model statement.
Its a nice way to extend the possibilities DDLMODEL though.