Version:

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:

  1. The call to show_security with the user or role name for which security information will be retrieved
  2. 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:

  1. The call to show_security with the user or role name for which table-level security information will be retrieved
  2. The extraction of the permissions security field from the returned structure, where the array of per-table security information is kept
  3. The indexing into the permissions array at position 0, as this example only extracts table security for the first table in the list
  4. 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.
  • Kinetica does not import users from LDAP. For each LDAP user you want to use in Kinetica, you must create a corresponding Kinetica user. The username in Kinetica must be the @ symbol followed by the LDAP username.
  • You will first need to connect as an internal user with system administration permissions in order to create an external user. If you create an external user with system administration permissions, all future work can be done with LDAP users.

Note

Kinetica LDAP integration only uses the username, not LDAP groups. Therefore, all role management of users, regardless of internal or external, must be done within Kinetica.

Below is an example using the external jdoe account that gets added to LDAP by a LDAP initialization script.

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:

  1. Open the /opt/gpudb/tomcat/conf/web.xml file in a text editor.

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

  3. Save the file, and restart the database.