Access control in IDS
There are various ways of securing access to data using SQL statements. Initially, this tutorial describes various access control mechanisms available in the database server to control access to the SQL objects. This is followed by a description of the column-level encryption feature, used to encrypt data in specified columns of a table.
The access control mechanisms available in IDS are broadly classified into discretionary access control (DAC) and label-based access control (LBAC). DAC is enforced in the database server using privileges and roles. LBAC enforcement is done by using security labels.
The database administrator (DBA) plays a critical role in ensuring that sensitive data is protected from unauthorized users. SQL provides the means in achieving data protection at different levels of granularity. Note that a DBA is different from a database system administrator (DBSA). The role of a DBSA is to perform server maintenance, whereas the role of a DBA is to create and maintain databases.
Discretionary access control (DAC) is the primary access control mechanism that enables access to SQL objects using privileges and roles. The objects in the database server that are protected using DAC are databases, tables, columns, views, table, types, routines, and languages. DAC cannot be used for row-level protection; for that, LBAC needs to be used. By protecting these objects and by granting privileges to access these objects to authorized users, effective access control is achieved. Users who do not have the necessary privileges will not be able to execute the queries that access these objects.
In a typical IDS installation, there is a large number of users accessing the system, executing queries, and modifying data. Granting necessary privileges to all these users can make the administrator role quite tedious. Furthermore, company policies might stipulate that users assume a different set of privileges depending on the work that they perform at any moment. This makes the administrator responsibility more onerous because privileges have to be constantly granted and revoked from users based on the least privilege principle. The roles capability in the server alleviates the extra effort of the administrator. Role-based access control (RBAC) extends privilege-based access control by grouping a set of privileges into roles and assigning these roles to users.
The following objects in the DBMS require users to have specific authorization or privileges in order to access them:
- Columns in tables
- User-defined types (UDT)
Most of the SQL queries submitted by the user interact with one or more of these objects. Privileges granted to the user on these objects dictate whether the user can perform the desired operation. Figure 2 shows the privilege checking done in the course of an SQL query execution path. First, database privileges are checked. This is followed by checking privileges on SQL objects associated with the user query. User privileges are based on privileges granted to the user explicitly, privileges derived from user's current role, and privileges that belong to PUBLIC.
Figure 2. Privilege checking in an SQL query
Table 1 provides a summary of privileges available for a particular SQL object:
Table 1. SQL objects and their privileges
|Database||Connect, resource, DBA|
|Table||Select, update, insert, delete, index, alter, references|
|Column||Select, update, references|
|Fragment||Insert, update, delete|
|View||Select, insert, delete, update|
Each SQL object is associated with certain privileges, and each
privilege has an associated set of capabilities. When a user is
granted a privilege, the user has permissions to exercise that
capability on the SQL object. IDS provides
to grant or remove privileges.
REVOKE statements to grant and revoke
privileges to users, respectively. Listing 2 shows the syntax of the
REVOKE statements on database objects. In the example,
privilege represents one of CONNECT, RESOURCE, or DBA privileges. The
"user-list" represents a list of users, including PUBLIC.
Listing 2. Syntax:
GRANT privilege TO user-list REVOKE privilege FROM user-list
A privilege is granted to a user or a list of users.
PUBLIC is a
keyword used to represent all the users in the database system. When a
privilege is granted to
PUBLIC, it means that all the users in the
system acquire that privilege. Similarly, when a privilege is revoked
PUBLIC, all users in the system are stripped of the privileges
PUBLIC. However, they still retain the privileges granted
to them individually, or granted through a role. The privileges that
you grant remain in effect until you revoke the privilege. Only the
granter of a privilege or DBA can revoke it. Table 2 lists
the table privileges you can grant to users:
Table 2. Database privileges
|DBA||Creator/owner of the database. Ability to grant privileges on other objects in the database.|
|Resource||Can connect to the database and create other objects.|
|Connect||Can connect to database and execute queries.|
The DBA privilege is granted to the user who creates the database, but can also be granted to other users later. The DBA privilege is the highest of the three database-level privileges. (Hereafter, this tutorial refers to a DBA privileged user as a DBA.) A DBA can perform all operations in the database. The DBA can also grant DBA, RESOURCE, and CONNECT privileges to other users of the database. Thus, more than one DBA can exist for a database. Typically, the creator of an object in a database is the owner of an object. A DBA can also create objects in the database to be owned by others. The tasks a DBA can perform include:
- Grant and revoke database-level privileges to other users
- Grant and revoke privileges on other objects in the database such as tables, views, sequences, UDT, routines, languages, and so on
- Create tables, views, and indexes in the database to be owned by other users
- Drop or alter any object in the database regardless of who owns it
- Run the
- Perform all operations that a RESOURCE privileged user can perform
The RESOURCE privilege has the second highest ranking in a database next to DBA. The DBA grants RESOURCE privilege to users to create other SQL objects within the database. The tasks a RESOURCE privileged user can perform include:
- Create permanent tables, indexes, and SPL routines
- Tasks that require allocation of disk space
- Perform all operations that a CONNECT privileged user can perform
The DBA grants the CONNECT privilege to users to connect to the database and execute queries. The CONNECT privilege has the least ranking because users are not able to create other objects within the database with this privilege. The following are the tasks that a CONNECT privileged user can perform:
- Connect to the database
- Run queries and DML statements on database tables on which they have the necessary table-level privileges
- Execute an SPL routine if they have the necessary table-level privileges
- Create views if they are permitted to query on tables on which the view is based
- Create temporary tables and create indexes on temporary tables
Table 3. Table privileges
|Select||Allows users to read data from the table. They can perform SELECT queries or create views on table.|
|Update||Allows users to update data in the table.|
|Insert||Allows users to insert rows in the table.|
|Delete||Allows users to delete rows from the table.|
|Index||Allows users to create indexes on tables.|
|Alter||Allows users to alter the table.|
|References||Allows users to create referential constraints on the table.|
|Under||Allows users to create sub-tables under this table.|
When a table-level privilege is granted, it is implicitly granted on all columns of the table. The column-level privilege helps you to further restrict the scope of user activity to specific columns. The privileges that are applicable at the column level are SELECT, UPDATE, and REFERENCES only.
You can use the
GRANT FRAGMENT statement to grant INSERT, UPDATE, and
DELETE privileges on individual fragments of a table. The fragment
privileges are only applicable to fragments created by
expression-based fragmentation. Fragment-level privileges that are
granted to fragments of a table cease to exist under one or more of
the following conditions:
- If the fragmentation strategy of the table is changed from expression-based to another strategy, such as round-robin
- If a fragment is dropped from the table, which results in the privileges on the fragment being dropped
- If the expression on which fragmentation is based is changed, and the fragment privileges are therefore also dropped and the user assumes default table privileges
Table 4 lists the type privileges you can grant to users:
Table 4. Type privileges
|Usage||Authorization to use the named data type.|
|Under||Authorization to use the named data type as a super-type in the type hierarchy.|
When a VIEW is created, IDS checks the privileges on the underlying tables before granting privileges to the creator or owner of the VIEW. If the user has the SELECT privilege on the underlying table, then the VIEW is created and the SELECT privilege is granted on the VIEW to the user. If the user does not have the SELECT privilege on the underlying table, then the VIEW is not created. If the user also has INSERT, UPDATE, and DELETE privileges on the underlying table, then the same privileges are granted on the VIEW to the user. If you are using a VIEW, your privileges for the VIEW, not the underlying tables, apply. The creator of the VIEW typically grants privileges on the VIEW to other users.
SEQUENCE privileges are similar to table-level privileges. The
difference is that only SELECT and ALTER privileges are granted on
sequences. A user with the SELECT privilege can use the sequence in
queries. A user with the ALTER privilege can alter the sequence using
ALTER SEQUENCE or
RENAME SEQUENCE statements. Sequences are
created only by DBA-privileged users. Both ALTER and SELECT privileges
on a sequence can be granted to a user or a role.
The privileges needed to create a routine are different from privileges needed to use the routine. The following privileges are needed to create a routine or procedure:
- DBA privilege or RESOURCE privilege on the database
- Language-level privilege on the language in which the routine is written
- EXTEND role privilege if the
IFX_EXTEND_ROLEconfiguration parameter is set to 1 and the routine is a Java™ technology or C UDR
By default, a routine creator has the privilege to execute the routine. If another user needs to execute the routine, then the creator of the routine must grant the EXECUTE privilege to that user.
In IDS, user-defined routines (UDRs) are written in SPL or in any of the external languages, such as C and Java. To create a routine in a particular language, the user needs the USAGE privilege for that language. By default, language usage privileges on SPL are available to the user informix and the user holding the DBA privilege to that database. However, only the user informix can grant the language usage privilege to others. The user with the DBA privilege cannot grant this privilege to others. The USAGE privilege to create SPL routines is granted to PUBLIC by default.