Privileges control users' access to database objects and the operations they can perform in the database.
PROGRAMidents are usually 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. (Alternatively, an OS_USER login can be used to login without providing a password.)
Passwords are stored in encrypted form in the data dictionary and cannot be read by any ident, including the system administrator. A password may only be changed by the ident to which it belongs or by the creator of the ident.
A set of access and privileges define the operations each ident is permitted to perform. There are three classes of privileges in a Mimer SQL database:
System privileges control the right to perform backup and restore operations, the right to execute the
UPDATE STATISTICSstatement 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 control membership in group idents, the right to invoke functions, method and procedures, the right to enter program idents, the right to create new tables or sequences in a specified databank and the right to use a domain, user-defined type 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, method or procedure may execute the routine
- a program ident may enter the program ident
- a schema may create objects in and drop objects from the schema
- a databank may create tables and sequences in the databank
- a table has all access rights on the table
- a domain may use that domain
- a user-defined type may use that user-defined type and its methods
- 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, methods and procedures, this actually depends on the creator's access rights on objects referenced from within the routine.
Access privileges 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 OPTIONwhich 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 OPTIONas 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 the
WITH GRANT OPTIONand subsequently grants the privilege again with the
WITH GRANT OPTION, the
WITH GRANT OPTIONwill 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 the
WITH GRANT OPTIONwithout revoking the associated privilege completely. See the Mimer SQL User's Manual, Defining Privileges for more information.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40