Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Granting Privileges


The following sections explain how to grant system, object and access privileges.

Granting System 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:

Privilege
Explanation
BACKUP
The right to perform backup and restore operations.
DATABANK
The right to create databanks.
IDENT
The right to create idents and schemas.
SCHEMA
The right to create schemas.
SHADOW
The right to create shadows and perform shadow control operations.
STATISTICS
The right to execute the UPDATE STATISTICS statement.

Examples

Give the ident HOTELADM the privilege to create new databanks:
 GRANT  DATABANK 
    TO  HOTELADM;
Give the idents AUDIT and ECONOMY_DEPT the privilege to create new idents with grant option:
 GRANT  IDENT
    TO  AUDIT, ECONOMY_DEPT
  WITH  GRANT OPTION;

Granting Object Privileges

Object privileges are held by idents on database objects (functions, procedures, programs, groups, tables, domains and sequences).

The four object privileges are:

Privilege
Explanation
EXECUTE
The right to execute a function or procedure or the right to enter a specified program ident.
MEMBER
Membership in a specified group ident.
TABLE
The right to create tables in a specified databank.
USAGE
The right to specify the named domain where a data type would normally be specified (in contexts where use of domains is allowed) or the right to use a specified sequence.

Examples

Give STEVE and MARIANNE the privilege to execute the SUMMARY_STATS procedure:
 GRANT  EXECUTE ON PROCEDURE SUMMARY_STATS
    TO  STEVE, MARIANNE;
Give ECONOMY_DEPT the privilege to enter the AUDIT program ident:
 GRANT  EXECUTE ON PROGRAM AUDIT
    TO  ECONOMY_DEPT;
Make STEVE, MARIANNE and JAMES members of the ECONOMY_DEPT group with grant option:
 GRANT  MEMBER ON ECONOMY_DEPT
    TO  STEVE, MARIANNE, JAMES
  WITH  GRANT OPTION;
Give the members of the ECONOMY_DEPT group the privilege to create new tables in the HOTELDB databank:
 GRANT  TABLE ON HOTELDB
    TO  ECONOMY_DEPT;
Give the members of the ECONOMY_DEPT group the privilege to use the LOCAL_CURRENCY domain:
 GRANT  USAGE ON DOMAIN LOCAL_CURRENCY
    TO  ECONOMY_DEPT;

Granting Access Privileges

Access privileges define what data the idents are allowed to manipulate in tables.

There are five access privileges:

Privilege
Explanation
SELECT
The right to read the table contents.
INSERT
The right to add new rows to the table (this privilege may be limited to specified columns within the table).
DELETE
The right to remove rows from the table.
UPDATE
The right to change the contents of existing rows in the table (this privilege may be limited to specified columns within the table).
REFERENCES
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, ALL may be followed by the optional keyword PRIVILEGES.

Examples

Give JAMES the privilege to read, insert, and delete rows from the BOOK_GUEST table and give the ident the right to pass these privileges on to other idents:
 GRANT  SELECT, INSERT, DELETE 
    ON  BOOK_GUEST
    TO  JAMES
  WITH  GRANT OPTION;
Give ECONOMY_DEPT and AUDIT all privileges that you hold on the table CHARGES but do not give them the right to pass these privileges on to other idents:
 GRANT  ALL ON CHARGES
    TO  ECONOMY_DEPT, AUDIT;
Give ECONOMY_DEPT the privilege to update all columns in the BOOK_GUEST table:
  GRANT  UPDATE ON BOOK_GUEST
     TO  ECONOMY_DEPT;
Give RECEPTION the privilege to update only the GUEST_LNAME, ADDRESS, and ROOMNO columns in the BOOK_GUEST table:
 GRANT  UPDATE (GUEST_LNAME,ADDRESS,ROOMNO)
    ON  BOOK_GUEST
    TO  RECEPTION;
Give ECONOMY_DEPT the right to use the ROOMS table as a foreign key:
 GRANT  REFERENCES
    ON  HOTELADM.ROOMS
    TO  ECONOMY_DEPT;


Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX