Security Usage

The examples below use the native API to manage users, roles, and permissions. Generally, the API is symmetric across the endpoint calls, so similarly named calls between API languages should have the same function.

The following security-related topics will be covered:

Authentication

Connecting & authenticating to a Kinetica instance generally requires four parameters:

Kinetica host
Kinetica port
User name
Password

Once an API connection has been established, those parameters will be reused in each subsequent call to the database.

Note

API authentication is only required if you've setup HTTPD for Kinetica.

To connect to a Kinetica instance in Python:

1
2
3
4
5
h_db = gpudb.GPUdb(
    host = ['http://' + kinetica_host + ':9191'],
    username = 'auser',
    password = 'password'
)

To connect in Java:

1
2
3
4
GPUdbBase.Options options = new GPUdbBase.Options();
options.setUsername(USERNAME);
options.setPassword(PASSWORD);
this.gpudb = new GPUdb(KINETICA_URL, options);

User Management

User accounts can be added & removed via API call. In Python, internal & external (LDAP, AD, etc.) users are created with the create_user_internal & create_user_external functions, respectively.

Creating Users

To create an internal user, auser, in Python:

1
2
3
4
h_db.create_user_internal(
    name = 'auser',
    password = 'password'
)

To create an external user, ruser, in Python:

1
2
3
h_db.create_user_external(
    name = 'ruser'
)

Deleting Users

To delete the internal user, auser, in Python:

1
h_db.delete_user('auser')

To delete the external user, ruser, in Python:

1
h_db.delete_user('ruser')

Role Management

Roles can be created in and removed from the system, and can be assigned to and unassigned from both users & roles, all via API call.

Creating Roles

To create a role, rx_user, in Python:

1
h_db.create_role('rx_user')

Assigning Roles

To assign the role, rx_user, to the auser user, in Python:

1
h_db.grant_role('rx_user', 'auser')

To assign a role, rx_user_table_read_access, to the rx_user role, in Python:

1
h_db.grant_role('rx_user_table_read_access', 'rx_user')

Unassigning Roles

To unassign a role, rx_user, from the auser user, in Python:

1
h_db.revoke_role('rx_user', 'auser')

To unassign a role, rx_user_table_read_access, from the rx_user role, in Python:

1
h_db.revoke_role('rx_user_table_read_access', 'rx_user')

Deleting Roles

To delete a role, rx_user, in Python:

1
h_db.delete_role('rx_user')

Permission Management

Permissions on database objects can be assigned to and unassigned from both users & roles, all via API call, in each of the following categories:

Managing System Permissions

System permissions can be granted to and revoked from a user or role using the API.

Granting System Permissions

To grant the system permission, system_admin, to the auser user, in Python:

1
h_db.grant_permission_system('auser', 'system_admin')

To grant the user administration permission, system_user_admin, to the auser user, in Python:

1
h_db.grant_permission_system('auser', 'system_user_admin')

To grant the system permission, system_read, to the rx_auditor role, in Python:

1
h_db.grant_permission_system('rx_auditor', 'system_read')

Revoking System Permissions

To revoke the system permission, system_admin, from the auser user, in Python:

1
h_db.revoke_permission_system('auser', 'system_admin')

To revoke the user administration permission, system_user_admin, from the auser user, in Python:

1
h_db.revoke_permission_system('auser', 'system_user_admin')

To revoke the system permission, system_read, from the rx_auditor role, in Python:

1
h_db.revoke_permission_system('rx_auditor', 'system_read')

Managing Table Permissions

Table permissions can be granted to and revoked from a user or role using the API.

Granting Table Permissions

To grant the table read permission, table_read, on the rx_order table in the example schema, to the zanalyst user, in Python:

1
h_db.grant_permission_table('zanalyst', 'table_read', 'example.rx_order')

To grant the table administration permission, table_admin, on the rx_order table in the example schema, to the rx_user role, in Python:

1
h_db.grant_permission_table('rx_user', 'table_admin', 'example.rx_order')

Note

Specifying only a schema name grants the specified access on all present & future tables in that schema to the specified user; this grant is maintained separately from any grants to individual tables within the schema.

Revoking Table Permissions

To revoke the table read permission, table_read, on the rx_order table in the example schema, from the zanalyst user, in Python:

1
h_db.revoke_permission_table('zanalyst', 'table_read', 'example.rx_order')

To revoke the table administration permission, table_admin, on the rx_order table in the example schema, from the rx_user role, in Python:

1
h_db.revoke_permission_table('rx_user', 'table_admin', 'example.rx_order')

Note

Specifying only a schema name revokes the specified access previously granted on that schema from the specified user; this revocation will not effect any grants the user has to individual tables within the schema.

Managing Table Permissions at the Row Level

Row-level table permissions can be granted to and revoked from a user or role using the API.

Granting Row-Level Table Permissions

To grant row-level table permissions, in Python, with the following criteria:

table read permission (table_read)
on the rx_order table in the example schema
for records with order timestamps on or after 2002-01-01
to user zanalyst

1
2
3
4
5
6
h_db.grant_permission_table(
    'zanalyst',
    'table_read',
    'example.rx_order',
    "order_ts >= '2002-01-01'"
)

To grant row-level table permissions, in Python, with the following criteria:

table read permission (table_read)
on the rx_order table in the example schema
for records associated with the querying user, matched by username
to role rx_user

1
2
3
4
5
6
h_db.grant_permission_table(
    'rx_user',
    'table_read',
    'example.rx_order',
    'name = USER()'
)

Revoking Row-Level Table Permissions

To revoke row-level table permissions, in Python, with the following criteria:

table read permission (table_read)
on the rx_order table in the example schema
for any/all granted rows (row-level access to a table must be revoked by revoking access to the full table)
from user zanalyst

1
2
3
4
5
h_db.revoke_permission_table(
    'zanalyst',
    'table_read',
    'example.rx_order'
)

To revoke row-level table permissions, in Python, with the following criteria:

table read permission (table_read)
on the rx_order table in the example schema
for any/all granted rows (row-level access to a table must be revoked by revoking access to the full column)
from role rx_user

1
2
3
4
5
h_db.revoke_permission_table(
    'rx_user',
    'table_read',
    'example.rx_order'
)

Managing Table Permissions at the Column Level

Column-level table permissions can be granted to and revoked from a user or role using the API.

Granting Column-Level Table Permissions

To grant column-level table permissions, in Python, with the following criteria:

table read permission (table_read)
on the rx_order table in the example schema
for columns rx_name, order_ts, and an obfuscated version of ssn
to user zanalyst

1
2
3
4
5
6
h_db.grant_permission_table(
    'zanalyst',
    'table_read',
    'example.rx_order',
    options = {"columns": "HASH(ssn), rx_name, order_ts"}
)

To grant column-level table permissions, in Python, with the following criteria:

table read permission (table_read)
on the rx_order table in the example schema
for columns name, rx_name, order_ts, as well as a masked version of the ssn, only showing the last 4 digits
to role rx_user

1
2
3
4
5
6
h_db.grant_permission_table(
    'rx_user',
    'table_read',
    'example.rx_order',
    options = {"columns": "MASK(ssn, 1, 6), name, rx_name, order_ts"}
)

Revoking Column-Level Table Permissions

To revoke column-level table permissions, in Python, with the following criteria:

table read permission (table_read)
on the rx_order table in the example schema
for obfuscated column ssn (other granted columns will remain visible)
from user zanalyst

1
2
3
4
5
6
h_db.revoke_permission_table(
    'zanalyst',
    'table_read',
    'example.rx_order',
    options = {"columns": "ssn"}
)

To revoke column-level table permissions, in Python, with the following criteria:

table read permission (table_read)
on the rx_order table in the example schema
for column rx_name and masked column ssn (other granted columns will remain visible)
from role rx_user

1
2
3
4
5
6
h_db.revoke_permission_table(
    'rx_user',
    'table_read',
    'example.rx_order',
    options = {"columns": "ssn, rx_name"}
)

Managing Credential Permissions

Credential permissions can be granted to and revoked from a user or role using the API.

Granting Credential Permissions

To grant the credential permission, credential_read, on a credential, to the auser user, in Python:

1
2
3
4
5
h_db.grant_permission_credential(
    name = 'auser',
    permission = 'credential_read',
    credential_name = 'auser_azure_active_dir_creds'
)

Revoking Credential Permissions

To revoke the credential permission, credential_read, on a credential, from the auser user, in Python:

1
2
3
4
5
h_db.revoke_permission_credential(
    name = 'auser',
    permission = 'credential_read',
    credential_name = 'auser_azure_active_dir_creds'
)

Managing Data Source Permissions

Data source permissions can be granted to and revoked from a user or role using the API.

Granting Data Source Permissions

To grant the data source permission, connect, on the kin_ds data source, to the auser user, in Python:

1
2
3
4
5
h_db.grant_permission_datasource(
    name = 'auser',
    permission = 'connect',
    datasource_name = 'kin_ds'
)

Revoking Data Source Permissions

To revoke the data source permission, connect, on the kin_ds data source, from the auser user, in Python:

1
2
3
4
5
h_db.revoke_permission_datasource(
    name = 'auser',
    permission = 'connect',
    datasource_name = 'kin_ds'
)

Managing Data Sink Permissions

Data sink permissions can be granted to and revoked from a user or role using the API.

Granting Data Sink Permissions

To grant the data sink permission, connect, on the kin_dsink data sink, to the auser user, in Python:

1
2
3
4
5
db.grant_permission(
    principal = 'auser',
    object = 'kin_dsink',
    object_type = 'datasink',
    permission = 'connect'

Revoking Data Sink Permissions

To revoke the data sink permission, connect, on the kin_dsink data sink, from the auser user, in Python:

1
2
3
4
5
db.revoke_permission(
    principal = 'auser',
    object = 'kin_dsink',
    object_type = 'datasink',
    permission = 'connect'

Managing Graph Permissions

Graph permissions can be granted to and revoked from a user or role using the API.

Granting Graph Permissions

To grant the graph permission, graph_read, on a graph, to the auser user, in Python:

1
2
3
4
5
6
h_db.grant_permission(
    principal = 'auser',
    object = 'big_cities_graph',
    object_type = 'graph',
    permission = 'read'
)

Revoking Graph Permissions

To revoke the graph permission, graph_read, on a graph, from the auser user, in Python:

1
2
3
4
5
6
h_db.revoke_permission(
    principal = 'auser',
    object = 'big_cities_graph',
    object_type = 'graph',
    permission = 'read'
)

Managing Directory Permissions

KiFS permissions can be granted to and revoked from a user or role using the API.

Granting Directory Permissions

To grant the directory permission, read, on the kdata directory, to the kuser user, in Python:

1
2
3
4
5
6
db.grant_permission(
    principal = 'kuser',
    object = 'kdata',
    object_type = 'directory',
    permission = 'read'
)

Revoking Directory Permissions

To revoke the directory permission, write, on the kdata directory, from the kuser user, in Python:

1
2
3
4
5
6
db.revoke_permission(
    principal = 'kuser',
    object = 'kdata',
    object_type = 'directory',
    permission = 'write'
)

Managing Procedure Permissions

Procedure permissions can be granted to and revoked from a user or role using the API.

While permissions for user-defined functions are able to be managed via the native API, permissions for SQL procedures are managed via SQL.

Granting UDF Permissions

To grant the execute permission, proc_execute, on a specific UDF to the auser user, in Python:

1
h_db.grant_permission_proc('auser', 'proc_execute', 'sum_of_squares')

To grant execute permission on all present & future UDFs to the auser user, in Python:

1
h_db.grant_permission_proc('a_role', 'proc_execute', '')

Revoking UDF Permissions

To revoke the execute permission, proc_execute, on a specific UDF from the auser user, in Python:

1
h_db.revoke_permission_proc('auser', 'proc_execute', 'sum_of_squares')

To revoke the ability for the auser user, to execute all present & future UDFs, in Python:

1
h_db.revoke_permission_proc('a_role', 'proc_execute', '')

Important

This revocation of execute on present & future UDFs is only a revocation of the corresponding grant of the same. This will not revoke execute permission to specific UDFs that a user has been granted; each of those must be revoked separately.

Managing Table Monitor Permissions

Table monitor permissions can be granted to and revoked from a user or role using the API.

Granting Table Monitor Permissions

To grant the table monitor permission, monitor_admin, on a table monitor, to the auser user, in Python:

1
2
3
4
5
6
h_db.grant_permission(
    principal = 'auser',
    object = 'order_tm',
    object_type = 'table_monitor',
    permission = 'admin'
)

Revoking Table Monitor Permissions

To revoke the table monitor permission, monitor_admin, on a table monitor, from the auser user, in Python:

1
2
3
4
5
6
h_db.revoke_permission(
    principal = 'auser',
    object = 'order_tm',
    object_type = 'table_monitor',
    permission = 'admin'
)

Security Information Retrieval

Complete security information for a user or role can be extracted via API calls.

Tip

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

Basic Security Information

The permission & role grants, resource groups, and default schema can be retrieved for a given user; while the permission & role grants and resource groups can be retrieved for a given role; all via the Python API. That information can be displayed within a table produced by tabulate, as show below.

The key API usage points to note below are:

The call to show_security with the user or role name for which security information will be retrieved
The extraction of the key security fields from the returned structure:
- types - type of security entity (internal user, external user, or role)
- roles - roles granted to the entity
- permissions - permissions granted directly to the entity, and, for a user, the default schema assigned
- resource_groups - resource groups assigned to the entity

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
security_info = h_db.show_security([user_role_name])

roles = security_info['roles'].get(user_role_name)
perms = security_info['permissions'].get(user_role_name)

data = [OrderedDict([
    ('Type', security_info['types'].get(user_role_name)),
    ('Roles', None if roles is None else "\n".join(roles)),
    ('Resource Group', security_info['resource_groups'].get(user_role_name)),
    ('Permissions', None if perms is None else ",\n".join(json.dumps(perm, indent = 4) for perm in perms))
])]

print( tabulate( data , headers = 'keys', tablefmt = 'grid') )

The output from a request for security information may look like the following:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
+---------------+---------------+---------------------------------+----------------------------------------+
| Type          | Roles         | Resource Group                  | Permissions                            |
+===============+===============+=================================+========================================+
| internal_user | authenticated | kinetica_default_resource_group | {                                      |
|               | public        |                                 |     "table_name": "example.rx_order",  |
|               |               |                                 |     "permission": "table_read"         |
|               |               |                                 | },                                     |
|               |               |                                 | {                                      |
|               |               |                                 |     "default_schema": "ki_home"        |
|               |               |                                 | }                                      |
+---------------+---------------+---------------------------------+----------------------------------------+

Table-Level Security Information

Table-level permission (including row-level & column-level security) can be retrieved, via Python API, by examining the permissions component of the show_security response structure.

The key API usage points to note below are:

The call to show_security with the user or role name for which table-level security information will be retrieved
The extraction of the permissions security field from the returned structure, where the array of per-table security information is kept
The extraction of the key security fields for each table:
- table_name - name of the table to which access has been granted
- permission - type of access granted for the table; in the case of row-level and column-level security, this will always be table_read
- columns - a list of the columns and optional per-column row-level filters to which column-level access has been granted
- filter_expression - the expression used to define the row-level access granted to the table as a whole

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
security_info = h_db.show_security([user_role_name])

perms = security_info['permissions'].get(user_role_name)

if perms:
    perms = [
            OrderedDict([
                    ('Table Name', perm['table_name']),
                    ('Permission', perm['permission']),
                    ('Column List', "\n".join(str([str(perm_item) for perm_item in col_perm]) for col_perm in json.loads(perm.get('columns', '[]')))),
                    ('Filter Expression', perm['filter_expression'])
            ]) for perm in perms if 'table_name' in perm
    ]

    print( tabulate( perms , headers = 'keys', tablefmt = 'grid') )

The output from a request for table-level security information may look like the following:

1
2
3
4
5
6
7
+------------------+--------------+---------------------------------------------------------------------+--------------------------+
| Table Name       | Permission   | Column List                                                         | Filter Expression        |
+==================+==============+=====================================================================+==========================+
| example.rx_order | table_read   | ['HASH(ssn)', "order_ts < '2002-01-01'", "order_ts < '2002-01-01'"] | order_ts >= '2002-01-01' |
|                  |              | ['rx_name', "order_ts < '2002-01-01'", "order_ts < '2002-01-01'"]   |                          |
|                  |              | ['order_ts', "order_ts < '2002-01-01'", "order_ts < '2002-01-01'"]  |                          |
+------------------+--------------+---------------------------------------------------------------------+--------------------------+

External (LDAP) Users

Differences between using an external user instead of an internal user:

Rather than connect to Kinetica through its port (normally 9191), you will connect to the HTTPD proxy's port (8082 by default). When creating this connection, you will pass the LDAP user's username and password.
For each LDAP user that needs to use Kinetica, a corresponding Kinetica user must be created either manually or automatically, via config. The username in Kinetica must be the @ symbol followed by the LDAP username.
If an external user is granted system administration permissions, the system can be operated & managed exclusively by externally-authenticated users.

Note

Kinetica LDAP integration passes the username & password to LDAP for authentication, and can also map LDAP groups to Kinetica roles. However, the permissions those roles have must be managed within Kinetica.

Below is an example of creating and using an external system administrator account, jdoe.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
try
{
      //Create a system admin role
      gpudb.createRole("sample_system_admin_role",null);

      //Assign permissions to role
      GrantPermissionSystemRequest grantSystemRequest = new GrantPermissionSystemRequest();
      grantSystemRequest.setName("sample_system_admin_role");
      grantSystemRequest.setPermission(GrantPermissionSystemRequest.Permission.SYSTEM_ADMIN);
      gpudb.grantPermissionSystem(grantSystemRequest);

      //Create a new external user and give it the new sample_system_admin_role
      gpudb.createUserExternal("@jdoe",null);
      gpudb.grantRole("sample_system_admin_role", "@jdoe",null);

      //Connect as the external user and create a table
      GPUdb testGPUdb = new GPUdb(PROXY_URL,new GPUdbBase.Options().setUsername("jdoe").setPassword("jdoe"));
      try
      {
         System.out.printf("Creating table as @jdoe\n");
         String typeId = RecordObject.createType(WeatherRecord.class, testGPUdb);
         testGPUdb.addKnownType(typeId, WeatherRecord.class);
         testGPUdb.createTable(TABLE_NAME, typeId, null);
         System.out.printf("created table\n");
      }
      catch(GPUdbException ex)
      {
         System.out.printf("failed to create table:%s\n", ex.getMessage());
      }
      System.out.println("Was the table created? " + tableExists(gpudb,TABLE_NAME));

}
catch (GPUdbException ex)
{
      Logger.getLogger(SecuritySamples.class.getName()).log(Level.SEVERE, null, ex);
}

Global Session Timeout Configuration

Administrators can control the global idle session timeout for several Apache Tomcat web applications included with all Kinetica installations (the applications can be found in /opt/gpudb/tomcat/webapps). To update the timeout:

Open the /opt/gpudb/tomcat/conf/web.xml file in a text editor.
Update the following setting for desired timeout value in minutes (the default is 30):
```
<session-config>
  <session-timeout>30</session-timeout>
</session-config>
```
Tip
Provide a value of 0 or -1 to remove session expiration.
Save the file, and restart the database.