通过hive元数据库查询每个表权限信息，每个用户权限信息

1、以表为单位，查询每个表具有什么权限select s.*,(select tbl_name from tbls where tbl_id=s.tbl_id) tbl_name,(select name from dbs where db_id=(select db_id from tbls where tbl_id=s.tbl_id)) db_name from (select principa

王富贵儿、

5186人浏览 · 2022-03-21 11:22:43

王富贵儿、 · 2022-03-21 11:22:43 发布

1、以表为单位，查询每个表具有什么权限

select s.*,(select tbl_name from tbls where tbl_id=s.tbl_id) tbl_name,(select name from dbs where db_id=(select db_id from tbls where tbl_id=s.tbl_id)) db_name from (select principal_name,principal_type,tbl_id,string_agg(tbl_priv,',') from tbl_privs group by principal_name,principal_type,tbl_id) s;

2、以账号为单位，查询每隔账号有哪些表的权限

select PRINCIPAL_NAME,PRINCIPAL_TYPE,substr(string_agg(db_name||'.'||tbl_name,','),1,100)m from (select s.*,(select tbl_name from tbls where tbl_id=s.tbl_id) tbl_name,(select name from dbs where db_id=(select db_id from tbls where tbl_id=s.tbl_id)) db_name from (select principal_name,principal_type,tbl_id from tbl_privs group by principal_name,principal_type,tbl_id) s)tt group by PRINCIPAL_NAME,PRINCIPAL_TYPE ；