Example of Granting Privileges to a Group of Users
The following request grants privileges to a group of users. In this example, all users created under the finance database are granted the privilege to SELECT data from the department table, which is in the personnel database:
GRANT SELECT ON personnel.department TO ALL finance;
Example of Granting SELECT on Any Object
The following statement allows user Moffit to retrieve information from any object in the personnel database:
GRANT SELECT ON personnel TO moffit;
Example of Granting SELECT on One Column of a Table
This example grants the SELECT privilege only on column_1 of the table named department in the personnel database to user Moffit.
GRANT SELECT (column_1) ON personnel.department TO moffit;
Example of Granting SELECT, INSERT, UPDATE, and DELETE on Any Object
The following statement allows user Peterson to perform selects, inserts, updates, and deletes on any object in the personnel database:
GRANT SELECT, INSERT, UPDATE, DELETE ON personnel TO peterson;
Example of Granting USER Privilege On a Database
The following statement allows user Phan to create users within the personnel database space, but it does not allow him to modify or drop any users within the personnel database except for the ones that he creates.
GRANT USER ON personnel TO phan;
Example of Granting Privileges Related to Triggers
The following request grants CREATE TRIGGER and DROP TRIGGER privileges to user TrigUser for any table in database TrigDB:
GRANT CREATE TRIGGER, DROP TRIGGER ON TrigDB TO TrigUser;
The following request grants CREATE TRIGGER and DROP TRIGGER privileges to user TrigUser for TrigDB as the subject table .
GRANT CREATE TRIGGER, DROP TRIGGER ON TrigTable TO TrigUser;
You can also grant the same privileges as in the previous requests using the short form TRIGGER to mean both CREATE TRIGGER and DROP TRIGGER.
For example:
GRANT TRIGGER ON TrigDB TO TrigUser; GRANT TRIGGER ON TrigTable TO TrigUser;
Example of Granting Privileges on an External Function
- ALTER FUNCTION on the specific external function find_text to users named user_dba and user_in_house.
- ALTER FUNCTION on all external UDFs in the SYSLIB database to user_dba.
- CREATE FUNCTION on the database document_db to user_dba.
- EXECUTE FUNCTION on the overloaded function text_process to user sammy.
GRANT ALTER FUNCTION ON SPECIFIC FUNCTION find_text TO user_dba, user_in_house; GRANT ALTER FUNCTION ON syslib TO user_dba; GRANT CREATE FUNCTION ON document_db TO user_dba, user_in_house; GRANT EXECUTE ON FUNCTION text_process(CHAR,CHAR,INTEGER) TO sammy;