GRANT

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 syntax that you use for the GRANT statement depends on whether you are granting privileges to a schema object or granting a role.

Only database and schema owners can use the CREATE TABLE statement, which means that table creation privileges cannot be granted to others, even with GRANT ALL PRIVILEGES.

Syntax for Schemas

GRANT ALL PRIVILEGES | schema-privilege {, schema-privilege }*
   ON [SCHEMA] schema-Name
   TO grantees

schema-privilege

  DELETE
| INSERT
| REFERENCES [( column-identifier {, column-identifier}* )]
| SELECT [( column-identifier {, column-identifier}* )]
| TRIGGER
| UPDATE [( column-identifier {, column-identifier}* )]

See the Privilege Types section below for more information.

Column-level privileges are available only with a Splice Machine Enterprise license.

You cannot grant or revoke privileges at the column-identifier level with the Community version of Splice Machine.

To obtain a license for the Splice Machine Enterprise Edition, please Contact Splice Machine Sales today.

schema-Name

The name of the schema to which you are granting access.

grantees

The user(s) or role(s) to whom you are granting access. See the About Grantees section below for more information.

NOTES:

  • When you drop a schema from your database, all privileges associated with the schema are removed.

  • Table-level privileges override schema-level privileges.

Syntax for Tables

GRANT ALL PRIVILEGES | table-privilege {, table-privilege }*   ON [TABLE] { tableName }
   TO grantees

table-privilege

  DELETE
| INSERT
| REFERENCES [( column-identifier {, column-identifier}* )]
| SELECT [( column-identifier {, column-identifier}* )]
| TRIGGER
| UPDATE [( column-identifier {, column-identifier}* )]

See the Privilege Types section below for more information.

Column-level privileges are available only with a Splice Machine Enterprise license.

You cannot grant or revoke privileges at the column-identifier level with the Community version of Splice Machine.

To obtain a license for the Splice Machine Enterprise Edition, please Contact Splice Machine Sales today.

table-Name

The name of the table to which you are granting access.

view-Name

The name of the view to which you are granting access.

schema-Name

The name of the schema to which you are granting access.

grantees

The user(s) or role(s) to whom you are granting access. See the About Grantees section below for more information.

NOTES:

  • When you drop a table from your database, all privileges associated with the table are removed.
  • Table-level privileges override schema-level privileges.

Syntax for Routines

GRANT EXECUTE
   ON { FUNCTION | PROCEDURE } {function-name | procedure-name}
   TO grantees

function-name |  procedure-name

The name of the function or procedure to which you are granting access.

grantees

The user(s) or role(s) to whom you are granting access. See the About Grantees section below for more information.

Syntax for User-defined Types

GRANT USAGE
   SQL Identifier
   TO grantees

[schema-name.] SQL Identifier

The type name is composed of an optional schemaName and a SQL Identifier. If a schemaName is not provided, the current schema is the default schema. If a qualified UDT name is specified, the schema name cannot begin with SYS.

grantees

The user(s) or role(s) to whom you are granting access. See the About Grantees section below for more information.

Syntax for Roles

GRANT roleName
    { roleName }*
   TO grantees

roleName

The name to the role(s) to which you are granting access.

grantees

The user(s) or role(s) to whom you are granting access. See the About Grantees section below for more information.

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.

About Grantees

A grantee can be one or more specific users, one or more specific roles, or all users (PUBLIC). 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.

Here’s the syntax:

{      roleName | PUBLIC }
  [, { roleName | PUBLIC } ] *

AuthorizationIdentifier

An expression.

roleName

The name of the role.

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.

PUBLIC

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. If the SELECT privilege is later revoked from the authorization ID harry, Harry will still be able to access the table t through the PUBLIC privilege.

Privilege Types

Privilege Type Usage
ALL PRIVILEGES

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.

Only database and schema owners can use the CREATE TABLE statement, which means that table creation privileges cannot be granted to others, even with GRANT ALL PRIVILEGES.

DELETE To grant permission to delete rows from the specified table.
INSERT To grant permission to insert rows into the specified table.
REFERENCES To grant permission to create a foreign key reference to the specified table. If a column list is pecified with the REFERENCES privilege, the permission is valid on only the foreign key reference to the specified columns.
SELECT To grant permission to perform 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.

TRIGGER To grant permission to create a trigger on the specified table.
UPDATE To grant permission to use the WHERE clause, you must have the SELECT privilege on the columns in the row that you want to update.

Usage Notes

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 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 documentation for the CREATE statements for more information.

Examples

Granting Privileges to Users

To grant the SELECT privilege on the schema SpliceBBall to the authorization IDs Bill and Joan, use the following syntax:

splice> GRANT SELECT ON SCHEMA SpliceBBall TO Bill, Joan;
0 rows inserted/updated/deleted

To grant the SELECT privilege on table Salaries to the authorization IDs Bill and Joan, use the following syntax:

splice> GRANT SELECT ON TABLE Salaries TO Bill, Joan;
0 rows inserted/updated/deleted

To grant the UPDATE and TRIGGER privileges on table Salaries to the authorization IDs Joe and Anita, use the following syntax:

splice> GRANT UPDATE, TRIGGER ON TABLE Salaries TO Joe, Anita;
0 rows inserted/updated/deleted

To grant the SELECT privilege on table Hitting in the Baseball_stats schema to all users, use the following syntax:

splice> GRANT SELECT ON TABLE Baseball_Stats.Hitting to PUBLIC;
0 rows inserted/updated/deleted

To grant the EXECUTE privilege on procedure ComputeValue to the authorization ID george, use the following syntax:

splice> GRANT EXECUTE ON PROCEDURE ComputeValue TO george;
0 rows inserted/updated/deleted

Granting Roles to Users

To grant the role ` purchases_reader_role to the authorization IDs george and maria`, use the following syntax:

splice> GRANT purchases_reader_role TO george,maria;
0 rows inserted/updated/deleted

Granting Privileges to Roles

To grant the SELECT privilege on schema SpliceBBall to the role purchases_reader_role, use the following syntax:

splice> GRANT SELECT ON SCHEMA SpliceBBall TO purchases_reader_role;
0 rows inserted/updated/deleted

To grant the SELECT privilege on table t to the role purchases_reader_role, use the following syntax:

splice> GRANT SELECT ON TABLE t TO purchases_reader_role;
0 rows inserted/updated/deleted

See Also