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:
- ALL - Grants all of the allowed
privileges to the specified user ID or public. This is not the same as the
system authority *ALL.
-
ALTER - Grants the privilege to
use the COMMENT ON statement.
-
EXECUTE - Grants the privilege to
execute the function or procedure.
On packages, you can grant the following privileges:
- ALL - Grants all of the allowed
privileges to the specified user ID or public. This is not the same as the
system authority *ALL.
-
ALTER - Grants the
privilege to use the COMMENT
ON or LABEL ON statements.
-
EXECUTE - Grants the privilege to
execute statements in the package.
On tables or views, you can grant the following privileges:
- ALL - Grants all of the
allowed privileges to the specified user ID or public. This is not the same
as the i5/OS system authority *ALL.
-
ALTER - Grants the
privilege to use the ALTER TABLE, COMMENT ON, or LABEL ON statements.
-
DELETE - Grants the
privilege to use
the DELETE statement. DELETE cannot be granted to read-only views.
-
INDEX - Grants the
privilege to use the CREATE INDEX
statement. This privilege cannot be granted on a view.
-
INSERT - Grants the
privilege to use the INSERT
statement. INSERT cannot be granted to a view that does not allow inserts.
-
REFERENCES - Grants the
privilege to add a referential constraint in which the table is a parent. You
can specify a list of columns that are allowed to be used in a referential constraint with this GRANT
statement. If you do not specify a list of columns, all of the
columns are included. You can grant this privilege to a view, but it will
not be used.
-
SELECT - Grants the
privilege to use the SELECT or CREATE VIEW statements.
-
UPDATE - Grants the
privilege to use the UPDATE statement.
You can specify a list of columns that are allowed to be updated with this
GRANT statement. If you do not specify a list of columns, all
of the columns are included. UPDATE cannot be granted to a view that does not allow
updates.
On user-defined types, you can grant the following privileges:
- ALL - Grants all of the
allowed privileges to the specified user ID or public. This is not the same
as the system authority *ALL.
-
ALTER - Grants the privilege to
use the COMMENT ON statement.
-
USAGE - Grants the
privilege to use the type in tables, functions, procedures, or as the source type in
a CREATE DISTINCT TYPE statement.
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 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 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 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 Center
.