Tagging
Tags can be used to classify views or columns in Denodo to make browsing easier, or for application of security policies.
Here is a guide to Tags in Denodo: https://community.denodo.com/kb/en/view/document/How%20to%20use%20Denodo%20Tags
Note that in order for new tags to appear in the Data Catalog, they must be manually synced once in each Data Catalog environment after they’re created, after which they will update automatically as changes are made and deployed.
Security
Global Security Policies in Denodo allow masking and filtering of columns and rows based on user-assigned roles and tags.
Here is a guide to Global Security Policies in Denodo: https://community.denodo.com/docs/html/browse/9.0/en/vdp/administration/global_security_policies/global_security_policies
Column-level Security (CLS)
While data classification is still a work in progress at Tufts, below is a table of some draft classifications.
Generally Available | Confidential | Restricted |
All data access roles (not public) | Dean/Division Leader Role | Reporting and Compliance Roles (University-wide access as needed) Others must provide justification for use |
Identified data | Reporting and Compliance Roles (University-wide access) | |
•Name •Title (fac/staff) / Level (students) •HR Title (fac/staff) •Department / Program •Supervisor •Campus •Pronouns (user provided) •Email address •Affiliations (faculty/staff/student/...) •Classification (temp, post doc, RA, staff/grade, faculty) | •Compensation •Course registrations •Age range (..., 25-35, 36-45, …) •Financial transaction data •Leave/return dates •Service dates •Space assignments •Grant proposals and awards •Enrollment status •Home address •Local address •Alum/donor name/contact info •Citizenship | •Race/ethnicity •Gender identity •Religion •PHI •Marital status •Date of birth •Benefit selections •Admissions decisions (before release) •Grades •Alum/donor gift history •Social security number •Driver’s license number •Passport number |
Tags, policies and roles have been added for Confidential and Restricted in the dev environment for testing. Ultimately, roles will exist in Grouper where their membership can be managed.
Confidential
VQL
CREATE OR REPLACE ROLE confidential ''
GRANT CONNECT, METADATA, EXECUTE ON test;
CREATE OR REPLACE TAG confidential
DESCRIPTION = 'Used to tag views or columns that fall under the Confidential data definition';
CREATE OR REPLACE GLOBAL_SECURITY_POLICY mask_confidential_columns
DESCRIPTION = 'Masks all columns with the confidential tag for non-admins without the confidential role'
ENABLED = TRUE
AUDIENCE (
NOT_IN ROLES (confidential, "grp_ds_denodo-admin")
)
ELEMENTS (
ALL VIEWS
)
RESTRICTION (
FILTER = ''
MASKING ANY (confidential) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT)
);
Restricted
VQL
CREATE OR REPLACE ROLE restricted ''
GRANT ROLE confidential
GRANT CONNECT, METADATA, EXECUTE ON test;
CREATE OR REPLACE TAG restricted
DESCRIPTION = 'Used to tag views or columns whose data fall under the Restricted definition'
CREATE OR REPLACE GLOBAL_SECURITY_POLICY mask_restricted_columns
DESCRIPTION = 'Masks all columns that have the restricted tag for non-admins without the restricted role.'
ENABLED = TRUE
AUDIENCE (
NOT_IN ROLES ("grp_ds_denodo-admin", restricted)
)
ELEMENTS (
ALL VIEWS
)
RESTRICTION (
FILTER = ''
MASKING ANY (restricted) WITH (HIDE) (numbers WITH DEFAULT, datetimes WITH DEFAULT, texts WITH DEFAULT)
);
This can be expanded upon by leveraging user attributes from LDAP/SAML: https://community.denodo.com/kb/en/view/document/How%20to%20implement%20ABAC%20Capabilities%20with%20Global%20Security%20Policies
Row-level Security (RLS)
In addition to column-level security, you can employ row-level security and limit the rows users can see.
VQL
CREATE OR REPLACE ROLE data_strategy ''
GRANT CONNECT, METADATA, EXECUTE ON test;
CREATE OR REPLACE ROLE iam ''
GRANT CONNECT, METADATA, EXECUTE ON test;
CREATE OR REPLACE ROLE read_all ''
GRANT CONNECT, METADATA, EXECUTE ON test;
CREATE OR REPLACE TAG rls_dept
DESCRIPTION = 'Used to tag views that should have RLS applied based on department';
CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_data_strategy
DESCRIPTION = 'RLS department policy for data strategy'
ENABLED = TRUE
AUDIENCE (
ANY ROLES (data_strategy)
)
ELEMENTS (
COLUMNS TAGGED ANY (rls_dept)
)
RESTRICTION (
FILTER = 'rls_dept = ''DS'''
REJECT
);
CREATE OR REPLACE GLOBAL_SECURITY_POLICY rls_dept_iam
DESCRIPTION = 'RLS department policy for data strategy'
ENABLED = TRUE
AUDIENCE (
ANY ROLES (iam)
)
ELEMENTS (
COLUMNS TAGGED ANY (rls_dept)
)
RESTRICTION (
FILTER = 'rls_dept = ''IAM'''
REJECT
);
This can be expanded upon by leveraging user attributes from LDAP/SAML: https://community.denodo.com/kb/en/view/document/How%20to%20implement%20ABAC%20Capabilities%20with%20Global%20Security%20Policies
Demo
A test view test.security_test test has been created in the test VDB.
CREATE OR REPLACE VIEW security_test FOLDER = '/02-transformation'
(
dept tags (rls_dept)
hire_dt TAGS (confidential),
termination_dt TAGS (confidential),
annual_rt TAGS (confidential),
date_of_birth TAGS (restricted),
gender TAGS (restricted),
race TAGS (restricted)
)
AS
with st_union as
(
SELECT '0001' AS emplid,
'John Doe' AS name,
'DS' AS dept,
cast('2024-11-01' AS TIMESTAMP) AS hire_dt,
cast('2024-11-02' AS TIMESTAMP) AS termination_dt,
100000.0 AS annual_rt,
cast('1990-01-01' AS DATE) AS date_of_birth,
'Male' AS gender,
'Unknown' AS race
FROM dual()
UNION ALL
SELECT '0002' AS emplid,
'John Doe' AS name,
'IAM' AS dept,
cast('2024-11-01' AS TIMESTAMP) AS hire_dt,
cast('2024-11-02' AS TIMESTAMP) AS termination_dt,
100000.0 AS annual_rt,
cast('1990-01-01' AS DATE) AS date_of_birth,
'Male' AS gender,
'Unknown' AS race
FROM dual()
)
select * from st_union CONTEXT ('formatted' = 'yes');
The queries below demonstrate combinations of CLS and RLS
General
select * from test.security_test context('impersonate_roles' = 'read_all');
Confidential
select * from test.security_test context('impersonate_roles' = 'read_all,confidential');
Restricted
Note that the confidential role has been added to restricted so it can satisfy any confidential policy restriction as well
select * from test.security_test context('impersonate_roles' = 'read_all,restricted');
Department RLS
select * from test.security_test context('impersonate_roles'='data_strategy');
Department RLS Unmasked
Caching
Denodo applies policies on top of cached data. Since policies do not apply to Administrator user types, users with the serveradmin role, or admins of individual VDB’s, it’s recommended that an admin user be used for caching jobs since this will guarantee that no policies are applied during caching.
Tableau
Using Denodo
Most use cases should be able to use Denodo security by prompting users for credentials to connect to a Denodo data source in Tableau. This can be set when publishing a workbook to the Tableau server.
Now when trying to access that workbook, users will be prompted for their credentials and Denodo will apply RLS based on their privileges
Note that if you have multiple data sources, users will be prompted for credentials for each data source. Thankfully Tableau can remember passwords for each user and data source and it’s recommended that users do this.
The above approach does require that data sources be Live. Because of this, all views should be optimized and cached to ensure adequate performance.
Using Tableau User Functions
In the rare case where you need to implement security within Tableau, you can do so by filtering based on usernames, or an individual AD group.
Users
If you have a column named userswith comma separated user names like jdoe01,jdoe02,jdoe03 you check if a user existing in the like by creating a calculated field with the following formula:
CONTAINS([users], USERNAME())
You can then create filter on your data for where this is True.
AD Group
If you have a column named group with a single AD group you can check if a user is a member by creating a calculated field with the formula:
ISMEMBEROF([group])
You can then create filter on your data for where this is True.
Tableau does not currently have an easy way to check for membership of multiple groups.
Other Tableau User Function can be found here: https://help.tableau.com/current/pro/desktop/en-us/functions_functions_user.htm