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, as described in these sections:

This topic also contains these sections that help explain the use of the GRANT statement:

Syntax for Schemas

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

schema-privilege

  DELETE
| INSERT
| MODIFY
| 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 Roles

GRANT roleName [ {, roleName }* ]  
   TO grantees
   [ [NOT] AS DEFAULT ]

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.

[NOT] AS DEFAULT

When you grant a role to a user, that role is, by default, applied to the user whenever s/he connects to the database. This is the behavior defined by the optional phrase AS DEFAULT.

If you do not want the role granted to the user by default, you must specify NOT AS DEFAULT; this means that the role will not automatically apply to sessions: you must use the   SET ROLE statement to apply a NOT AS DEFAULT role in a session.

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.

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 Sequences

GRANT USAGE
   ON SEQUENCE sequence-name
   TO grantees

sequence-name

An SQL_Identifier specifying the name of the sequence 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.

About Grantees

A grantee can be one or more specific users or groups, 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.

When using an LDAP Group name in a GRANT or REVOKE statement: if the group name contains characters other than alphanumerics or the underscore character (A-Z, a-z, 0-9, _), you must:

  • Enclose the group name in double quotes
  • Convert all alphabetic characters in the group name to uppercase.

For example, if you are granting rights to an LDAP Group with name This-is-my-LDAP-Group, you would use a statement like this:

   GRANT SELECT ON TABLE Salaries TO "THIS-IS-MY-LDAP-GROUP";

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.
MODIFY

Schema-level privilege that grants permission to modify the schema itself.

Permission to modify the schema does not imply granting of other permissions; use ALL PRIVILEGES to grant all permissions.

REFERENCES 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.
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

This section contains examples for:

  • Granting Privileges to Users (#UserPrivs)
  • Granting Roles to Users (#UserRoles)
  • Granting Privileges to Roles (#RolePrivs)

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

This grants the role to both users for the current session, and also sets the role as a default role whenever one of the users connects to the database. The as default behavior is applied by default, or you can specify it explicitly:

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

To grant the role to george only for the current session, use:

splice> GRANT purchases_reader_role TO george NOT AS DEFAULT;
0 rows inserted/updated/deleted

A More Extensive ROLE Example

Let’s set up our example. First we’ll use create 4 schemas and 4 roles, and we’ll grant all priveleges to each role on its respective schema:

splice> CREATE SCHEMA test_schema1;
0 rows inserted/updated/deleted
splice> CREATE ROLE test_role1;
0 rows inserted/updated/deleted
splice> GRANT ALL PRIVILEGES ON SCHEMA test_schema1 TO test_role1;
0 rows inserted/updated/deleted

splice> CREATE SCHEMA test_schema2;
0 rows inserted/updated/deleted
splice> CREATE ROLE test_schema2;
0 rows inserted/updated/deleted
splice> GRANT ALL PRIVILEGES ON SCHEMA test_schema2 TO test_role2;
0 rows inserted/updated/deleted

splice> CREATE SCHEMA test_schema3;
0 rows inserted/updated/deleted
splice> CREATE ROLE test_role3;
0 rows inserted/updated/deleted
splice> GRANT ALL PRIVILEGES ON SCHEMA test_schema3 TO test_role3;
0 rows inserted/updated/deleted

splice> CREATE SCHEMA test_schema4;
0 rows inserted/updated/deleted
splice> CREATE ROLE test_role4;
0 rows inserted/updated/deleted
splice> GRANT ALL PRIVILEGES ON SCHEMA test_schema4 TO test_role4;
0 rows inserted/updated/deleted

Next we’ll create two users so we can demonstrate assigning different roles to different users:

splice> CALL syscs_util.syscs_create_user('user1', 'user1pswd');
Statement executed;

splice> CALL syscs_util.syscs_create_user('user2', 'user2pswd');
Statement executed

Now we’ll grant the role test_role1 to all users (the public user), and GRANT specific roles to specific users:

splice> GRANT test_role1 TO public AS DEFAULT;
0 rows inserted/updated/deleted

splice> GRANT test_role2 TO user1 AS DEFAULT;
0 rows inserted/updated/deleted

splice> GRANT test_role3 TO user1;
0 rows inserted/updated/deleted

Now let’s CONNECT as user1 and check our role assignments:

splice> CONNECT 'jdbc:splice://localhost:1527/splicedb;user=user1;password=user1pswd' AS user1_connection;

splice> VALUES current_user
1
----------------------------------------------------------------------------------------------------------
USER1

splice> VALUES current_role;
1
----------------------------------------------------------------------------------------------------------
"TEST_ROLE2", "TEST_ROLE3", "TEST_ROLE1"
1 row selected

As you can see, when user1 connects, s/he is granted:

  • TEST_ROLE1 because it is now granted by default to all users (public).
  • TEST_ROLE2 and TEST_ROLE3 because they were granted to user1 as a default privilege upon connecting.

Now we’ll CONNECT as user2:

splice> CONNECT 'jdbc:splice://localhost:1527/splicedb;user=user2;password=user2pswd' as user2_connection;

splice> VALUES current_user
1
----------------------------------------------------------------------------------------------------------
USER2

splice> VALUES current_role;
1
----------------------------------------------------------------------------------------------------------
"TEST_ROLE1"
1 row selected

Note that user2 is connected with only one role, TEST_ROLE1 because that role has been GRANTed by default to all users (public) and no other roles have been granted to user2.

Unsetting the AS DEFAULT Role Setting

If you want to GRANT a role to a user just for the current session, you can use the NOT AS DEFAULT syntax.

You can use the same syntax to modify an existing role from DEFAULT to non-DEFAULT:

splice> GRANT test_role1 TO public not AS DEFAULT;
Statement executed;

As a result, new public connections will no longer be granted the privileges associated with test_role1.

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