Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2012
    Posts
    10

    Unanswered: Missing entries in SYSIBM.SYSTABAUTH Table

    Hi,

    We have found that the privilege records are totally missing for one of our databases in SYSIBM.SYSTABAUTH table, though the database objects are showing up in SYSIBM.SYSTABLES table and privileges were granted for each of the tables created.

    I am very confused. Does anyone have a clue as to what is happening and the reason for this problem?

    Thank You,

    sb

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Please provide the database version, release, and operating system.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2012
    Posts
    10

    Missing entries in SYSIBM.SYSTABAUTH Table

    Please provide the database version, release, and operating system.

    I am sorry. I think I was in too much of a hurry to post the question.

    We are using DB2 V9.1 on z/OS (mainframes).

    Thanks,

    sb

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The SQLID issuing the grants did not have authority to issue the grants? The SQLID that issued the grants was revoked?

  5. #5
    Join Date
    Nov 2012
    Posts
    10
    The SQL ID that I used was able to grant the privileges and the batch jobs run to update tables completed successfully. It is still confusing why the DB2 catalog table SYSTABAUTH is not reflecting the privileges. This is a new database and all privilege records are missing for this new database.

    Thanks,

    sb

  6. #6
    Join Date
    Nov 2012
    Posts
    10

    Missing entries in SYSIBM.SYSTABAUTH Table

    The SQL ID that I used was able to grant the privileges and the batch jobs run to update tables completed successfully. It is still confusing why the DB2 catalog table SYSTABAUTH is not reflecting the privileges. This is a new database and all privilege records are missing for this new database.

    Thanks,

    sb

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There are some authorizations at the database level that do not require specification for each table. Check SYSIBM.SYSDBAUTH.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Nov 2012
    Posts
    10

    Missing entries in SYSIBM.SYSTABAUTH Table

    I checked SYSDBAUTH table and everything looks fine there. I compared the authorization levels for a database that is working fine with the new database and they have similar authorization levels for all SQL ids that are associated with them. Not sure what is causing this problem.

    sb1965

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what grants were issued?

  10. #10
    Join Date
    Nov 2012
    Posts
    10
    All of them including CREATETABAUTH
    CREATETSAUTH
    DBADMAUTH
    DBCTRLAUTH
    DBMAINTAUTH
    DISPLAYDBAUTH
    DROPAUTH
    IMAGCOPYAUTH
    LOADAUTH
    REORGAUTH
    RECOVERDBAUTH
    REPAIRAUTH
    STARTDBAUTH
    STATSAUTH
    STOPAUTH

    Thanks,

    sb1965

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    and which of those should be displaying anything in systabauth???

  12. #12
    Join Date
    Nov 2012
    Posts
    10
    all of them should have either 'G' or 'Y' i systabauth. But there are no records at all in systabauth table for the new db.

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    in systabauth??? Methinks you would only see that for selectauth, insertauth, indexauth, alterauth, updateauth and deletauth, but you did not specify any of those as the grants you executed.
    Maybe you should be looking in the other *AUTH tables for the grants you said you executed???
    CREATETABAUTH
    CREATETSAUTH
    DBADMAUTH
    DBCTRLAUTH
    DBMAINTAUTH
    DISPLAYDBAUTH
    DROPAUTH
    IMAGCOPYAUTH
    LOADAUTH
    REORGAUTH
    RECOVERDBAUTH
    REPAIRAUTH
    STARTDBAUTH
    STATSAUTH
    STOPAUTH

    Where do they reside???

  14. #14
    Join Date
    Nov 2012
    Posts
    10
    Both databases reside in the same DB2 sub-system on the same mainframe system. I found the problem (at least one problem). I was querying the SYSTABAUTH table based on the DBNAME value and looking for the new database name. It was returning ZERO records. I then queried based on the table names (TTNAME) I created and it then displayed the records. But here is the real problem. The DBNAME for all the tables in the new database is just a Blank.

    As I checked the documentation for SYSTABAUTH table, it has the following for DBNAME column.

    "If the privileges were received from a user with DBADM, DBCTRL, or DBMAINT authority, DBNAME is the name of the database on which the GRANTOR has that authority. Otherwise, DBNAME is blank."

    The SQL id that created the tables and executed the grants has the DBADM authority according to SYSDBAUTH table. So, the question for the DB2 world is why would the DBNAME in SYSTABAUTH table be a blank despite having DBADM authority on the database? I think we are getting there gradually. Please let me know if any one has any clue. I have not seen such a problem before.

    Thanks so much,

    sb1965

  15. #15
    Join Date
    Nov 2012
    Posts
    10
    Anyone has a clue on this problem? Kind of lost on this one.

    sb1965

Posting Permissions

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