Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Can I remove privileges on packages that were created by currently non-existing users

    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:
    Code:
    SELECT 
    DISTINCT
    	SUBSTR(A.PKGSCHEMA,1,18) AS PKGSCHEMA,
    	SUBSTR(A.PKGNAME,1,18) AS PKGNAME,
    	A.CONTROLAUTH,
    	A.BINDAUTH,
    	A.EXECUTEAUTH,
    	B.PKG_CREATE_TIME,
    	B.VALID
    FROM
    	SYSCAT.PACKAGEAUTH A,
    	SYSCAT.PACKAGES B
    WHERE
    	A.PKGSCHEMA=B.PKGSCHEMA
    	AND A.PKGNAME=B.PKGNAME
    	AND A.GRANTOR NOT IN ('SYSTEM', 'SYSIBM', 'ADMIN')
      AND A.GRANTEE='PUBLIC'
    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
    Attached Files Attached Files
    Last edited by grofaty; 12-16-11 at 07:27.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by przytula_guy View Post
    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
    How to check the package version and fixpack number of package?
    Last edited by grofaty; 12-16-11 at 12:47.

  4. #4
    Join Date
    Nov 2011
    Location
    Shen Zhen,China
    Posts
    37
    # 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

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    @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?

  6. #6
    Join Date
    Nov 2011
    Location
    Shen Zhen,China
    Posts
    37
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •