Security - Kinetica Docs

Kinetica provides basic table-level role-based access control for users. It also allows global read/write and administrative access to be granted. For details about Kinetica security, see Security Concepts.

A limited set of user/role information can be extracted via function calls. See User/Security Functions for details.

Security features accessible via SQL include:

User Management
Role Management
Privilege Management
Impersonation (Execute As…) - enables execution of commands as another user

User Management

The ability to manage user accounts is available through SQL, using the following commands:

See Users for details about users within Kinetica.

CREATE USER

Users can be added to the system and assigned permissions either directly or via roles. Optionally, the user can be associated with a resource group and/or be assigned a default schema.

CREATE USER Syntax

CREATE USER <user name>
[ < [WITH] PASSWORD [=] | IDENTIFIED BY [PASSWORD] > '<user password>' ]
[ [WITH] RESOURCE GROUP [=] <group name> ]
[ [WITH] DEFAULT SCHEMA [=] <schema name> ]
[ [WITH] ACTIVATED [=] <TRUE | FALSE> ]

The password needs to be single-quoted and must not contain single quotes.

Examples

Two of the ways to create a new internal user with the user ID of jsmith and a password of secret are:

CREATE USER Example

CREATE USER jsmith IDENTIFIED BY 'secret'
CREATE USER jsmith WITH PASSWORD 'secret'

To create an internal user with the user ID of jsmith, a password of secret, and assigning the memory_over_execution resource group:

CREATE USER with Resource Group Example

CREATE USER jsmith
IDENTIFIED BY 'secret'
WITH RESOURCE GROUP memory_over_execution

To create a user with an existing external LDAP user, the user name should match the LDAP user name and be prepended with the @ symbol; no password is supplied, as the user will be externally authenticated:

CREATE USER (External) Example

CREATE USER "@jsmith"

ALTER USER

Any of the following facets of a user can be altered, either individually or as a group:

ALTER USER Syntax

ALTER USER <user name>
SET
    [ PASSWORD [=] '<user password>' ]
    [ RESOURCE GROUP [=] <group name | DEFAULT> ]
    [ DEFAULT SCHEMA [=] <schema name | DEFAULT> ]
    [ ACTIVATED [=] <TRUE | FALSE> ]

The password needs to be single-quoted and must not contain single quotes.

Examples

To alter a user with the user ID of jsmith, assigning a password of new_secret, and the memory_over_execution resource group:

ALTER USER with Resource Group Example

ALTER USER jsmith
SET
    PASSWORD = 'new_secret'
    RESOURCE GROUP memory_over_execution

To unassign the memory_over_execution resource_group from the jsmith user:

ALTER USER Removing Resource Group Example

ALTER USER jsmith
SET
    RESOURCE GROUP DEFAULT

DROP USER

Any user, other than the default users, can be removed from the system. Note that any database objects created by a user will remain when the user is removed.

DROP USER Syntax

DROP USER <user name>

Examples

To drop an internal user jsmith:

DROP USER (Internal) Example

DROP USER jsmith

To drop an external LDAP user jsmith:

DROP USER (External) Example

DROP USER "@jsmith"

SHOW SECURITY FOR User

For any one or more (or all) users in the system, the following can be listed:

In addition, permissions, roles, & resource groups will be listed recursively for the roles the specified users have and the roles those roles have, etc. This allows the full set of permissions for a given user, both direct & inherited, to be viewed with one command.

SHOW SECURITY FOR User Syntax

SHOW SECURITY FOR <user name>,...

Examples

To show the permissions, roles, resource group, and default schema for user jsmith:

SHOW SECURITY FOR User Example

SHOW SECURITY FOR jsmith

Role Management

The ability to manage roles is available through SQL, using the following commands:

CREATE ROLE
ALTER ROLE
DROP ROLE
SHOW SECURITY FOR Role

See Roles for details about roles within Kinetica.

CREATE ROLE

A new role can be created as a container for permissions or other roles, though both of those must be granted to the role after its creation. Optionally, the role can be associated with a resource group.

CREATE ROLE Syntax

CREATE ROLE <role name>
[ [WITH] RESOURCE GROUP [=] <group name> ]

Examples

To create an analyst role:

CREATE ROLE Example

CREATE ROLE analyst

To create an executive role, assigning it the execution_over_memory resource group:

CREATE ROLE with Resource Group Example

CREATE ROLE executive
WITH RESOURCE GROUP execution_over_memory

ALTER ROLE

A role can have its associated resource group modified.

ALTER ROLE Syntax

ALTER ROLE <role name>
SET RESOURCE GROUP [=] <group name | DEFAULT>

Examples

To assign the memory_over_execution resource group to the analyst role:

ALTER ROLE Example

ALTER ROLE analyst
SET RESOURCE GROUP memory_over_execution

To unassign the resource group from the analyst role:

ALTER ROLE Removing Resource Group Example

ALTER ROLE analyst
SET RESOURCE GROUP DEFAULT

DROP ROLE

Dropping a role will remove the associated permissions & roles granted through the role to all users with the role. Users & roles granted the same permissions either directly or via other roles will retain those permissions. Any role, other than the default roles, can be removed from the system.

DROP ROLE Syntax

DROP ROLE <role name>

Examples

To drop the analyst role:

DROP ROLE Example

DROP ROLE analyst

SHOW SECURITY FOR Role

For any one or more (or all) roles in the system, the following can be listed:

In addition, these attributes will be listed recursively for the roles the specified roles have and the roles those roles have, etc. This allows the full set of permissions for a given role, both direct & inherited, to be viewed with one command.

SHOW SECURITY FOR Role Syntax

SHOW SECURITY FOR <role name>,...

Examples

To show the permissions, roles, and resource group for the analyst role:

SHOW SECURITY FOR Role Example

SHOW SECURITY FOR analyst

Privilege Management

The ability to manage user & role privileges is available through SQL, using the following commands:

Roles (GRANT | REVOKE)
System Permissions (GRANT | REVOKE)
Schema Permissions (GRANT | REVOKE)
Table Permissions (GRANT | REVOKE)
Credential Permissions (GRANT | REVOKE)
Data Source Permissions (GRANT | REVOKE)
Data Sink Permissions (GRANT | REVOKE)
Directory Permissions (GRANT | REVOKE)
Function Permissions (GRANT | REVOKE)
Graph Permissions (GRANT | REVOKE)
Procedure Permissions (GRANT | REVOKE)
SQL-GPT Context Permissions (GRANT | REVOKE)
Stream Permissions (GRANT | REVOKE)
SHOW SECURITY
CHECK

See Users for details about user accounts within Kinetica.

GRANT Role

Roles can be granted directly to users or other roles.

GRANT Role Syntax

GRANT <role name> TO <user name | role name>

Examples

To grant a role allowing access to analyst tables to the analyst role, and then grant that analyst role to user jsmith:

GRANT ROLE Example

GRANT analyst_table_access TO analyst
GRANT analyst TO jsmith

GRANT SYSTEM Permission

System permissions can be granted directly to users or roles.

System Administration

GRANT System Administrative Permission Syntax

GRANT SYSTEM ADMIN TO <user name | role name>

Permission	Description
`SYSTEM ADMIN`	System administrator permission

System Management

GRANT System Management Permission Syntax

GRANT SYSTEM < CREATE | MONITOR | READ | WRITE > TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`SYSTEM CREATE`	System create permission
`SYSTEM MONITOR`	System monitor permission
`SYSTEM READ`	System read permission
`SYSTEM WRITE`	System write permission
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

User Administration

GRANT User Administrative Permission Syntax

GRANT USER ADMIN TO <user name | role name>

Permission	Description
`USER ADMIN`	User administrator permission

Create Directory

GRANT KiFS Directory Create Permission Syntax

GRANT CREATE DIRECTORY TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`CREATE DIRECTORY`	Create directory permission
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Create UDF/UDTF

GRANT UDF Create Permission Syntax

GRANT CREATE FUNCTION TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`CREATE FUNCTION`	Create UDF/UDTF permission
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Examples

To grant system administrator permission to jsmith:

GRANT SYSTEM ADMIN Example

GRANT SYSTEM ADMIN TO jsmith

To grant read access to all tables to the auditor role:

GRANT SYSTEM READ Example

GRANT SYSTEM READ TO auditor

To grant user administrator permission to jsmith:

GRANT USER ADMIN Example

GRANT USER ADMIN TO jsmith

GRANT Schema Permission

Schema permissions, which allow management of schemas and their contained objects can be granted directly to users or roles.

GRANT Schema Permission Syntax

GRANT < SELECT | INSERT | UPDATE | DELETE | CREATE <object type> | ALL > [PRIVILEGES]
ON [SCHEMA] <schema reference>
TO <user name | role name>
[WITH GRANT OPTION]

Parameters

PRIVILEGES

Optional keyword for SQL-92 compatibility.

SCHEMA

Optional keyword to avoid ambiguity between a grant to a schema and a grant to a table/view in the user’s default schema.

Either the name of the schema to which access is being granted or a wildcard expression signifying the schema(s) and table(s)/views(s) to which access is being granted.

Wildcard Form	Selected Objects
`*`	All schemas currently in the database
`.`	All schemas, tables, & views currently in the database

For granting CREATE privilege, the object type to give permission to create in the given schema.Supported object types include:

Target Type	Description
`CONTEXT`	SQL-GPT context object
`CREDENTIAL`	Credential object
`DATA SINK`	External data sink
`DATA SOURCE`	External data source
`GRAPH`	Graph
`PROCEDURE`	SQL stored procedure
`STREAM`	Stream
`TABLE`	Table (or view)

WITH GRANT OPTION

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

The ALL permission corresponds to the native table_admin permission, which gives full read/write access as well as the additional permission to CREATE, ALTER, & DROP any table or view in the specified schema(s).

Examples

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

GRANT Table Permission

Table permissions, which can be applied to tables and views, can be granted directly to users or roles.

For granting the ability to create tables, see GRANT Schema Permission.

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 [TABLE] <object reference> [<column list>]]
TO <user name | role name>
[WHERE <expression>]
[WITH GRANT OPTION]

Parameters

PRIVILEGES

Optional keyword for SQL-92 compatibility.

Optional object specifier for granting permission to a specific object or set of objects. If not specified, then the grant will be for all tables and views in all schemas now and in the future

TABLE

Optional keyword for SQL-92 compatibility, and to avoid ambiguity between a grant to a table/view in the user’s default schema (when <schema name> is omitted) and a grant to 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.

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

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

GRANT Credential Permission

Credential permissions, which allow management of credentials, can be granted directly to users or roles.

For granting the ability to create credentials, see GRANT Schema Permission.

GRANT Credential Permission Syntax

GRANT <READ | ADMIN> ON CREDENTIAL <credential name> TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`ADMIN`	Modify or drop the given credential (includes `READ` access).
`READ`	Use or view the configuration of the given credential.
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Examples

To grant read access on the credential named auser_azure_active_dir_creds to the auser user:

GRANT Credential Use Permission Example

GRANT READ ON CREDENTIAL auser_azure_active_dir_creds TO auser

To grant administrative access on the credential to the auser user:

GRANT Credential Administrative Permission Example

GRANT ADMIN ON CREDENTIAL auser_azure_active_dir_creds TO auser

GRANT Data Source Permission

Data source permissions, which allow management of data sources, can be granted directly to users or roles.

For granting the ability to create data sources, see GRANT Schema Permission.

GRANT Data Source Permission Syntax

GRANT <CONNECT | ADMIN> ON DATA SOURCE <data source name> TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`ADMIN`	Modify or drop the given data source (includes `CONNECT` access).
`CONNECT`	Connect to, load data into, or view the configuration of the given data source.
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Examples

To grant connect access on the data source named kin_ds to the auser user:

GRANT Data Source Use Permission Example

GRANT CONNECT ON DATA SOURCE kin_ds TO auser

To grant administrative access on the data source named kin_ds_anon to the auser user:

GRANT Data Source Administrative Permission Example

GRANT ADMIN ON DATA SOURCE kin_ds_anon TO auser

GRANT Data Sink Permission

Data sink permissions, which allow management of data sinks, can be granted directly to users or roles.

For granting the ability to create data sinks, see GRANT Schema Permission.

GRANT Data Sink Permission Syntax

GRANT <CONNECT | ADMIN> ON DATA SINK <data sink name> TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`ADMIN`	Modify or drop the given data sink (includes `CONNECT` access).
`CONNECT`	Connect to, load data into, or view the configuration of the given data sink.
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Examples

To grant connect & load access on the data sink named kin_dsink to the auser user:

GRANT Data Sink Use Permission Example

GRANT CONNECT ON DATA SINK kin_dsink TO auser

To grant administrative access on the data sink named kin_dsink_http to the auser user:

GRANT Data Sink Administrative Permission Example

GRANT ADMIN ON DATA SINK kin_dsink_http TO auser

GRANT Directory Permission

KiFS directory permissions, which allow management of files within KiFS directories, can be granted directly to users or roles.

For granting the ability to create directories, see GRANT SYSTEM Permission.

GRANT Directory Permission Syntax

GRANT <READ | WRITE> ON DIRECTORY <directory name> TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`READ`	List, download, or load data from files within the given directory.
`WRITE`	Upload files to or delete files from the given directory (includes `READ` access).
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Examples

To grant read access on a directory named kdata to the kuser user:

GRANT Directory Read Permission Example

GRANT READ ON DIRECTORY 'kdata' TO kuser

To grant write access on a directory named kdata to the kuser user:

GRANT Directory Write Permission Example

GRANT WRITE ON DIRECTORY 'kdata' TO kuser

GRANT Function (UDF/UDTF) Permission

Function permissions, which allow management of UDFs/UDTFs, can be granted directly to users or roles.

For granting the ability to create UDFs/UDTFs, see GRANT SYSTEM Permission.

GRANT FUNCTION (UDF) Permission Syntax

GRANT <EXECUTE | ADMIN> ON FUNCTION < <function name> | * > TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`ADMIN`	Drop the given UDF, and display full configuration about it (includes `EXECUTE` access). When `` is used instead of function name*, gives permission to manage Python UDF function environments.
`EXECUTE`	Run and terminate the given UDF, and display limited configuration about it.
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Use * instead of function name to grant the specified permission to all existing & future UDFs.

Examples

To grant execute on the udf_sos_py_proc UDF to udf_user:

GRANT Function Execute Permission Example

GRANT EXECUTE ON FUNCTION UDF_SOS_PY_PROC TO udf_user

To grant administrative access on every existing & future UDF, as well as the ability to manage Python UDF environments, to udf_user:

GRANT Function Administrative Permission Example

GRANT ADMIN ON FUNCTION * TO udf_user

GRANT Graph Permission

Graph permissions, which allow management of graphs, can be granted directly to users or roles.

For granting the ability to create graphs, see GRANT Schema Permission.

GRANT Graph Permission Syntax

GRANT <READ | WRITE | ADMIN> ON GRAPH <graph name> TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`ADMIN`	Drop the given graph (includes `WRITE` access).
`READ`	Query, match, solve, & show the given graph.
`WRITE`	Modify the given graph (includes `READ` access).
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Examples

To grant query, match, solve, & show access on the graph named big_cities_graph to geo_user:

GRANT Graph Permission Example

GRANT READ ON GRAPH big_cities_graph TO geo_user

GRANT Procedure Permission

Procedure permissions, which allow execution of SQL procedures, can be granted directly to users or roles.

For granting the ability to create SQL procedures, see GRANT Schema Permission.

GRANT EXECUTE PROCEDURE Permission Syntax

GRANT EXECUTE PROCEDURE ON <procedure name> TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`EXECUTE`	Run the given SQL procedure.
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Examples

To grant execute on the sqlp_weekly procedure to spuser:

GRANT EXECUTE PROCEDURE Permission Example

GRANT EXECUTE PROCEDURE ON example.sqlp_weekly TO spuser

GRANT SQL-GPT Context Permission

SQL-GPT context permissions, which allow management of SQL-GPT contexts, can be granted directly to users or roles.

For granting the ability to create SQL-GPT contexts, see GRANT Schema Permission.

GRANT SQL-GPT Context Permission Syntax

GRANT <READ | ADMIN> ON CONTEXT <context name> TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`ADMIN`	Modify or drop the given context (includes `READ` access).
`READ`	Use the given context to generate SQL using a SQL-GPT query.
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Examples

To grant read access on the SQL-GPT context named example.atc_ctx to the ctxuser user:

GRANT SQL-GPT Context Permission Example

GRANT READ ON CONTEXT example.atc_ctx TO ctxuser

GRANT Stream Permission

Stream permissions, which allow removal & display of streams, can be granted directly to users or roles.

For granting the ability to create streams, see GRANT Schema Permission.

GRANT Stream Administrative Permission Syntax

GRANT ADMIN ON STREAM <stream name> TO <user name | role name> [WITH GRANT OPTION]

Permission	Description
`ADMIN`	Drop the given stream.
`WITH GRANT OPTION`	Grant this permission with a grant option to grant it to others.

Examples

To grant administrative access on the stream named kin_stream to the auser user:

GRANT Stream Administrative Permission Example

GRANT ADMIN ON STREAM example.kin_stream TO auser

REVOKE Role

Roles can be revoked from users or other roles.

REVOKE Role Syntax

REVOKE <role name> FROM <user name | role name>

Examples

To revoke a role allowing access to analyst tables from the analyst role:

REVOKE Role from Role Example

REVOKE analyst_table_access FROM analyst

To revoke the analyst role from user jsmith:

REVOKE Role from User Example

REVOKE analyst FROM jsmith

REVOKE SYSTEM Permission

System permissions can be revoked from users or roles.

REVOKE System Administrative Permission Syntax

REVOKE SYSTEM < ADMIN | CREATE | MONITOR | READ | WRITE > FROM <user name | role name>

REVOKE User Administrative Permission Syntax

REVOKE USER ADMIN FROM <user name | role name>

REVOKE KiFS Directory Create Permission Syntax

REVOKE CREATE DIRECTORY FROM <user name | role name>

REVOKE UDF Create Permission Syntax

REVOKE CREATE FUNCTION FROM <user name | role name>

Examples

To revoke system administrator permission from jsmith:

REVOKE SYSTEM ADMIN Example

REVOKE SYSTEM ADMIN FROM jsmith

To revoke read access to all tables from the auditor role:

REVOKE SYSTEM READ Example

REVOKE SYSTEM READ FROM auditor

To revoke user administrator permission from jsmith:

REVOKE USER ADMIN Example

REVOKE USER ADMIN FROM jsmith

REVOKE Schema Permission

Schema permissions, which allow management of schemas and their contained objects can be revoked from users or roles.

REVOKE Schema Permission Syntax

REVOKE < SELECT | INSERT | UPDATE | DELETE | CREATE <object type> | ALL > [PRIVILEGES]
ON [SCHEMA] <schema reference>
FROM <user name | role name>

Parameters

PRIVILEGES

Optional keyword for SQL-92 compatibility

SCHEMA

Optional keyword to avoid ambiguity between revoking a grant to a schema and a grant to a table/view in the user’s default schema

Either the name of the schema to which access is being revoked or a wildcard expression signifying the schema(s) and table(s)/views(s) to which access is being revoked

Wildcard Form	Selected Objects
`*`	All schemas currently in the database
`.`	All schemas, tables, & views currently in the database

For revoking CREATE privilege, the object type to remove permission to create in the given schema.Supported object types include:

Target Type	Description
`CONTEXT`	SQL-GPT context object
`CREDENTIAL`	Credential object
`DATA SINK`	External data sink
`DATA SOURCE`	External data source
`GRAPH`	Graph
`PROCEDURE`	SQL stored procedure
`STREAM`	Stream
`TABLE`	Table (or view)

Examples

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

REVOKE Table Permission

Table permissions, which can be applied to tables and views, can be revoked from users or roles.

For revoking the ability to create tables, see REVOKE Schema Permission.

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 [TABLE] [<schema name>.]<table/view name> [<column list>]
FROM <user name | role name>

Parameters

PRIVILEGES

Optional keyword for SQL-92 compatibility

TABLE

Optional keyword for SQL-92 compatibility, and to avoid ambiguity between revoking a grant to a table/view in the user’s default schema (when <schema name> is omitted) and a grant to a schema

The name of the schema containing the table/view to which access is being revoked; if omitted, the table/view will be looked for in the default schema

The name of the table to which access is being revoked; the wildcard * can be used to revoke access to all tables/views within the given <schema name> schema

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

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

REVOKE Credential Permission

Permissions on credentials that have been granted directly to users or roles can be revoked.

For revoking the ability to create credentials, see REVOKE Schema Permission.

REVOKE Credential Permission Syntax

REVOKE <READ | ADMIN> ON CREDENTIAL <credential name> FROM <user name | role name>

Examples

To revoke read access on the credential named auser_azure_active_dir_creds from the auser user:

REVOKE Credential Use Permission Example

REVOKE READ ON CREDENTIAL auser_azure_active_dir_creds FROM auser

To revoke administrative access on the credential from the auser user:

REVOKE Credential Administrative Permission Example

REVOKE ADMIN ON CREDENTIAL auser_azure_active_dir_creds FROM auser

REVOKE Data Source Permission

Permissions on data sources that have been granted directly to users or roles can be revoked.

For revoking the ability to create data sources, see REVOKE Schema Permission.

REVOKE Data Source Permission Syntax

REVOKE <CONNECT | ADMIN> ON DATA SOURCE <data source name> FROM <user name | role name>

Examples

To revoke connect access on the data source named kin_ds from the auser user:

REVOKE Data Source Use Permission Example

REVOKE CONNECT ON DATA SOURCE kin_ds FROM auser

To revoke administrative access on the data source named kin_ds_anon from the auser user:

REVOKE Data Source Administrative Permission Example

REVOKE ADMIN ON DATA SOURCE kin_ds_anon FROM auser

REVOKE Data Sink Permission

Permissions on data sinks that have been granted directly to users or roles can be revoked.

For revoking the ability to create data sinks, see REVOKE Schema Permission.

REVOKE Data Sink Permission Syntax

REVOKE <CONNECT | ADMIN> ON DATA SINK <data sink name> FROM <user name | role name>

Examples

To revoke connect access on the data sink named kin_dsink from the auser user:

REVOKE Data Sink Use Permission Example

REVOKE CONNECT ON DATA SINK kin_dsink FROM auser

To revoke administrative access on the data sink named kin_dsink_http from the auser user:

REVOKE Data Sink Administrative Permission Example

REVOKE ADMIN ON DATA SINK kin_dsink_http FROM auser

REVOKE Directory Permission

Permissions on KiFS directories that have been granted directly to users or roles can be revoked.

For revoking the ability to create directories, see REVOKE SYSTEM Permission.

REVOKE Directory Permission Syntax

REVOKE <READ | WRITE> ON DIRECTORY <directory name> FROM <user name | role name>

Examples

To revoke read access on a directory named kdata from the kuser user:

REVOKE Directory Read Permission Example

REVOKE READ ON DIRECTORY 'kdata' FROM kuser

To revoke write access on a directory named kdata from the kuser user:

REVOKE Directory Write Permission Example

REVOKE WRITE ON DIRECTORY 'kdata' FROM kuser

REVOKE Function (UDF/UDTF) Permission

Permissions relating to UDFs/UDTFs and Python UDF function environments that have been granted directly to users or roles can be revoked.

For revoking the ability to create UDFs/UDTFs, see REVOKE SYSTEM Permission.

REVOKE FUNCTION (UDF) Permission Syntax

REVOKE <EXECUTE | ADMIN> ON FUNCTION < <function name> | * > FROM <user name | role name>

Examples

To revoke execute on the udf_sos_py_proc UDF from udf_user:

REVOKE Function Execute Permission Example

REVOKE EXECUTE ON FUNCTION UDF_SOS_PY_PROC FROM udf_user

To revoke administrative access on every existing & future UDF, as well as the ability to manage function environments, from udf_user:

REVOKE Function Administrative Permission Example

REVOKE ADMIN ON FUNCTION * FROM udf_user

REVOKE Graph Permission

Permissions on graphs that have been granted directly to users or roles can be revoked.

For revoking the ability to create graphs, see REVOKE Schema Permission.

REVOKE Graph Permission Syntax

REVOKE <READ | WRITE | ADMIN> ON GRAPH <graph name> FROM <user name | role name>

Examples

To revoke query, match, solve, & show access on the graph named big_cities_graph from geo_user:

REVOKE Graph Permission Example

REVOKE READ ON GRAPH big_cities_graph FROM geo_user

REVOKE Procedure Permission

Permissions relating to SQL procedures can be revoked from users or roles.

For revoking the ability to create SQL procedures, see REVOKE Schema Permission.

REVOKE EXECUTE PROCEDURE Permission Syntax

REVOKE EXECUTE PROCEDURE ON <procedure name> FROM <user name | role name>

Examples

To revoke execute on the sqlp_weekly procedure from spuser:

REVOKE EXECUTE PROCEDURE Permission Example

REVOKE EXECUTE PROCEDURE ON example.sqlp_weekly FROM spuser

REVOKE SQL-GPT Context Permission

Permissions on SQL-GPT contexts that have been granted directly to users or roles can be revoked.

For revoking the ability to create SQL-GPT contexts, see REVOKE Schema Permission.

REVOKE SQL-GPT Context Permission Syntax

REVOKE <READ | ADMIN> ON CONTEXT <context name> FROM <user name | role name>

Examples

To revoke read access on the SQL-GPT context named example.atc_ctx from the ctxuser user:

REVOKE SQL-GPT Context Permission Example

REVOKE READ ON CONTEXT example.atc_ctx FROM ctxuser

REVOKE Stream Permission

Permissions on streams that have been granted directly to users or roles can be revoked.

For revoking the ability to create streams, see REVOKE Schema Permission.

REVOKE Stream Administrative Permission Syntax

REVOKE ADMIN ON STREAM <stream name> FROM <user name | role name>

Examples

To revoke administrative access on the stream named kin_stream from the auser user:

REVOKE Stream Administrative Permission Example

REVOKE ADMIN ON STREAM example.kin_stream FROM auser

SHOW SECURITY

For any one or more (or all) users and roles in the system, the following can be listed:

In addition, permissions, roles, & resource groups will be listed recursively for the roles the specified users/roles have and the roles those roles have, etc. This allows the full set of permissions for a given user or role, both direct & inherited, to be viewed with one command.

SHOW SECURITY Syntax

SHOW SECURITY [FOR <user name | role name>,...]

Examples

To show the permissions, roles, resource groups, and default schema for user jsmith:

SHOW SECURITY FOR User Example

SHOW SECURITY FOR jsmith

To show the permissions, roles, and resource groups for the analyst role:

SHOW SECURITY FOR Role Example

SHOW SECURITY FOR analyst

To show the permissions, roles, resource groups, and default schema for the anonymous user and the public role:

SHOW SECURITY FOR Multiple Users/Roles Example

SHOW SECURITY FOR anonymous, public

To show all users & roles:

SHOW SECURITY FOR All Users/Roles Example

SHOW SECURITY

CHECK

Users (or the current user) can have their effective permissions checked, including:

Role assignments
Administrative permissions
Object-level access

A result of YES or NO will be returned, based on whether the given user has the specified permissions or not, either directly or through a role or administrative access. Row/Column-level permission information will be returned after the YES, if applicable.

CHECK PERMISSION Syntax

CHECK <permission> [PERMISSION] [ON [<target type>] <target>]
[FOR <user>]
[IF EXISTS]

CHECK ROLE Syntax

CHECK ROLE OF <role>
[FOR <user>]
[IF EXISTS]

Parameters

Permission to verify for the given userSupported permissions include:

Permission	Description
`ADMIN` \| `ALL`	Applicable to credential objects and tables (or views)
`CONNECT`	Applicable to external data sources
`DELETE`	Applicable to tables
`EXECUTE`	Applicable to User-Defined Functions (UDFs) and SQL stored procedures
`INSERT`	Applicable to tables
`READ`	Applicable to credential objects
`SELECT`	Applicable to tables (or views)
`SYSTEM ADMIN`	System admin permission
`SYSTEM CREATE`	System create permission
`SYSTEM MONITOR`	System monitor permission
`SYSTEM READ`	System read permission
`SYSTEM WRITE`	System write permission
`UPDATE`	Applicable to tables
`USER ADMIN`	User admin permission

PERMISSION

Optional keyword for clarity

Keyword for specifying the optional ON clause, which checks for the specified permission on the following target (and optional target type) given in this clause

Optional keyword for specifying the object type of the target; may be necessary to disambiguate same-named objects of differing typesSupported target types include:

Target Type	Description
`CONTEXT`	SQL-GPT context object
`CREDENTIAL`	Credential object
`DATA SINK`	External data source
`DATA SOURCE`	External data source
`DIRECTORY`	KiFS directory
`FUNCTION`	User-Defined Function (UDF)
`GRAPH`	Graph
`PROCEDURE`	SQL stored procedure
`SCHEMA`	Schema
`STREAM`	Stream
`TABLE`	Table (or view)

Name of the object to which the given user’s specified permission will be checked

<role>

Name of the role of which the given user’s membership will be checked

FOR <user>

Optional FOR clause, which checks whether the specified permission/role exists for the user given in this clause; if omitted, the current user will be checked

IF EXISTS

Optional error suppression clause, which causes the check to return No if any of the entities specified do not exist; without this clause, non-existent entities will return an error

Impersonation (Execute As…)

When logged into the database as one user, SQL commands can be run as a second user (that user can be impersonated). To do this, first log in to the database as an administrator, then switch to the second user, and finally, run the command. Afterwards, the original user can be reverted back to in order to execute commands as that administrator again; alternatively, an option can be specified to prevent switching back to the original user. The ability to impersonate other users is available through SQL, using the following commands:

EXECUTE AS
REVERT
SET USER

Only a user with administrator privilege can impersonate another user, and only an administrator with a Kinetica database account. Any users who are mapped to Kinetica as administrators via external authentication will need to ensure that a corresponding Kinetica administrator account has been created for impersonation to succeed.Additionally, impersonation is only supported via ODBC/JDBC.

EXECUTE AS

During a SQL session, a user can execute subsequent commands as another user.

This command is only available through KiSQL or database clients configured with the Kinetica ODBC/JDBC driver.

EXECUTE AS Syntax

EXEC[UTE] AS USER = '<user name>' [WITH NO REVERT]

The WITH NO REVERT option prevents the impersonated user from switching back to the original user. This is useful in several cases, including:

An administrator needs to run a script as a user with specific permissions, but doesn’t want the script to be able to revert back to administrator privileges during its execution.
An application server connects to the database with its own credentials, but issues commands to the database on behalf of an application user. Again, there is a need to prevent the application user from reverting back to the application server’s database account and using those privileges to execute commands.

Example

To demonstrate the uses of EXECUTE AS USER, the following commands can be issued upon logging into the database as an administrator. First, verify the current user is admin:

Show Current User Example

SELECT CURRENT_USER() AS whoami

Current User Output

+----------+
| whoami   |
+----------+
| admin    |
+----------+

Then, switch to a user auser and verify the session is now executing commands as that user:

Switch User Example

EXECUTE AS USER = 'auser'

Show Switched User

SELECT CURRENT_USER() AS whoami

Switched User Output

+----------+
| whoami   |
+----------+
| auser    |
+----------+

Accessing a database object that auser has permission for results in a successful outcome:

Query Accessible Table as Switched User Example

SELECT *
FROM example.auser_can_access

Output

+------+
|   id |
+------+
|    1 |
+------+

Accessing an object that auser doesn’t have permission for results in an error:

Query Inaccessible Table as Switched User Example

SELECT *
FROM example.auser_cant_access

Output

[GPUdb]executeSql: SqlEngine: Object 'auser_cant_access' not found within 'example' (<REF>)

To switch back to the original user and verify the current identity, use the REVERT command:

Switch Back to Original User Example

REVERT

Show Switched Back User

SELECT CURRENT_USER() AS whoami

Switched Back User Output

+----------+
| whoami   |
+----------+
| admin    |
+----------+

Now, switch to auser using the WITH NO REVERT option:

Switch User without Reversion Example

EXECUTE AS USER = 'auser' WITH NO REVERT

Show Switched User

SELECT CURRENT_USER() AS whoami

Switched User Output

+----------+
| whoami   |
+----------+
| auser    |
+----------+

Attempts to switch back to the administrator user will fail:

Attempt to Switch Back Example

REVERT

Output

Error: May not revert user after WITH NO REVERT

Verify the session is still executing commands as the auser user:

Show Current User

SELECT CURRENT_USER() AS whoami

Current User Output

+----------+
| whoami   |
+----------+
| auser    |
+----------+

REVERT

After using EXECUTE AS or SET USER to switch to another user, the original user can be switched back to using the following command:

This command is only available through KiSQL or database clients configured with the Kinetica ODBC/JDBC driver.

REVERT Syntax

REVERT

See the EXECUTE AS example for an example usage of REVERT.

SET USER

The SET USER (or SETUSER) command is simply an alias for the EXECUTE AS and REVERT commands.

This command is only available through KiSQL or database clients configured with the Kinetica ODBC/JDBC driver.

The following commands are equivalent for switching to another user:

SET USER Syntax

< < SET USER <user name> > | < EXECUTE AS USER = '<user name>' > >

There is no ability to prevent reversion to the original user when using the SET USER command. To prevent reversion, see EXECUTE AS.

The following commands are equivalent for switching back to the original user:

Revert User Syntax

< SET USER | REVERT >

​User Management

​CREATE USER

​Examples

​ALTER USER

​Examples

​DROP USER

​Examples

​SHOW SECURITY FOR User

​Examples

​Role Management

​CREATE ROLE

​Examples

​ALTER ROLE

​Examples

​DROP ROLE

​Examples

​SHOW SECURITY FOR Role

​Examples

​Privilege Management

​GRANT Role

​Examples

​GRANT SYSTEM Permission

​System Administration

​System Management

​User Administration

​Create Directory

​Create UDF/UDTF

​Examples

​GRANT Schema Permission

​Parameters

​Examples

​GRANT Table Permission

​Parameters

​Examples

​Row-Level Security Examples

​Column-Level Security Examples

​GRANT Credential Permission

​Examples

​GRANT Data Source Permission

​Examples

​GRANT Data Sink Permission

​Examples

​GRANT Directory Permission

​Examples

​GRANT Function (UDF/UDTF) Permission

​Examples

​GRANT Graph Permission

​Examples

​GRANT Procedure Permission

​Examples

​GRANT SQL-GPT Context Permission

​Examples

​GRANT Stream Permission

​Examples

​REVOKE Role

​Examples

​REVOKE SYSTEM Permission

​Examples

​REVOKE Schema Permission

​Parameters

​Examples

​REVOKE Table Permission

​Parameters

​Examples

​Row-Level Security Examples

​Column-Level Security Examples

​REVOKE Credential Permission

​Examples

​REVOKE Data Source Permission

​Examples

​REVOKE Data Sink Permission

​Examples

​REVOKE Directory Permission

​Examples

​REVOKE Function (UDF/UDTF) Permission

​Examples

​REVOKE Graph Permission

​Examples

​REVOKE Procedure Permission

​Examples

User Management

CREATE USER

Examples

ALTER USER

Examples

DROP USER

Examples

SHOW SECURITY FOR User

Examples

Role Management

CREATE ROLE

Examples

ALTER ROLE

Examples

DROP ROLE

Examples

SHOW SECURITY FOR Role

Examples

Privilege Management

GRANT Role

Examples

GRANT SYSTEM Permission

System Administration

System Management

User Administration

Create Directory

Create UDF/UDTF

Examples

GRANT Schema Permission

Parameters

Examples

GRANT Table Permission

Parameters

Examples

Row-Level Security Examples

Column-Level Security Examples

GRANT Credential Permission

Examples

GRANT Data Source Permission

Examples

GRANT Data Sink Permission

Examples

GRANT Directory Permission

Examples

GRANT Function (UDF/UDTF) Permission

Examples

GRANT Graph Permission

Examples

GRANT Procedure Permission

Examples

GRANT SQL-GPT Context Permission

Examples

GRANT Stream Permission

Examples

REVOKE Role

Examples

REVOKE SYSTEM Permission

Examples

REVOKE Schema Permission

Parameters

Examples

REVOKE Table Permission

Parameters

Examples

Row-Level Security Examples

Column-Level Security Examples

REVOKE Credential Permission

Examples

REVOKE Data Source Permission

Examples

REVOKE Data Sink Permission

Examples

REVOKE Directory Permission

Examples

REVOKE Function (UDF/UDTF) Permission

Examples

REVOKE Graph Permission

Examples

REVOKE Procedure Permission

Examples