First published in the Washington Area Informix User Group Newsletter
Volume 4, No. 3 - July 1994
Informix applies security at the database, table and column level. All security is based on a users login name. Using Informix SQL, the creator of a database can give and remove privileges. The SQL command to give access is the grant command and the revoke command removes privileges.
Database Privileges
There are three levels of database privileges: DBA, Resource and Connect. Connect privilege allows a user to access the database and add, modify and delete data. Resource privilege allows a user all Connect privileges, and in addition, the ability to create new tables, indexes and procedures. DBA privilege allows connect and resource privileges, and also gives the user the authority to grant resource, connect or DBA to another user. A user with DBA privilege can also drop any object in the database, and/or drop the database entirely.
Connect privilege is sufficient for most database activity. A programmer or developer who is creating new objects may require Resource privilege. DBA privilege is like the "super-user" of the Unix Operating System. Only a few selected users should be granted DBA privileges.
Table Privileges
There are six basic table privileges and a seventh privilege that was added with Informix release 5.0. The privileges are:
Select - view data in a table Insert - add new rows of data to a table Update- change existing rows of data in a table Delete - remove rows of data from a table Index - add indexes to a table. A user must also have the database privilege of resource to index a permanent table. Alter - modify the structure of a table. Reference - ability to reference columns in referential constraints. This is a new feature with Release 5.0.
Column Privileges
There are two column level privileges - select and update. In order for column level privileges to be effective, the user should not have select or update privileges at the table level. If the user has table level select or update privileges, these will override the column level privileges. Column level privileges limit what a user can see or change to specified columns of a record in a table.
Public Privileges
Informix uses the keyword public to identify privileges that apply to all users. To allow all users on a system to access a database, the database privilege Connect would be granted to public. To allow all users with access to a database to select data from a column, select privileges would be granted to public. The public keyword is a very powerful tool to allow open access to your data. By the same token, to prevent all users not specifically authorized from connecting to a database and selecting data, public privileges can be revoked entirely.
Owner Privileges
The Owner of an object (database, table, etc.) in a database has special privileges over that object. The owner has all seven table level privileges. These cannot be revoked from the user who created an object. This can create problems when a system moves from development to production, or when the original owner of a table or database leaves the organization. This privilege is also not easily identified. When you use the Informix SQL "info" command to see table privileges, owners' privileges are not identified. Another user, even a DBA, cannot revoke privileges from an owner. This requires that when a database or table is created, the owner must be carefully decided. The owner of a table is also the only one who can grant other users privileges for that table.
Informix Defaults
When an Informix database is created, the only database level privilege granted is DBA privilege to the creator. When a table is created, by default public is granted select, update, insert and delete privileges on that table. This means that unless the privileges are changed, any person with database access can view and modify a table.
Database Security Model
Establishing database security takes planning. For best results, start when the database is created. A production environment is different from an environment where programs and the database are still under development. To facilitate database security, two environments are needed - one for the development of software, and one for the production system. Another key component of an effective database security model is definition and assignment of the roles of Database Administrator, and Database Security Officer. These roles may be combined and handled by one individual, or user login.
The Database Administrator
It is strongly recommended that an abstract user login be created as the Database Administrator (DBA). This is essentially a super-user for a database. Establishing a separate login minimizes the risk of a user accidently dropping a database they have created. This is especially important in a production environment. The DBA login name should be the creator of the database and all tables in the database. The DBA should be the only user with alter privileges on any table in the database. As the owner of the database and all tables within the database, the DBA can grant privileges to other users.