Finding & Removing Obsolete IDs accessing DB2, on
z/OS
Whenever a person changes
department or company, his/her ID is obsolete on z/OS and needs to be removed
from the system. This is more applicable to the IDs of people leaving the
company. This activity involves finding
out the authorizations held by the obsolete IDs on the Tables, PLANs, PACKAGEs,
etc. Removing the IDs from system without proper analysis can result in
applications crash.
System Admin team found out some
obsolete IDs having SYSADM authority in the system, and asked my (DBA) team to
do the due diligence before the SYSADM authority is revoked from those
IDs.
To come up with the details, I
coded the below SQL queries. Details got from these queries helped a lot in
doing efficient analysis work, before SYSADM authority was revoked from the obsolete
IDs.
Query 1: To
find out the Valid/Invalid PACKAGEs owned by the obsolete IDs
SELECT
TBL.GRANTEE
,TBL.COLLID
,TBL.OWNER
,COUNT(TBL.NAME) AS TOTAL_PK
,COUNT(TBL.VAL) AS VALID_PK
,COUNT(TBL.INVAL) AS INVALID_PK
,COUNT(TBL.OTHERS) AS VALID_OTHERS_PK
,COUNT(TBL.OPER) AS OPER_PK
,COUNT(TBL.NOTOPER) AS
NONOPER_PK
FROM
(SELECT
PA.GRANTEE
,PA.COLLID
,PA.NAME
,PK.OWNER
,CASE
WHEN PK.VALID='Y'
THEN
'VALID'
END VAL
,CASE
WHEN PK.VALID='N'
THEN
'INVALID'
END INVAL
,CASE
WHEN PK.VALID NOT
IN
('Y','N')
THEN
'OTHERS'
END OTHERS
,CASE
WHEN PK.OPERATIVE='Y'
THEN
'OPERATIVE'
END OPER
,CASE
WHEN PK.OPERATIVE='N'
THEN
'BIND/REBIND
NEEDED'
END NOTOPER
FROM
SYSIBM.SYSPACKAUTH
PA
INNER JOIN
SYSIBM.SYSPACKAGE
PK ON
PA.COLLID = PK.COLLID AND
PA.NAME = PK.NAME
WHERE
PA.GRANTEE IN
('ECD9442'
,'ECD9648')) TBL
GROUP
BY
TBL.GRANTEE
,TBL.COLLID
,TBL.OWNER
ORDER
BY
TBL.GRANTEE
,TBL.COLLID
,TBL.OWNER
WITH UR;
Query 2: To find out the authorizations held by the obsolete
IDs on the Tables
SELECT
TA.GRANTEE
,TB.DBNAME
,TB.TSNAME
,TA.TCREATOR
,TA.TTNAME
,TB.TYPE
,TA.DELETEAUTH
,TA.INSERTAUTH
,TA.SELECTAUTH
,TA.UPDATEAUTH
FROM
SYSIBM.SYSTABAUTH
TA
INNER JOIN
SYSIBM.SYSTABLES
TB ON
TA.TCREATOR = TB.CREATOR AND
TA.TTNAME = TB.NAME
WHERE
TA.GRANTEE IN ('ECD9442','ECD9648’)
WITH UR;
Query 3: To find out whether BIND/REBIND needed for the
PACKAGE
SELECT
PA.GRANTEE
,PA.COLLID
,PA.NAME
,PA.BINDAUTH
,PA.COPYAUTH
,PA.EXECUTEAUTH
,CASE
WHEN PK.VALID='Y'
THEN
'VALID
PACKAGE'
WHEN PK.VALID='N'
THEN
'INVALID
PACKAGE'
ELSE PK.VALID
END
,CASE
WHEN PK.OPERATIVE='Y'
THEN
'PACKAGE
CAN BE ALLOCATED'
ELSE 'BIND/REBIND
NEEDED BEFORE PACKAGE ALLOCATION'
END
FROM
SYSIBM.SYSPACKAUTH
PA
INNER JOIN
SYSIBM.SYSPACKAGE
PK ON
PA.COLLID = PK.COLLID AND
PA.NAME = PK.NAME
WHERE
PA.GRANTEE IN
('ECD9442’,'ECD9648’)
ORDER
BY
PA.GRANTEE,PA.COLLID,PA.NAME
WITH UR;
Query 4: To find out whether BIND/REBIND needed for the PLAN
SELECT
PA.GRANTEE
,PA.NAME
,PA.BINDAUTH
,PA.EXECUTEAUTH
,CASE
WHEN PL.VALID='Y'
THEN
'VALID
PLAN'
WHEN PL.VALID='N'
THEN
'INVALID
PLAN'
ELSE PL.VALID
END
,CASE
WHEN PL.OPERATIVE='Y'
THEN
'PLAN
CAN BE ALLOCATED'
ELSE 'BIND/REBIND
NEEDED BEFORE PLAN ALLOCATION'
END
FROM
SYSIBM.SYSPLANAUTH
PA
INNER JOIN
SYSIBM.SYSPLAN
PL ON
PA.NAME = PL.NAME
WHERE
PA.GRANTEE IN 'ECD9442'
ORDER
BY
PA.GRANTEE, PA.NAME
WITH UR;
Query 5: To generate REBIND PACKAGE statements
SELECT
'REBIND PACKAGE '
,CASE
WHEN VERSION =
' '
THEN
CONCAT(CONCAT('(',CONCAT(CONCAT(RTRIM(COLLID,' '),'.'),RTRIM(NAME,' '))),')')
ELSE CONCAT(CONCAT('(',CONCAT(CONCAT(CONCAT(CONCAT(RTRIM(COLLID,' '),'.'),RTRIM(NAME,' ')),'.'),VERSION)),')')
END
,CONCAT('OWNER(DCTOP99)',';')
FROM
SYSIBM.SYSPACKAGE
WHERE
OWNER IN
('ECD9442'
,'ECD9648'
,'LCA8455'
,'LXA8455'
,'PCA8838') AND
COLLID NOT IN
('DB'
,'DB2OSC'
,'DB2XML') AND
TYPE <> 'N'
ORDER
BY
COLLID
,NAME
WITH UR;
I came across an issue while
working on REBIND PACKAGE for changing the OWNER value from obsolete IDs to
super user ID.
We have some Native SQL Stored
Procedures owned by the obsolete IDs. OWNER of the Native SQL Stored Procedure
can't be changed by REBIND PACKAGE..OWNER<owner name> statement, but can
be changed only by altering the Native SQL stored Procedure.
Below ALTER PROCEDURE statement
can fail with -4706 SQL CODE.
ALTER PROCEDURE
"LDADEV2"."ZRBRINVP" ACTIVE VERSION PACKAGE OWNER
"MAINDEV"
DSNT408I
SQLCODE = -4706, ERROR: ALTER STATEMENT FOR AN SQL ROUTINE CANNOT BE
PROCESSED
BECAUSE THE OPTIONS CURRENTLY IN EFFECT (ENVID 58) ARE NOT
THE SAME AS THE
ONES THAT WERE IN EFFECT (ENVID 10) WHEN THE ROUTINE
OR VERSION WAS
FIRST DE .
DSNT418I
SQLSTATE = 530A4 SQLSTATE RETURN CODE
DSNT415I
SQLERRP = DSNXIANR SQL PROCEDURE DETECTING ERROR
DSNT416I
SQLERRD = 35 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I
SQLERRD = X'00000023' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000'
X'00000000' SQL DIAGNOSTIC INFORMATION
The -4706 error occurs when you
try to change the OWNER for a native SQL procedure and one or more environment
characteristics are different versus the environment characteristics associated
with the procedure version (the version you're seeking to alter) when it (the
version you're trying to change) was created via either CREATE PROCEDURE or
ALTER PROCEDURE. Look in the SYSIBM.SYSENVIRONMENT catalog table to see
how the environment characteristics that were in effect when the procedure
version was created differ from those in effect when you issued the ALTER
PROCEDURE statement.
We can’t alter the Procedure without
adding the new Version and activating that, when there is CCSID mismatch. To
ALTER the Procedure, following steps need to be followed.
Step 1: To create the new version.
ALTER PROCEDURE
"LDADEV2"."ZRBRINVP" ADD VERSION
VERSION2
(IN
IN_INVOICE
INTEGER,
IN
IN_PREFIX CHAR( 2) FOR SBCS DATA CCSID
ASCII,
IN
IN_STATUS CHAR( 1) FOR SBCS DATA CCSID
ASCII)
LANGUAGE
SQL
NOT
DETERMINISTIC
MODIFIES
SQL
DATA
CALLED
ON NULL
INPUT
DYNAMIC
RESULT SETS
1
DISALLOW
DEBUG
MODE
PARAMETER
CCSID
ASCII
QUALIFIER
LCA8455
PACKAGE
OWNER
MAINDEV
ASUTIME
NO LIMIT
COMMIT
ON RETURN
NO
INHERIT
SPECIAL
REGISTERS
WLM
ENVIRONMENT FOR DEBUG MODE DB2ET1S4
NODEFER
PREPARE
CURRENT
DATA
NO
DEGREE
1
DYNAMICRULES
RUN
APPLICATION
ENCODING SCHEME
EBCDIC
WITHOUT
EXPLAIN
WITHOUT
IMMEDIATE
WRITE
ISOLATION
LEVEL
CS
WITHOUT
KEEP
DYNAMIC
OPTHINT
''
RELEASE
AT
COMMIT
REOPT
NONE
VALIDATE
RUN
ROUNDING
DEC_ROUND_HALF_EVEN
DECIMAL(15)
BEGIN
UPDATE
LDADEV2.ZRBRINVHDR
SET
STATUS =
'P'
WHERE
INVC_NUMBER = IN_INVOICE AND INVC_PREFIX = IN_PREFIX
AND
STATUS
= IN_STATUS
;
COMMIT
;
END
Step 2: To activate the new
version.
ALTER PROCEDURE
"LDADEV2"."ZRBRINVP" ACTIVATE VERSION VERSION2
No comments:
Post a Comment