Access Rights and Privileges
Privileges control how users may access database objects and the operations they can perform in the database.
USER and PROGRAM idents are protected by a password, which must be given together with the correct ident name in order for a user to gain access to the database or to enter a PROGRAM ident. Passwords are stored in encrypted form in the data dictionary and cannot be read by any ident, including the system administrator. An ident's password may only be changed by the ident or by the creator of the ident.
A set of privileges define the operations each ident is permitted to perform. There are three classes of privileges in a Mimer SQL database: system, object and access.
System privileges, which control the right to perform backup and restore operations, the right to execute the UPDATE STATISTICS statement as well as the right to create new databanks, idents, schemas and to manage shadows.
System privileges are granted to the system administrator when the system is installed and may be granted by the administrator to other idents in the database. As a general rule, system privileges should be granted to a restricted group of users.
Note: An ident who is given the privilege to create new idents is also able to create new schemas.
Object privileges, which control membership in GROUP idents, the right to invoke functions and procedures, the right to enter PROGRAM idents, the right to create new tables in a specified databank and the right to use a domain or sequence.
The creator of an object is automatically granted full privileges on that object.
Thus the creator of:
- a group is automatically a member of the group
- a function or procedure may execute it
- a PROGRAM ident may enter it
- a schema may create objects in and drop objects from it
- a databank may create tables in the databank
- a table holds all privileges on the table
- a domain may use that domain
- a sequence may use that sequence.
The creator of an object generally has the right to grant any of these privileges to other users, in the case of functions and procedures this actually depends on the creator's privileges on objects referenced from within the routine.
Access privileges, which define access to the contents of the database, i.e. the rights to retrieve data from tables or views, delete data, insert new rows, update data and to refer to table columns as foreign key references.
Granted privileges can be regarded as instances of grantor/privilege stored for an ident. An ident will hold more than one instance of a privilege if different grantors grant it.
A privilege will be held as long as at least one instance of that privilege is stored for the ident. All privileges may be granted with the WITH GRANT OPTION which means that the receiver has, in turn, the right to grant the privilege to other idents. An ident will hold a privilege with the WITH GRANT OPTION as long as at least one of the instances stored for the ident was granted with this option.
If the same grantor grants a privilege to an ident more than once, this will not result in more than one instance of the privilege being recorded for the ident. If a particular grantor grants a privilege without WITH GRANT OPTION and subsequently grants the privilege again with WITH GRANT OPTION, then WITH GRANT OPTION will be added to the existing instance of the privilege.
Each instance of a privilege held by an ident is revoked separately by the appropriate grantor. It is possible to revoke WITH GRANT OPTION without revoking the associated privilege completely. Revoking Privileges describes revoking privileges in more detail.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40