Database Security

---

Mimer SQL supports a sophisticated system of access rights and privileges, which permit detailed control of database security.

The main components of the database security system are:

• idents
• system, object and access privileges
• restriction views.
• PSM routines

The Role of Idents in Database Security

Access to the Mimer SQL system as a whole is managed through the use of idents and privileges.

Careful advance planning of the hierarchical structure of idents in the database is vital to the long-term viability of the system. A poorly planned ident structure can easily become impossible to follow and control after a relatively short period of system use.

SYSADM

The Mimer SQL installation process creates one user ident, for use in database administration, with the name SYSADM.

The SYSADM ident has all the system privileges (BACKUP, DATABANK, IDENT, SCHEMA, SHADOW and STATISTICS - see System, Object and Access Privileges, with the ability to grant these privileges to other idents, i.e. the privileges are held with the WITH GRANT OPTION.

The SYSADM ident also has SELECT access on all tables in the data dictionary, again, with the WITH GRANT OPTION. The SYSADM user is ultimately responsible for the structure of the whole system.

Re-creating system databanks can only be done by SYSADM, however, in other respects SYSADM is just an ordinary USER ident in the system.

It is quite possible, and may be advisable, especially in large systems, that SYSADM does not have access to the actual contents of the database; the database administration role should be concerned with objects in the system, not the actual data.

Public Group

All idents created in the system automatically belong to a logical group (specified using the keyword PUBLIC in Mimer SQL statements) which is intended to be used for granting global privileges.

Guidelines for Structuring Idents

The following general recommendations are made for structuring the idents in a system:

• Create PROGRAM idents for functional roles within the system. These are not coupled to any physical individual or group of individuals and thus have a lifetime independent of the turnover of personnel. (Database administration is an example of a functional role, but it is represented by a user ident rather than a program ident for practical purposes - see Idents for details on idents).
• Create USER or OS_USER idents for physical users of the system. These may be dropped when the person concerned should no longer have access to the database. Do not grant privileges directly to user idents, other than membership to groups. Administration is much simpler if privileges are granted through groups.
• Use GROUP idents to represent logical classes of users in the system. Grant privileges to groups rather than to individuals. This makes the granting of access rights to the system easier to organize and a clearer overview of the privilege structure within the system is maintained. It also means that new idents can be granted suitable privileges efficiently through membership in one or more groups.
• Grant the privilege to create objects (DATABANK, IDENT and TABLE privileges) to program idents only. In this way, individual USER idents may be dropped with no cascade effects (see Cascade Effects Between Privileges). (Creation of domains requires no special privilege and may thus be performed by any ident with a schema. Creation of views requires only SELECT access to the table on which the view is based).
• Use the WITH GRANT OPTION sparingly and try to minimize the number of levels in the ident hierarchy. This reduces the risk of cascading revocation of privileges, see Cascade Effects Between Privileges.

If these recommendations are followed, the maintenance of the ident structure in the system will be much more straightforward. Access to the contents of the database will be granted to relatively few GROUP idents instead of many individual program or user idents.

When a physical individual should no longer have access to the database, the corresponding USER ident can be dropped with no cascade effects.

System, Object and Access Privileges

Each ident is given privileges within the system which determine the operations the ident is permitted to perform.

Note: In addition to holding any relevant privilege(s), an ident must also be the creator of at least one schema before the ident is able to create private database objects (i.e. domains, functions, indexes, modules, procedures, sequences, synonyms, tables, triggers and views) - see Schemas.

Privileges may be granted either directly or by making the ident a member of a GROUP ident. The privileges are classified as follows:

System Privileges

System privileges give the right to create global objects in the database. There are the following system privileges:

System Privilege	Description
BACKUP	gives the right to perform backup and restore operations
DATABANK	gives the right to create databanks
IDENT	gives the right to create idents and schemas
SCHEMA	gives the right to create schemas
SHADOW	gives the right to create shadows and perform shadow control operations
STATISTICS	gives the right to execute the UPDATE STATISTICS statement.

System privileges are granted to SYSADM at installation time and may be passed on to other idents with or without the WITH GRANT OPTION.

An ident receiving a privilege with the WITH GRANT OPTION may pass the privilege on to another ident.

Object Privileges

Object privileges give rights associated with certain specified objects in the system. There are the following object privileges:

Object Privilege	Description
TABLE	gives the right to create tables in a given databank
SEQUENCE	gives the right to create sequences in a given databank
EXECUTE	gives the right to execute a function, procedure statement, or the right to enter (become) a specified program ident
MEMBER	makes an ident a member in the specified GROUP
USAGE	gives the right to specify the named domain where a data type would normally be specified (in contexts where use of a domain is allowed) or the right to use a specified sequence or collation.

Object privileges are initially, automatically, granted only to the creator of the object (e.g. the creator of a databank automatically has TABLE privilege on the databank).

The privileges may be passed on to other idents with or without the WITH GRANT OPTION.

Access Privileges

Access privileges give rights of access to the contents of a specified table or view. There are the following access privileges:

Access Privilege	Description
SELECT	gives the right to read the table contents
INSERT	gives the right to add new rows to the table (this privilege may be limited to specified columns within the table)
DELETE	gives the right to remove rows from the table
UPDATE	gives the right to change the contents of existing rows in the table (this privilege may be limited to specified columns within the table)
REFERENCES	gives the right to use the primary or alternate keys of the table as a foreign key from another table (this privilege may be limited to specified columns within the table).

In addition to the five access privileges listed above, the keyword ALL may be used as a shorthand method of specifying all the privileges possessed by the granting ident. For example, if an ident has only SELECT and UPDATE privileges on a table and ALL is granted on that table to a new ident, the new ident will only be given SELECT and UPDATE.

Access privileges are initially granted to the creator of the table with the WITH GRANT OPTION. The privileges may be passed on to other idents with or without the WITH GRANT OPTION.

Certain operations are not controlled by explicit privileges, but may only be performed by the creator of the object involved. These operations include ALTER (with the exception of ALTER IDENT, which may be performed by either the ident itself or by the creator of the ident), DROP and COMMENT. Privileges may only be explicitly revoked by their grantor, however cascade effects may go wider.

Cascade Effects Between Privileges

Dropping an object from the database or revoking a privilege from an ident may have cascade effects on other objects and idents, depending on the way the database is organized.

The keywords CASCADE and RESTRICT may be used in the DROP and REVOKE statements.

When using RESTRICT (the default), the operation will fail with no changes being made if any cascade effects result from it.

When using CASCADE, the following operations have the consequences described:

• If an ident is dropped, all objects created by the ident are dropped and all privileges granted by the ident are revoked.
• If a databank is dropped, all tables in the databank are also dropped.
• If a table is dropped, all views and synonyms based on the table are dropped. Also, triggers and routines that references the table are dropped.
• If a privilege with the WITH GRANT OPTION is revoked from an ident, all instances of that privilege granted to other idents under the authorization of that WITH GRANT OPTION are also revoked. The WITH GRANT OPTION can be revoked separately.
• If SELECT privilege on a table is revoked from an ident, views created by the ident under the authorization of that SELECT privilege are dropped.

If DATABANK privilege is revoked from an ident, existing databanks created under that privilege are not dropped.

The cascade effects of revoking privileges only occur when the last instance of the privilege is revoked (a new instance of the privilege is created each time the privilege is granted to the same ident on the same object). An ident grants privileges, creates views and so on under the authorization of the most recently received valid instance of the WITH GRANT OPTION, SELECT or other relevant privilege.

The data dictionary keeps a record of the specific instance of an authorization under which an operation was performed. The cascade effects apply only to privileges granted or objects created under the specific instance of the authorization which is being revoked.

This is illustrated in the example cases that follow:

CASE 1

1. A grants with grant option to M

   M grants to X

2. B grants with grant option to M

   M grants to Y

3. A revokes from M

   Both X and Y keep privileges

4. B revokes from M

   Both X and Y lose privileges

CASE 2

1. A grants with grant option to M
2. B grants without grant option to M

   M grants to X

   M grants to Y

3. A revokes from M

   M loses grant option

   Both X and Y lose privileges

4. B revokes from M

   M loses privilege

Restriction Views

Views are a powerful tool for restricting user access to specific parts of the database and they complement the use of access privileges in maintaining database security.

By defining restriction views (i.e. views based on one table but restricted only to specific rows and/or columns in the table), access may be provided to a subset of the contents of a table without affecting the physical database structure. In this way, the database may be designed optimally according to the relational model, while user access can be defined according to actual data retrieval requirements.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se