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