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.
The following types of privileges can be granted:
- Delete data from a specific table.
- Insert data into a specific table.
- Create a foreign key reference to the named table or to a subset of columns
from a table.
- Select data from a table, view, or a subset of columns in a table.
- Create a trigger on a table.
- Update data in a table or in a subset of columns in a table.
- Run a specified function or procedure.
- Use a sequence generator or a user-defined type.
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.
Syntax for sequence generators
GRANT USAGE ON SEQUENCE [ schemaName. ] SQL92Identifier 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.
The sequence name is composed of an optional schemaName and a
SQL92Identifier. If a schemaName is not provided, the current
schema is the default schema. If a qualified sequence name is specified, the
schema name cannot begin with SYS.
Syntax for user-defined types
GRANT USAGE ON TYPE [ schemaName. ] SQL92Identifier 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.
The type name is composed of an optional schemaName and a
SQL92Identifier. If a schemaName is not provided, the current
schema is the default schema. If a qualified type name is specified, the schema
name cannot begin with SYS.
Syntax for roles
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.
privilege-types
ALL PRIVILEGES |
privilege-list
privilege-list
table-privilege {, table-privilege }*
table-privilege
DELETE |
INSERT |
REFERENCES [column list] |
SELECT [column list] |
TRIGGER |
UPDATE [column list]
column list
( column-identifier {, column-identifier}* )
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 privilege-list.
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 column list 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.
grantees
{ 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.
routine-designator
{
function-name | procedure-name
}
Examples
To grant the SELECT
privilege on table
t to the authorization IDs
maria and
harry,
use the following syntax:
GRANT SELECT ON TABLE t TO maria,harry
To
grant the UPDATE and TRIGGER privileges on table
t to the authorization IDs
anita and
zhi,
use the following syntax:
GRANT UPDATE, TRIGGER ON TABLE t TO anita,zhi
To
grant the SELECT privilege on table
s.v to all users, use the following syntax:
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;
