16 Informix Privileges And Security Issues

Introduction

Access to each database or even specific tables or data fields should be restricted to certain users.

Privileges

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.
The different autority levels are:

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

All the privileges of connect users plues the ability to create, alter, drop and index own 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.

PUBLIC: Keyword that represents all users who access the database server.

To grant connect privileges to everybody:
GRANT CONNECT TO PUBLIC;

To grant connect privileges to user1:
GRANT CONNECT TO user1;

To revoke connect privileges from everybody:
REVOKE CONNECT FROM PUBLIC;

Table-Level and Column-Level Privileges

Insert

Insert privileges allow users to add new data to a table.

Delete

Delete privileges allow users to remove data from a table.

Select

Select privileges allow users to select data from a table. They can be granted at the table level or at specific column levels.

Examples:

user1 can look at ay column or any row of the customer_table.
GRANT SELECT ON customer_table TO user1;

PUBLIC can query only the customer_id and balance columns of the customer_table.
GRANT SELECT (customer_id, balance) ON customer_table TO PUBLIC;

Update

Users can change an entire row in the table or just specific fields.

References

You can grant users the ability to force referential constraints on the entire row or specific columns of a table.

Index

The index privilege grants users the ability to create and drop indexes related to a table.

Alert

The alter privilege allows users to change the layout of the columns within the table.

All

Using the all keyword grants or revokes any table privileges that the user might have.

Combinations

You can grant or revoke different combinations of table and column privileges in one command.

GRANT INSERT, DELETE, UPDATE
ON customer_table TO PUBLIC;

Other Keywords

WITH GRANT OPTION: The user receiving the privileges can also grant the same privileges to other users.
GRANT INSERT, DELETE, SELECT, UPDATE
ON customer_table TO user1
WITH GRANT OPTION;

AS: The AS keyword allows you to perform a grant as if another user performs the grant

Stored Procedures and Triggers

Stored procedures are considered separate database entities, and because they are separate, users must have the appropriate privileges to create, edit, and execute them. Stored procedures can have access to restricted areas, so they provide a mean for users to have access to restricted areas.

Triggers provide the means to automatically process a task when other events occur in the database, such as specific data access or creation.

Views

A view is a logical representation of physical columns from one or multiple tables.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.