Privileges control the operations which users are allowed to perform in the database. Well-structured privileges are essential for maintaining data security.
There are three types of privileges:
•System privileges, which give the right to create global objects within the database.
•Object privileges, which give rights over certain specified objects in the database.
•Access privileges, which give rights of access to the data in a specified table or view.
System privileges are granted to the system administrator upon installation, and may be passed on to other idents. Objects and access privileges are initially granted only to the creator of an object. The creator may however pass the privileges on to other idents.
Granting and Revoking Privileges
Privileges are granted to idents with the GRANT statement and revoked from idents with the REVOKE statement.
All privileges may be granted with the ‘with grant option’, which means that the receiver of the privilege in turn has the right to grant that privilege to other idents.
The creator of an object is automatically granted full privileges on that object with grant option. Thus the creator of:
•a group is automatically a member of that group
•a program ident may enter it
•a table has full access privileges
•a schema may create objects in it and drop them, etc.
When privileges that were granted with the ‘with grant option’ are revoked, the right to grant those privileges to other idents is also revoked.
The ‘with grant option’ can be revoked separately without revoking the privilege itself.
Idents may only grant privileges that they themselves possess to other idents, that is, idents cannot grant privileges to themselves.
Likewise, privileges may only be revoked by the grantor - idents cannot revoke privileges from themselves.
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 himself or by the creator of the ident), DROP and COMMENT.
In the initial installation, one user ident, the system administrator with user ident name SYSADM, is automatically created.
The system administrator has the following privileges (with grant option):
•BACKUP
•DATABANK
•IDENT
•SCHEMA
•SHADOW
•SELECT access on all tables and views in the data dictionary.
The system administrator is ultimately responsible for the structure of the whole system. In other respects, however, the system administrator is an ordinary user ident in the system.
There is no ident in Mimer SQL with automatic right of access to all objects within the system.
It is quite possible, and may be advisable especially in large system, that the system administrator is prevented from accessing the actual contents of the database; the system administrator’s job is to manage objects in the system, not work on the data.
Certain system utilities may only be run by idents with BACKUP or SHADOW privilege, see the Mimer SQL System Management Handbook.
When granting privileges, the keyword PUBLIC refers to a logical group that covers all idents in the database, including those created in the future.
Recommendations for Ident Structure
The following general recommendations can be made for structuring the idents in a system:
•Functional roles within the system, generally defined by one or more applications that are run, should be assigned to program idents. These are not coupled to any physical individual or group of individuals and thus have a lifetime independent of turnover of personnel.
The system administrator is just such a function, but is coupled to a user ident rather than a program ident for practical purposes.
•People accessing the system are represented by USER idents. They may be dropped if the person concerned leaves the company.
User idents should not be granted privileges directly, other than membership in groups. A USER ident with an OS_USER login is allowed access to the database on the authorization of a valid log-in to the operating system.
•Group idents are used to represent logical users of the system. Privileges are granted to groups rather than to individual programs or users. The individual idents are granted membership in the group to which they belong, and thereby gain the correct access to the system.
•USER idents should not in general be able to create objects. This is performed by specifying WITHOUT SCHEMA when the user is created. In this way, individual user idents may be dropped with no cascading effects.
•WITH GRANT OPTION should be used sparingly and the ident hierarchy kept shallow. This minimizes the chance of undesired cascading revocation of privileges.
If these recommendations are followed, maintenance of the ident structure in the system is simplified. Access to the contents of the database is granted to relatively few group idents instead of many individual programs or users, and when a physical individual leaves the company, their user ident can be dropped with no cascading consequences.
The following sections explain how to grant system, object and access privileges.
System privileges are granted to the system administrator at the time of installation of the system. System privileges refer to global information, that affects the database as a whole.
The system privileges are:
|
Explanation |
|
|---|---|
|
The right to perform backup and restore operations. |
|
|
The right to create databanks. |
|
|
The right to create idents and schemas. |
|
|
The right to create schemas. |
|
|
The right to create shadows and perform shadow control operations. |
|
|
The right to execute the UPDATE STATISTICS statement. |
Give the ident MIMER_STORE the privilege to create new databanks:
GRANT DATABANK TO mimer_store;
Give the ident MIMER_STORE the privilege to create new idents with grant option:
GRANT IDENT TO mimer_store WITH GRANT OPTION;
Object privileges are held by idents on database objects (functions, procedures, programs, groups, tables, domainssequences).
The object privileges are:
|
Explanation |
|
|---|---|
|
The right to execute a function or procedure, or the right to enter a specified program ident. |
|
|
Membership in a specified group ident. |
|
|
The right to create tables in a specified databank. |
|
|
The right to create sequences in a specified databank. |
|
|
The right to specify the named domain where a data type would normally be specified (in contexts where use of domains are allowed), or the right to use a specified sequence. |
Give MIMER_WEB the privilege to execute the COMING_SOON procedure:
GRANT EXECUTE ON PROCEDURE coming_soon TO mimer_web;
Make MIMER_ADM a member of the MIMER_ADMIN_GROUP group with grant option:
GRANT MEMBER ON mimer_admin_group TO mimer_adm WITH GRANT OPTION;
Give MIMER_ADM the privilege to create new tables in the MIMER_STORE databank:
GRANT TABLE ON mimer_store TO mimer_adm;
Give the members of the MIMER_ADMIN_GROUP group the privilege to use the NAME domain:
GRANT USAGE ON DOMAIN name TO mimer_admin_group;
Access privileges define what data the idents are allowed to manipulate in tables.
There are five access privileges:
|
Explanation |
|
|---|---|
|
The right to read the table contents. |
|
|
The right to add new rows to the table (this privilege may be limited to specified columns within the table). |
|
|
The right to remove rows from the table. |
|
|
The right to change the contents of existing rows in the table (this privilege may be limited to specified columns within the table). |
|
|
The right to use the primary or unique key of the table as a foreign key reference (this privilege may be limited to specified columns within the table). |
The keyword ALL may be used as shorthand for all of privileges that the grantor holds with grant option.
Give the MIMER_ADMIN_GROUP group the privileges to read and update rows from the PRODUCERS table:
GRANT SELECT, UPDATE ON producers TO mimer_admin_group;
Give MIMER_USER_GROUP all privileges that you hold on the table COUNTRIES and give them the right to pass those privileges on:
GRANT ALL ON countries TO mimer_user_group WITH GRANT OPTION;
Give MIMER_ADMIN_GROUP the privilege to select all rows in the CURRENCIES table, with the privilege to only update the EXCHANGE_RATE column:
GRANT SELECT, UPDATE(exchange_rate) ON currencies TO
mimer_admin_group;
Give everyone the privilege to select all rows in the CURRENCIES table:
GRANT SELECT ON currencies TO PUBLIC;
Give MIMER_ADM the right to use the ITEMS table as a foreign key:
GRANT REFERENCES ON mimer_store.items TO mimer_adm;
Privileges can only be revoked by the grantor. Care must be taken when revoking privileges, especially when those privileges were granted ‘with grant option’. Revoking such privileges from an ident can have recursive effects on all idents who have been granted privileges by that ident. See Recursive Effects of Revoking Privileges for details.
The keywords CASCADE and RESTRICT can be used in the REVOKE statements to control whether the recursive effects should be allowed or not. If RESTRICT (the default) is specified and any recursive effects are identified the whole revoke operation will fail, leaving all objects intact. If the keyword CASCADE is specified, the revoke operation will proceed with recursive effects.
Privileges granted to a group cannot be revoked separately from individual members of the group. To revoke a group privilege from an individual, either revoke the privilege from the group or revoke the membership of the individual in the group.
If a privilege has been granted with the WITH GRANT OPTION it is possible to revoke the grant option only. That is, the ident looses the right to grant the privilege to other idents. The ident still has the privilege, but privileges granted to other idents are revoked.
Revoking system privileges does not affect objects already created under the authorization of the privilege.
The following examples show how to revoke system privileges.
Take away the privilege to create new databanks from the ident MIMER_STORE:
REVOKE DATABANK FROM mimer_store RESTRICT;
Take away the privilege to create new idents from the ident MIMER_STORE:
REVOKE IDENT FROM mimer_store RESTRICT;
The following examples show how to revoke object privileges.
Take away the privilege to execute the COMING_SOON procedure from MIMER_WEB:
REVOKE EXECUTE ON PROCEDURE coming_soon FROM mimer_web;
Take away MIMER_ADM's membership of the MIMER_ADMIN_GROUP group:
REVOKE MEMBER ON mimer_admin_group FROM mimer_adm;
Take away the right to use the domain NAME from the group MIMER_ADMIN_GROUP:
REVOKE USAGE ON DOMAIN name FROM mimer_admin_group;
Note:Revoking usage on domain prevents the ident from using that domain as a data type in new definitions, any existing definitions created by the ident will remain unaffected.
The following examples show how to revoke access privileges.
Revoke the privilege to read and update rows from the PRODUCERS table from the group MIMER_STORE_GROUP:
REVOKE SELECT, UPDATE ON producers FROM mimer_admin_group;
When the REFERENCES privilege on a table is taken away from an ident, all foreign key links referencing that table are removed.
Revoke the right to use columns in the ITEMS table as a foreign key from MIMER_ADM:
REVOKE REFERENCES ON mimer_store.items FROM mimer_adm RESTRICT;
Revoke the right to grant select on the COUNTRIES table. Any grants that members of the group have made will also be revoked:
REVOKE GRANT OPTION FOR SELECT ON countries
FROM mimer_user_group CASCADE;
The Keyword ALL
The keyword ALL may be used as a shorthand for all the privileges that may be revoked in the current context.
Recursive Effects of Revoking Privileges
If CASCADE is specified in a REVOKE statement, the following recursive effects may occur:
•If a privilege WITH GRANT OPTION is revoked from an ident, all instances of that privilege granted to other idents under the authorization of the WITH GRANT OPTION are also revoked. Privileges granted for procedures, functions and triggers that reference objects accessed by the WITH GRANT OPTION will also disappear.
•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 REFERENCE privilege on a table is revoked from an ident, any FOREIGN KEY constraints in tables created by that ident under the authorization of that REFERENCE privilege are removed.
•If the privilege held by an ident on an object referenced in a routine or trigger is revoked, the routine or trigger will be dropped. (This applies to EXECUTE on a routine, USAGE on a sequence or an access privilege on a table or view held WITH GRANT OPTION).
Dependencies
The recursive effect of revoking a privilege depends on how many instances of that privilege have been granted. An ident will hold more than one instance of a privilege when it has been granted more than once (by different idents, as an ident cannot grant the same privilege to the same ident more than once).
One or more of those instances may have been granted WITH GRANT OPTION.
The data dictionary keeps a record of which instance of a privilege has WITH GRANT OPTION and which does not.
The recursive effects will occur only when the last instance of the required privilege is revoked. That is, when the last instance of the privilege held WITH GRANT OPTION is revoked from an ident, all instances of the ident granting the privilege to others will be withdrawn; and when the last instance of the privilege is revoked from the ident, the cascade effects of the ident no longer holding the privilege will occur.
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
As a consequence of the cascading effects of revoking privileges, careful advance planning of the hierarchical structure of idents in a system can be essential to the long term viability of the system.
An unplanned ident structure can easily become impossible to overview and control after a relatively short period of system use.