Database Related Permissions

The PeopleSoft Application Server uses the database user context to support the aggregation operations. The database user mentioned in the application configuration should have appropriate rights to fetch data related to the entities and attributes mentioned in the SQL query related to the Person.

  1. Log in to the Oracle database as database administrator. Create the new administrator user account using SQL*Plus as follows:

    create user ${new user} identified by password;

    grant create session to ${new user};

    grant create synonym to ${new user};

  2. Grant permissions to the new administrator user (${new user}):

    grant select on ${Administrator}.PS_PERSONAL_DATA to ${new user}

    grant select on ${Administrator}.PS_PERSONAL_PHONE to ${new user}

    grant select on ${Administrator}.PS_EMAIL_ADDRESSES to ${new user}

    grant select on ${Administrator}.PS_JOB to ${new user}

    grant select on ${Administrator}.PS_PERSON_IMCHAT to ${new user} *This permission is required for aggregation of the NetworkId.

  3. Sign in with the new user name (${new user}) and create the following synonym:

    create synonym PS_PERSONAL_DATA for ${Administrator}.PS_PERSONAL_DATA;

    create synonym PS_PERSONAL_PHONE for ${Administrator}.PS_PERSONAL_PHONE;

    create synonym PS_EMAIL_ADDRESSES for ${Administrator}.PS_EMAIL_ADDRESSES;

    create synonym PS_JOB for ${Administrator}.PS_JOB;

    create synonym PS_PERSON_IMCHAT for ${Administrator}.PS_PERSON_IMCHAT; *This permission is required for aggregation of the NetworkId.

  1. Sign in to the Microsoft SQL Server for creating the administrator user account, and use the following:

    CREATE LOGIN <LOGINUSER> WITH PASSWORD = '<PASSWORD>'

    use [master]

    grant Connect SQL to [user]

  2. Create a corresponding database user and grant permissions to the newly created user (${new user}):

    use [HCM92]

    create user [dbusername] for login [LOGINUSER]

    grant select on PS_PERSONAL_PHONE to [dbusername];

    grant select on PS_EMAIL_ADDRESSES to [dbusername];

    grant select on PS_PERSONAL_DATA to [dbusername];

    grant select on PS_JOB to [dbusername];

    grant select on PS_PERSON_IMCHAT to [dbusername]; *This permission is required for aggregation of the NetworkId.

The driver class is com.ibm.db2.jcc.DB2Driver.

Upload the db2jcc.jar file for the DB2 database.

  1. Create a new OS (Operating System) user and add it to the db2admns OS group. Sign in to the DB2 database as database administrator. Create the new administrator user account using:

    GRANT CONNECT ON DATABASE TO USER ${new user}

  2. Grant permissions to the newly created user (${new user}):

    grant select on ${Administrator}.PS_PERSONAL_DATA to ${new user}

    grant select on ${Administrator}.PS_PERSONAL_PHONE to ${new user}

    grant select on ${Administrator}.PS_EMAIL_ADDRESSES to ${new user}

    grant select on ${Administrator}.PS_JOB to ${new user}

    grant select on ${Administrator}.PS_PERSON_IMCHAT to ${new user} *This permission is required for aggregation of the NetworkId.

  3. Sign in with the new user name (${new user}) and create the following synonym:

    create ALIAS PS_PERSONAL_DATA for ${Administrator}.PS_PERSONAL_DATA;

    create ALIAS PS_PERSONAL_PHONE for ${Administrator}.PS_PERSONAL_PHONE;

    create ALIAS PS_EMAIL_ADDRESSES for ${Administrator}.PS_EMAIL_ADDRESSES;

    create ALIAS PS_JOB for ${Administrator}.PS_JOB;

    create ALIAS PS_PERSON_IMCHAT for ${Administrator}.PS_PERSON_IMCHAT; *This permission is required for aggregation of the NetworkId.