Hi,
we have production system on DB2 v8.2 fp9 on Windows 2003 and database is working without any problem at all. Database is configured to use Windows security which is default setting. I have checked the privileges on all SYSCAT.xxxAUTH tables and found out that there are some packages on SYSCAT.PACKAGEAUTH table that were granted by users that does not exists anymore. Database was migrated several times from one Windows computer to another and on production computer there are now some privileges set by the user that is not defined anymore on the operating system.

I executed bellow SQL to get all of the privileges that were granted by the users that are not defined on Windows anymore:
Questions:
1. I would like to remove all the privileges from PUBLIC group that are not 100% necessary. For example: user1 has granted the execute privilege on package to PUBLIC group. But this user1 does not exists any more on the Windows. Are the privileges granted by now non-existing users still valid? Is it save to remove those privileges or not?

2. Executing above SQL I see there are some old packages that most probably were created on one of the pre-migrated system. What are this packages all about? I see package names start with SQLxxx so they must be a system packages. Most probably some old db2 system bind that was executed by db2 admin. Most of the packages (see attachment) have a VALID=N. According to documentation "N = Needs rebinding" IBM DB2 9.7 for Linux, UNIX and Windows Information Center So it looks like this packages are not valid anymore and are not used by anyone on the system. Is it save to remove them? If there are some function or some other dependencies I will probably get error like 'function depends on this package', so deleting function first would solve the problem. Is it save to remove such packages (VALID=N)?

3. I have found out that there are some of the packages that were execute granted with two users. Looking into syscat.pacakgeauth view it looks package has the same privileges set (the only difference is grantor). Is it save to remove duplicate grants (so leaving only single execute grant to package)?

Thanks

db2 does not check the user physically - granted by
yes the grant is still valid
even grant to non-exist user is not checked
packages with schema nullid are packages created by bind of db2cli - db2ubind....
for each fp/level there are new packages
in the pakage header the v/r can be identified
if you have no clients anymore with this level - packages can be dropped - otherwise tey will get -805 and need bind again..

How to check the package version and fixpack number of package?

# Revoke all privileges on packages for UID:
db2 "select 'REVOKE ' || strip(strip(case when CONTROLAUTH IN ('Y', 'G') then 'CONTROL, ' else '' end ||case when BINDAUTH IN ('Y', 'G') then 'BIND, ' else '' end ||case when EXECUTEAUTH IN ('Y', 'G') then 'EXECUTE, ' else '' end, trailing, ' '), trailing, ',') || ' ON PACKAGE ' || rtrim(PKGSCHEMA) ||'.' || PKGNAME || ' FROM ' ||case when granteetype = 'U' then 'USER ' else 'GROUP ' end ||rtrim(grantee) || ';' from syscat.PACKAGEAUTH where grantee IN ('DB2PROD')"

TIP: ***
1 revoke bind & execute on package should run AFTER revoke control, or it won't work

2 for system packages you have to use:
revoke execute on specific function xxxx from xxx restrict

3 for error:
SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "PACKAGE"
cannot be processed because there is an object "TAP.SP_HELP", of type
"PROCEDURE", which depends on it. SQLSTATE=42893

you have to drop and re-create it。
***



db2bfd can show the detail of the bind file

Example:
db2bfd -b asnsqlcd.bnd

asnsqlcd.bnd: Header Contents

Header Fields:

Field Value
----- -----
releaseNum 0x800
Endian 0x42
numHvars 9
maxSect 4
numStmt 29
optInternalCnt 4
optCount 9


Name Value
------------------ -----
Isolation Level Uncommitted Read
Creator "NULLID "
App Name "ASNDD9A0"
Timestamp "ASNCOMON:User defined timestamp"
Cnulreqd Yes
Sql Error No package
Validate Bind
Date Default/local
Time Default/local


*** All other options are using default settings as specified by the server ***

also check the info centre:

IBM DB2 9.7 for Linux, UNIX and Windows Information Center

@wolaos123, thanks for answering. What I am now wondering is what are packages like NULLID.SQLA2D01 (the whole package list is attached in my first post) all about? What does SQLA2D01 means? Most probably some db2cli bind file has created them many many years ago and this packages are not needed any more, but I have to be 100% sure there are not needed any more. How to find out which version/fixpack of DB2 this package belongs to?

not sure if a system package from v8 or lower,at least I can't find it since v9.1 or higer

here is the link for your referrence.

IBM DB2 v9.5 Bind File and Package Name List - United States