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:
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.
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;
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:
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.
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;
Granting Access Privileges
Access privileges define what data the idents are allowed to manipulate in tables.
There are five access privileges:
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.
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;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40