<commit_before>-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

-- Some handy views for admins viewing security tables, NOT used by the app

-- View to show all the app/doc permissions for a user/group including ones
-- inherited from group memberships
create or replace view v_permission as
select
    su.uuid,
    su.name,
    su.is_group,
    null via_group,
    null doc_name,
    null doc_type,
    ap.permission,
    'App' permission_type
from app_permission ap
inner join stroom_user su on su.uuid = ap.user_uuid
union
select
    su_user.uuid,
    su_user.name,
    su_user.is_group,
    su_grp.name via_group,
    null doc_name,
    null doc_type,
    ap.permission,
    'App' permission_type
from app_permission ap
inner join stroom_user su_grp on su_grp.uuid = ap.user_uuid
inner join stroom_user_group sug on su_grp.uuid = sug.group_uuid
inner join stroom_user su_user on su_user.uuid = sug.user_uuid
union
select
    su.uuid,
    su.name,
    su.is_group,
    null via_group,
    d.name doc_name,
    d.type doc_type,
    dp.permission,
    'Document' permission_type
from doc_permission dp
inner join stroom_user su on su.uuid = dp.user_uuid
inner join doc d on d.uuid = dp.doc_uuid
where d.ext = 'meta'
union
select
    su_user.uuid,
    su_user.name,
    su_user.is_group,
    su_grp.name via_group,
    d.name doc_name,
    d.type doc_type,
    dp.permission,
    'Document' permission_type
from doc_permission dp
inner join stroom_user su_grp on su_grp.uuid = dp.user_uuid
inner join stroom_user_group sug on su_grp.uuid = sug.group_uuid
inner join stroom_user su_user on su_user.uuid = sug.user_uuid
inner join doc d on d.uuid = dp.doc_uuid
where d.ext = 'meta';

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set tabstop=4 shiftwidth=4 expandtab:<commit_msg>gh-2942 Remove doc tbl from v_permission<commit_after>-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

-- Some handy views for admins viewing security tables, NOT used by the app

-- View to show all the app/doc permissions for a user/group including ones
-- inherited from group memberships
-- Can't join to doc to get the name as the table is in another module and therefore
-- potentially in a different db to this view.
create or replace view v_permission as
select
    su.uuid user_or_group_uuid,
    su.name user_or_group_name,
    su.is_group,
    null via_group,
    null doc_uuid,
    ap.permission,
    'App' permission_type
from app_permission ap
inner join stroom_user su on su.uuid = ap.user_uuid
union
select
    su_user.uuid user_or_group_uuid,
    su_user.name user_or_group_name,
    su_user.is_group,
    su_grp.name via_group,
    null doc_uuid,
    ap.permission,
    'App' permission_type
from app_permission ap
inner join stroom_user su_grp on su_grp.uuid = ap.user_uuid
inner join stroom_user_group sug on su_grp.uuid = sug.group_uuid
inner join stroom_user su_user on su_user.uuid = sug.user_uuid
union
select
    su.uuid user_or_group_uuid,
    su.name user_or_group_name,
    su.is_group,
    null via_group,
    dp.doc_uuid,
    dp.permission,
    'Document' permission_type
from doc_permission dp
inner join stroom_user su on su.uuid = dp.user_uuid
inner join doc d on d.uuid = dp.doc_uuid
where d.ext = 'meta'
union
select
    su_user.uuid user_or_group_uuid,
    su_user.name user_or_group_name,
    su_user.is_group,
    su_grp.name via_group,
    dp.doc_uuid,
    dp.permission,
    'Document' permission_type
from doc_permission dp
inner join stroom_user su_grp on su_grp.uuid = dp.user_uuid
inner join stroom_user_group sug on su_grp.uuid = sug.group_uuid
inner join stroom_user su_user on su_user.uuid = sug.user_uuid;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set tabstop=4 shiftwidth=4 expandtab:
{'fraction_non_alphanumeric': 0.06664917344528995, 'fraction_numerical': 0.0026239832065074785, 'mean_word_length': 3.7412935323383083, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1225929', 'n_tokens_mistral': 1307, 'n_tokens_neox': 1308, 'n_words': 411}