Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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:

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:

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, methods, modules, procedures, sequences, synonyms, tables, triggers, types, 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, method or 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 user-defined type or domain where a data type would normally be specified (in contexts where use of a user-defined type or 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 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
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX