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.