Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42

    Unanswered: querying the system tables...

    Greetings,

    I need a query that lists all the tables in the database and their associated user rights by table (read/update). I have been searching the system tables and am unsure of which table to query for this information. Can anyone assist?

    The closest one I have found is: "select * from sysibm.systabauth". But, I need a query that lists our user tables, not the system ones.

    Thx

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    All you really need is syscat.tabauth.

    SELECT * from syscat.tabauth where tabschema = 'MySchema'

    HTH

    Andy

  3. #3
    Join Date
    Mar 2004
    Posts
    24

    Query System Tables for Permissions by User

    Hello,


    If you want more of a report look try this query.

    select grantee,ttname, selectauth, insertauth, updateauth, deleteauth from sysibm.systabauth where tcreator not in ('SYSIBM','SYCAT','PUBLIC')order by grantee,ttname asc;

    Thanks,

    Beck

  4. #4
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42

    Thumbs up

    Thanks for those queries... It looks like I can use them. Thanks again.

  5. #5
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42
    Does anyone know where the values of the fields are defined or documented anywhere for the system table sysibm.sysauth?

    I am assuming this, is this correct?

    y - yes, they have the privelege
    n - no, they do not have the privelege
    g - granted, and can grant this authority to another user/group

    Thx

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not know anything about sysibm.sysauth table (it doesn't exist in UDB). But for tabauth the following documentation is from syscat.tabauth:

    SYSCAT.TABAUTH catalog view

    Contains one or more rows for each user or group who is granted a privilege on a particular table or view in the database. All the table privileges for a single table or view granted by a specific grantor to a specific grantee appear in a single row.

    Table 45. SYSCAT.TABAUTH Catalog View
    Column Name Data Type Nullable Description
    GRANTOR VARCHAR(128)
    Authorization ID of the user who granted the privileges or SYSIBM.
    GRANTEE VARCHAR(128)
    Authorization ID of the user or group who holds the privileges.
    GRANTEETYPE CHAR(1)


    U = Grantee is an individual user.

    G = Grantee is a group.


    TABSCHEMA VARCHAR(128)
    Qualified name of the table or view.
    TABNAME VARCHAR(128)
    CONTROLAUTH CHAR(1)
    Indicates whether grantee holds CONTROL privilege on the table or view:

    Y = Privilege is held.

    N = Privilege is not held.

    ALTERAUTH CHAR(1)
    Indicates whether grantee holds ALTER privilege on the table:

    Y = Privilege is held.

    N = Privilege is not held.

    G = Privilege is held and grantable.

    DELETEAUTH CHAR(1)
    Indicates whether grantee holds DELETE privilege on the table or view:

    Y = Privilege is held.

    N = Privilege is not held.

    G = Privilege is held and grantable.

    INDEXAUTH CHAR(1)
    Indicates whether grantee holds INDEX privilege on the table:

    Y = Privilege is held.

    N = Privilege is not held.

    G = Privilege is held and grantable.

    INSERTAUTH CHAR(1)
    Indicates whether grantee holds INSERT privilege on the table or view:

    Y = Privilege is held.

    N = Privilege is not held.

    G = Privilege is held and grantable.

    SELECTAUTH CHAR(1)
    Indicates whether grantee holds SELECT privilege on the table or view:

    Y = Privilege is held.

    N = Privilege is not held.

    G = Privilege is held and grantable.

    REFAUTH CHAR(1)
    Indicates whether grantee holds REFERENCE privilege on the table or view:

    Y = Privilege is held.

    N = Privilege is not held.

    G = Privilege is held and grantable.

    UPDATEAUTH CHAR(1)
    Indicates whether grantee holds UPDATE privilege on the table or view:

    Y = Privilege is held.

    N = Privilege is not held.

    G = Privilege is held and grantable.


    HTH

    Andy

  7. #7
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42
    Thanks alot, Andy!

    Where is this information found?

    I would love to be able to look this up myself .

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look in the SQL Reference Manual (Vol1 for V 8.1). It is in one of the appendixes (catalog views).

    Andy

Posting Permissions

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