Privileges in the system catalog tables

Database-level and table-level privileges are recorded in the system catalog tables. Any user with the Connect privilege can query the system catalog tables to determine what privileges are granted and to whom.

Database-level privileges and roles are recorded in the sysusers system catalog table, in which the primary key is the username column, and the usertype column contains a single character C (for Connect), R (for Resource), or D (for DBA) that specifies the highest database-level privilege that username holds, or G if username is the authorization identifier of a role. The last column, defrole, stores the default role if username holds a default role. (Neither a default role nor database- level privileges can be granted to a role, but a role can hold other access privileges, such as table-level privileges, and a role can be granted a non-default role.) The username in the row that shows the highest database-level privilege held by the PUBLIC group is public.

Table-level privileges are recorded in systabauth system catalog table, which uses a composite primary key of the table number, grantor, and grantee. In the tabauth column, the privileges are encoded in the list as follows.

Code: Meaning
s: unconditional select
u: update
-: ungranted privileges
i: insert
d: delete
x: index
a: alter
r: references

A hyphen means an ungranted privilege, so that a grant of all privileges is shown as su-idxar, and -u------ shows a grant of only Update. The code letters are normally lowercase, but they are uppercase when the keywords WITH GRANT OPTION are used in the GRANT statement.

When an asterisk (*) appears in the third position, some column-level privilege exists for that table and grantee. The specific privilege is recorded in syscolauth. Its primary key is a composite of the table number, the grantor, the grantee, and the column number. The only attribute is a three-letter list that shows the type of privilege: s, u, or r.