GRANT

The GRANT statement grants privileges on a database object. You can grant privileges to functions, procedures, packages, tables, and user-defined types to a specific user ID or to PUBLIC. Additionally, you can allow the user ID to grant the same privileges to another user ID by specifying the WITH GRANT OPTION.

Different objects allow for different types of privileges. 

On function and procedures, you can grant the following privileges:

On packages, you can grant the following privileges:

On tables or views, you can grant the following privileges:

On user-defined types, you can grant the following privileges:

Example: Function or procedure

Grant the EXECUTE privilege on procedure CORPDATA.PROCA to PUBLIC.

GRANT EXECUTE
  ON PROCEDURE CORPDATA.PROCA
  TO PUBLIC 

For more information, see GRANT (FUNCTION OR PROCEDURE) in the SQL Reference topic in the Information CenterLink to Information center.

Example: Package

Grant the EXECUTE privilege on package CORPDATA.PKGA to PUBLIC.

GRANT EXECUTE
  ON PACKAGE CORPDATA.PKGA
  TO PUBLIC 

For more information, see GRANT (PACKAGE) in the SQL Reference topic in the Information CenterLink to Information center.

Example: Table or view

Given that you have authority, grant all the privileges that you have on the table DEPARTMENT to PUBLIC.

GRANT ALL
  ON CORPDATA.DEPARTMENT
  TO PUBLIC
Grant the appropriate privileges on the EMPLOYEE table so that ROANNA and EMMA can read it and insert new entries into it. Do not allow them to change or remove any existing entries.
GRANT SELECT, INSERT 
  ON EMPLOYEE
  TO ROANNA, EMMA
Grant column update privileges on PROJECT and MA_PROJ to FRED. Additionally, allow FRED to grant the same option to other users. Both columns specified in this GRANT statement must exist in both PROJECT and MA_PROJ.
GRANT UPDATE(PROJNO, PROJNAME).
  ON PROJECT, MA_PROJ
  TO FRED WITH GRANT OPTION

For more information, see GRANT (TABLE) in the SQL Reference topic in the Information CenterLink to Information center.

Example: User-defined type

Grant the USAGE privilege on distinct type SHOE_SIZE to user JONES. This GRANT statement does not give JONES the privilege to execute the cast functions that are associated with the distinct type SHOE_SIZE.

GRANT USAGE
  ON DISTINCT TYPE SHOE_SIZE
  TO JONES 

For more information, see GRANT (USER-DEFINED TYPE) in the SQL Reference topic in the Information CenterLink to Information center.