INFORMIX
Informix Guide to SQL: Tutorial
Chapter 11: Granting and Limiting Access to Your Database
Home Contents Index Master Index New Book

Granting Privileges

 The authorization to use a database is called a privilege. For example, the authorization to use a database is called the Connect privilege, and the authorization to insert a row into a table is called the Insert privilege. You control the use of a database by granting these privileges to other users or by revoking them.

The following groups of privileges control the actions a user can perform on data.

Database-Level Privileges

 The three levels of database privilege (Connect, Resource, and DBA) provide an overall means of controlling who accesses a database.

Connect Privilege

The least of the privilege levels is Connect, which gives a user the basic ability to query and modify tables. Users with the Connect privilege can perform the following functions:

Before users can access a database, they must have the Connect privilege. Ordinarily, in a database that does not contain highly sensitive or private data, you give the GRANT CONNECT TO PUBLIC privilege shortly after you create the database.

If you do not grant the Connect privilege to public, the only users who can access the database through the database server are those to whom you specifically grant the Connect privilege. If limited users should have access, this privilege lets you provide it to them and deny it to all others.

The Users and the Public
Privileges are granted to single users by name or to all users under the name of public. Any privileges granted to public serve as default privileges.

Prior to executing a statement, the database server determines whether a user has the necessary privileges. (The information is in the system catalog; see "Privileges in the System Catalog".)

The database server looks first for privileges that are granted specifically to the requesting user. If it finds such a grant, it uses that information. It then checks to see if less restrictive privileges have been granted to public. If so, the database server uses the less-restrictive privileges. If no grant has been made to that user, the database server looks for privileges granted to public. If it finds a relevant privilege, it uses that one.

Thus, to set a minimum level of privilege for all users, grant privileges to public. You can override that, in specific cases, by granting higher individual privileges to users.

Resource Privilege

The Resource privilege carries the same authorization as the Connect privilege. In addition, users with the Resource privilege can create new, permanent tables, data types (opaque, distinct, complex), indexes, and stored routines, thus permanently allocating disk space.

Database Administrator Privilege

The highest level of database privilege is Database Administrator, or DBA. When you create a database, you are automatically the DBA. Holders of the DBA privilege can perform the following functions:

Warning: Although users with the DBA privilege can modify most system catalog tables, Informix strongly recommends that you do not update, delete, or insert any rows in them. Modifying the system catalog tables can destroy the integrity of the database. Informix does support using the ALTER TABLE statement to modify the size of the next extent of system catalog tables.

Ownership Rights

 The database, and every table, view, index, procedure, and synonym in it, has an owner. The owner of an object is usually the person who created it, although a user with the DBA privilege can create objects to be owned by others.

The owner of an object has all rights to that object and can alter or drop it without additional privileges.

Table-Level Privileges

You can apply several 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. The Under privilege controls the authorization to define a table in an inheritance hierarchy as a supertable.

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 all table privileges except Alter and References to public. Automatically granting all table privileges to public means that 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.

Access Privileges

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 performed.

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 in the System Catalog
Privileges are recorded in the system catalog tables. Any user with the Connect privilege can query the system catalog tables to determine what privileges have been granted and to whom.

Database privileges are recorded in the sysusers 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 11-1 shows.

Figure 11-1
List of Encoded Privileges

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.

Index, Alter, and References Privileges

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 a table is created.

You can grant the Index privilege to anyone, but to exercise the ability, 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 very well and whom you trust to exercise their power very 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 very well.

Column-Level Privileges

You can qualify the Select, Update, and References privileges with the names of specific columns. Naming specific columns allows you to grant very specific access to a table. You can permit a user to see only certain columns, to update only certain columns, or to impose referential constraints on certain columns.

You can limit privileges on certain columns so that only certain users can access the salary, performance review, or other sensitive information about an employee. To make the example specific, suppose a table of employee data is defined as the following example shows:

Because this table contains sensitive data, you execute the following statement immediately after you create it:

For selected persons in the Human Resources department and for all managers, you might execute the following statement:

In this way, you permit certain users to view all columns. (The final section of this chapter discusses a way to limit the view of managers to their employees only.) For the first-line managers who carry out performance reviews, you could execute a statement such as the following one:

This statement permits the managers to enter their evaluations of their employees. You would execute a statement such as the following one only for the manager of the Human Resources department or whoever is trusted to alter salary levels:

For the clerks in the Human Resources department, you could execute a statement such as the following one:

This statement gives certain users the ability to maintain the nonsensitive columns but denies them authorization to change performance ratings or salaries. The person in the MIS department who assigns computer user IDs is the beneficiary of a statement such as the following one:

On behalf of all users who are allowed to connect to the database but who are not authorized to see salaries or performance reviews, execute statements such as the following one permit them to see the nonsensitive data:

These users can perform queries such as the following one:

However, any attempt to execute a query such as the following one produces an error message and no data:

Type-Level Privileges

 Universal Server allows you to create new data types, including opaque types, distinct types, and complex types. When a data type is created only the DBA or owner of the a new data type can apply type-level privileges that control who can use the data type. Universal Server supports the following type-level privileges:

Usage Privileges for User-Defined Types

 To control who can use an opaque type, distinct type, or named row type, you specify the Usage privilege on the data type. The Usage privilege allows the DBA or owner of the type to restrict a user's ability to assign a data type to a column (or table for a named row type) or assign a cast to the data type. The Usage privilege is granted to public automatically when a data type is created (except in ANSI-compliant databases). In an ANSI-compliant database, the Usage privilege on a data type is granted to the owner of the data type.

To limit who can use an opaque, distinct, or named row type, you must first revoke the Usage privilege for public and then specify the names of the users to whom you want to grant the Usage privilege. For example, to limit the use of a data type called circle to a group of users, you might execute the following statements:

Routine-Level Privileges

 Universal Server allows users to create user-defined routines (UDRs) in SPL or C language. To create a routine, a user must have RESOURCE privilege in the database. In addition, to create a UDR in C language, a user must also have the Usage privilege on C language.(Usage privilege on SPL is granted to public by default.) The following statement shows how you grant a group of users permission to create a UDR in the C language:

You can apply the Execute privilege on a routine to authorize nonowners to run a routine. If you create a routine in a database that is not ANSI compliant, the default routine-level privilege is PUBLIC; you do not need to grant the Execute privilege to specific users unless you have first revoked it. If you create a routine in an ANSI-compliant database, no other users have the Execute privilege by default; you must grant specific users the Execute privilege. The following example grants the Execute privilege to the user orion so that orion can use the stored routine that is named read-address:

Routine-level privileges are recorded in the sysprocauth system catalog table. The sysprocauth table uses a primary key of the routine number, grantor, and grantee. In the procauth column, the execute privilege is indicated by a lowercase letter e. If the execute privilege was granted with the WITH GRANT option, the privilege is represented by an uppercase letter E.

For more information on routine-level privileges, see "Privileges on Routines".

Automating Privileges

This design might seem to force you to execute a tedious number of GRANT statements when you first set up the database. Furthermore, privileges require constant maintenance as people change jobs. For example, if a clerk in Human Resources is terminated, you should revoke the Update privilege as soon as possible; otherwise the unhappy employee might execute a statement such as the following one:

Less dramatic, but equally necessary, changes of privilege are required daily, or even hourly, in any model that contains sensitive data. If you anticipate this need, you can prepare some automated tools to help maintain privileges.

Your first step should be to specify privilege classes that are based on the jobs of the users, not on the structure of the tables. For example, a first-line manager needs the following privileges:

When the manager is promoted to a staff position or sent to a field office, you must revoke all those privileges and grant a new set of privileges.

Define the privilege classes you support, and for each class specify the databases, tables, and columns to which you must give access. Then devise two automated routines for each class, one to grant the class to a user and one to revoke it.

Automating with a Command Script

Your operating system probably supports automatic execution of command scripts. In most operating environments, interactive SQL tools such as DB-Access and the SQL Editor accept commands and SQL statements to execute from the command line. You can combine these two features to automate privilege maintenance.

The details depend on your operating system and the version of DB-Access or the SQL Editor that you are using. In essence, create a command script that performs the following functions:

In this way, you can reduce the change of the privilege class of a user to one or two commands.

Using Roles

Another way to avoid the difficulty of changing user privileges on a case-by- case basis is to use roles. The concept of a role in the database environment is similar to the group concept in an operating system. A role is a database feature that lets the DBA standardize and change the privileges of many users by treating them as members of a class.

For example, you can create a role called news_mes that grants connect, insert, and delete privileges for the databases that handle company news and messages. When a new employee arrives, you need only add that person to the role news_mes. The new employee acquires the privileges of the role news_mes. This process also works in reverse. To change the privileges of all the members of news_mes, change the privileges of the role.

Creating a Role
To start the role-creation process, determine the name of the role along with the connections and privileges you want to grant. Although the connections and privileges are strictly in your domain, you need to consider some factors when you name a role. Do not use any of the following words as role names.
alter

 default

 index

 null

 resource

connect

delete

insert

public

select

DBA

execute

none

references

update

A role name must be different from existing role names in the database. A role name must also be different from user names that are known to the operating system, including network users known to the server computer. To make sure your role name is unique, check the names of the users in the shared memory structure who are currently using the database as well as the following system catalog tables:

When the situation is reversed, and you are adding a user to the database, check that the user name is not the same as any of the existing role names.

After you have approved the role name, use the CREATE ROLE statement to create a new role. After the role is created, all privileges for role administration are, by default, given to the DBA.

Manipulating User Privileges and Granting Roles to Other Roles
As DBA, you can use the GRANT statement to grant role privileges to users. You can also give a user the option to grant privileges to other users. Use the WITH GRANT OPTION clause of the GRANT statement to do this. You can use the WITH GRANT OPTION clause only when you are granting privileges to a user.

For example, the following query returns an error because you are granting privileges to a role with the grantable option:

Important: Do not use the WITH GRANT OPTION clause of the GRANT statement when you grant privileges to a role. Only a user can grant privileges to other users.

When you grant role privileges, you can substitute a role name for the user name in the GRANT statement. You can grant a role to another role. For example, say that role A is granted to role B. When a user enables role B, the user gets privileges from both role A and role B.

However, a cycle of role-grant cannot be transitive. If role A is granted role B, and role B is granted role C, then granting C to A returns an error.

If you need to change privileges, use the REVOKE statement to delete the existing privileges, and then use the GRANT statement to add the new privileges.

Users Need to Enable Roles
After the DBA grants privileges and adds users to a role, you must use the SET ROLE statement in a database session to enable the role. Unless you enable the role, you are limited to the privileges that are associated with PUBLIC or the privileges that are directly granted to you because you are the owner of the object.

Confirming Membership in Roles and Dropping Roles
You can find yourself in a situation where you are uncertain which user is included in a role. Perhaps you did not create the role or the person who created the role is not available. Issue queries against the sysroleauth and sysusers tables to find who is authorized for which table and how many roles are in existence.

After you determine which users are members of which roles, you might discover that some roles are no longer useful. To remove a role, use the DROP ROLE statement. Before you remove a role, the following conditions must be met:



Informix Guide to SQL: Tutorial, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.