Display table privileges

If you are the owner of a table (that is, if you created it), you have all privileges on that table. Otherwise, you can determine the privileges you have for a certain table by querying the system catalog. The system catalog consists of system tables that describe the database structure. The privileges granted on each table are recorded in the systabauth system table. To display these privileges, you must also know the unique identifier number of the table. This number is specified in the systables system table. To display privileges granted on the orders table, you might enter the following SELECT statement:

SELECT * FROM systabauth
  WHERE tabid = (SELECT tabid FROM systables
                       WHERE tabname = 'orders');

The output of the query resembles the following example:

grantorgrantee tabid               tabauth

tfecitmutator  101      su-i-x--
tfecitprocrustes101     s--idx--
tfecitpublic   101      s--i-x--

The grantor is the user who grants the privilege. The grantor is usually the owner of the table but the owner can be another user that the grantor empowered. The grantee is the user to whom the privilege is granted, and the grantee public means any user with Connect privilege. If your user name does not appear, you have only those privileges granted to public.

The tabauth column specifies the privileges granted. The letters in each row of this column are the initial letters of the privilege names, except that i means Insert and x means Index. In this example, public has Select, Insert, and Index privileges. Only the user mutator has Update privileges, and only the user procrustes has Delete privileges.

Before the database server performs any action for you (for example, execution of a DELETE statement), it performs a query similar to the preceding one. If you are not the owner of the table, and if the database server cannot find the necessary privilege on the table for your user name or for public, it refuses to perform the operation.