[
https://issues.apache.org/jira/browse/SENTRY-1967?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16192461#comment-16192461
]
Alexander Kolbasov commented on SENTRY-1967:
--------------------------------------------
Yes, I think we should have a confluence page with tips and tricks - I'll
create something and put your script there.
I have a similar script but it is written in Python and works not directly with
DB but with dump of tables in Excel format, I'll post it there as well. It does
similar joins programmatically.
> query to regenerate hdfs ACLs
> -----------------------------
>
> Key: SENTRY-1967
> URL: https://issues.apache.org/jira/browse/SENTRY-1967
> Project: Sentry
> Issue Type: Improvement
> Components: Docs
> Affects Versions: 1.8.0, 2.0.0
> Reporter: Ruslan Dautkhanov
> Labels: documentation
>
> 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.
> {code:sql}
> 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:%'
> ;
> {code}
> 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(...)
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
