Security

Kinetica provides basic table-level role-based access control for users. It also allows global read/write and administrative access to be granted. Limited permission management and reporting capability is available through SQL.

Important

For details about Kinetica security, including managing users & roles, see Security Concepts.

Tip

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 Security Detail

The ability to view user security details is available through SQL, using the following command:

See Users for details about users within Kinetica.

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
1
SHOW SECURITY FOR <user name>,...

For example, to show the permissions, roles, resource group, and default schema for user jsmith:

SHOW SECURITY FOR User Example
1
SHOW SECURITY FOR jsmith

Role Security Detail

The ability to view role security detail is available through SQL, using the following command:

See Roles for details about roles within Kinetica.

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
1
SHOW SECURITY FOR <role name>,...

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

SHOW SECURITY FOR Role Example
1
SHOW SECURITY FOR analyst

Privilege Management

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

See Users for details about user accounts within Kinetica.

GRANT SYSTEM Permission

System permissions can be granted directly to users or roles.

GRANT System Administrative Permission Syntax
1
GRANT SYSTEM < ADMIN | READ | WRITE > TO <user name | role name>
GRANT User Administrative Permission Syntax
1
GRANT USER ADMIN TO <user name | role name>

For example, to grant system administrator permission to jsmith:

GRANT SYSTEM ADMIN Example
1
GRANT SYSTEM ADMIN TO jsmith

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

GRANT SYSTEM READ Example
1
GRANT SYSTEM READ TO auditor

To grant user administrator permission to jsmith:

GRANT USER ADMIN Example
1
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
1
2
3
GRANT < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
ON [SCHEMA] <schema reference>
TO <user name | role name>
ParametersDescription
PRIVILEGESOptional keyword for SQL-92 compatibility
SCHEMAOptional keyword to avoid ambiguity between a grant to a schema and a grant to a table/view in the user's default schema
<schema reference>

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 FormSelected Objects
*All schemas currently in the database
*.*All schemas, tables, & views currently in the database

Note

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

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
1
2
3
4
GRANT < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
ON [TABLE] [<schema name>.]<table/view name> [<column list>]
TO <user name | role name>
[WHERE <expression>]
ParametersDescription
PRIVILEGESOptional keyword for SQL-92 compatibility
TABLEOptional 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
<schema name>The name of the schema containing the table to which access is being granted; if omitted, the table/view will be looked for in the default schema
<table/view name>The name of the table/view to which access is being granted; the wildcard * can be used to grant access to all tables/views within the given <schema name> schema
<column list>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

Note

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
1
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
1
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
1
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
1
2
3
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
1
2
3
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
1
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
1
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.

GRANT Credential Permission Syntax
1
GRANT <READ | ADMIN> ON CREDENTIAL <credential name> TO <user name | role name>
PermissionDescription
ADMINModify or drop the given credential (includes READ access)
READUse or view the configuration of the given credential

For example, to grant read access on the credential named auser_azure_active_dir_creds to the auser user:

GRANT Credential Use Permission Example
1
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
1
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.

GRANT Data Source Permission Syntax
1
GRANT <CONNECT | ADMIN> ON DATA SOURCE <data source name> TO <user name | role name>
PermissionDescription
ADMINModify or drop the given data source (includes CONNECT access)
CONNECTConnect to, load data from, or view the configuration of the given data source

For example, to grant connect access on the data source named kin_ds to the auser user:

GRANT Data Source Use Permission Example
1
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
1
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.

GRANT Data Sink Permission Syntax
1
GRANT <CONNECT | ADMIN> ON DATA SINK <data sink name> TO <user name | role name>
PermissionDescription
ADMINModify or drop the given data sink (includes CONNECT access)
CONNECTConnect to, load data into, or view the configuration of the given data sink

For example, to grant connect & load access on the data sink named kin_dsink to the auser user:

GRANT Data Sink Use Permission Example
1
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
1
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.

GRANT Directory Permission Syntax
1
GRANT <READ | WRITE> ON DIRECTORY <directory name> TO <user name | role name>
PermissionDescription
READList, download, or load data from files within the given directory
WRITEUpload files to or delete files from the given directory (includes READ access)

For example, to grant read access on a directory named kdata to the kuser user:

GRANT Directory Read Permission Example
1
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
1
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.

GRANT FUNCTION (UDF) Permission Syntax
1
GRANT <EXECUTE | ADMIN> ON FUNCTION < <function name> | * > TO <user name | role name>
PermissionDescription
ADMIN

Drop the given UDF, and display full configuration about it (includes EXECUTE access).

When * is used instead of function name, gives permission to create, drop, & execute any UDF, and also gives permission to manage Python UDF function environments

EXECUTERun and terminate the given UDF, and display limited configuration about it
<function name>The name of the UDF to which access is being granted; use * instead of function name to grant access to all existing & future UDFs

For example, to grant execute on the udf_sos_py_proc UDF to udf_user:

GRANT Function Execute Permission Example
1
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
1
GRANT ADMIN ON FUNCTION * TO udf_user

GRANT Graph Permission

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

GRANT Graph Permission Syntax
1
GRANT <READ | WRITE | ADMIN> ON GRAPH <graph name> TO <user name | role name>

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

GRANT Graph Permission Example
1
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.

GRANT EXECUTE PROCEDURE Permission Syntax
1
GRANT EXECUTE PROCEDURE ON <procedure name> TO <user name | role name>

For example, to grant execute on the sqlp_weekly procedure to spuser:

GRANT EXECUTE PROCEDURE Permission Example
1
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.

GRANT SQL-GPT Context Permission Syntax
1
GRANT <READ | ADMIN> ON CONTEXT <context name> TO <user name | role name>

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

GRANT SQL-GPT Context Permission Example
1
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.

GRANT Stream Administrative Permission Syntax
1
GRANT ADMIN ON STREAM <stream name> TO <user name | role name>

For example, to grant administrative access on the stream named kin_stream to the auser user:

GRANT Stream Administrative Permission Example
1
GRANT ADMIN ON STREAM kin_stream TO auser

REVOKE SYSTEM Permission

System permissions can be revoked from users or roles.

REVOKE System Administrative Permission Syntax
1
REVOKE SYSTEM < ADMIN | READ | WRITE > FROM <user name | role name>
REVOKE User Administrative Permission Syntax
1
REVOKE USER ADMIN FROM <user name | role name>

For example, to revoke system administrator permission from jsmith:

REVOKE SYSTEM ADMIN Example
1
REVOKE SYSTEM ADMIN FROM jsmith

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

REVOKE SYSTEM READ Example
1
REVOKE SYSTEM READ FROM auditor

To revoke user administrator permission from jsmith:

REVOKE USER ADMIN Example
1
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
1
2
3
REVOKE < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
ON [SCHEMA] <schema reference>
FROM <user name | role name>
ParametersDescription
PRIVILEGESOptional keyword for SQL-92 compatibility
SCHEMAOptional keyword to avoid ambiguity between revoking a grant to a schema and a grant to a table/view in the user's default schema
<schema reference>

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 FormSelected Objects
*All schemas currently in the database
*.*All schemas, tables, & views currently in the database

Note

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 revoke SELECT access on all schemas from the auditor role:

REVOKE Read on Schemas from Role Example
1
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
1
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
1
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.

Row-level security can be revoked for SELECT privilege only by revoking SELECT privilege from the entire table.

Note

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
1
2
3
REVOKE < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
ON [TABLE] [<schema name>.]<table/view name> [<column list>]
FROM <user name | role name>
ParametersDescription
PRIVILEGESOptional keyword for SQL-92 compatibility
TABLEOptional 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
<schema name>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
<table/view name>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
<column list>Optional comma-separated list of specific table columns to which access is being revoked; see Column-Level Security for details

Note

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

REVOKE Credential Permission Syntax
1
REVOKE <READ | ADMIN> ON CREDENTIAL <credential name> FROM <user name | role name>

For example, to revoke read access on the credential named auser_azure_active_dir_creds from the auser user:

REVOKE Credential Use Permission Example
1
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
1
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.

REVOKE Data Source Permission Syntax
1
REVOKE <CONNECT | ADMIN> ON DATA SOURCE <data source name> FROM <user name | role name>

For example, to revoke connect access on the data source named kin_ds from the auser user:

REVOKE Data Source Use Permission Example
1
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
1
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.

REVOKE Data Sink Permission Syntax
1
REVOKE <CONNECT | ADMIN> ON DATA SINK <data sink name> FROM <user name | role name>

For example, to revoke connect access on the data sink named kin_dsink from the auser user:

REVOKE Data Sink Use Permission Example
1
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
1
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.

REVOKE Directory Permission Syntax
1
REVOKE <READ | WRITE> ON DIRECTORY <directory name> FROM <user name | role name>

For example, to revoke read access on a directory named kdata from the kuser user:

REVOKE Directory Read Permission Example
1
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
1
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.

REVOKE FUNCTION (UDF) Permission Syntax
1
REVOKE <EXECUTE | ADMIN> ON FUNCTION < <function name> | * > FROM <user name | role name>

For example, to revoke execute on the udf_sos_py_proc UDF from udf_user:

REVOKE Function Execute Permission Example
1
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
1
REVOKE ADMIN ON FUNCTION * FROM udf_user

REVOKE Graph Permission

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

REVOKE Graph Permission Syntax
1
REVOKE <READ | WRITE | ADMIN> ON GRAPH <graph name> FROM <user name | role name>

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

REVOKE Graph Permission Example
1
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.

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

For example, to revoke execute on the sqlp_weekly procedure from spuser:

REVOKE EXECUTE PROCEDURE Permission Example
1
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.

REVOKE SQL-GPT Context Permission Syntax
1
REVOKE <READ | ADMIN> ON CONTEXT <context name> FROM <user name | role name>

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

REVOKE SQL-GPT Context Permission Example
1
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.

REVOKE Stream Administrative Permission Syntax
1
REVOKE ADMIN ON STREAM <stream name> FROM <user name | role name>

For example, to revoke administrative access on the stream named kin_stream from the auser user:

REVOKE Stream Administrative Permission Example
1
REVOKE ADMIN ON STREAM 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
1
SHOW SECURITY [FOR <user name | role name>,...]

For example, to show the permissions, roles, resource groups, and default schema for user jsmith:

SHOW SECURITY FOR User Example
1
SHOW SECURITY FOR jsmith

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

SHOW SECURITY FOR Role Example
1
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
1
SHOW SECURITY FOR anonymous, public

To show all users & roles:

SHOW SECURITY FOR All Users/Roles Example
1
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
1
2
3
CHECK <permission> [PERMISSION] [ON [<target type>] <target>]
[FOR <user>]
[IF EXISTS]
CHECK ROLE Syntax
1
2
3
CHECK ROLE OF <role>
[FOR <user>]
[IF EXISTS]
ParametersDescription
<permission>

Permission to verify for the given user

Supported permissions include:

PermissionDescription
ADMIN | ALLApplicable to credential objects and tables (or views)
CONNECTApplicable to external data sources
DELETEApplicable to tables
EXECUTEApplicable to User-Defined Functions (UDFs) and SQL stored procedures
INSERTApplicable to tables
READApplicable to credential objects
SELECTApplicable to tables (or views)
SYSTEM ADMINSystem admin permission
SYSTEM READSystem read permission
SYSTEM WRITESystem write permission
UPDATEApplicable to tables
USER ADMINUser admin permission
PERMISSIONOptional keyword for clarity
ONKeyword for specifying the optional ON clause, which checks for the specified permission on the following target (and optional target type) given in this clause
<target type>

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

Supported target types include:

Target TypeDescription
CREDENTIALCredential object
DATASOURCEExternal data source
DIRECTORYKiFS directory
FUNCTIONUser-Defined Function (UDF)
PROCEDURESQL stored procedure
SCHEMASchema
TABLETable (or view)
<target>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 EXISTSOptional 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:

Important

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.

Note

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

EXECUTE AS Syntax
1
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
1
SELECT CURRENT_USER() AS whoami
Show Current User Output
1
2
3
4
5
+----------+
| whoami   |
+----------+
| admin    |
+----------+

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

Switch User Example
1
2
EXECUTE AS USER = 'auser'
SELECT CURRENT_USER() AS whoami
Show Switched User Output
1
2
3
4
5
+----------+
| whoami   |
+----------+
| auser    |
+----------+

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

Query Accessible Table as Switched User Example
1
2
SELECT *
FROM example.auser_can_access
Query Output
1
2
3
4
5
+------+
|   id |
+------+
|    1 |
+------+

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

Query Inaccessible Table as Switched User Example
1
2
SELECT *
FROM example.auser_cant_access
Query Output
1
[GPUdb]executeSql: SqlEngine: Object 'auser_cant_access' not found within 'example' (S/SDc:1436)

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

Switch Back to Original User Example
1
2
REVERT
SELECT CURRENT_USER() AS whoami
Show Switched Back User Output
1
2
3
4
5
+----------+
| whoami   |
+----------+
| admin    |
+----------+

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

Switch User without Reversion Example
1
2
EXECUTE AS USER = 'auser' WITH NO REVERT
SELECT CURRENT_USER() AS whoami
Show Switched User Output
1
2
3
4
5
+----------+
| whoami   |
+----------+
| auser    |
+----------+

Attempts to switch back to the administrator user will fail:

Attempt to Switch Back Example
1
REVERT
Error Output
1
Error: May not revert user after WITH NO REVERT

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

Show Current User
1
SELECT CURRENT_USER() AS whoami
Show Current User Output
1
2
3
4
5
+----------+
| 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:

Note

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

REVERT Syntax
1
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.

Note

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
1
2
SET USER <user name>
EXECUTE AS USER = '<user name>'

Important

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
1
2
SET USER
REVERT