Informix Privileges

Privileges

Three levels of data-related security keep database users (users who must have some type of access to data in the database) from accessing specific data items. These levels are database, table, and column.

Database-Level Privileges

All users must have access to a database to use data within a server. The three database-level privileges are connect, resource, and DBA. Table 16.1 shows the different authority levels associated with each privilege.

Table 16.1. Database-level privileges.

Privileges		Connect	Resource	DBA
Select, insert, update, delete, use temporary tables, and use views.		Yes	Yes	Yes
Create, alter, drop, and index own tables.		No	Yes	Yes
Grant, revoke, drop other owned tables, and start and stop server.		No	No	Yes

Connect

The minimum database-level privilege is the connect level. Users with connect can perform select, insert, update, and delete statements, run stored procedures against tables, create views on tables, and create temporary tables with or without indexes.

Resource

Users with resource privileges have all the privileges of connect users. They also have the added ability to create, alter, and drop their own tables and place indexes on these tables.

DBA

The creator and owner of a database is automatically given DBA privileges. A DBA has the same privileges as the connect and resource users with added abilities. The added abilities include granting and revoking connect, resource, and DBA privileges to and from other users, and dropping and altering other users' tables and views. Users with DBA privilege can also drop, start, stop, and recover the database.

Granting and Revoking Database-Level Privileges

The user who creates the database is automatically given DBA privileges, which is the only level that can perform grants and revokes. The first DBA can create other DBAs with a grant statement in SQL. A grant gives authority to specific users at whatever level you choose. The DBA can also use a revoke to remove or lower the authority.

Informix has a keyword called PUBLIC that represents all users who access the database server. To specify users, use their UNIX IDs. You can specify a list of users by separating each UNIX ID with a comma.

The database to which users get access is the database to which the DBA is connected when running the SQL to perform the grant. If the database server has multiple databases, the DBA must perform a grant for each database to provide access to them all. If the user is allowed access to only one of the available databases, you perform the grant within only that specific database when it is open.

To grant connect privileges, use this syntax:

GRANT CONNECT TO PUBLIC;

GRANT CONNECT TO user1;

GRANT CONNECT TO usera,userb,userc;

To revoke connect privileges, use this syntax:

REVOKE CONNECT FROM PUBLIC;

REVOKE CONNECT FROM user1;

REVOKE CONNECT FROM usera,userb,userc;

To grant resource privileges, use this syntax:

GRANT RESOURCE TO PUBLIC;

GRANT RESOURCE TO user1;

GRANT RESOURCE TO usera,userb,userc;

To revoke resource privileges, use this syntax:

REVOKE RESOURCE FROM PUBLIC;

REVOKE RESOURCE FROM user1;

REVOKE RESOURCE FROM usera,userb,userc;

To grant DBA privileges, use this syntax:

GRANT DBA TO user1;

GRANT DBA TO usera,userb,userc;

To revoke DBA privileges, use this syntax:

REVOKE DBA FROM user1;

REVOKE DBA FROM usera,userb,userc;

It is not a good idea to grant DBA privileges to PUBLIC. Imagine giving hundreds of users the ability to drop the database! When initially granting privileges, remember to grant only connect or resource levels to PUBLIC.

Table-Level and Column-Level Privileges

When a user has access to a database, the DBA can limit access to specific tables and columns within tables. The creator of the table or any resource-level or DBA-level user can create tables. That owner or any DBA can grant table-level privileges to other users for that table. A total of eight keywords provide different table-level privileges: insert, delete, select, update, references, index, alter, and all.

Insert

Granting insert privileges allows users to add new data to the table. Revoking that privilege stops users from adding data to the table.

GRANT INSERT ON customer_table TO user1;

REVOKE INSERT ON customer_table FROM PUBLIC;

Delete

Granting delete privileges allows users to remove data from a table. Revoking that privilege stops users from removing data from the table.

GRANT DELETE ON customer_table TO user1;

REVOKE DELETE ON customer_table FROM PUBLIC;

Select

Select privileges can be granted at the table level or at specific column levels. Users can have the ability to query an entire row in the table or just specific fields. In the first example, user1 can look at any column or any row of the customer_table. The second grant only allows PUBLIC to query only the customer_id and balance columns of the customer_table. You can revoke privileges in the same way.

GRANT SELECT ON customer_table TO user1;

GRANT SELECT (customer_id, balance)

ON customer_table TO PUBLIC;

REVOKE SELECT ON customer_table FROM user3;

REVOKE SELECT (customer_id, balance)

ON customer_table FROM user4;

Update

You can grant update privileges at the table level or specific column levels. Users can have the ability to change an entire row in the table or just specific fields. In the first example, user1 can update any column or any row of the customer_table. The second grant allows PUBLIC to update only the customer_id and balance columns of the customer_table. You can revoke privileges in the same way.

GRANT UPDATE ON customer_table TO user1;

GRANT UPDATE (customer_id, balance)

ON customer_table TO PUBLIC;

REVOKE UPDATE ON customer_table FROM user3;

REVOKE UPDATE (customer_id, balance)

ON customer_table FROM user4;

References

You can grant users the ability to force referential constraints on the entire row or specific columns of a table. The user must be a resource database-level user before the references privilege works. Referential constraints perform tasks such as cascading deletes or any other task that relies on how columns relate to other columns.

GRANT REFERENCES ON customer_table TO user1;

GRANT REFERENCES (customer_id, balance)

ON customer_table TO PUBLIC;

REVOKE REFERENCES ON customer_table FROM user3;

REVOKE REFERENCES (customer_id, balance)

ON customer_table FROM user4;

Index

The index privilege grants users the ability to create and drop indexes related to a table. Users must have the resource privilege in combination with the index privilege. Users with connect cannot create an index, even if they have the index privilege. There is no column-level privilege because indexes are built on all table rows.

GRANT INDEX ON customer_table TO user1;

REVOKE INDEX ON customer_table FROM user3;

Alter

The alter privilege allows users to change the layout of the columns within the table. Users with alter can add, delete, and change columns and the column data types. Only users with knowledge of the database system and how to protect it should have this privilege. This privilege is almost as high-level as DBA. Alter applies only to the table level.

GRANT ALTER ON customer_table TO user1;

REVOKE ALTER ON customer_table FROM user3;

All

The keyword all provides all table and column privileges to users. Using the all keyword grants or revokes any table privileges that the user might have.

GRANT ALL ON customer_table TO user1;

REVOKE ALL ON customer_table FROM user2;

Combinations

You can grant or revoke different combinations of table and column privileges in one command. Place the privileges in any sequence, separated by a comma, after the grant or revoke keyword.

GRANT INSERT, DELETE, UPDATE

ON customer_table TO PUBLIC;

GRANT SELECT, UPDATE (customer_id, balance)

ON customer_table TO user2;

REVOKE INDEX, ALTER ON customer_table FROM user1;

You can also combine table-level and column-level privileges in one statement. Column-level privileges use the specified columns, and table-level privileges use the specified table.

GRANT INSERT, DELETE, SELECT, UPDATE

(customer_id, balance)

ON customer_table TO user2;

REVOKE INDEX, SELECT, ALTER (customer_id, balance)

ON customer_table FROM user3;

Other Keywords

You can use two other keywords in conjunction with the GRANT command. The first is the WITH GRANT OPTION keyword. When combined with the GRANT command, the user receiving the privileges can also grant the same privileges to other users.

In the following example, user1 not only has insert, delete, select and update privileges on customer_table, but he or she can also grant any or all of these privileges to other users.

GRANT INSERT, DELETE, SELECT, UPDATE

ON customer_table TO user1

WITH GRANT OPTION;

If user1 has one or all of the privileges revoked, all the users that user1 granted privileges to will also have the same privileges revoked.

The other keyword used with grant is the AS keyword. The AS keyword allows you to perform a grant as if another user performs the grant. This sets up the situation described previously; if the grantor is revoked, all the users granted by that user are also revoked.

Continuing with the preceding example, user1 was given insert, delete, select, and update privileges on customer_table and the right to grant these privileges. A DBA, the owner of the table, or the user that granted user1 the privileges could then grant as user1 to other users:

GRANT INSERT, DELETE, SELECT, UPDATE

ON customer_table TO user2, user3, user4, user5

AS user1;

Now user1 through user5 have the same privileges. To revoke the privileges on all five users, just revoke user1:

REVOKE ALL ON customer_table FROM user1;

Summary

Security is an important issue with database creators, owners, providers, and users. Not only does security provide a means of keeping the data safe and intact from loss, but it also goes another level by keeping the content of the data safe and secure from misuse or abuse.

You can achieve both levels of database security by using GRANT and REVOKE statements to set privileges at the database, table, and column levels. Setting different types of users also separates users responsible for managing the database, DBAs, and normal users.

You can use stored procedures and triggers to audit how and when data is used or changed and also restrict access to data. You can set up a stored procedure to perform a task on data, and only privileged users can access that stored procedure to perform the task.

Another way of restricting how users access data is through views. You can use a view to force users to perform tasks on a subset of the actual data, rather than access the entire database.

Finally, you can use the operating system procedures to lock users out of the database and the entire system. Client applications should build in specific logon processes to allow only privileged users into the database server.