-- 确认当前盘点环境和级别
SELECT
'当前盘点级别' as check_item,
'空间级盘点' as level,
current_workspace() as workspace_scope,
'仅可见当前工作空间数据' as visibility_limit,
'如需全局视图请使用管理控制台' as suggestion;
步骤 2：用户角色分布分析
-- 分析用户的角色分配情况（已脱敏）
SELECT
CASE
WHEN user_name LIKE 'admin%' THEN 'admin_user_' || ROW_NUMBER() OVER (ORDER BY user_name)
WHEN user_name LIKE 'dev%' THEN 'dev_user_' || ROW_NUMBER() OVER (ORDER BY user_name)
ELSE 'user_' || ROW_NUMBER() OVER (ORDER BY user_name)
END as masked_user,
role_names,
LENGTH(role_names) - LENGTH(REPLACE(role_names, ',', '')) + 1 as role_count,
CASE
WHEN role_names LIKE '%system_admin%' THEN '包含实例级角色'
WHEN role_names LIKE '%workspace_admin%' THEN '空间管理员'
WHEN role_names LIKE '%workspace_dev%' THEN '开发人员'
ELSE '普通用户'
END as user_category
FROM information_schema.users
ORDER BY role_count DESC;
步骤 3：角色使用情况分析
-- 分析角色的分配和使用情况
SELECT
role_name,
CASE
WHEN role_name LIKE 'instance_%' THEN '实例级角色（在空间级视图中）'
WHEN role_name = 'system_admin' THEN '实例级角色（即将下线）'
WHEN role_name LIKE 'workspace_%' THEN '空间级-系统预置'
ELSE '空间级-自定义'
END as role_classification,
CASE
WHEN user_names IS NULL OR user_names = '' THEN '⚠️ 未分配'
ELSE '✅ 已分配'
END as assignment_status,
CASE
WHEN user_names IS NOT NULL THEN
LENGTH(user_names) - LENGTH(REPLACE(user_names, ',', '')) + 1
ELSE 0
END as assigned_user_count,
comment as description
FROM information_schema.roles
ORDER BY
CASE WHEN user_names IS NULL THEN 0 ELSE 1 END DESC,
role_name;
-- 检测潜在的权限冗余（需验证角色间是否真的存在继承关系）
WITH permission_check AS (
SELECT
CASE
WHEN user_name LIKE 'admin%' THEN 'admin_user'
WHEN user_name LIKE 'dev%' THEN 'dev_user'
ELSE 'regular_user'
END as user_type,
role_names,
CASE
WHEN role_names LIKE '%system_admin%'
AND role_names LIKE '%workspace_%'
THEN '可能存在跨级别冗余（需验证）'
WHEN role_names LIKE '%workspace_admin%'
AND role_names LIKE '%workspace_user%'
THEN '可能存在同级别冗余（需验证）'
ELSE '表面无冗余'
END as redundancy_check,
'建议：实际测试各角色权限以确认是否真的冗余' as action_needed
FROM information_schema.users
)
SELECT * FROM permission_check
WHERE redundancy_check != '表面无冗余';
步骤 5：生成盘点报告
-- 生成空间级权限盘点摘要
WITH summary AS (
SELECT
COUNT(DISTINCT user_name) as total_users,
COUNT(DISTINCT role_name) as total_roles,
SUM(CASE WHEN user_names IS NOT NULL THEN 1 ELSE 0 END) as assigned_roles,
SUM(CASE WHEN user_names IS NULL THEN 1 ELSE 0 END) as unassigned_roles
FROM information_schema.users
CROSS JOIN information_schema.roles
)
SELECT
'===== 空间级权限盘点报告 =====' as report_header,
current_workspace() as workspace_name,
total_users || ' 个用户' as user_summary,
total_roles || ' 个角色（' || assigned_roles || ' 已分配，' || unassigned_roles || ' 未分配）' as role_summary,
ROUND(assigned_roles * 100.0 / total_roles, 1) || '%' as role_utilization,
'注意：本报告仅包含当前工作空间数据' as important_notice
FROM summary;
-- 创建空间级权限监控视图
CREATE VIEW workspace_permission_monitor AS
SELECT
current_date() as monitor_date,
current_workspace() as workspace,
'空间级监控' as monitor_level,
COUNT(DISTINCT u.user_name) as user_count,
COUNT(DISTINCT r.role_name) as role_count,
SUM(CASE WHEN r.user_names IS NOT NULL THEN 1 ELSE 0 END) as active_roles,
ROUND(
SUM(CASE WHEN r.user_names IS NOT NULL THEN 1 ELSE 0 END) * 100.0 /
COUNT(DISTINCT r.role_name),
1
) as role_usage_percentage,
COUNT(DISTINCT CASE WHEN u.role_names LIKE '%system_admin%' THEN u.user_name END) as admin_users
FROM information_schema.users u
CROSS JOIN information_schema.roles r
GROUP BY workspace;
7.2 定期检查脚本
-- 每月执行的权限健康检查
WITH health_check AS (
-- 检查1: 未使用的角色
SELECT
'unused_roles' as check_type,
COUNT(*) as issue_count,
'存在未分配的角色' as description
FROM information_schema.roles
WHERE user_names IS NULL OR user_names = ''
UNION ALL
-- 检查2: 可能的权限冗余
SELECT
'potential_redundancy' as check_type,
COUNT(*) as issue_count,
'用户可能存在角色冗余' as description
FROM information_schema.users
WHERE LENGTH(role_names) - LENGTH(REPLACE(role_names, ',', '')) + 1 > 2
UNION ALL
-- 检查3: 高权限用户
SELECT
'high_privilege_users' as check_type,
COUNT(*) as issue_count,
'拥有admin权限的用户' as description
FROM information_schema.users
WHERE role_names LIKE '%admin%'
)
SELECT
check_type,
issue_count,
description,
CASE
WHEN issue_count > 0 THEN '需要关注'
ELSE '正常'
END as status
FROM health_check
ORDER BY issue_count DESC;
八、总结与建议
8.1 核心要点
明确盘点级别：始终清楚您是在进行空间级还是实例级盘点
理解局限性：空间级盘点无法看到全局情况，重大决策需要实例级视角
验证假设：不要假设角色间的继承关系，需要实际验证
善用工具：优先使用 Studio Web 界面进行日常管理，SQL 用于批量操作
持续优化：建立定期审计机制，持续改进权限管理
8.2 行动建议
立即行动：
登录 Studio 查看当前权限分布
使用本指南的查询语句进行首次空间级盘点
短期计划：
在 Studio 中设置权限变更审批流程
建立月度权限审计机制
长期目标：
实现自动化权限监控和优化
建立跨工作空间的权限标准化体系
8.3 获取帮助
如需进行实例级权限审计或有其他问题，请：
在 Studio 中查看帮助文档
联系您的云器 Lakehouse 管理员
查阅云器 Lakehouse 官方文档
提交技术支持工单
附录：快速参考卡
任务
空间级方法
实例级方法
查看所有用户
Studio → 工作空间 → 用户<br>或 SELECT * FROM information_schema.users
