GRANT Table Permission - Kinetica Docs

Table permissions, which can be applied to tables and views, can be granted directly to users or roles. Row-level security can be invoked for SELECT privilege by specifying a WHERE clause expression. Column-level security can be invoked for SELECT privilege by specifying a list of accessible columns and/or column security functions.

GRANT Table Permission Syntax

GRANT < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
ON <ALL TABLES [IN [SCHEMA] <schema reference>] | [TABLE | SCHEMA] <object reference> [<column list>]>
TO <user name | role name>
[WHERE <expression>]
[WITH GRANT OPTION]

GRANT Create Table Permission Syntax

GRANT CREATE [PRIVILEGES] ON TABLES
[IN [SCHEMA] <schema reference>]
TO <user name | role name> [WITH GRANT OPTION]

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 granted. 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 granting user can see, across all schemas
`*`	All existing schemas that the granting user can see, which will grant 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 applies across all schemas, both now and in the future. Wildcards are not permitted with the IN keyword.

Optional comma-separated list of specific table columns and/or column security functions applied to columns on which to grant access; see Column-Level Security for details.

WHERE <expression>

Optional filter expression on the table to which access is being granted, determining to which rows of the target table the grantee will be given access; see Row-Level Security for details.

WITH GRANT OPTION

Grant this permission with a grant option to grant it to others.

WITH GRANT OPTION is not allowed when also granting with row or column restrictions.

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.

Examples

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

GRANT All Permissions on Table to User Example

GRANT ALL PRIVILEGES ON TABLE example.network_config TO jsmith

To grant SELECT access on the network_config_history table to the analyst role:

GRANT Read on Table to Role Example

GRANT SELECT ON example.network_config_history TO analyst

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

GRANT All Permissions on Current Tables/Views in Schema to Role Example

GRANT ALL ON example.* TO developer

To grant SELECT access on all schemas to the auditor role:

GRANT Read on Schemas to Role Example

GRANT SELECT ON * TO auditor

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

GRANT Read on Schema to Role Example

GRANT SELECT ON app_schema TO app_user

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

GRANT All Permissions on All Schemas/Tables/Views to Role Example

GRANT ALL ON *.* TO dbadmin

Row-Level Security Examples

To grant SELECT access on an rx_order table for all orders placed since the year 2002:

GRANT Row-Level Access by Filter Example

GRANT SELECT ON example.rx_order
	TO zanalyst
	WHERE order_ts >= '2002-01-01'

To grant SELECT access on an rx_order table for only orders belonging to the current user:

GRANT Row-Level Access Tied to Current User Example

GRANT SELECT ON example.rx_order
	TO rx_user
	WHERE name = USER()

Column-Level Security Examples

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

obfuscated version of the orderer’s social security number
name of the prescription ordered
order date/time

GRANT Obfuscated Column-Level Access Example

GRANT SELECT ON example.rx_order(HASH(ssn), rx_name, order_ts) TO zanalyst

To grant 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 orderer
name of the prescription ordered
order date/time

GRANT Masked Column-Level Access Example

GRANT SELECT ON example.rx_order(MASK(ssn, 1, 6), name, rx_name, order_ts) TO rx_user

​Parameters

​Examples

​Row-Level Security Examples

​Column-Level Security Examples

Parameters

Examples

Row-Level Security Examples

Column-Level Security Examples