Skip to main content

Privacera Documentation

Validate Trino or Starburst Trino with Privacera Platform

  1. Log in to your Trino or Starburst Trino server where it is installed.

  2. Based on your configuration, you can start a Trino or Starburst Trino server.

    Start Trino

    ./trino --user $user --catalog $catalog_name

    For Hive catalogs in Trino server, by default, the privacera_hive service is used for access management. For all other catalogs, the privacera_trino service is used.

    or

    Start Starburst Trino.

    ./trino-cli --user $user --catalog $catalog_name

    For Hive catalogs in Starburst Trino server, by default, the privacera_hive service is used for access management. For all other catalogs, the Starburst Trino service is used.

Verify Trino or Starburst Trino Integration

Run some sample queries to verify whether Trino or Starburst Trino has been properly integrated with Privacera and is ready to use.

Prerequisite

It is assumed that Trino server is configured with Privacera Trino plugin and Redshift catalog.

Create Policies

  1. Create a policy as shown below for displaying metadata related to Redshift catalog.

    trino_policy_redshift_catalog.jpg

  2. For a few catalogs such as PostgreSQL, Redshift, Hive and so on, metadata is stored in an information_schema table. So, create a policy to select on the schema as shown below.

    trino_policy_inform_schema.jpg

Run queries

Create schema

  1. Query to create a new schema.

    CREATE SCHEMA customer_schema;

    The following output is displayed. You will be denied to create a schema since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino> create schema custome_schema; 
                Query failed:AccessDenied:Cannot create schema custome_schema

  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Create permissions for your Trino user under privacera_trino service as shown below.

    trino_policy_create.jpg

  3. Rerun the query to create a new schema.

    CREATE SCHEMA customer_schema;

    The following output is displayed. Now, you will be able to create the schema.

    trino>create schema customer_schema; CREATE SCHEMA

    To verify if the schema has been created, run the following command.

    trino>showschemas;Schema--------------------customer_schema
Create table

  1. Query to create a new table.

    CREATE TABLE IF NOT EXISTS customer_schema.customer_table(idint,person_namevarchar,ssnvarchar,countryvarchar,account_idvarchar);

    The following output is displayed. You will be denied to create a table since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    CREATE TABLE IF NOT EXISTS customer_schema.customer_table(idint,person_namevarchar,ssnvarchar,countryvarchar,account_idvarchar);

Query failed:AccessDenied:Cannot create table customer_table

  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Create permissions for your Trino user under privacera_trino service as shown below.

    trino_policy_create_table.jpg

  3. Rerun the query to create a new table.

    CREATE TABLE IF NOT EXISTS customer_schema.customer_table(idint,person_namevarchar,ssnvarchar,countryvarchar,account_idvarchar);

    The following output is displayed. Now, you will be able to create the table.

    CREATE TABLE IF NOT EXISTS customer_schema.customer_table(idint,person_namevarchar,ssnvarchar,countryvarchar,account_idvarchar);
Insert Data in Table

  1. Query to insert data in the table.

    insert into customer_schema.customer_tablevalues(1,'Nancy','208-95-5535','UK','856-232-9702');

    The following output is displayed. You will be denied to insert data since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino insert into customer_schema.customer_tablevalues(1,'Nancy','208-95-5535','UK','856-232-9702');

Query failed:AccessDenied:Cannotinsertintotablecustomer_table

  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Insert permissions for your Trino user under privacera_trino service as shown below.

    trino_policy_insert_table.jpg

  3. Rerun the query to insert multiple data in the table.

    insert into customer_schema.customer_table values(1,'Nancy','208-95-5535','UK','856-232-9702');insertintocustomer_schema.customer_table values(2,'Genne','242-92-5772','US','661-338-6787');insertintocustomer_schema.customer_table values(3,'Edward','251-91-5542','US','231-338-5789');insert into customer_schema.customer_table values(4,'Pearlene','217-97-5522','UK','708-471-6810');insert into customer_schema.customer_table values(5,'James','263-95-5530','US','209-626-9041');insert into customer_schema.customer_table values(6,'Nancy','281-98-5531','US','303-239-4282');

    The following output is displayed. Now, you will be able to insert data in the table.

    trino insert into customer_schema_t.customer_tablevalues(1,'Nancy','208-95-5535','UK','856-232-9702');
Select data in table

  1. Query to select data in the table.

    select * from customer_schema.customer_table;

    The following output is displayed. You will be denied to select data since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino select * from customer_schema.customer_table;

Query failed:AccessDenied:Cannot select from columns[country,account_id,person_name,id,ssn]in table or view customer_table

  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Select permissions for your Trino user under privacera_trino service as shown below.

    trino_policy_select_table.jpg

  3. Rerun the query to select data in the table.

    SELECT * FROM customer_schema.customer_data;

    The following output is displayed. Now, you will be able to insert data in the table.

    trino:>select* from customer_schema.customer_table;

id|person_name|ssn|country|account_id----+-------------+-------------+---------+--------------1
Nancy|208-95-5535|UK|856-232-97022|
Genne|242-92-5772|US|661-338-67873|
Edward|251-91-5542|US|231-338-57894|
Pearlene|217-97-5522|UK|708-471-68105|
James|263-95-5530|US|209-626-90416|
Nancy|281-98-5531|US|303-239-4282

(6rows)Query,FINISHED,1nodeSplits:17total,17done(100.00%)0.40[6rows,0B][15rows/s,0B/s]
Row-level filtering

Trino supports row-level filtering at a table-level.

  1. Create a row-level policy on the table created above to display rows only with US country.

    trino_policy_row_level_filter.jpg

  2. Query the table to display rows only with the country US.

    trino:customer_schema> select * from customer_table;

    The following output will be displayed.

    trino:customer_schema>select * from customer_table;

id|person_name|ssn|country|account_id----+-------------+-------------+---------+--------------2|
Genne|242-92-5772|US|661-338-67873|
Edward|251-91-5542|US|231-338-57895|
James|263-95-5530|US|209-626-90416|
Nancy|281-98-5531|US|303-239-4282
(4rows)
Column-level masking

Trino supports column-level masking at a table-level.

  1. Create a column-masking policy on the table created above to show only the last 4 digits of the Social Security Number (SSN).

    trino_policy_column_mask.jpg

  2. Query the table to show only the last 4 digits of the Social Security Number (SSN).

    trino:customer_schema> select * from customer_table;

    The following output will be displayed.

    trino:customer_schema> select * from customer_table;

id|person_name|ssn|country|account_id----+-------------+-------------+---------+--------------1|
Nancy|XXXXXXX5535|UK|856-232-97022|Genne|XXXXXXX5772|US|661-338-67873|
Edward|XXXXXXX5542|US|231-338-57894|Pearlene|XXXXXXX5522|UK|708-471-68105|
James|XXXXXXX5530|US|209-626-90416|Nancy|XXXXXXX5531|US|303-239-4282
(6rows)Query,FINISHED
Access views in AWS Athena

Use the following steps to provide access for views created in AWS Athena. As a result, you will be able to query the views.

  1. Copy the Hive catalog properties (or create a symlink) as awsdatacatalog.properties in /etc/catalog folder.

    ln -s etc/catalog/hive.properties etc/catalog/awsdatacatalog.properties

  2. Restart the Trino server.

  3. In Access Management > Resource Policies, update the privacera_hive default policy.

    1. Edit all - database, table policy.

    2. In Select User, add 'Trino' from the dropdown as the default view owner, and save.

  4. (Optional) To change the default view owner from 'Trino' to any other owner such as 'Hadoop', do the following:

    1. In the access-control.properties file, add the owner to the ranger.policy.authorization.viewowner.default variable.

      vi etc/access-control.propertiesranger.policy.authorization.viewowner.default=<view-owner>

    2. Restart the Trino server.

      Accordingly, update the owner in the all - database, table policy of the privacera_hive service.

Ownership Permission

Note

Some Trino connectors do not support COMMENT and SET PROPERTIES commands.

  1. Table level access control provides ownership of tables to users to have permissions to perform the below queries. Without setting the correct permissions, the following output is displayed.

    Query failed: Access Denied: Cannot set properties to postgresql.customer_schema.customer_table

    To verify, you can check the Privacera Portal Audits page.

  2. To create these permissions in Privercera Portal, create a policy with ownership permissions for your Trino user under privacera_trino service as follows:

    • Policy Type: Access

    • Policy Name: Enter a policy name, such as "Ownership for Trino Tables"

    • catalog: postgresql

    • schema: customer_schema

    • table: customer_table

    • column: *

    Under Allow Conditions -> Select User, enter the user name(s) with Permissions Ownership.

Note

The following queries use the following structure for the table value: catalog.schema.table

Query to set table properties

  • ALTER TABLE postgresql.customer_schema.customer_table SET PROPERTIES sorted_by = array['id'];

Query to set table comment

  • COMMENT ON TABLE postgresql.customer_schema.customer_table IS 'PostgreSQL Table';

Query to set column comment

  • COMMENT ON COLUMN postgresql.customer_schema.customer_table.person_name IS 'Person Name';

Query to add column

  • ALTER TABLE postgresql.customer_schema.customer_table ADD COLUMN name2 varchar;

Query to rename column

  • ALTER TABLE postgresql.customer_schema.customer_table RENAME COLUMN name2 to name3;

Query to rename table

  • ALTER TABLE postgresql.customer_schema.customer_table RENAME TO postgresql.customer_schema.customer_table_2;

Query to drop table

  • DROP TABLE postgresql.customer_schema.customer_table;

Use case with Hive connector

Hive policy authorization

You can configure access control on the Hive connector by setting the Hive catalog properties file.

  1. On your Trino server, navigate to the etc folder containing the Hive configuration files.

  2. Run the following command:

    vi etc/catalog/hive.properties

  3. Add the following properties in the Hive catalog to grant permission:

    Property

    Description

    Example

    hive.allow-drop-table

    Set the property to drop the table in the Hive catalog.

    hive.allow-drop-table=true

    hive.allow-add-column

    Set the property to add the column in the table in the Hive catalog.

    hive.allow-add-column=true

    hive.allow-rename-column

    Set the property to rename the column in the table in the Hive catalog.

    hive.allow-rename-column=true

    hive.allow-drop-column

    Set the property to drop the column in the table in the Hive catalog.

    hive.allow-drop-column=true

    hive.allow-rename-table

    Set the property to rename the table in the Hive catalog.

    hive.allow-rename-table=true

  4. Restart the Trino server.

Configure Hive policy authentication

When the Privacera Plugin is deployed in your Trino server, the TRINO_HIVE_POLICY_AUTHZ_ENABLED is set to true by default, allowing you to configure Hive policy authorization.

You can enable/disable the authorization in your Trino server. To configure, do the following:

  1. Go to the Ranger Trino config folder.

  2. Run the following command:

    vi install.properties

  3. Add/Edit the following property. By default, the value is set to true.

    HIVE_POLICY_AUTHZ_ENABLED=true

  4. Run the following command:

    ./enable-trino-plugin.sh

  5. Restart the Trino server.

Configure multiple Hive catalogs

When the Privacera Plugin is deployed in your Trino server, the TRINO_HIVE_POLICY_REPO_CATALOG_MAPPING property allows you to map multiple Hive catalogs.

You can configure Hive catalogs in your Trino server. To configure, do the following

  1. Go to the Ranger Trino config folder.

  2. Run the following command:

    vi install.properties

  3. Add/Edit the following property:

    HIVE_POLICY_REPO_CATALOG_MAPPING=privacera_hive:hive,hive2

    Enter the value for the property in the following format:

    {hive_policy_repo-1}:{comma_separated_hive_catalogs};{hive_policy_repo-2}:{comma_separated_hive_catalogs}

    The format indicates Hive policy repository and Hive catalog mapping. See example below:

    Example: privacera_hive:hivecatalog1,hivecatalog2;privacera_hive_1:hive3,hive4,hive5

  4. Run the following command:

    ./enable-trino-plugin.sh

  5. Restart the Trino server.