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 Queries (SQL) 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 user accounts 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:

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:

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-level permissions can be granted directly to users or roles.

To grant a system-level permission or a user-administration permission:

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 Table Permission

Table-level permissions, which can be applied to schemas, 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 | [<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
<schema name>Either the name of the schema to which access is being granted or the name of the schema containing the table to which access is being granted
<table/view name>The name of the table to which access is being granted
<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

Wildcards (*) can replace either the schema name, table/view name, or both, to specify all database objects within a given domain:

Wildcard FormSelected Objects
*All schemas
SCHEMA_NAME.*All tables/views under the schema named SCHEMA_NAME
*.*All tables/views under all schemas

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 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 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 tables within the archive schema to the analyst role:

GRANT Read on Schema Tables to Role Example
1
GRANT SELECT ON example.* TO analyst

To grant full access on all schemas and tables to the dbadmin role:

GRANT All Permissions on All Tables to Role Example
1
GRANT ALL ON *.* TO dbadmin

Row-Level Security Examples

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

GRANT Row-Level Access by Filter Example
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 perscription 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 perscription 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

The permission to use and manage a credential, can be granted directly to users or roles.

To grant read access, including use & display, on a credential to a user:

GRANT Credential Use Permission Syntax
1
GRANT READ ON CREDENTIAL < <credential name> | * > TO <user name | role name>

To grant administrative access, including use, modification, removal, & display, on a credential to a user:

GRANT Credential Administrative Permission Syntax
1
GRANT ADMIN ON CREDENTIAL < <credential name> | * > TO <user name | role name>

Tip

Provide * instead of a specific credential name to grant the permission on all credentials.

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 all credentials to the auser user:

GRANT Credential Administrative Permission Example
1
GRANT ADMIN ON CREDENTIAL * TO auser

GRANT Data Source Permission

The permission to connect to and load data from a data source can be granted directly to users or roles.

GRANT Data Source Permission Syntax
1
GRANT CONNECT ON DATA SOURCE <data source name> TO <user name | role name>

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

GRANT Data Source Permission Example
1
GRANT CONNECT ON DATA SOURCE kin_ds TO auser

GRANT Data Sink Permission

The permission to connect to and load data into a data sink can be granted directly to users or roles.

GRANT Data Sink Permission Syntax
1
GRANT CONNECT ON <data sink name> TO <user name | role name>

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

GRANT Data Sink Permission Example
1
GRANT CONNECT ON DATA SINK kin_dsink TO auser

GRANT Directory Permission

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

To grant read access on a directory to a user, allowing the user to list, download, and load data from files within that directory:

GRANT Directory Read Permission Syntax
1
GRANT READ ON DIRECTORY < <directory name> > TO <user name | role name>

To grant write access on a directory to a user, allowing the user to upload files to and delete files from that directory, as well as performing any action allowed by read access:

GRANT Directory Write Permission Syntax
1
GRANT WRITE ON DIRECTORY < <directory name> > TO <user name | role name>

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-level 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 Procedure Permission

Procedure-level 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

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-level permissions can be revoked from users or roles.

To revoke a system-level permission or a user-administration permission:

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 write access to all tables from the auditor role:

REVOKE SYSTEM WRITE Example
1
REVOKE SYSTEM WRITE FROM auditor

To revoke user administrator permission from jsmith:

REVOKE USER ADMIN Example
1
REVOKE USER ADMIN FROM jsmith

REVOKE Table Permission

Table-level permissions, which can be applied to schemas, 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 | [<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
<schema name>Either the name of the schema to which access is being revoked or the name of the schema containing the table to which access is being revoked
<table/view name>The name of the table to which access is being revoked
<column list>Optional comma-separated list of specific table columns to which access is being revoked; see Column-Level Security for details

Wildcards (*) can replace either the schema name, table/view name, or both, to specify all database objects within a given domain:

Wildcard FormSelected Objects
*All schemas
SCHEMA_NAME.*All tables/views under the schema named SCHEMA_NAME
*.*All tables/views under all schemas

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 DELETE access on the network_config_history table from the analyst role:

REVOKE Delete on Table from Role Example
1
REVOKE DELETE ON example.network_config_history FROM analyst

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

REVOKE Delete on Schemas from Role Example
1
REVOKE DELETE ON * FROM auditor

To revoke UPDATE access on all tables within the archive schema from the analyst role:

REVOKE Update on Schema Tables from Role Example
1
REVOKE UPDATE ON example.* FROM analyst

To revoke full access on all schemas and tables from the dbadmin role:

REVOKE All Permissions on All Tables from Role Example
1
REVOKE ALL ON *.* FROM dbadmin

Row-Level Security Examples

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

REVOKE Row-Level Access Example
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 perscription ordered

    REVOKE Masked Column-Level Access Example
    1
    
    REVOKE SELECT ON example.rx_order(ssn, rx_name) FROM rx_user
    

REVOKE Credential Permission

The permission to use and manage a credential, can be revoked directly from users or roles.

To revoke read or administrative access on a credential from a user:

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

Tip

Provide * instead of a specific credential name to revoke the access on all credentials.

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 all credentials from the auser user:

REVOKE Credential Admin Permission Example
1
REVOKE ADMIN ON CREDENTIAL * FROM auser

REVOKE Data Source Permission

The permission to connect to and load data from a data source can be revoked directly from users or roles.

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

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

REVOKE Data Source Permission Example
1
REVOKE CONNECT ON DATA SOURCE kin_ds FROM auser

REVOKE Data Sink Permission

The permission to connect to and load data into a data sink can be revoked directly from users or roles.

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

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

REVOKE Data Sink Permission Example
1
REVOKE CONNECT ON DATA SINK kin_dsink FROM auser

REVOKE Directory Permission

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

To revoke read access on a directory from a user:

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

To revoke write access on a directory from a user:

REVOKE Directory Write Permission Syntax
1
REVOKE 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 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

SHOW SECURITY

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

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.

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: Error: 'SqlEngine: Object 'auser_cant_access' not found within 'example' (S/SDc:1031); error in Job process'

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
GeneralException: 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:

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.

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