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

All you really need is syscat.tabauth.

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

HTH

Andy

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

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

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

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

Thanks alot, Andy!

Where is this information found?

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

Look in the SQL Reference Manual (Vol1 for V 8.1). It is in one of the appendixes (catalog views).

Andy