[SENTRY-1967] query to regenerate hdfs ACLs

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.8.0, 2.0.0
• Component/s: Docs
• Labels: documentation
• Assignee: Unassigned
• Reporter: Ruslan Dautkhanov
• Created: 27/Sep/17 03:29
• Updated: 05/Oct/17 04:57

Description

A Confluence wiki page might be a better place for the below query? cc akolb

We used below query to temporarily workaround broken Sentry HDFS plugin.
It generates set of hdfs dfs -setfacl -m group: ... commands that produce ACLs similar to what Sentry HDFS plugin generates.

SELECT 'hdfs dfs -setfacl '
      ||case when row_type='-R' then '-R ' end
      ||'-m '
      ||case when row_type='default' then 'default:' end
      ||'group:'||g.group_name||':'||
        CASE dp.action    
            WHEN 'all'    THEN 'rwx'
            WHEN '*'      THEN 'rwx'
            WHEN 'select' THEN 'r-x'
            WHEN 'insert' THEN '-wx'
        END
      ||' /hivewarehouse/'
      ||CASE dp.db_name
            WHEN 'default' THEN ''
            ELSE             dp.db_name||'.db'
        END
      ||'/'
      ||CASE dp.PRIVILEGE_SCOPE 
            WHEN 'DATABASE' THEN ''
            WHEN 'TABLE'    THEN dp.table_name || '/'
        END
      as hdfs_dfs_command
FROM SENTRY_DB_PRIVILEGE dp
   , SENTRY_ROLE_DB_PRIVILEGE_MAP m 
   , SENTRY_ROLE r
   , SENTRY_ROLE_GROUP_MAP rgm
   , SENTRY_GROUP g
   , (select '-R' as row_type from dual
      union all
      select 'default' from dual
     ) duplicator
WHERE DP.DB_PRIVILEGE_ID=M.DB_PRIVILEGE_ID
  AND M.ROLE_ID=R.ROLE_ID
  AND RGM.ROLE_ID=R.ROLE_ID 
  AND RGM.GROUP_ID=G.GROUP_ID
  AND DP.PRIVILEGE_SCOPE IN ('DATABASE', 'TABLE', 'COLUMN')
  AND DP.DB_NAME NOT LIKE 'file:%'
  ;

Update 10/4: added -R and `default:` ACL, because of -R, this script can run very slow on very big hivewarehouse directories with many tables/ many underlying hdfs directories/ files.

ps. This query is oracle-specific. To port to other databases, remove "from dual" and change || to concat(...)