Security

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.

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 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
1
2
3
4
CREATE USER <user name>
[ < [WITH] PASSWORD [=] | IDENTIFIED BY [PASSWORD] > '<user password>' ]
[ [WITH] RESOURCE GROUP [=] <group name> ]
[ [WITH] DEFAULT SCHEMA [=] <schema name> ]

Note

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

For example, 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
1
2
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
1
2
3
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
1
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
1
2
3
4
5
ALTER USER <user name>
SET
    [ PASSWORD [=] '<user password>' ]
    [ RESOURCE GROUP [=] <group name | DEFAULT> ]
    [ DEFAULT SCHEMA [=] <schema name | DEFAULT> ]

Note

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

For example, 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
1
2
3
4
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
1
2
3
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
1
DROP USER <user name>

For example, to drop an internal user jsmith:

DROP USER (Internal) Example
1
DROP USER jsmith

To drop an external LDAP user jsmith:

DROP USER (External) Example
1
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
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 Management

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

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
1
2
CREATE ROLE <role name>
[ [WITH] RESOURCE GROUP [=] <group name> ]

For example, to create an analyst role:

CREATE ROLE Example
1
CREATE ROLE analyst

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

CREATE ROLE with Resource Group Example
1
2
CREATE ROLE executive
WITH RESOURCE GROUP execution_over_memory

ALTER ROLE

A role can have its associated resource group modified.

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

For example, to assign the memory_over_execution resource group to the analyst role:

ALTER ROLE Example
1
2
ALTER ROLE analyst
SET RESOURCE GROUP memory_over_execution

To unassign the resource group from the analyst role:

ALTER ROLE Removing Resource Group Example
1
2
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
1
DROP ROLE <role name>

For example, to drop the analyst role:

DROP ROLE Example
1
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
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 Role

Roles can be granted directly to users or other roles.

GRANT Role Syntax
1
GRANT <role name> TO <user name | role name>

For example, 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
1
2
GRANT analyst_table_access TO analyst
GRANT analyst TO jsmith

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>
Parameters Description
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
<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 Form Selected 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>]
Parameters Description
PRIVILEGES Optional keyword for SQL-92 compatibility
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
<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>
Permission Description
ADMIN Modify or drop the given credential (includes READ access)
READ Use 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>
Permission Description
ADMIN Modify or drop the given data source (includes CONNECT access)
CONNECT Connect 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>
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

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>
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)

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 Permission

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

GRANT EXECUTE FUNCTION Permission Syntax
1
GRANT EXECUTE FUNCTION ON <function name> TO <user name | role name>

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

GRANT EXECUTE FUNCTION Permission Example
1
GRANT EXECUTE FUNCTION ON UDF_SOS_PY_PROC 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 Role

Roles can be revoked from users or other roles.

REVOKE Role Syntax
1
REVOKE <role name> FROM <user name | role name>

For example, to revoke a role allowing access to analyst tables from the analyst role:

REVOKE Role from Role Example
1
REVOKE analyst_table_access FROM analyst

To revoke the analyst role from user jsmith:

REVOKE Role from User Example
1
REVOKE analyst FROM jsmith

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>
Parameters Description
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
<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 Form Selected 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>
Parameters Description
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
<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 Permission

Permissions relating to UDFs/UDTFs, can be revoked from users or roles.

REVOKE EXECUTE FUNCTION Permission Syntax
1
REVOKE EXECUTE FUNCTION ON <function name> FROM <user name | role name>

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

REVOKE EXECUTE FUNCTION Permission Example
1
REVOKE EXECUTE FUNCTION ON UDF_SOS_PY_PROC 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]
Parameters Description
<permission>

Permission to verify for the given user

Supported 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 READ System read permission
SYSTEM WRITE System write permission
UPDATE Applicable to tables
USER ADMIN User admin permission
PERMISSION Optional keyword for clarity
ON 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
<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 Type Description
CREDENTIAL Credential object
DATASOURCE External data source
DIRECTORY KiFS directory
FUNCTION User-Defined Function (UDF)
PROCEDURE SQL stored procedure
SCHEMA Schema
TABLE Table (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 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:

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