[
https://issues.apache.org/jira/browse/SENTRY-1967?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruslan Dautkhanov updated SENTRY-1967:
--------------------------------------
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.
{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(...)
was:
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 -m 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
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}
Hint: test output before you run.. but we actually had to run this in
production (with Oracle backend but should work the same way with other sql
dialects).
> 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: docuentation
>
> 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)
