You can apply seven privileges, table by table, to allow nonowners the privileges of owners. Four of them, the Select, Insert, Delete, and Update privileges, control access to the contents of the table. The Index privilege controls index creation. The Alter privilege controls the authorization to change the table definition. The References privilege controls the authorization to specify referential constraints on a table.
In an ANSI-compliant database, only the table owner has any privileges. In other databases, the database server, as part of creating a table, automatically grants to public all table privileges except Alter and References. When you automatically grant all table privileges to public, a newly created table is accessible to any user with the Connect privilege. If this is not what you want (if users exist with the Connect privilege who should not be able to access this table), you must revoke all privileges on the table from public after you create the table.
Four privileges govern how users can access a table. As the owner of the table, you can grant or withhold the following privileges independently:
The Select privilege is necessary for a user to retrieve the contents of a table. However, the Select privilege is not a precondition for the other privileges. A user can have Insert or Update privileges without having the Select privilege.
For example, your application might have a usage table. Every time a certain program is started, it inserts a row into the usage table to document that it was used. Before the program terminates, it updates that row to show how long it ran and perhaps to record counts of work its user performs.
If you want any user of the program to be able to insert and update rows in this usage table, grant Insert and Update privileges on it to public. However, you might grant the Select privilege to only a few users.
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 privileges are recorded in the sysusers system catalog table, in which the primary key is user ID, and the only other column contains a single character C, R, or D for the privilege level. A grant to the keyword of PUBLIC is reflected as a user name of public (lowercase).
Table-level privileges are recorded in systabauth, which uses a composite primary key of the table number, grantor, and grantee. In the tabauth column, the privileges are encoded in the list that Figure 24 shows.
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.
The Index privilege permits its holder to create and alter indexes on the table. The Index privilege, similar to the Select, Insert, Update, and Delete privileges, is granted automatically to public when you create a table.
You can grant the Index privilege to anyone, but to exercise the privilege, the user must also hold the Resource database privilege. So, although the Index privilege is granted automatically (except in ANSI-compliant databases), users who have only the Connect privilege to the database cannot exercise their Index privilege. Such a limitation is reasonable because an index can fill a large amount of disk space.
The Alter privilege permits its holder to use the ALTER TABLE statement on the table, including the power to add and drop columns, reset the starting point for SERIAL columns, and so on. You should grant the Alter privilege only to users who understand the data model well and whom you trust to exercise their power carefully.
The References privilege allows you to impose referential constraints on a table. As with the Alter privilege, you should grant the References privilege only to users who understand the data model well.
You can grant or revoke the Under privilege to control whether users can use a typed table as a supertable in an inheritance hierarchy. The Under privilege is granted to public automatically when a table is created (except in ANSI-compliant databases). In an ANSI-compliant database, the Under privilege on a table is granted to the owner of the table. To restrict which users can define a table as a supertable in an inheritance hierarchy, you must first revoke the Under privilege for public and then specify the users to whom you want to grant the Under privilege. For example, to specify that only a limited group of users can use the employee table as a supertable in an inheritance hierarchy, you might execute the following statements:
REVOKE UNDER ON employee FROM PUBLIC; GRANT UNDER ON employee TO johns, cmiles, paulz
For information about how to use the UNDER clause to create tables in an inheritance hierarchy, see Table Inheritance.
Use the GRANT FRAGMENT statement to grant insert, update, and delete privileges on individual fragments of a fragmented table. The GRANT FRAGMENT statement is valid only for tables that are fragmented with expression-based distribution schemes.
Suppose you create a customer table that is fragmented by expression into three fragments, which reside in the dbspaces dbsp1, dbsp2, and dbsp3. The following statement shows how to grant insert privileges on the first two fragments only (dbsp1 and dbsp2) to users jones, reed, and mathews.
GRANT FRAGMENT INSERT ON customer (dbsp1, dbsp2) TO jones, reed, mathews
To grant privileges on all fragments of a table, use the GRANT statement or the GRANT FRAGMENT statement.
For information on the GRANT FRAGMENT and REVOKE FRAGMENT statements, see the IBM Informix: Guide to SQL Syntax.