If you've ever done PeopleSoft security, I'm sure you've had somebody ask you to "Set Bob up like Ann, but make sure Bob can't change anything." Which is fine if you have the read-only permission lists to do it. But building permission lists from scratch can be time consuming. Here's a couple of SQL's to make your task easier.
First, you'll want to copy Ann's permission lists, and name them so you'll know they're read-only. I generally append an "R" at the end, but whatever naming convention works for you. Using the online Copy Permission List functionality is the easiest.
Then for each permission list, run the following SQL to make them read-only. Replace &1 with the name of the permission list. It'll make an effort to leave Inquire Only and Report pages read/write so you can create run controls.
update psauthitem a
set authorizedactions = case when bitand(authorizedactions,1)>0 then authorizedactions -1 else authorizedactions end,
displayonly = 1
where classid = '&1' and
barname not like 'REPORT%' and barname not like 'INQ%' and barname not like 'RPT%'
/
UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'CLM'
/
UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'SYS'
/
UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'CLM'
/
UPDATE PSCLASSDEFN SET VERSION = (SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'CLM') where CLASSID = '&1'
/
The script isn't 100% accurate, so you'll probably want to run a query like the following so that you can see what access the user is left with and whether it's appropriate or not:
select distinct AUTH.CLASSID, A.MENUNAME, D.PNLGRPNAME, B.ITEMLABEL,
rtrim((case when bitand(authorizedactions,1) > 0 then 'A,' END ||
case when bitand(authorizedactions,2) > 0 then 'U,' END ||
case when bitand(authorizedactions,4) > 0 then 'U+,' END ||
case when bitand(authorizedactions,8) > 0 then 'C,' END ||
case when displayonly = 1 then 'V,' END),',') AuthAction
FROM PSAUTHITEM AUTH, PSMENUDEFN A, PSMENUITEM B, PSPNLGROUP C, PSPNLGRPDEFN D
WHERE A.MENUNAME = B.MENUNAME AND
B.PNLGRPNAME = C.PNLGRPNAME AND
D.PNLGRPNAME = C.PNLGRPNAME AND
AUTH.MENUNAME = A.MENUNAME AND
AUTH.BARNAME = B.BARNAME AND
AUTH.BARITEMNAME = B.ITEMNAME AND
AUTH.PNLITEMNAME = C.ITEMNAME AND
AUTH.CLASSID = '&1'
ORDER BY A.MENUNAME, D.PNLGRPNAME
/ My apologizes for non-Oracle database users -- bitand is an Oracle-specific function and I don't know any good equivalents for SQL Server or other platforms. If you do please share!
Comments () |
 |
|
|
|
 |
*2 > 0 THEN 'U ,' END ||