REVOKE Table Permission - Kinetica Docs

Table permissions, which can be applied to tables and views, can be revoked from users or roles. Row-level security can be revoked for SELECT privilege only by revoking SELECT privilege from the entire table.

Access to individual rows cannot be revoked; instead, full SELECT access should be revoked, followed by a grant of access to the appropriate rows.

Column-level security can be revoked for SELECT privilege by specifying a list of columns for which access will be revoked, regardless of any security functions that may have been applied to the column when granting access (HASH, MASK).

REVOKE Table Permission Syntax

REVOKE < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
ON <ALL TABLES [IN [SCHEMA] <schema reference>] | [TABLE | SCHEMA] <object reference> [<column list>]>
FROM <user name | role name>

REVOKE Create Table Permission Syntax

REVOKE CREATE [PRIVILEGES] ON TABLES
[IN [SCHEMA] <schema reference>]
FROM <user name | role name>

Parameters

PRIVILEGES

Optional keyword for SQL-92 compatibility

TABLE / SCHEMA

Optional SQL-92 keywords that disambiguate the object reference: TABLE selects a table/view in the user’s default schema (when the schema is omitted), while SCHEMA selects a schema.

The name of the table/view and/or schema to which access is being revoked; the following forms are valid:

Form	Selected Objects
<schema>.<object>	Fully-qualified table or view name in the specified schema
`.`	All existing tables & views that the revoking user can see, across all schemas
`*`	All schemas the revoking user can see, removing access to all tables and views in those schemas now and in the future
<schema>	All tables and views in the specified schema now and in the future
<object>	Table or view in the default schema. If a schema exists with the same name, it will be used. To avoid this, specify the fully-qualified object name.

The schema the permission applies to (with the ALL or CREATE forms). If the IN [SCHEMA] clause is omitted, the permission is removed from all schemas, both now and in the future. Wildcards are not permitted with the IN keyword.

Optional comma-separated list of specific table columns to which access is being revoked; see Column-Level Security for details

The ALL permission corresponds to the native table_admin permission, which gives full read/write access as well as the additional permission either to ALTER and DROP the specified table or to CREATE, ALTER, & DROP all tables in the specified schema.

Examples

For example, to revoke full access on the network_config table from jsmith:

REVOKE All Permissions on Table from User Example

REVOKE ALL PRIVILEGES ON TABLE example.network_config FROM jsmith

To revoke SELECT access on the network_config_history table from the analyst role:

REVOKE Read on Table from Role Example

REVOKE SELECT ON example.network_config_history FROM analyst

To revoke full access on each of the tables & views within the example schema from the developer role:

REVOKE All Permissions on Current Tables/Views in Schema from Role Example

REVOKE ALL ON example.* FROM developer

To revoke SELECT access on all schemas from the auditor role:

REVOKE Read on Schemas from Role Example

REVOKE SELECT ON * FROM auditor

To revoke SELECT access on all current and future tables & views within the app_schema schema from the app_user role:

REVOKE Read on Schema from Role Example

REVOKE SELECT ON app_schema FROM app_user

To revoke full access on each of the schemas, tables, & views in the database individually from the dbadmin role:

REVOKE All Permissions on All Schemas/Tables/Views from Role Example

REVOKE ALL ON *.* FROM dbadmin

Row-Level Security Examples

To revoke SELECT access on an rx_order table for all orders placed since the year 2002 (as was granted in the row-level grant example), revoke all SELECT access:

REVOKE Row-Level Access Example

REVOKE SELECT ON example.rx_order FROM zanalyst

Column-Level Security Examples

To revoke SELECT access on the following columns of an rx_order table:

orderer’s social security number

REVOKE Obfuscated Column-Level Access Example

REVOKE SELECT ON example.rx_order(ssn) FROM zanalyst

To revoke SELECT access on the following columns of an rx_order table:

masked version of the orderer’s social security number (last 4 digits)
name of the prescription ordered

REVOKE Masked Column-Level Access Example

REVOKE SELECT ON example.rx_order(ssn, rx_name) FROM rx_user

​Parameters

​Examples

​Row-Level Security Examples

​Column-Level Security Examples

Parameters

Examples

Row-Level Security Examples

Column-Level Security Examples