GRANT ACCESS PRIVILEGE

Grants one or more access privileges on a table or view.

grant_access_privilege.png

 

where access is:

access.png

 

Usage

Embedded/Interactive/Module/ODBC/JDBC.

Description

The specified access privileges are granted to the ident(s) listed. When WITH GRANT OPTION is specified, the ident may in turn grant the specified access privileges to another ident. If the privileges are granted to a GROUP ident, all members of the group receive the privileges.

Access Privileges

The access privileges are as follows:

DELETE

allows the ident to delete rows from the table or view identified by table-name. If a selective delete is specified, involving a WHERE clause, appropriate privileges must also to held to permit execution of the search-condition, otherwise the delete operation will fail.

INSERT

allows the ident to insert new rows into the table or view identified by table-name. If a column-name list is supplied, the access is restricted to the specified columns, otherwise the access applies to the entire table (including any new columns subsequently added).

REFERENCES

allows the ident to use the table identified by table-name in the FOREIGN KEY clause of a CREATE TABLE statement. REFERENCES access may only be granted on a base table, not on a view. If a column-name list is supplied, the access is restricted to the specified columns, otherwise the access applies to the entire table (including any new columns subsequently added).

SELECT

Allows the ident to select rows from the table or view identified by table-name.

UPDATE

Allows the ident to update the table or view identified by table-name. If a column-name list is supplied, the access is restricted to the specified columns, otherwise the access applies to the entire table (including any new columns subsequently added). If a selective update is specified, involving a WHERE clause, appropriate privileges must also to held to permit execution of the search-condition, otherwise the update operation will fail.

Access privileges may be granted in any combination. Specification of the keyword ALL (followed by the optional keyword PRIVILEGES) instead of an explicit list of access privileges results in all applicable privileges being granted to the specified ident(s) (i.e. all privileges which the grantor is authorized to grant).

Restrictions

The grantor must have grant option on the access privilege.

Notes

If the grantor loses WITH GRANT OPTION, any access privileges he has granted using it are automatically revoked.

An ident may not grant access privileges to himself.

Example

The following example is taken from the Mimer SQL User's Manual, Granting Access Privileges.

GRANT SELECT, UPDATE(exchange_rate) ON currencies TO mimer_admin_group;

Standard Compliance

Standard

Compliance

Comments

SQL-2016

Core

Fully compliant.

SQL-2016

Features outside core

Feature F731, “INSERT column privileges” support for granting insert on individual columns.

 

Mimer SQL extension

The keyword PRIVILEGES is optional and not mandatory in Mimer SQL.