【 OB or 其他组件 】
【 使用版本 】2.x
【问题描述】请问有没有SQL可以直接查出租户下的所有用户的所有权限（库、表、存储过程、列）
SELECT
u.user_name AS user_name,
u.host AS user_host,
u.priv_super,
u.priv_create,
u.priv_select,
u.priv_insert,
u.priv_update,
u.priv_delete,
ru.user_name AS granted_role_name,
ru.priv_create AS role_priv_create,
ru.priv_select AS role_priv_select,
ru.priv_update AS role_priv_update,
ru.priv_insert AS role_priv_insert,
ru.priv_delete AS role_priv_delete,
rm.admin_option,
rm.disable_flag,
dp.database_name AS db_priv_db_name,
dp.priv_select AS db_priv_select,
dp.priv_insert AS db_priv_insert,
dp.priv_update AS db_priv_update,
dp.priv_delete AS db_priv_delete,
dp.priv_create AS db_priv_create,
tp.table_name AS table_priv_table_name,
db.database_name AS table_belong_db_name,
atable.table_type,
atable.load_type,
tp.priv_select AS table_priv_select,
tp.priv_insert AS table_priv_insert,
tp.priv_update AS table_priv_update,
tp.priv_delete AS table_priv_delete,
tp.priv_create AS table_priv_create,
cp.column_name,
cp.all_priv AS column_all_priv,
rp.routine_name,
rp.routine_type,
rp.all_priv AS routine_all_priv
FROM
__all_user u
LEFT JOIN __all_tenant_role_grantee_map rm
ON u.tenant_id = rm.tenant_id AND u.user_id = rm.grantee_id
LEFT JOIN __all_user ru
ON rm.tenant_id = ru.tenant_id AND rm.role_id = ru.user_id AND ru.type = 1
LEFT JOIN __all_database_privilege dp
ON u.tenant_id = dp.tenant_id AND u.user_id = dp.user_id
LEFT JOIN __all_table_privilege tp
ON u.tenant_id = tp.tenant_id AND u.user_id = tp.user_id
LEFT JOIN __all_table atable
ON tp.tenant_id = atable.tenant_id AND tp.table_name = atable.table_name
LEFT JOIN __all_database db
ON atable.tenant_id = db.tenant_id AND atable.database_id = db.database_id
LEFT JOIN __all_column_privilege cp
ON u.tenant_id = cp.tenant_id AND u.user_id = cp.user_id
LEFT JOIN __all_routine_privilege rp
ON u.tenant_id = rp.tenant_id AND u.user_id = rp.user_id
WHERE
u.type = 0 and u.user_name=‘user1’
ORDER BY
u.user_name, db.database_name, tp.table_name, cp.column_name\G
这个在4.x还能执行，不过显示的好难看 
，如果一部分一部分查感觉又有点太麻烦了
不好意思 2.x的版本很低了 开源确实也没有这个版本 建议使用ob435和ob425的版本
1 个赞