Use the GRANT statement to give privileges to a specific user or role, or to all users, to perform actions on database objects. You can also use the GRANT statement to grant a role to a user, to PUBLIC, or to another role.
Before you issue a GRANT statement, check that the derby.database.sqlAuthorization property is set to true. The derby.database.sqlAuthorization property enables the SQL Authorization mode.
You can grant privileges on an object if you are the owner of the object or the database owner. See the CREATE statement for the database object that you want to grant privileges on for more information.
The syntax that you use for the GRANT statement depends on whether you are granting privileges to a schema object or granting a role.
For more information on using the GRANT statement, see "Using SQL standard authorization" in the Derby Developer's Guide.
GRANT privilegeType ON [ TABLE ] { tableName | viewName } TO grantees
GRANT EXECUTE ON FUNCTION functionName TO grantees
GRANT EXECUTE ON PROCEDURE procedureName TO grantees
GRANT USAGE ON SEQUENCE sequenceName TO grantees
In order to use a sequence generator, you must have the USAGE privilege on it. This privilege can be granted to users and to roles. See CREATE SEQUENCE statement for more information.
GRANT USAGE ON TYPE typeName TO grantees
In order to use a user-defined type, you must have the USAGE privilege on it. This privilege can be granted to users and to roles. See CREATE TYPE statement for more information.
GRANT USAGE ON DERBY AGGREGATE aggregateName TO grantees
In order to use a user-defined aggregate, you must have the USAGE privilege on it. This privilege can be granted to users and to roles. See CREATE DERBY AGGREGATE statement for more information.
GRANT roleName [ , roleName ]* TO grantees
Before you can grant a role to a user or to another role, you must create the role using the CREATE ROLE statement. Only the database owner can grant a role.
A role A contains another role B if role B is granted to role A, or is contained in a role C granted to role A. Privileges granted to a contained role are inherited by the containing roles. So the set of privileges identified by role A is the union of the privileges granted to role A and the privileges granted to any contained roles of role A.
DELETE | INSERT | REFERENCES [ columnList ] | SELECT [ columnList ] | TRIGGER | UPDATE [ columnList ]
Use the ALL PRIVILEGES privilege type to grant all of the privileges to the user or role for the specified table. You can also grant one or more table privileges by specifying a privilegeList.
Use the DELETE privilege type to grant permission to delete rows from the specified table.
Use the INSERT privilege type to grant permission to insert rows into the specified table.
Use the REFERENCES privilege type to grant permission to create a foreign key reference to the specified table. If a columnList is specified with the REFERENCES privilege, the permission is valid on only the foreign key reference to the specified columns.
Use the SELECT privilege type to grant permission to perform SELECT statements or selectExpressions on a table or view. If a column list is specified with the SELECT privilege, the permission is valid on only those columns. If no column list is specified, then the privilege is valid on all of the columns in the table.
For queries that do not select a specific column from the tables involved in a SELECT statement or selectExpression (for example, queries that use COUNT(*)), the user must have at least one column-level SELECT privilege or table-level SELECT privilege.
Use the TRIGGER privilege type to grant permission to create a trigger on the specified table.
Use the UPDATE privilege type to grant permission to use the UPDATE statement on the specified table. If a column list is specified, the permission applies only to the specified columns. To update a row using a statement that includes a WHERE clause, you must have the SELECT privilege on the columns in the row that you want to update.
{ authorizationIdentifier | roleName | PUBLIC }
[ , { authorizationIdentifier | roleName | PUBLIC } ]*
You can grant privileges or roles to specific users or roles or to all users. Use the keyword PUBLIC to specify all users. When PUBLIC is specified, the privileges or roles affect all current and future users. The privileges granted to PUBLIC and to individual users or roles are independent privileges. For example, a SELECT privilege on table t is granted to both PUBLIC and to the authorization ID harry. The SELECT privilege is later revoked from the authorization ID harry, but Harry can access the table t through the PUBLIC privilege.
Either the object owner or the database owner can grant privileges to a user or to a role. Only the database owner can grant a role to a user or to another role.
GRANT SELECT ON TABLE t TO maria,harry
GRANT UPDATE, TRIGGER ON TABLE t TO anita,zhi
GRANT SELECT ON TABLE s.v to PUBLIC
To grant the EXECUTE privilege on procedure p to the authorization ID george, use the following syntax:
GRANT EXECUTE ON PROCEDURE p TO george
To grant the role purchases_reader_role to the authorization IDs george and maria, use the following syntax:
GRANT purchases_reader_role TO george,maria
To grant the SELECT privilege on table t to the role purchases_reader_role, use the following syntax:
GRANT SELECT ON TABLE t TO purchases_reader_role
To grant the USAGE privilege on the sequence generator order_id to the role sales_role, use the following syntax:
GRANT USAGE ON SEQUENCE order_id TO sales_role;
To grant the USAGE privilege on the user-defined type price to the role finance_role, use the following syntax:
GRANT USAGE ON TYPE price TO finance_role;
To grant the USAGE privilege on the user-defined aggregate types.maxPrice to the role sales_role, use the following syntax:
GRANT USAGE ON DERBY AGGREGATE types.maxPrice TO sales_role;