The Database Environment

This chapter describes the database environment which is composed of a set of Mimer SQL system databanks, one or more idents authorized to connect to the database and the databanks created by the idents. It also describes database security and data integrity.

The objects that are created in the database are described in the Mimer SQL Reference Manual, Mimer SQL Database Objects.

The Data Dictionary

The database environment is controlled through a central data dictionary, stored in the system databank SYSDB and is automatically maintained by Mimer SQL. The data dictionary contains meta-data describing all the objects in the database. System access to the data dictionary tables is performed by internal routines and is transparent to the user.

Restricted facilities for examining the contents of the data dictionary are available to all users through the LIST and DESCRIBE functions in BSQL, see the Mimer SQL User's Manual, Mimer BSQL for a more complete description of these facilities.

In general, a user may read data dictionary information for database objects to which they have access. The BSQL facilities use pre-defined views on the data dictionary tables to present the information in a structured form, see the Mimer SQL Reference Manual, Data Dictionary Views for documentation on the data dictionary views available to all users.

The SYSADM user ident may read the contents of the data dictionary tables directly, and may grant SELECT access on the tables to any other user ident. The organization of the data dictionary tables is documented in Data Dictionary Tables of this manual.

No individual user, including SYSADM, may update data dictionary tables directly. All write operations in the data dictionary are performed by internally controlled routines, to ensure consistency within the dictionary.

Idents

An ident in a Mimer SQL system is an authorized user of the system, or the collective identity of a group of users sharing common privileges.

There are three types of idents: USER, PROGRAM and GROUP idents.

USER Idents

USER idents are authorized to connect to a Mimer SQL database, by using the CONNECT statement in an application program or by entering the correct ident name and password in an interactive environment.

Any privileges a user ident holds may be exercised once the ident has logged on. USER idents are generally associated with specific physical individuals authorized to connect to the database.

An OS_USER login can be added to a user which allows the user currently logged in to the operating system to connect to a Mimer SQL database without providing a password. (If the Mimer USER ident name is the same as the operating system user name, its possible to connect to Mimer SQL without providing user name.)

If a USER with an OS_USER login is defined with a password in Mimer SQL, the ident may connect to Mimer SQL in the same way as any other user ident (i.e. by providing user name and password).

PROGRAM Idents

PROGRAM idents may not initiate a connection to a Mimer SQL database, but may be entered from within an application program or interactive environment by using the ENTER statement.

A connection to the database should have been established before the ENTER statement is used. The ident using the ENTER statement must hold EXECUTE privilege on the PROGRAM ident.

Entering a PROGRAM ident is analogous to logging on as a USER ident, in that the PROGRAM ident gains access to the system and any privileges the ident holds become applicable.

PROGRAM idents are generally associated with specific functions within the system, not with physical individuals.

GROUP Idents

GROUP idents are collective identities for groups of USER or PROGRAM idents.

Any privileges granted to or revoked from a GROUP ident automatically apply to all members of the group.

Any ident can be a member of as many groups as required, and one group can include any number of members.

GROUP idents provide a facility for organizing the privilege structure in the database system. For examples showing the use of a GROUP ident, see the Mimer SQL User’s Manual.

Idents – Access and Authority

USER and PROGRAM idents are authorized users of the system.

Every PROGRAM ident has a unique ident name and a private password which must be correctly supplied to the ENTER statement in application programs.

Every USER ident has a unique ident name and an optional private password which must be correctly supplied to the CONNECT statement in application programs. Alternatively a USER with an OS_USER login may access the database without explicitly providing a password on condition that the username for the user currently logged in to the operating system correspond to the definition of an OS_USER in the Mimer SQL database.

When Mimer SQL is installed, the user ident SYSADM, used for database administration, is automatically created. The password for SYSADM is defined when the system is installed, see SDBGEN - Generating the System Databanks.

All idents in the system belong to a group which is specified by using the keyword PUBLIC in Mimer SQL statements. Privileges granted to PUBLIC are global to the system.

Schemas

A schema defines a local environment within which private database objects can be created. The ident creating the schema has the right to create objects in it and to drop objects from it.

When a USER or PROGRAM ident is created, a schema with the same name is created by default, and the created ident becomes the creator of the schema. This happens unless WITHOUT SCHEMA is specified in the CREATE IDENT statement.

When a private database object is created, its name can be specified in a fully qualified form which identifies the schema in which it is to be created. The names of objects must be unique within the schema to which they belong, according to the rules for the particular object-type.

If an unqualified name is specified for a private database object, a schema name equivalent to the name of the current ident is assumed.

Databanks

A Mimer SQL database consists of the Mimer SQL system databanks and a number of user databanks.

Each databank is a single physical file in the host file system.

Mimer SQL System Databanks

The Mimer SQL system databanks are fundamental to the functioning of a Mimer SQL database and they are created during the process of installing a database.

System databanks are not used for storing user-defined information and cannot be updated directly by users.

If any one of the system databanks is damaged or missing, attempts to log on to Mimer SQL will fail. Backup and restore procedures for the system databanks are described in Backing-up and Restoring Data.

The Mimer SQL system databanks are:

SYSDB

LOGDB

TRANSDB

SQLDB

SYSDB

SYSDB is the most important system databank as it stores the tables that make up the data dictionary, see The Data Dictionary.

Among other things, the data dictionary holds information about the other databanks that make up the database, the tables each user databank contains, the users (idents) that are known to the Mimer SQL system and the access rights each ident has.

TRANSDB

TRANSDB stores information that is vital for keeping the database in a consistent state.

LOGDB

LOGDB records all write operations performed within transactions on SYSDB and user databanks which have been defined with the LOG option.

The information in this databank is used by the backup and restore facilities, see Backing-up and Restoring Data, to restore the contents of a database in the event of a system failure.

A readlog facility is provided to allow the information in this databank to be examined, see Mimer SQL User's Manual, READLOG.

SQLDB

SQLDB is used by the transaction handling mechanism to store row data read from the database, see Transaction Control, and is used by Mimer SQL for temporary storage of result tables.

User Databanks

User databanks contain the tables in the database created by the users of the system. Typically these databanks are created by the system administrator and TABLE privilege is granted to the users of the system.

The CREATE DATABANK statement is used to create user databanks, see the Mimer SQL Reference Manual, SQL Statements.

Except at the point when tables are created, the existence of databanks is transparent to users and application programs. When access is requested to a table, information in the data dictionary is used by Mimer SQL to locate the table and make it available, if permissible.

The division of a database into databanks is made on the basis of file handling considerations from the operating system viewpoint and on the basis of transaction control considerations from the database viewpoint. The use of databanks allows considerable flexibility in the physical placement of data on the computer system.

Databank Options

Databanks may be defined with the LOG, TRANSACTION, WORK, or READ ONLY options which determine transaction handling and logging behavior, as follows:

LOG

All operations on the databank are performed under transaction control. All transactions are logged.

TRANSACTION

All operations on the databank are performed under transaction control. No transactions are logged.

WORK

All operations on the databank are performed without transaction control (even if they are requested within a transaction), and are not logged.

READ ONLY

Only read only transactions are allowed. No transactions are logged.

TEMPORARY

This option is SQLDB specific.

Note:Operations performed on databanks with the TRANSACTION or WORK option cannot be restored in the event of system failure, see Backing-up and Restoring Data.

If a databank is dropped from the database, the tables stored in the databank are also dropped and the physical databank file is deleted from disk.

Creating Idents and a Databank – an Example

In the following example, a databank and two users (idents) are created. The idents are then given the authority to create tables within the databank:

SQL> CREATE DATABANK DEVELOP SET FILE 'dev.dbf',

           FILESIZE 50 M, OPTION LOG;

SQL> CREATE IDENT DEVUSER AS USER USING 'devuser';

SQL> GRANT TABLE ON DEVELOP TO DEVUSER;

SQL> CREATE IDENT JIM AS USER;

SQL> ALTER IDENT JIM ADD OS_USER 'JIM';

SQL> ALTER IDENT JIM ADD OS_USER 'JAMES';

SQL> GRANT TABLE ON DEVELOP TO JIM;

Locating Databank Files

The system databank SYSDB is always stored in a file located in the directory defined as the home directory for the database, see The Local Database. The file locations of all the other system databanks and the user databanks are stored in the data dictionary.

The file specification for the databank file is exactly as specified when the databank was created. If a databank file specification is given in full, it is unambiguously specified and no variable factors are involved in resolving the location of the file.

If a databank file specification appears in the data dictionary without an absolute directory name, the database home directory will be used to complete the file specification.

This substitution is applied whenever the location of the databank file must be determined, (i.e. when the databank is created or altered and whenever tables stored in it are accessed).

Subsequent redefinition of the database home directory or any variables used in the file specification will, therefore, alter the expected location of such databank files.

Linux:Databases on Linux platforms may be set up with a directory search path instead of a single home directory, see The Local Database.

The first directory in the search path list must be the database home directory, where SYSDB is located. Other databank files can be located in any of the directories in the search path list.

 

 

VMS:Whenever a databank file is specified without a directory name under OpenVMS, it must be located in the database home directory.

If a logical name is included in the file specification, this will be recorded in the data dictionary and will be used whenever the location of the databank file is resolved.

Any logical names used in databank file specifications must be created as GROUP or SYSTEM wide logical names so that the database server process has access to them.

 

Win:Databases on Windows platforms may be set up with a directory search path instead of a single home directory, see The Local Database.

The first directory in the search path list must be the database home directory, where SYSDB is located. Other databank files can be located in any of the directories in the search path list.

The flexibility achieved by not using full databank file specifications must be weighed against the loss of explicitly specified information from the data dictionary. In addition, the centralized use of mechanisms such as environmental variables or logical names in a complex system requires careful and disciplined management.

In particular, it is necessary for the database server process to have access to all relevant environmental variables and logical names in order to use them when accessing the databanks.

Organizing Databank Files

There are a number of factors involved in the organization of physical databank files that are important to database security and the overall performance of the Mimer SQL system.

Allocating Disk Space

Whenever possible, pre-allocate file space for databanks early in the lifetime of the databank file system.

The databank creation facilities allow the initial size of a new databank file to be specified in terms of the number of Mimer SQL pages. The size of a Mimer SQL page is 4 kilobytes.

The size of the databank file will be extended automatically by the database server during the lifetime of the databank as more space is required for data storage.

Linux:Under Linux, the environment variable MIMER_EXTEND can be set to the number of Mimer SQL pages by which all databank files will be extended. The default setting is 128.

 

VMS:By default, under OpenVMS, databank files will be extended by 1000 OpenVMS blocks at a time. The extend size for a databank file can be altered by using the following DCL command:

$ SET FILE/EXTENSION=extensionsize file.DBF

 

The databank file must not be in use by the database server (or accessed in single user mode), when this command is used.

Win:Under Windows, the number of Mimer SQL pages by which all databank files will be extended is determined by the Mimer SQL system and is not configurable.

 

An attempt to extend a file will fail if the disk is full, the databank attribute MAXSIZE is reached, or any imposed disk quota is exceeded.

Having a small file extension size may cause disk fragmentation leading to reduced I/O performance. In addition, if the databank is growing rapidly, the frequently occurring file extension operations may have a negative effect on performance.

A databank file which is created with the size it will actually need in production will be accessed more efficiently than one created with a small initial size and then incrementally extended.

The SQL statement ALTER DATABANK SET FILESIZE can be used to change the size of a databank file to a specified size. ALTER DATABANK DROP FILESIZE is used to shrink the database file as much as possible. The attributes MAXSIZE, MINSIZE and GOALSIZE can also be used to manage the databank file size. Refer to the Mimer SQL Reference Manual, ALTER DATABANK, for details.

Mimer SQL databank files are organized internally into 4, 32 and 128 kilobyte databank blocks.

Accessing an internal databank block which is physically split over two or more distinct areas of allocated disk will require two disk read operations.

To avoid the risk of fragmenting the internal databank blocks, ensure that the number of disk blocks allocated for databank file extensions maps onto a whole number of 128 kilobyte databank blocks.

This will optimize disk I/O efficiency.

VMS:Disk blocks under OpenVMS are 512 bytes in size, therefore a disk cluster size which is a multiple of 8 will avoid fragmenting the 4 kilobyte databank blocks. The cluster size is set when formatting a disk.

Use the following command to check the cluster size of a disk that is already formatted:

$ SHOW DEVICE/FULL

 

Win:On Windows machines, disk clustering effects are hardware dependent and are not configurable.

Disks are typically configured in terms of an even number of 512 byte or 1024 byte disk blocks and will therefore always work efficiently with Mimer SQL databank files.

Use of disk defragmentation utilities may improve performance for large block
I/Os.

 

Protecting Data Against Loss

For data security reasons, in case of a disk failure, it is strongly recommended that LOGDB is located on a disk unit that is physically separate from that on which the other databanks are located. See Background Information for more information.

Ideally, TRANSDB and LOGDB should always be located on different physical disks which are served by separate disk controllers and no other databank files should be located on either disk.

The ordinary maintenance procedures for any computer system must involve backup and restore. A strategy, structure and procedure must be set up to include the Mimer SQL databases in the system backup routines. See Backing-up and Restoring Data for a detailed discussion of backup and restore.

Note:A system without a complete and valid backup and restore procedure runs the risk of losing valuable data.

Balanced I/O

If several physical disk units are available, the various databanks should be distributed across the available disk units in order to balance the system I/O load.

To optimize the distribution of I/O across disks, place databanks on physical disks in such a way that databanks which are likely to be accessed at the same time are on different disk units.

It is generally the case that TRANSDB will be accessed at the same time as other databanks during a transaction.

Reserved Directories

The structure of the databank file system and procedures such as backup and restore are generally simplified if databank files are placed in directories reserved solely for that purpose. The system administrator should create and maintain a directory structure that best suits the local system.

It is very common practice to reserve entire disks for databanks to allow for the ultimate size of the files.

Other Performance Issues

The placement of databanks on physical disk units will depend on exactly how they will be used when the database system is in operation.

The following issues generally have a more significant effect on database performance than the disk I/O factors relating specifically to physical layout of the Mimer SQL database:

the amount of virtual memory paging

the speed of the disk

the involvement of unnecessary network communication.

For example, to enhance performance, frequently accessed databanks such as TRANSDB may be placed on separate, high performance disks and sufficient memory should be allocated to avoid paging.

Altering Databank Locations

User databanks may be relocated by moving the physical file using operating system commands and then changing the file location stored in the data dictionary by using the ALTER DATABANK statement to specify the new file specification, see the Mimer SQL Reference Manual for the statement syntax.

The ALTER DATABANK statement may only be issued by the owner of the databank.

Example

1Disconnect the databank from the system.

SQL> SET DATABANK databank_name OFFLINE;

 

2Move or copy/delete the databank file to its new location.

3Alter the databank file name in the data dictionary and reconnect the databank to the system.

SQL> ALTER DATABANK databank_name SET FILE ´new_filename´;

SQL> SET DATABANK databank_name ONLINE PRESERVE LOG;

 

Facilities for changing the file specifications stored in the data dictionary for the system databanks, other than SYSDB, are provided by the BSQL program when a system databank is inaccessible, see Re-creating TRANSDB, LOGDB and SQLDB.

SYSDB must always be located in the home directory for the database.

The location of a databank cannot be altered while the database server is accessing it or while it is being accessed in single-user mode.

Note:You cannot move databanks between databases by copying the databank file and using the facilities to alter the databank location recorded in the data dictionary. You must use the LOAD command. See Loading and Unloading Data and Definitions.

Linux:Databases on Linux platforms may be set up with a directory search path instead of a single home directory, see Locating Databank Files.

A databank created without specifying the directory in the file specification may be moved between any of the directories in the search path list without the need to alter anything in the data dictionary. Before being moved, the databank should be set offline to ensure that the file is not locked by the database server.

 

Accessing Databank Files

The databank files in a Mimer SQL database are accessed by the database server regardless of the user running the applications. The operating system privileges that apply to accessing the databank files are associated with the database server.

If a Mimer SQL database is accessed in single-user mode, see Executing in Single-user Mode, the user must have the appropriate operating system level privileges in order to access the databank files.

Ownership of the databank files should not be confused with the creator of the databanks, which is internal to the Mimer SQL data dictionary. It is quite possible that a user who has created databanks is denied direct access at the operating system level to the files for those databanks.

Databank File Deletion

If a databank or shadow is dropped, the corresponding file will also be deleted from disk. Remember that dropping a Mimer SQL ident will also drop all objects, including databanks, that the ident has created.

When a databank is dropped, all shadows of the databank will also be dropped.

Note:If the databank is OFFLINE when it is dropped, the databank file (and any shadow files) will remain on disk in the file system and must be manually deleted.

Transaction Control

Transaction control provides a means of protecting the database from corruption which might arise from two users attempting to the change the same information at the same time and also provides the basis for ensuring database consistency, see Database Consistency.

Optimistic Concurrency Control

Mimer SQL transaction management uses Optimistic Concurrency Control (OCC), which is described in the Mimer SQL Programmer's Manual, Transaction Handling and Database Security.

This type of concurrency control overcomes many of the problems that can occur with conventional locking techniques (e.g. deadlocks and locks being retained by defunct connections). Superior performance is achieved because there is no need for the overhead of a deadlock detection mechanism, since deadlocks cannot occur.

Transaction Phases

A transaction is an atomic operation. Atomic means that all the changes that form the transaction are applied to the database, or none of them are applied.

Three transaction phases exist: build-up, during which the database operations are requested, prepare, during which the transaction is validated, and commitment, during which the operations performed in the transaction are written to disk.

The transaction begins by taking a snapshot of the database in a consistent state.

During build-up, changes requested to the contents of the database are kept in a write-set and are not visible to other users of the system. This allows the database to remain fully accessible to all users. The application program in which build-up occurs sees the database as though the changes had already been applied. Changes requested during transaction build-up become visible to other users when the transaction is successfully committed.

During build-up, a read-set records the state of the database as seen at the time of each operation (including intended changes). If the state of the database at commitment is inconsistent with the read-set, a conflict is reported and the transaction is rolled back (i.e. the write-set is erased and no changes are made to the database). This can happen if, for instance, a transaction asks to update a row which is deleted by another user after build-up has started but before the transaction is committed. The application program is responsible for taking appropriate action if a transaction conflict occurs.

Transaction control behavior in application programs and a number of the system facilities, notably Backup and Restore – see Backing-up and Restoring Data, is controlled at the databank level by setting the option (LOG, TRANSACTION, WORK or READ ONLY) for the databank.

Only operations performed in databanks set up with the LOG option are logged in the Mimer SQL system databank LOGDB. Write operations against tables in LOG and TRANSACTION databanks must be performed under transaction control (i.e. within a transaction).

Refer to the Mimer SQL Programmer's Manual, Transaction Handling and Database Security for more information.

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 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. Create the user idents WITHOUT SCHEMA. 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, 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 or per-compiled 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 nameddomain 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

1A grants with grant option to M

M grants to X

2B grants with grant option to M

M grants to Y

3A revokes from M

Both X and Y keep privileges

4B revokes from M

Both X and Y lose privileges

CASE 2

1A grants with grant option to M

2B grants without grant option to M

M grants to X

M grants to Y

3A revokes from M

M loses grant option

Both X and Y lose privileges

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

Data Integrity

The following facilities are available for ensuring the integrity of a Mimer SQL database:

domains

entity integrity (non-null primary keys)

referential integrity (foreign keys)

table integrity

view integrity

Domains

Domains define sets of permissible values. By assigning a table column to a domain when the table is created or altered, the values which the column may contain are restricted to those defined in the domain. Any number of columns may use a given domain.

The ident defining a table column must hold USAGE privilege on any domain used.

A default value may also be defined for a domain. The domain default value is inserted into a column defined using the domain when data is inserted without an explicit column value being specified.

If the default value for the domain is defined outside the range of restriction values for the column, attempts to store the default value in a column using the domain will fail. In such a case an explicit value must always be specified when inserting data into the column.

The use of domains in table definitions is recommended, since this can provide an automatic check on the validity of data inserted into the column. However, domain definitions should be carefully planned, since a domain definition cannot currently be altered after it has been defined.

Entity Integrity

Entity integrity refers to the requirement that every row in a table must be uniquely identified and that no row in a table may be identified by null (i.e. by an unknown value).

Entity integrity can only be enforced if a primary key constraint or unique constraints are applied.

All primary key columns in tables created by Mimer SQL are defined as NOT NULL, thus ensuring entity integrity. Other (i.e. non-primary key) columns may also be defined as NOT NULL as required.

Referential Integrity

Referential integrity refers to the requirement that data entered into a table in the database must already be present in another table (e.g. a component may not be entered in a parts list if it does not already exist in the set of known components in the database).

Mimer SQL supports referential integrity through the FOREIGN KEY clause in the CREATE TABLE statement.

The properties of a FOREIGN KEY are as follows:

The columns defined as a foreign key must correspond exactly in number and data type to the primary key or unique key columns in the referenced table.

Data inserted into the foreign key columns (by either INSERT or UPDATE operations) must either already be present in the primary key or a unique key of the reference table or include at least one null column.

A primary or unique key value that is referenced by a foreign key must not be removed by an update operation. It may be possible to remove such a value with a delete operation provided the ON DELETE rule is used to update the referencing table in a way that preserves the referential integrity.

Note:Referential integrity constraints are effectively checked at the end of the INSERT, DELETE or UPDATE statement. Both the table containing the foreign key reference and the referenced table must be stored in a databank with either the TRANSACTION or LOG option.

Table Integrity

Table integrity refers to the facility in Mimer SQL of defining CHECK clauses in a table definition, whereby the contents of one column is checked against the contents of one or more other columns in the same row of the table.

Data may only be entered into the table if the CHECK constraint is not violated.

View Integrity

View integrity refers to the facility in Mimer SQL of including a WITH CHECK OPTION clause in a view definition. If a view is defined with a WITH CHECK OPTION, data which violates the definition of the view may not be entered into the view by INSERT or UPDATE operations.

When a view is defined with a CHECK OPTION, any views defined on that view will inherit the CHECK OPTION.