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 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.
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 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 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 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.
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.
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.
A Mimer SQL database consists of the Mimer SQL system databanks and a number of user databanks.
Each databank is one or several physical files in the host file system.
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 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.
SYSDB is the only databank that must consist of only one file.
TRANSDB stores information that is vital for keeping the database in a consistent state.
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 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 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.
Databanks may be defined with the LOG, TRANSACTION, WORK, or READ ONLY options which determine transaction handling and logging behavior, as follows:
All operations on the databank are performed under transaction control. All transactions are logged.
All operations on the databank are performed under transaction control. No transactions are logged.
All operations on the databank are performed without transaction control (even if they are requested within a transaction), and are not logged.
Only read only transactions are allowed. No transactions are logged.
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 files are 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;
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.
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 |
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.
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.
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 1
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;
Example 2
By adding a new file and then deleting the original file, the databank tables will be available to users during the process.
1Add a new file to the databank.
SQL> ALTER DATABANK databank_name ADD FILE 'new_file';
2Drop the original databank file.
SQL> ALTER DATABANK databank_name DROP FILE 'old_file';
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. |
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.
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.
Tables and indexes reside in a databank. In the past a databank has been equivalent to a file in the file system. Now, a databank may consist of one or several files. If the files are placed on separate drives, both read and write performance is increased. This is because it is possible to both read and write to blocks in parallel on separate drives.
When there are several files, the database system will distribute the data evenly between the files automatically. In a b*-tree, blocks in one file may point to blocks in the same or other files in the databank in any fashion.
If a file is added to an existing databank, the file is added without moving any data. However, whenever new blocks are needed, the new file will be used and eventually the new file and the old one will hold equal amounts of data.
Any databank in the system can be a multifile databank except SYSDB. This means that the system databanks TRANSDB, LOGDB, and SQLDB may use the multifile support.
It is possible to add and drop files while a databank is in use. When a drop operation is done all the data in the dropped file is reallocated in the remaining files in the databank. This may take a long time if there is significant amount of data in the file. If the command is canceled, the operation is aborted and whatever data has been moved will remain in its new location. The file will in this case be active and new data will be stored in it.
Only one add or drop operation is allowed per databank concurrently. An error message, “databank locked” (error code -16172,) is given if this is tried.
Tip:If you have a databank that you want to move to another location, for example, another disk drive this can be done by first doing ALTER DATABANK ADD FILE in the new location, followed by ALTER DATABANK DROP FILE for the old location. This will effectively move all the data in the databank to the new location/drive. Since both of these commands can be done on while the data is use, it can actually be done on a live system with no impact for applications except some extra I/O activity as the data is relocated.
Multifile databanks are very easy to both add and drop. However, there are some things to consider when using multifile databanks.
Since the files in a multifile databank have block references back and forth it means that all files in a multifile databank have a strong bond. It is, for example, not possible to replace one of the files. If this is done inadvertently the system will detect this and will give error message “One of the files for databank <%> does not match the other files in databank file set”, with error code -16264.
For multifile databanks there exist two sets of commands which may at first appear similar. They are:
ALTER DATABANK x ADD FILE 'filename'
and
ALTER DATABANK x ADD FILENAME 'filename'
The first command, ADD FILE, is used on a live system when the databank is accessible and we want to concurrently add a new file to the multifile set.
The second command, ADD FILENAME, is used when the data dictionary is out of date. If, for example, a backup has two files that we want to bring back. But if we have dropped one of the files since the backup, then in this case the data dictionary only has information about a single file. ADD FILENAME adds the file to the data dictionary.
So ADD FILE creates a new multi-file in a healthy system. ADD FILENAME is a data dictionary operation only, and is used to correct situations when we want to add existing files to a databank.
The corresponding DROP operations are also available.
ALTER DATABANK x DROP FILE 'filename'
and
ALTER DATABANK x DROP FILENAME 'filename'
The first command, DROP FILE, is used on a live system to migrate the data away from the file and then remove the file from the file system and the data dictionary.
The second command, DROP FILENAME, is used when the data dictionary is out of date. If, for example, a backup has a singe file that we want to bring back. But if we have added a file since the backup, then in this case the data dictionary has two files. DROP FILENAME removes the filename from the data dictionary.
So DROP FILE removes a file from a multifile databank in a healthy system. DROP FILENAME is a data dictionary operation only, and is used to correct situations when we want to remove references to files that no longer exists.
It can be noted that we want the data dictionary to have the correct number of files in the correct location. Each backup file may be placed in any of the locations pointed to by the data dictionary. I.e. copying file a1 to location /dev2/dbfiles/xx, and file b1 to location /dev1/dbs/yy works just as well as copying a1 to /dev1/dbs/yy, and b1 to /dev2/dbfiles/xx. I.e. the system will sort out the actual contents as long as the files belong together. (In this example, the data dictionary file names are /dev1/dbs/yy and /dev2/dbfiles/xx for the databank.)
To be able to use the ALTER DATABANK commands the database server must be up and running. This means that all the system databanks must be available. Since the system databanks, except for SYSDB, may also consist of several files we must have a way to handle these. Any errors for the system databanks are handled with the batch SQL utility (bsql). When running bsql you will be guided along for each system databank in turn. It allows ALTER and DROP FILENAME operations to be performed. In addition, the databanks may be recreated from scratch or in some cases by reinitializing existing files. For these operations to be allowed you must log in as SYSADM. Some cases are covered in the later scenarios below.
Multifile scenarios
Here we describe a number of possible scenarios that may occur for multifile databanks.
Scenario 1
Let us assume you have a multifile databank with two files, each on its own disk. Let us also assume you have the same number of backup files.
One of the files is accidentally deleted. In this case you cannot bring back only the deleted file. You have to bring back BOTH files since they are strongly bonded. After both files are present the ALTER DATABANK x RESTORE USING LOG command is used to apply the changes since the backup. The restore will use both files automatically. RESTORE will only find data to restore if databank option LOG is used (see CREATE DATABANK or ALTER DATABANK command.)
Scenario 2
Let us assume you have a multifile databank with two files, each on its own disk. Let us also assume you have the same number of backup files.
One disk crashes and this disk is no longer accessible. Again, you have to copy BOTH of your backup files to the file system. One of the files overwrites the undamaged, remaining file, and the other file now needs to be placed in a new location. The SYSDB databank holds the data dictionary. The data dictionary points to the old location of the file on the device that is no longer available.
This location is now changed using the ALTER DATABANK x ALTER 'oldloc.dbf' SET FILE 'newlocation.dbf' command.
Next, the ALTER DATABANK x RESTORE USING LOG command is used to apply the changes since the backup.
Scenario 3
Let us assume you have a multifile databank with two files. You have taken an online backup of the databank. Online backups are currently always in a single file.
One of the files is accidentally deleted. In this case we want to bring back the backup. However, the data dictionary has two files and we only have a single file now. We remove one of the file references from the data dictionary with ALTER DATABANK x DROP FILENAME 'filenametodel.dbf'.
The dictionary only has a single file now for the databank. We can now copy the backup to the remaining file's location.
Next, the ALTER DATABANK x RESTORE USING LOG command is used to apply the changes since the backup.
Scenario 4
You have a multifile databank consisting of three files. You accidentally delete one the files and you have no backup of the databank.
In this case the entire contents of the databank is lost. There is no way to find the data without the missing file. If you have a databank with one file that is accidentally deleted the result is the same.
Scenario 5
Let us assume you have a multifile databank with two files. You have a backup of, among other files, the system databank SYSDB.
The file system with SYSDB is corrupted. You have to reinitialize it. You then bring in your backup of SYSDB. The SYSDB needs to be restored. This is done using the batch SQL utility. Batch SQL will prompt you for confirmation that you want to restore SYSDB (provided you have logged in as SYSADM).
In this case your SYSDB with its data dictionary will have the correct contents and should correspond to the other files present on the system.
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.
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.
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.
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 give the right to create global objects in the database. There are the following system privileges:
|
System Privilege |
Description |
|---|---|
|
BACKUP |
|
|
DATABANK |
|
|
IDENT |
|
|
SCHEMA |
|
|
SHADOW |
gives the right to create shadows and perform shadow control operations |
|
STATISTICS |
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 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 give rights of access to the contents of a specified table or view. There are the following access privileges:
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
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.
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 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 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 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 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 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.