In PeopleSoft you can use datamover to add SQL security.  The reason for this is even though you log into PeopleSoft as a specific user once the user is verified it actually uses the user that is associated with the psaccessprfl access profile.  This means that in oracle typically the user sysadm is the user behind the access profile, and since the sysadm user is typically built using the PSADMIN role profile, you have priviledges to run a lot of commands from datamover.

A good example of this is when you build a table in PeopleSoft but you can’t see it in SQL because the user account you have in Oracle does not access to the new table.  So in order to grant access to this you can do a couple of different statements.  The simplest is to grant select access on the new table to a specific SQL user account:

grant select on new_table to sql_user;

Often times there are roles setup that are assigned to a user, if you do a:

select * from dba_roles;

You can see all the roles that are on the system.  Typically there is a role that will be setup that will allow “select” access and another role that is setup to allow “select, update, delete, insert”, sometimes referred to as SUDI access.  So lets say for example there is a role called SUDI_ALL_TABS, you can issue the statement in datamover:

grant select, update, delete, insert on new_table to SUDI_ALL_TABS;

Note: The new_table needs to have the ps_ qualifier on it, as this is not a typical function of datamover and therefore needs the fully qualified table name.

When trying to execute a crystal check on one of my development systems I found a problem after trying to fix another problem.  All the crystals were going to error status and the message was coming up:

File: SQL Access ManagerSQL error. Stmt #: 2  Error Position: 0  Return: 404 – ORA-01017: invalid username/password; logon denied  (200,0)

It turns out that the PSACCESSPRFL table had an extra entry which was invalid for the environment.  Once I removed the invalid entry from the PSACCESSPRFL table the problem was resolved.

The typical content reference that references a component will automatically pull its security based on security assigned to a permission list(s).  However, if you put that component in multiple places within the registry, PeopleSoft will role-up permissions to the folders, and all of sudden you have end-users with access to menu folders that typically only an administrator would be allowed to see.  To control this, you can set the content reference to “public”, and then at the folder level add the necessary security, so for an administrative content reference, the parent folder would only have the administrative roles/permissions, and the content reference would be public.  This way the end-user can’t see the content reference in the administrative menu because they don’t have access to the parent folder level.

PeopleSoft created the Portal Registry based on same old fundamental designs that has some limitations to them.  One of those limitations is using the same component to do different functions.  There are times where a component could be used for multiple operations for example: Self Service. But the administrative functions could use it as well.  The option is to create your content reference the same way as always, but in the parameters add something like “attr1=admin”.  This will pass in a parameter to the content reference, so when you go to that new content reference, you can reference in the peoplecode using the call:  %Request.GetParameter(“attr1”); This will return the variable “admin” and you can do the necessary page operations for the administrative function.  Subsequently you can do the same thing again for the self service page where the parameter is: “attr1=selfservce”.  You can create an unlimited number of content references as long as they have a unique set of parameters.