Security Integrity

The following table describes the audit queries and resolutions for this area:

Query Description Resolution

SEC-01

Authorized Signon Operator does not exist in the Class Definition table. Incomplete permission list: Orphan signon times.

(Verifies the existence of permission lists owning signon times.)

Delete the extra signon times. If this is a permission list that should exist, recreate it through PeopleTools Security.

DELETE FROM PSAUTHSIGNON
WHERE CLASSID='x'

SEC-02

Incomplete permission list: Orphan page permissions.

(Verifies the existence of permission lists owning page permissions.)

Delete the extra page permissions. If this is a permission list that should exist, recreate it through PeopleTools Security.

DELETE FROM PSAUTHITEM
WHERE CLASSID='x'

SEC-03

Incomplete permission list: Orphan process groups.

(Verifies existence of permission lists owning process groups.)

Delete the extra process group authorizations. If this is a permission list that should exist, recreate it through PeopleTools Security.

DELETE FROM PSAUTHPRCS
WHERE CLASSID='x'

When the SEC-3 exception does not list the permission list, check the CLASSID of the permission list that are named as a BLANK and delete them using the following script:.

SELECT DISTINCT A.CLASSID FROM PSAUTHPRCS A
WHERE NOT EXISTS(SELECT 'X'FROM PSCLASSDEFN B
WHERE B.CLASSID = A.CLASSID);

Delete FROM PSAUTHPRCS A
WHERE NOT EXISTS(SELECT 'X'FROM PSCLASSDEFN B
WHERE B.CLASSID = A.CLASSID);

SEC-04

Incomplete permission list: Orphan process profiles.

(Verifies existence of permission lists owning process profiles.)

Delete the extra process profiles. If this is a permission list that should exist, recreate it through PeopleTools Security.

DELETE FROM PSPRCSPRFL
WHERE CLASSID='x'

SEC-05

Permission list references a nonexistent process group.

(Verifies the existence of process groups.)

Delete the extraneous process groups. If this group should exist, recreate it.

DELETE FROM PSAUTHPRCS
WHERE CLASSID='x' AND
 PRCSGRP = 'y'

SEC-17

Primary Permission List specified for user does not exist in the Permission List Definition table.

(Verifies the existence of Permission Lists assigned to User Profiles Primary Permission List.)

If this is a permission list that should exist, recreate it through PeopleTools Security. Otherwise, delete the Primary Permission List value for the User Profiles with the following SQL:

UPDATE PSOPRDEFN A SET A.OPRCLASS = ' '
WHERE A.OPRCLASS <> ' ' AND
NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN B
WHERE B.CLASSID = A.OPRCLASS)

SEC-18

User named in a User-Role relationship does not exist in the User Definition table.

(Verifies the existence of Users owning Roles.)

If this is a user that should exist, recreate it through PeopleTools Security. Otherwise, delete the user from the role.

DELETE FROM PSROLEUSER
WHERE NOT EXISTS
(SELECT 'X' FROM PSOPRDEFN B
WHERE B.OPRID = PSROLEUSER.ROLEUSER)

SEC-19

Role named in a User-Role relationship does not exist in the Role Definition table.

(Verifies the existence of Roles owned by Users.)

If this is a role that should exist, recreate it through PeopleTools Security. Otherwise, delete the role from the user.

DELETE FROM PSROLEUSER
WHERE NOT EXISTS
(SELECT 'X' FROM PSROLEDEFN B
WHERE B.ROLENAME = PSROLEUSER.ROLENAME)

SEC-20

Role named in a Role-Permission List relationship does not exist in the Role Definition table.

(Verifies existence of Roles owning Permission Lists.)

If this is a role that should exist, recreate it through PeopleTools Security. Otherwise, delete the role from the permission list.

DELETE FROM PSROLECLASS
WHERE NOT EXISTS
(SELECT 'X' FROM PSROLEDEFN B
WHERE B.ROLENAME = PSROLECLASS.ROLENAME)

SEC-21

Permission List named in a Role-Permission List relationship does not exist in the Permission List Definition table.

(Verifies existence of Permission Lists assigned to Roles.)

If this permission list should exist, recreate it through PeopleTools Security. Otherwise, delete the permission list from the role.

DELETE FROM PSROLECLASS
WHERE NOT EXISTS
(SELECT 'X' FROM PSCLASSDEFN B
WHERE B.CLASSID = PSROLECLASS.CLASSID)

SEC-24

Row Level Security Permission List specified for user does not exist in the Permission List Definition table.

(Verifies the existence of Permission Lists Assigned to User Profiles Row Security Permission List.)

If this is a permission list that should exist, recreate it through PeopleTools Security. Otherwise, delete the Row Security Permission List value for the User Profiles with the following SQL:

UPDATE PSOPRDEFN A SET A.ROWSECCLASS = ' '
WHERE A.ROWSECCLASS <> ' '
AND NOT EXISTS(SELECT 'X' FROM PSCLASSDEFN B
WHERE B.CLASSID = A.ROWSECCLASS)

SEC-25

Process Profile Permission List specified for user does not exist in the Permission List Definition table.

(Verifies the existence of Permission Lists Assigned to User Profiles Process Profile Permission List.)

If this is a permission list that should exist, recreate it through PeopleTools Security. Otherwise, delete the Process Profile Permission List value for the User Profiles with the following SQL:

UPDATE PSOPRDEFN SET PRCSPRFLCLS = ' '
WHERE  PRCSPRFLCLS <> ' ' AND NOT EXISTS
 ( SELECT 'X' FROM PSCLASSDEFN B
WHERE B.CLASSID = PRCSPRFLCLS )

SEC-26

Navigator Homepage Permission List specified for user does not exist in the Permission List Definition table.

(Verifies the existence of Permission Lists Assigned to User Profiles Navigator Homepage Permission List.)

If this is a permission list that should exist, recreate it through PeopleTools Security. Otherwise, delete the Navigator Homepage Permission List value for the User Profiles with the following SQL:

UPDATE PSOPRDEFN A SET A.DEFAULTNAVHP = ' '
WHERE A.DEFAULTNAVHP <> ' ' AND NOT EXISTS
(SELECT 'X' FROM PSCLASSDEFN B
WHERE B.CLASSID = A. DEFAULTNAVHP)

SEC-27

Access Profile specified for user does not exist in the Access Profile table.

If this is an Access Profile that should exist, recreate it through Application Designer or Data Mover, or with help from your DBA. Otherwise, manually change the value to a valid Access Profile or blank through PeopleTools Security. It is not advised to blank out the Access Profile unless you intend to disable the User Profile. If so, you can delete the Access Profile value for the User Profiles with the following SQL:

DELETE FROM PS_SCRTY_SRCHGRP A
WHERE NOT EXISTS
(SELECT 'X' FROM PSCLASSDEFN B
WHERE B.CLASSID = A.CLASSID)

SEC-28

Invalid entries in the PSAUTHITEM table.

See the SEC-28 Resolution section following this table.

SEC-29

The displayed PSPRSMPERM rows contain invalid PORTAL_PERMTYPE values.

Run the following SQL:

DELETE FROM PSPRSMPERM
WHERE PORTAL_PERMTYPE = ' '
 AND EXISTS
(SELECT 'X'
 FROM PSPRSMPERM PP2
 WHERE PSPRSMPERM.PORTAL_NAME =
  PP2.PORTAL_NAME
 AND PSPRSMPERM.PORTAL_REFTYPE =
  PP2.PORTAL_REFTYPE
 AND PSPRSMPERM.PORTAL_OBJNAME =
  PP2.PORTAL_OBJNAME
 AND PSPRSMPERM.PORTAL_PERMNAME
 = PP2.PORTAL_PERMNAME
 AND PP2.PORTAL_PERMTYPE 
 <> ' ');
UPDATE PSPRSMPERM
SET PORTAL_PERMTYPE = 'P'
WHERE PORTAL_PERMTYPE = ' '
 AND EXISTS
(SELECT 'X' FROM
 PSCLASSDEFN
 WHERE CLASSID =
 PSPRSMPERM.PORTAL_PERMNAME);

SEC-30

Missing users in the PS_ROLEXLATOPR table.

Every User that is defined in the PSOPRDEFN table should have a corresponding Role User entry in the PS_ROLEXLATOPR table.

SEC-31

Verify that the user definition table PSOPRDEFN has an entry corresponding to each user assigned to a role.

The role users returned by the audit do not have corresponding user IDs in the PSOPRDEFN table. That is, the user ID’s don’t exist. These role users should be removed from the PS_ROLEXLATOPR table.

Run the following SQL: 

DELETE FROM PS_ROLEXLATOPR A
WHERE NOT EXISTS
  (SELECT 'X'
   FROM PSOPRDEFN B
   WHERE B.OPRID = A.OPRID)

SEC-32

Verify that no inactive roles exist in the PSROLEDEFN table.

The roles returned by the audit need to be fixed by either deleting them or making them active.

To remove a role, use the following SQL: 

DELETE FROM PSROLEDEFN
WHERE ROLESTATUS <> 'A'

Or, use the Delete Roles page. Select PeopleTools, and then Security, and then Permissions and Roles, and then Delete Roles.

To activate inactive roles, use the following SQL: 

UPDATE PSROLEDEFN
SET ROLESTATUS = 'A'
WHERE ROLESTATUS <>'A'

SEC-34

Incomplete permission list: Orphan service operation.

Use the Integration Broker interface to open the service operation listed in the audit. On the Web Service Access page, remove any invalid permission lists.

Or, submit the following SQL: 

DELETE FROM PSAUTHWS
WHERE NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN WHERE PSAUTHWS.CLASSID = PSCLASSDEFN.CLASSID);

SEC-35

Incomplete Permission List: Orphan Search Groups.

(Verifies the existence of Permission Lists owning Search Groups.)

If this is a Permission List that should exist, recreate it through PeopleTools Security. Otherwise, delete the Permission List value for a Search Group with the following SQL:

DELETE FROM PS_SCRTY_SRCHGRP A
WHERE NOT EXISTS
(SELECT 'X' FROM PSCLASSDEFN B
WHERE B.CLASSID = A.CLASSID)

SEC-36

Permission List references a non-existent Search Group.

(Verifies the existence of Search Groups Assigned to Permission Lists.)

Use the Integration Broker interface to open the service operation listed in the audit. On the Web Service Access page, remove any invalid permission lists. Or, submit the following SQL: 

DELETE FROM PS_SCRTY_SRCHGRP A
WHERE NOT EXISTS
(SELECT 'X' FROM PSPTSF_SRCCAT B
WHERE B.PTSF_ISGBLSRCH = 'Y'
AND B.PTSF_SRCCAT_NAME = A.PTSF_SRCHGRP_NAME)

SEC-37

Permission List or Permission List Alias in a Permission List-Permission List Alias relationship does not exist in the Permission List Definition table.

(Verifies the existence of Permission List and Permission List Alias defined in the Permission List-Permission List Alias relationship.)

If the reported Permission List or Permission List Alias should exist, recreate it through PeopleTools Security. Otherwise, delete the Permission List.

Run the following SQL:

DELETE FROM PS_PTCLASSIDALIAS A
WHERE A.CLASSID NOT IN
        (SELECT B.CLASSID FROM PSCLASSDEFN B)
   OR A.PTCLASSIDALIAS NOT IN
        (SELECT B.CLASSID FROM PSCLASSDEFN B)

SEC-38

Role or Role Alias in a Role-Role Alias relationship does not exist in the Role Definition table.

(Verifies the existence of Roles and Role Alias defined in the Role-Role Alias relationship.)

If the orphaned Role or Role Alias should exist, recreate it through PeopleTools Security. Otherwise, delete the Role.

Run the following SQL:

DELETE FROM PS_PTROLENAMEALIAS A
WHERE A.ROLENAME NOT IN
        (SELECT B.ROLENAME FROM PSROLEDEFN B)
   OR A.PTROLENAMEALIAS NOT IN
        (SELECT B.ROLENAME FROM PSROLEDEFN B)

SEC-39

Role Workflow Routing Query does not exist.

If the Workflow Routing Query should exist, recreate it through PeopleTools Query Manager. Otherwise, delete the invalid Role Definition.

SEC-40

Role Dynamic Members Query Rule does not exist.

If the Dynamic Members Query Rule should exist, recreate it through PeopleTools Query Manager. Otherwise, delete the invalid Role Definition.

SEC-41

Role Dynamic Members PeopleCode Rule does not exist.

If the Dynamic Members PeopleCode Rule should exist, recreate it through Application Designer. Otherwise, delete the invalid Role Definition.

SEC-42

Role Dynamic Members LDAP Directory Rule does not exist.

If the Dynamic Members LDAP Directory Rule should exist, recreate it through LDAP Role Membership Rules. Otherwise, edit the Role using PeopleTools Security and deselect the check box named Directory Rule Enabled on the Dynamic Members page of the Roles component.

SEC-43

PSROLEGROUP rows exist for role that does not exist.

If the missing Role should exist, recreate it through PeopleTools Security. Otherwise, delete the invalid Role Group entry.

SEC-44

Orphaned Role Grant references

If the missing Role should exist, recreate it through PeopleTools Security. Otherwise, delete the invalid Role Grant entry.

SEC-28 Resolution

Run the following SQL:

DELETE FROM PSAUTHITEM
WHERE (PSAUTHITEM.MENUNAME NOT LIKE 'WEBLIB_%'
       AND PSAUTHITEM.MENUNAME NOT IN ('CLIENTPROCESS',
                                       'DATA_MOVER',
                                       'IMPORT_MANAGER',
                                       'OBJECT_SECURITY',
                                       'QUERY',
                                       'PERFMONPPMI'
                                      )
       AND PSAUTHITEM.MENUNAME NOT IN ('APPLICATION_DESIGNER', 'OBJECT_RUNTIME')
AND NOT (PSAUTHITEM.MENUNAME = 'ADS_DESIGNER'
       AND PSAUTHITEM.BARNAME = 'TOOLS_DATASETS')
AND PSAUTHITEM.MENUNAME <>'REN'
       AND NOT EXISTS
         (SELECT 'X'
            FROM PSMENUITEM MI
            WHERE PSAUTHITEM.MENUNAME = MI.MENUNAME
              AND PSAUTHITEM.BARNAME = MI.BARNAME
              AND PSAUTHITEM.BARITEMNAME = MI.ITEMNAME
              AND (   MI.ITEMTYPE IN (0, 1, 2, 3, 4, 6, 7, 8, 10, 11)
                   OR (MI.ITEMTYPE = 5
                       AND EXISTS
                         (SELECT 'X'
                            FROM PSPNLGRPDEFN GD, PSPNLGROUP GI
                            WHERE MI.PNLGRPNAME = GD.PNLGRPNAME
                              AND MI.MARKET = GD.MARKET
                              AND GD.PNLGRPNAME = GI.PNLGRPNAME
                              AND GD.MARKET = GI.MARKET
                              AND PSAUTHITEM.PNLITEMNAME = GI.ITEMNAME
                         )
                      )
                   OR (MI.ITEMTYPE = 9
                       AND EXISTS
                         (SELECT 'X'
                            FROM PSPCMNAME PCN, PSPCMPROG PCP
                            WHERE PCN.OBJECTID1 = 3
                              AND PCN.OBJECTVALUE1 = MI.MENUNAME
                              AND PCN.OBJECTID2 = 4
                              AND PCN.OBJECTVALUE2 = MI.BARNAME
                              AND PCN.OBJECTID3 = 5
                              AND PCN.OBJECTVALUE3 = MI.ITEMNAME
                              AND PCN.OBJECTID4 = 12
                              AND PCN.OBJECTVALUE4 = 'ItemSelected'
                              AND PCN.OBJECTID1 = PCP.OBJECTID1
                              AND PCN.OBJECTVALUE1 = PCP.OBJECTVALUE1
                              AND PCN.OBJECTID2 = PCP.OBJECTID2
                              AND PCN.OBJECTVALUE2 = PCP.OBJECTVALUE2
                              AND PCN.OBJECTID3 = PCP.OBJECTID3
                              AND PCN.OBJECTVALUE3 = PCP.OBJECTVALUE3
                              AND PCN.OBJECTID4 = PCP.OBJECTID4
                              AND PCN.OBJECTVALUE4 = PCP.OBJECTVALUE4
                         )
                      )
                   OR (MI.ITEMTYPE = 12
                       AND EXISTS
                         (SELECT 'X'
                            FROM PSXFERITEM XI
                            WHERE MI.MENUNAME = XI.MENUNAME
                              AND MI.ITEMNAME = XI.ITEMNAME
                         )
                      )
                  )
         )
      )
OR (PSAUTHITEM.MENUNAME LIKE 'WEBLIB_%'
    AND NOT EXISTS
      (SELECT 'X'
         FROM PSPCMPROG PCP
         WHERE PCP.OBJECTID1 = 1
           AND PCP.OBJECTVALUE1 = PSAUTHITEM.MENUNAME
           AND PCP.OBJECTID2 = 2
           AND PCP.OBJECTVALUE2 = PSAUTHITEM.BARNAME
      )
   )
OR (PSAUTHITEM.MENUNAME IN ('CLIENTPROCESS',
                            'DATA_MOVER',
                            'IMPORT_MANAGER',
                            'OBJECT_SECURITY',
                            'QUERY',
                            'PERFMONPPMI'
                           )
      AND (PSAUTHITEM.BARNAME <> ' '
           OR PSAUTHITEM.BARITEMNAME <> ' '
           OR PSAUTHITEM.PNLITEMNAME <> ' '
          )
   )
OR (PSAUTHITEM.MENUNAME IN ('APPLICATION_DESIGNER', 'OBJECT_RUNTIME')
      AND ((PSAUTHITEM.BARNAME <> ' '
            AND PSAUTHITEM.BARNAME NOT IN
              (SELECT OBJNAME
                 FROM PS_APP_DES_OBJECTS
                 WHERE PSAUTHITEM.BARNAME = OBJNAME
              )
           )
            OR PSAUTHITEM.BARITEMNAME <> ' '
            OR PSAUTHITEM.PNLITEMNAME <> ' '
          )
   )
OR (PSAUTHITEM.MENUNAME = 'OBJECT_RUNTIME'
      AND PSAUTHITEM.BARNAME = ' ')
OR (PSAUTHITEM.MENUNAME = 'REN'
      AND ((PSAUTHITEM.BARNAME <> ' '
            AND PSAUTHITEM.BARNAME NOT IN
              (SELECT OBJNAME
                 FROM PS_APP_DES_OBJECTS
                 WHERE PSAUTHITEM.BARNAME = OBJNAME
              )
           )
            OR PSAUTHITEM.BARITEMNAME <> ' '
            OR PSAUTHITEM.PNLITEMNAME <> ' '
          )
   )