/
view-permissions.sql
77 lines (68 loc) · 1.99 KB
/
view-permissions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
/** Get all views by user **/
select distinct * from (
select
w.name as workbook_name
, v.name as view_name
, regexp_replace(v.repository_url, '/sheets'::text, ''::text) AS view_url
, su.name as user_name
, s.url_namespace as site_url
, s.name as site_name
-- , su.admin_level
from
next_gen_permissions ngp
join system_users su on ngp.grantee_id = su.id
join views v on ngp.authorizable_id = v.id
join capabilities c on ngp.capability_id = c.id
join workbooks w on v.workbook_id = w.id
join sites s on v.site_id = s.id
where
ngp.grantee_type = 'User'
and ngp.authorizable_type = 'View'
and c.name = 'read'
-- and su.name='bsullins'
union all
/** Get views by group access **/
select
w.name as workbook_name
, v.name as view_name
, regexp_replace(v.repository_url, '/sheets'::text, ''::text) AS view_url
, uig.name as user_name
, s.url_namespace as site_url
, s.name as site_name
-- , uig.admin_level
from
next_gen_permissions ngp
join (
SELECT
-- users.id
system_users.name
-- , users.login_at
-- , system_users.friendly_name
-- , users.licensing_role_id
-- , licensing_roles.name AS licensing_role_name
-- , system_users.domain_id
-- , users.system_user_id
-- , domains.name AS domain_name
-- , domains.short_name AS domain_short_name
-- , users.site_id
-- , groups.name AS group_name
, groups.id as group_id
-- , system_users.admin_level
FROM
system_users
join users on users.system_user_id = system_users.id
join licensing_roles on users.licensing_role_id = licensing_roles.id
join domains on system_users.domain_id = domains.id
join group_users on group_users.user_id = users.id
join groups on group_users.group_id = groups.id
) uig on ngp.grantee_id = uig.group_id
join views v on ngp.authorizable_id = v.id
join capabilities c on ngp.capability_id = c.id
join workbooks w on v.workbook_id = w.id
join sites s on v.site_id = s.id
where
ngp.grantee_type = 'Group'
and ngp.authorizable_type = 'View'
and c.name = 'read'
-- and uig.name='bsullins'
) as tbl