Security

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.

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

For example, two of the ways to create a new internal user with the user ID of jsmith and a password of secret are:

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:

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:

1

CREATE USER "@jsmith"

ALTER USER

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

• password
• resource group
• default schema

1
2
3
4
5

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

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:

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:

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.

1

DROP USER <user name>

For example, to drop an internal user jsmith:

1

DROP USER jsmith

To drop an external LDAP user jsmith:

1

DROP USER "@jsmith"

SHOW SECURITY FOR User

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

• permissions
• roles
• resource groups
• default schema

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.

1

SHOW SECURITY FOR <user name>,...

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

1

SHOW SECURITY FOR jsmith

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.

1
2

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

For example, to create an analyst role:

1

CREATE ROLE analyst

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

1
2

CREATE ROLE executive
WITH RESOURCE GROUP execution_over_memory

ALTER ROLE

A role can have its associated resource group modified.

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:

1
2

ALTER ROLE analyst
SET RESOURCE GROUP memory_over_execution

To unassign the resource group from the analyst role:

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.

1

DROP ROLE <role name>

For example, to drop the analyst role:

1

DROP ROLE analyst

SHOW SECURITY FOR Role

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

• permissions
• roles
• resource groups

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.

1

SHOW SECURITY FOR <role name>,...

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

1

SHOW SECURITY FOR analyst

GRANT Role

Roles can be granted directly to users or other roles.

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:

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.

1

GRANT SYSTEM < ADMIN | READ | WRITE > TO <user name | role name>

1

GRANT USER ADMIN TO <user name | role name>

For example, to grant system administrator permission to jsmith:

1

GRANT SYSTEM ADMIN TO jsmith

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

1

GRANT SYSTEM READ TO auditor

To grant user administrator permission to jsmith:

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.

1
2
3

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

1

GRANT SELECT ON * TO auditor

1

GRANT SELECT ON app_schema TO app_user

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.

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

1

GRANT ALL PRIVILEGES ON TABLE example.network_config TO jsmith

1

GRANT SELECT ON example.network_config_history TO analyst

1

GRANT ALL ON example.* TO developer

1
2
3

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

1
2
3

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

1

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

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.

1

GRANT <READ | ADMIN> ON CREDENTIAL <credential name> TO <user name | role name>

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

1

GRANT READ ON CREDENTIAL auser_azure_active_dir_creds TO auser

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

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.

1

GRANT <CONNECT | ADMIN> ON DATA SOURCE <data source name> TO <user name | role name>

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

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:

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.

1

GRANT <CONNECT | ADMIN> ON DATA SINK <data sink name> TO <user name | role name>

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

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:

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.

1

GRANT <READ | 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:

1

GRANT READ ON DIRECTORY 'kdata' TO kuser

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

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.

1

GRANT <EXECUTE | ADMIN> ON FUNCTION < <function name> | * > TO <user name | role name>

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

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:

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.

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:

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.

1

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

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

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.

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:

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.

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:

1

GRANT ADMIN ON STREAM kin_stream TO auser

REVOKE Role

Roles can be revoked from users or other roles.

1

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

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

1

REVOKE analyst_table_access FROM analyst

To revoke the analyst role from user jsmith:

1

REVOKE analyst FROM jsmith

REVOKE SYSTEM Permission

System permissions can be revoked from users or roles.

1

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

1

REVOKE USER ADMIN FROM <user name | role name>

For example, to revoke system administrator permission from jsmith:

1

REVOKE SYSTEM ADMIN FROM jsmith

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

1

REVOKE SYSTEM READ FROM auditor

To revoke user administrator permission from jsmith:

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.

1
2
3

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

1

REVOKE SELECT ON * FROM auditor

1

REVOKE SELECT ON app_schema FROM app_user

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.

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

1
2
3

REVOKE < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
ON [TABLE] [<schema name>.]<table/view name> [<column list>]
FROM <user name | role name>

1

REVOKE ALL PRIVILEGES ON TABLE example.network_config FROM jsmith

1

REVOKE SELECT ON example.network_config_history FROM analyst

1

REVOKE ALL ON example.* FROM developer

1

REVOKE SELECT ON example.rx_order FROM zanalyst

1

REVOKE SELECT ON example.rx_order(ssn) FROM zanalyst

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.

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:

1

REVOKE READ ON CREDENTIAL auser_azure_active_dir_creds FROM auser

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

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.

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:

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:

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.

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:

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:

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.

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:

1

REVOKE READ ON DIRECTORY 'kdata' FROM kuser

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

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.

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:

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:

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.

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:

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.

1

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

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

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.

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:

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.

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:

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:

• permissions
• roles
• resource groups
• default schema (users only)

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.

1

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

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

1

SHOW SECURITY FOR jsmith

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

1

SHOW SECURITY FOR analyst

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

1

SHOW SECURITY FOR anonymous, public

To show all users & roles:

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.

1
2
3

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

1
2
3

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

EXECUTE AS

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

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.

1

SELECT CURRENT_USER() AS whoami

1
2
3
4
5

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

1
2

EXECUTE AS USER = 'auser'
SELECT CURRENT_USER() AS whoami

1
2
3
4
5

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

1
2

SELECT *
FROM example.auser_can_access

1
2
3
4
5

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

1
2

SELECT *
FROM example.auser_cant_access

1

[GPUdb]executeSql: SqlEngine: Object 'auser_cant_access' not found within 'example' (S/SDc:1436)

1
2

REVERT
SELECT CURRENT_USER() AS whoami

1
2
3
4
5

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

1
2

EXECUTE AS USER = 'auser' WITH NO REVERT
SELECT CURRENT_USER() AS whoami

1
2
3
4
5

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

1

REVERT

1

Error: May not revert user after WITH NO REVERT

1

SELECT CURRENT_USER() AS whoami

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:

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:

1
2

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

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

1
2

SET USER
REVERT