Informix Guide to SQL: Tutorial Modifying Data

You can use the following database privileges to control who accesses a database:

• Database-level privileges
• Table-level privileges
• Column-level privileges
• Routine-level privileges

This section briefly describes database- and table-level privileges. For more information about database privileges, see the Informix Guide to Database Design and Implementation. For a list of privileges and a description of the GRANT and REVOKE statements, see the Informix Guide to SQL: Syntax.

When you create a database, you are the only one who can access it until you, as the owner or database administrator (DBA) of the database, grant database-level privileges to others. The following table shows the database-level privileges.

When you create a table in a database that is not ANSI compliant, all users have access privileges to the table until you, as the owner of the table, revoke table-level privileges from specific users. The following table introduces the four privileges that govern how users can access a table.

The people who create databases and tables often grant the Connect and Select privileges to public so that all users have them. If you can query a table, you have at least the Connect and Select privileges for that database and table.

You need the other table-level privileges to modify data. The owners of tables often withhold these privileges or grant them only to specific users. As a result, you might not be able to modify some tables that you can query freely.

Because these privileges are granted on a table-by-table basis, you can have only Insert privileges on one table and only Update privileges on another, for example. The Update privileges can be restricted even further to specific columns in a table.

For more information on these and other table-level privileges, see the Informix Guide to Database Design and Implementation.

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.

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.