Create the database account

The MCP server connects to PeopleSoft with its own database account. That account is the real guarantee that AI Assist can’t change anything: it can read the PeopleTools tables the tools need, and nothing else. This page gives your database administrator the scripts to create it.

Never reuse PeopleSoft's own accounts

Don’t point the server at your PeopleSoft owner ID, such as SYSADM, or at PeopleSoft’s access ID. Those accounts can change anything in the database. Create a dedicated account, as shown below, in each database you want AI Assist to read.

The tables the tools read

The eleven tools read 28 PeopleTools tables:

Tables	Used by
PSSTATUS	Every tool, to read the PeopleTools release
PSRECDEFN, PSRECFIELD, PSDBFIELD, PSDBFLDLABL	search_definitions, describe_record, find_references
PSINDEXDEFN, PSKEYDEFN	describe_record
PSPNLDEFN, PSPNLFIELD, PSPNLGRPDEFN, PSPNLGROUP	search_definitions, describe_record, describe_component, find_references
PSMENUITEM, PSPRSMDEFN	search_definitions, describe_component, get_security
PSPCMPROG, PSPCMTXT	describe_component, get_peoplecode, search_code, describe_app_engine
PSSQLDEFN, PSSQLTEXTDEFN	search_definitions, get_sql, search_code, describe_app_engine
PSAEAPPLDEFN, PSAESECTDEFN, PSAESTEPDEFN, PSAESTMTDEFN	search_definitions, describe_app_engine
PSXLATITEM	get_translate_values
PSAUTHITEM, PSCLASSDEFN, PSROLECLASS	get_security
PSROLEUSER	get_security, to count the users in each role
PSQRYRECORD, PSQRYFIELD	find_references, to find PeopleSoft Query definitions

PSROLEUSER links user IDs to roles. If you’d rather the server not see it, leave it out: get_security then reports roles without user counts.

Likewise, PSQRYRECORD and PSQRYFIELD store each private query under its owner’s user ID. find_references only counts private queries, but you can leave both tables out: it then reports queries as not checked.

For run_select, also grant read access, in the same way, to each application table you put on its allow-list, and to nothing else.

Find the owner of your PeopleSoft tables

PeopleSoft tables belong to your PeopleSoft owner ID, usually SYSADM on Oracle Database and dbo on SQL Server. The scripts below use those names; if yours differ, find the owner first:

Oracle Database

Run as a DBA

SELECT OWNER
  FROM DBA_TABLES
 WHERE TABLE_NAME = 'PSRECDEFN';

Microsoft SQL Server

Run in the PeopleSoft database

SELECT SCHEMA_NAME(schema_id) AS owner
  FROM sys.tables
 WHERE name = 'PSRECDEFN';

You’ll give the server this owner as AIPS_DB_SCHEMA, so it doesn’t need synonyms.

Create the account

The scripts create an account named AIPS_MCP and grant it read access to each table individually. On Oracle Database, they grant READ rather than SELECT, because SELECT would also let the account lock rows; Read-only access explains why that matters. Replace the password, and the owner and database names if yours differ.

Oracle Database

Run as a DBA

CREATE USER AIPS_MCP IDENTIFIED BY "choose-a-strong-password";
GRANT CREATE SESSION TO AIPS_MCP;

GRANT READ ON SYSADM.PSSTATUS      TO AIPS_MCP;
GRANT READ ON SYSADM.PSRECDEFN     TO AIPS_MCP;
GRANT READ ON SYSADM.PSRECFIELD    TO AIPS_MCP;
GRANT READ ON SYSADM.PSDBFIELD     TO AIPS_MCP;
GRANT READ ON SYSADM.PSDBFLDLABL   TO AIPS_MCP;
GRANT READ ON SYSADM.PSINDEXDEFN   TO AIPS_MCP;
GRANT READ ON SYSADM.PSKEYDEFN     TO AIPS_MCP;
GRANT READ ON SYSADM.PSPNLDEFN     TO AIPS_MCP;
GRANT READ ON SYSADM.PSPNLFIELD    TO AIPS_MCP;
GRANT READ ON SYSADM.PSPNLGRPDEFN  TO AIPS_MCP;
GRANT READ ON SYSADM.PSPNLGROUP    TO AIPS_MCP;
GRANT READ ON SYSADM.PSMENUITEM    TO AIPS_MCP;
GRANT READ ON SYSADM.PSPRSMDEFN    TO AIPS_MCP;
GRANT READ ON SYSADM.PSPCMPROG     TO AIPS_MCP;
GRANT READ ON SYSADM.PSPCMTXT      TO AIPS_MCP;
GRANT READ ON SYSADM.PSSQLDEFN     TO AIPS_MCP;
GRANT READ ON SYSADM.PSSQLTEXTDEFN TO AIPS_MCP;
GRANT READ ON SYSADM.PSAEAPPLDEFN  TO AIPS_MCP;
GRANT READ ON SYSADM.PSAESECTDEFN  TO AIPS_MCP;
GRANT READ ON SYSADM.PSAESTEPDEFN  TO AIPS_MCP;
GRANT READ ON SYSADM.PSAESTMTDEFN  TO AIPS_MCP;
GRANT READ ON SYSADM.PSXLATITEM    TO AIPS_MCP;
GRANT READ ON SYSADM.PSAUTHITEM    TO AIPS_MCP;
GRANT READ ON SYSADM.PSCLASSDEFN   TO AIPS_MCP;
GRANT READ ON SYSADM.PSROLECLASS   TO AIPS_MCP;
GRANT READ ON SYSADM.PSROLEUSER    TO AIPS_MCP;
GRANT READ ON SYSADM.PSQRYRECORD   TO AIPS_MCP;
GRANT READ ON SYSADM.PSQRYFIELD    TO AIPS_MCP;

Microsoft SQL Server

Run as an administrator

USE master;
CREATE LOGIN aips_mcp WITH PASSWORD = 'choose-a-strong-password';

USE HRDEV;
CREATE USER aips_mcp FOR LOGIN aips_mcp;

GRANT SELECT ON dbo.PSSTATUS      TO aips_mcp;
GRANT SELECT ON dbo.PSRECDEFN     TO aips_mcp;
GRANT SELECT ON dbo.PSRECFIELD    TO aips_mcp;
GRANT SELECT ON dbo.PSDBFIELD     TO aips_mcp;
GRANT SELECT ON dbo.PSDBFLDLABL   TO aips_mcp;
GRANT SELECT ON dbo.PSINDEXDEFN   TO aips_mcp;
GRANT SELECT ON dbo.PSKEYDEFN     TO aips_mcp;
GRANT SELECT ON dbo.PSPNLDEFN     TO aips_mcp;
GRANT SELECT ON dbo.PSPNLFIELD    TO aips_mcp;
GRANT SELECT ON dbo.PSPNLGRPDEFN  TO aips_mcp;
GRANT SELECT ON dbo.PSPNLGROUP    TO aips_mcp;
GRANT SELECT ON dbo.PSMENUITEM    TO aips_mcp;
GRANT SELECT ON dbo.PSPRSMDEFN    TO aips_mcp;
GRANT SELECT ON dbo.PSPCMPROG     TO aips_mcp;
GRANT SELECT ON dbo.PSPCMTXT      TO aips_mcp;
GRANT SELECT ON dbo.PSSQLDEFN     TO aips_mcp;
GRANT SELECT ON dbo.PSSQLTEXTDEFN TO aips_mcp;
GRANT SELECT ON dbo.PSAEAPPLDEFN  TO aips_mcp;
GRANT SELECT ON dbo.PSAESECTDEFN  TO aips_mcp;
GRANT SELECT ON dbo.PSAESTEPDEFN  TO aips_mcp;
GRANT SELECT ON dbo.PSAESTMTDEFN  TO aips_mcp;
GRANT SELECT ON dbo.PSXLATITEM    TO aips_mcp;
GRANT SELECT ON dbo.PSAUTHITEM    TO aips_mcp;
GRANT SELECT ON dbo.PSCLASSDEFN   TO aips_mcp;
GRANT SELECT ON dbo.PSROLECLASS   TO aips_mcp;
GRANT SELECT ON dbo.PSROLEUSER    TO aips_mcp;
GRANT SELECT ON dbo.PSQRYRECORD   TO aips_mcp;
GRANT SELECT ON dbo.PSQRYFIELD    TO aips_mcp;

What not to grant

Grant table by table, as above. Broad grants would give the server access to your application data, and to tables like PSOPRDEFN, which stores users’ password hashes, and PSACCESSPRFL, which stores the access ID’s password:

• On Oracle Database, don’t grant roles such as DBA or RESOURCE, or system privileges such as SELECT ANY TABLE.
• On SQL Server, don’t add the user to db_datareader, db_owner, or any server role.

Limit the account’s sessions (optional)

The server keeps a small connection pool and applies its own timeouts, but you can also cap the account in the database. On Oracle Database, a profile limits its sessions and ends idle ones:

Oracle Database: optional profile

CREATE PROFILE AIPS_MCP_PROFILE LIMIT
  SESSIONS_PER_USER 10
  IDLE_TIME 60;

ALTER USER AIPS_MCP PROFILE AIPS_MCP_PROFILE;

IDLE_TIME is in minutes. Profile limits like these are enforced when the RESOURCE_LIMIT initialization parameter is TRUE.

Verify the account

Connect as the new account and run these read-only queries. The first proves the connection and read access, and the others list everything the account can do.

Oracle Database

Run as AIPS_MCP

-- Connection and read access: returns the PeopleTools release
SELECT TOOLSREL FROM SYSADM.PSSTATUS;

-- System privileges: expect only CREATE SESSION
SELECT PRIVILEGE FROM SESSION_PRIVS;

-- Table privileges: expect READ on the 28 tables, and nothing else
SELECT TABLE_NAME, PRIVILEGE
  FROM USER_TAB_PRIVS
 ORDER BY TABLE_NAME;

Microsoft SQL Server

Run as aips_mcp

-- Connection and read access: returns the PeopleTools release
SELECT TOOLSREL FROM dbo.PSSTATUS;

-- Permissions: expect CONNECT, and SELECT on the 28 tables
SELECT permission_name, OBJECT_NAME(major_id) AS object_name
  FROM sys.database_permissions
 WHERE grantee_principal_id = USER_ID()
 ORDER BY object_name;

The server runs the same kind of checks itself: its check command, covered in Get and configure the server, reports any table it can’t read and any privilege beyond read access as an error.