REVOKE

Use the REVOKE statement to remove privileges from a specific user or role, or from all users, to perform actions on database objects. You can also use the REVOKE statement to revoke a role from a user, from PUBLIC, or from another role.

The syntax that you use for the REVOKE statement depends on whether you are revoking privileges to a schema object or revoking a role.

Syntax for SCHEMA

REVOKE privilege-type
   ON [ SCHEMA ] schema
   FROM grantees

privilege-type

   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 schemafor which you are revoking access.

grantees

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

Syntax for Tables

REVOKE privilege-type
   ON [ TABLE ] table-Name
   FROM grantees

privilege-type

  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 for which you are revoking access.

view-Name

The name of the view for which you are revoking access.

grantees

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

Syntax for Routines

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

function-name |  procedure-name

The name of the function or procedure for which you are revoking access.

grantees

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

RESTRICT

You must use this clause when revoking access for routines.

The RESTRICT clause specifies that the EXECUTE privilege cannot be revoked if the specified routine is used in a view, trigger, or constraint, and the privilege is being revoked from the owner of the view, trigger, or constraint.

Syntax for User-defined types

REVOKE USAGE
   ON TYPE SQL Identifier
   FROM grantees RESTRICT

[schema-name.] SQL Identifier

The user-defined type (UDT) 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) for whom you are revoking access. See the About Grantees section below for more information.

RESTRICT

You must use this clause when revoking access for user-defined types.

The RESTRICT clause specifies that the EXECUTE privilege cannot be revoked if the specified UDT is used in a view, trigger, or constraint, and the privilege is being revoked from the owner of the view, trigger, or constraint.

Syntax for Roles

REVOKE roleName
    { roleName }*
   FROM grantees

roleName

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

grantees

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

Only the database owner can revoke a role.

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 revoke 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.
DELETE To revoke permission to delete rows from the specified table.
INSERT To revoke permission to insert rows into the specified table.
REFERENCES To revoke 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 revoke 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 revoke permission to create a trigger on the specified table.
UPDATE To revoke 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 revoked:

  • 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 routine (function or procedure).

  • Use a user-defined type.

Before you issue a REVOKE statement, check that the derby.database.sqlAuthorization property is set to true. The derby.database.sqlAuthorization property enables the SQL Authorization mode.

You can revoke 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 revoke privileges on for more information.

You can revoke privileges for an object if you are the owner of the object or the database owner.

Prepared statements and open result sets

Checking for privileges happens at statement execution time, so prepared statements are still usable after a revoke action. If sufficient privileges are still available for the session, prepared statements will be executed, and for queries, a result set will be returned.

Once a result set has been returned to the application (by executing a prepared statement or by direct execution), it will remain accessible even if privileges or roles are revoked in a way that would cause another execution of the same statement to fail.

Cascading object dependencies

For views, triggers, and constraints, if the privilege on which the object depends on is revoked, the object is automatically dropped. Splice Machine does not try to determine if you have other privileges that can replace the privileges that are being revoked.

Limitations

The following limitations apply to the REVOKE statement:

Table-level privileges

All of the table-level privilege types for a specified grantee and table ID are stored in one row in the SYSTABLEPERMS system table. For example, when user2 is granted the SELECT and DELETE privileges on table user1.t1, a row is added to the SYSTABLEPERMS table. The GRANTEE field contains user2 and the TABLEID contains user1.t1. The SELECTPRIV and DELETEPRIV fields are set to Y. The remaining privilege type fields are set to N.

When a grantee creates an object that relies on one of the privilege types, Splice Machine engine tracks the dependency of the object on the specific row in the SYSTABLEPERMS table. For example, user2 creates the view v1 by using the statement SELECT * FROM user1.t1, the dependency manager tracks the dependency of view v1 on the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1).

The dependency manager knows only that the view is dependent on a privilege type in that specific row, but does not track exactly which privilege type the view is dependent on.

When a REVOKE statement for a table-level privilege is issued for a grantee and table ID, all of the objects that are dependent on the grantee and table ID are dropped. For example, if user1 revokes the DELETE privilege on table t1 from user2, the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1) is modified by the REVOKE statement. The dependency manager sends a revoke invalidation message to the view user2.v1 and the view is dropped even though the view is not dependent on the DELETE privilege for GRANTEE(user2), TABLEID(user1.t1).

Column-level privileges

Only one type of privilege for a specified grantee and table ID are stored in one row in the SYSCOLPERMS system table. For example, when user2 is granted the SELECT privilege on table user1.t1 for columns c12 and c13, a row is added to the SYSCOLPERMS. The GRANTEE field contains user2, the TABLEID contains user1.t1, the TYPE field contains S, and the COLUMNS field contains c12, c13.

When a grantee creates an object that relies on the privilege type and the subset of columns in a table ID, Splice Machine engine tracks the dependency of the object on the specific row in the SYSCOLPERMS table. For example, user2 creates the view v1 by using the statement SELECT c11 FROM user1.t1, the dependency manager tracks the dependency of view v1 on the row in SYSCOLPERMS for GRANTEE(user2), TABLEID(user1.t1), TYPE(s). The dependency manager knows that the view is dependent on the SELECT privilege type, but does not track exactly which columns the view is dependent on.

When a REVOKE statement for a column-level privilege is issued for a grantee, table ID, and type, all of the objects that are dependent on the grantee, table ID, and type are dropped. For example, if user1 revokes the SELECT privilege on column c12 on table user1.t1 from user2, the row in SYSCOLPERMS for GRANTEE(user2), TABLEID( ser1.t1), TYPE(s) is modified by the REVOKE statement. The dependency manager sends a revoke invalidation message to the view user2.v1 and the view is dropped even though the view is not dependent on the column c12 for GRANTEE(user2), TABLEID(user1.t1), TYPE(s).

Roles

Splice Machine tracks any dependencies on the definer’s current role for views and constraints, constraints, and triggers. If privileges were obtainable only via the current role when the object in question was defined, that object depends on the current role. The object will be dropped if the role is revoked from the defining user or from PUBLIC, as the case may be.

Also, if a contained role of the current role in such cases is revoked, dependent objects will be dropped. Note that dropping may be too pessimistic. This is because Splice Machine does not currently make an attempt to recheck if the necessary privileges are still available in such cases.

Revoke Examples

Revoking User Privileges

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

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

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

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

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

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

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

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

To revoke the EXECUTE privilege on procedure ComputeValue from the authorization ID george, use the following syntax:

splice> REVOKE EXECUTE ON PROCEDURE ComputeValue FROM george;
0 rows inserted/updated/deleted

Revoking User Roles

To revoke the role ` purchases_reader_role from the authorization IDs george and maria`, use the following syntax:

splice> REVOKE purchases_reader_role FROM george,maria;
0 rows inserted/updated/deleted

Revoking Role Privileges

To revoke the SELECT privilege on schema SpliceBBall from the role purchases_reader_role, use the following syntax:

splice> REVOKE SELECT ON SCHEMA SpliceBBall FROM purchases_reader_role;
0 rows inserted/updated/deleted

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

splice> REVOKE SELECT ON TABLE t FROM purchases_reader_role;
0 rows inserted/updated/deleted

See Also