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:

h_db = gpudb.GPUdb(
    host = kinetica_host,
    port = "9191",
    username = 'auser',
    password = 'password',
    encoding = "BINARY"
)

To connect in Java:

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:

h_db.create_user_internal(
    name = 'auser',
    password = 'password'
)

To create an external user, ruser, in Python:

h_db.create_user_external(
    name = 'ruser'
)

Deleting Users¶

To delete the internal user, auser, in Python:

h_db.delete_user('auser')

To delete the external user, ruser, in Python:

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:

h_db.create_role('rx_user')

Assigning Roles¶

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

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

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

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

Unassigning Roles¶

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

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

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

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

Deleting Roles¶

To delete a role, rx_user, in Python:

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.

Managing System Permissions¶

System-level 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 user, auser, in Python:

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

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

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

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

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

Revoking System Permissions¶

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

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

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

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

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

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

Managing Table Permissions¶

Table-level 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 table, rx_order, to the user, zanalyst, in Python:

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

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

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

Note

Specifying an empty table name grants the specified access to all tables for the specified user

Revoking Table Permissions¶

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

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

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

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

Note

Specifying an empty table name revokes the specified access to all tables for the specified user

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 table rx_order
for records with order timestamps on or after 2002-01-01
to user zanalyst

h_db.grant_permission_table(
    'zanalyst',
    'table_read',
    '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 table rx_order
for records associated with the querying user, matched by username
to role rx_user

h_db.grant_permission_table(
    'rx_user',
    'table_read',
    '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 table rx_order
for any/all granted rows (row-level access to a table must be revoked by revoking access to the full table)
from user zanalyst

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

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

table read permission (table_read)
on table rx_order
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

h_db.revoke_permission_table(
    'rx_user',
    'table_read',
    '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 table rx_order
for columns rx_name, order_ts, and an obfuscated version of ssn
to user zanalyst

h_db.grant_permission_table(
    'zanalyst',
    'table_read',
    '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 table rx_order
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

h_db.grant_permission_table(
    'rx_user',
    'table_read',
    '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 table rx_order
for obfuscated column ssn (other granted columns will remain visible)
from user zanalyst

h_db.revoke_permission_table(
    'zanalyst',
    'table_read',
    'rx_order',
    options = {"columns": "ssn"}
)

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

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

h_db.revoke_permission_table(
    'rx_user',
    'table_read',
    'rx_order',
    options = {"columns": "ssn, rx_name"}
)

Managing Procedure Permissions¶

Procedure-level 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 user, auser, in Python:

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

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

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 user, auser, in Python:

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

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

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.

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¶

A user's or role's permission & role grants can be retrieved, via Python API, and displayed within a table produced by tabulate.

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

role_security = h_db.show_security([user_role_name])

data = [OrderedDict([
    ('Type', role_security['types'].get(user_role_name)),
    ('Roles', role_security['roles'].get(user_role_name)),
    ('Permissions', role_security['permissions'].get(user_role_name))
])]

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

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

+---------------+-------------------------------+--------------------------------------------------------------+
| Type          | Roles                         | Permissions                                                  |
+===============+===============================+==============================================================+
| internal_user | [u'authenticated', u'public'] | [{u'table_name': u'rx_order', u'permission': u'table_read'}] |
+---------------+-------------------------------+--------------------------------------------------------------+

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 indexing into the permissions array at position 0, as this example only extracts table security for the first table in the list
The extraction of the key security fields for the first 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

role_security = h_db.show_security([user_role_name])

data = role_security['permissions'].get(user_role_name)

if data:
    data[0] = OrderedDict([
        ('Table Name', data[0]['table_name']),
        ('Permission', data[0]['permission']),
        ('Column List', data[0].get('columns', [])),
        ('Filter Expression', data[0]['filter_expression'])
    ])

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

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

+--------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| Table Name   | Permission   | Column List                                                                                                                                                                                                |   Filter Expression |
+==============+==============+============================================================================================================================================================================================================+=====================+
| rx_order     | table_read   | [["HASH(ssn)","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'"]] |                   0 |
+--------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+

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.

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.

Table Of Contents

Security Usage¶

Authentication¶

User Management¶

Creating Users¶

Deleting Users¶

Role Management¶

Creating Roles¶

Assigning Roles¶

Unassigning Roles¶

Deleting Roles¶

Permission Management¶

Managing System Permissions¶

Granting System Permissions¶

Revoking System Permissions¶

Managing Table Permissions¶

Granting Table Permissions¶

Revoking Table Permissions¶

Managing Table Permissions at the Row Level¶

Granting Row-Level Table Permissions¶

Revoking Row-Level Table Permissions¶

Managing Table Permissions at the Column Level¶

Granting Column-Level Table Permissions¶

Revoking Column-Level Table Permissions¶

Managing Procedure Permissions¶

Granting UDF Permissions¶

Revoking UDF Permissions¶

Security Information Retrieval¶

Basic Security Information¶

Table-Level Security Information¶

External (LDAP) Users¶

Global Session Timeout Configuration¶