Implementing CFS

After Vertica Analytic Database ingests documents from IDOL, you can implement CFS to secure those documents. Implementing the security requires action from both the Vertica database administrator and the user who runs queries.

The database administrator must:

Upon CFS implementation, the user can Query the IDOL Data:

Modify the CFS Configuration File

Set the following in the CFS configuration file to have CFS automatically index the metadata:

  1. In the [Indexing] section, set the IndexerSections parameter to vertica:
    [Indexing] IndexerSections=vertica IndexBathSize=1 IndexTimeInterval=30
  1. Create a new section with the same name you entered in the IndexerSections parameter and enter the following parameters and keywords:
    [vertica] IndexerType=Library ConnectionString=Driver=Vertica;Server=123.456.478.900;Databaswe=myDb;UID=dbadmin;PWD=password TableName=myFlexTable LibraryDirectory= ./shared_library_indexers LibraryName=VerticaIndexer

The verticaIndexer (LibraryName above) is part of CFS. To use this tool,you must install and configure the Vertica ODBC drivers on the same machine as CFS. CFS sends JSON-formatted data to the Flex table using ODBC. For more information, see Installing ODBC Drivers on Linux, Solaris, AIX, and HP-UX.

Create a View

The Vertica database administrator must create a view from the flex table where the ingested IDOL data resides. Users querying IDOL data do so using this view, thus preventing unauthorized access to the flex table.

This example creates a view from the idol_table:

=> CREATE VIEW idol_view as select * from idol_table where v_idol.idol_check_acl(acl, security_section, security_type); CREATE VIEW

You must then run GRANT privileges as follows:

  1. Grant usage on the v_idol schema:

    => GRANT USAGE on schema v_idol to user1; GRANT PRIVILEGE
  1. Grant SELECT privileges on the view to any user who needs access:

    => GRANT SELECT on idol_view to user1; GRANT PRIVILEGE
  1. Grant usage on the library. This allows you to set UDSession parameters later:

    => GRANT USAGE on library v_idol.IdolLib to user1; GRANT PRIVILEGE
  1. Grant execute privileges on the acl-checking function.

    => GRANT EXECUTE on function v_idol.idol_check_acl(long varchar, long varchar, long varchar) to user1; GRANT PRIVILEGE

    Important: When granting EXECUTE privileges on a CFS function, ensure that you only grant the privilege to v_idol.idol_check_acl(). Never grant EXECUTE privileges to the following CFS functions:

    v_idol.install_community_key()
    v_idol.describe_community_key()
    v_idol.delete_community_key()

Install the Security Key

Before a user can access the view containing IDOL data, the database administrator must retrieve and install the security key. Store the security key in Vertica's Distributed File System (DFS) with the following command:

=> SELECT v_idol.install_community_key(USING PARAMEERS file_path='/home/user1/Downloads/IDOL/cli/aes.txt');

The Security Key is the same one used in IDOL. You can only install one key at a time. Installing another key overwrites the existing one.

The SecurityInfoKeys parameter in the following IDOL configuration files indicates the key you need to install:

After installing the key, run the following command to ensure it was correctly installed :

=> SELECT v_idol.describe_community_key(); describe_community_key --------------------------------------------------------------------- daf3cc7d3c9368c2e78ed336aec6d8e75ee038c47ajc76f86a3bb5b197639ed43725a (1 row)

See DELETE_COMMUNITY_KEY for information on deleting security keys.

Verify CFS implementation success

As a database administrator, do the following to verify the successful implementation of CFS:

  1. Retrieve the Security Information String (SIS) from your organization's application. This is accomplished outside of Vertica.
  2. Set the retrieved SIS for your current session:
    => ALTER SESSION set UDPARAMETER FOR v_idol.IdolLib IdolSecurityInfo = 'MzA0U08/+T59PHj6Pn64v/m8A';
  1. Run the following ACL check function to see if it returns true only for rows to which you should have access:
    => SELECT v_idol.idol_check_acl(acl, security_section, security_type) from cfs_table; idol_check_acl ---------------- f t (2 rows)

    Alternatively you can run the ACL check function and explicitly pass the SIS:

    => SELECT v_idol.idol_check_acl(acl, security_section, security_type using parameters sis='MzA0U08/+T59PHj6Pn64v/m8A') 
    FROM cfs_table;
      idol_check_acl
    ----------------
                   f
                   t
    (2 rows)			

Query the IDOL Data

A user querying IDOL must do the following:

  1. Retrieve the Security Information String (SIS) from your organization's application. This is accomplished outside of Vertica.
  1. Set the retrieved SIS for your current user session:
    => ALTER SESSION set UDPARAMEER FOR v_idol.IdolLib IdolSecurityInfo = 'MzA0U08/+T59PHj6Pn64v/m8A';

This example checks the v_idol.t flex table for the idol access control list, security section, and security type. All these parameters must match the metadata in the flex table.

  1. Run the query:
    => SELECT * FROM idol_view;