10/16/10

Security Integrity Issues

There are a number of security integrity issues that happen after major security changes, during upgrades,  when migrating security definitions from one database to another and after running custom processes to modify security definitions. 

Running Portal Security Sync does not solve all the security integrity issues as it only reinstates the correct security relationships between objects in the portal registry. There can be issues like a permission list referencing a nonexistent process group, a role referencing a permission list which is not there, roles assigned to users who are not there (in PSOPRDEFN), an invalid entry in PSAUTHITEM table. 

Howe do we verify and validate all such security integrity issues? To obtain certain information on PeopleSoft one often tends to overlook PeopleBooks. Neither googling is always required nor a search in My Oracle Support. PeopleBooks offers plenty of information in Data Management book and below table is an excerpt on Security Integrity issues.

Query
Description
Resolution
SEC-1
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-2
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-3
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'
SEC-4
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-5
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-6
User profile references a role that does not exist:
Open the user profile in PeopleTools Security and remove the reference to the Role that does not exist.
SEC-7
Role references a permission list that does not exist:
Open the Role in PeopleTools Security and remove the reference to the Permission List that does not exist.
SEC-8
Role references a user that does not exist in the PSOPRDEFN table.
Remove the user from the PSROLEUSER table.
SEC-9
Permission list references a role that does not exist in the PSROLEDEFN table.
Remove the role from the PSROLECLASS table.
SEC-28
Invalid entries in the PSAUTHITEM table.
(Continues in next row)
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 LIKE ('APPLICATION_DESIGNER%') 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))
SEC-28
(Continued)
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)))))
SEC-28
(Continued)
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 LIKE ('APPLICATION_DESIGNER%') 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 = '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 <> ' ' ))
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, Security, Permissions & Roles, 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 A WHERE NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN B WHERE B.CLASSID = A.CLASSID)

 


2 comments:

  1. Just what i'm looking for. Thanks.

    ReplyDelete
  2. I think they missed one -- I have recently found rows in PSAUTHWS referencing service ops that are not defined (anymore) in PSOPERATIONS:

    SELECT * FROM psauthws a
    where not exists (
    select 'x'
    from psoperation b
    where b.ib_operationname = a.ib_operationname)

    -- there are even several in our DMO instance (Tools 8.51.x), presumably happens when they "undeliver" a service or some of its operations.

    -- Anne

    ReplyDelete

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month