{ "metadata": { "kernelspec": { "display_name": "Streamlit Notebook", "name": "streamlit" }, "lastEditStatus": { "notebookId": "nrpfataaglaxrgpuqahi", "authorId": "384200596366", "authorName": "IBURILO", "authorEmail": "igor.burilo.sme@sonymusic-pde.com", "sessionId": "16a8f9a0-76d8-4daf-9bb7-ed98c97f1a12", "lastEditTime": 1754495482805 } }, "nbformat_minor": 5, "nbformat": 4, "cells": [ { "cell_type": "markdown", "id": "9509dc27-8a87-4f6b-a09b-244981e91781", "metadata": { "name": "cell4", "collapsed": false }, "source": "Get users with their roles.\n\nCheck also login info, so we could filter our not active users.\n\nLook only at human users (if this info is available)." }, { "cell_type": "code", "id": "00a386bd-a6c2-46e1-93fd-76de90f62a65", "metadata": { "language": "sql", "name": "cell5" }, "outputs": [], "source": "with users as (\n SELECT \n u.name as user \n , u.email\n , u.type as user_type\n \t, u.CREATED_ON user_created\n , u.disabled\n , case when u.deleted_on is not null then 1 else 0 end as isdeleted\n , u.last_success_login \n , u.user_id\n FROM \n SNOWFLAKE.ACCOUNT_USAGE.USERS u \n WHERE \n u.disabled = false \n and u.deleted_on is null\n and u.last_success_login is not null\n and coalesce(u.type, '') not in ('SERVICE', 'LEGACY_SERVICE')\n), \nusers_roles as (\n select\n u.user\n , u.email\n --, count(1) OVER (PARTITION BY u.user) AS roles_count \n , grants.role\n \t, u.user_created\n , u.last_success_login\n , u.user_id \n from\n users u\n left join snowflake.account_usage.grants_to_users grants on u.user = grants.grantee_name \n where\n grants.deleted_on IS NULL\n group by all\n),\nusers_grouped as (\n select\n u.user\n , u.email\n , count(1) AS roles_count \n , array_agg(role) as roles\n \t, any_value(u.user_created) user_created\n , any_value(u.last_success_login) last_success_login\n , any_value(u.user_id) user_id\n from\n users_roles u\n group by 1, 2\n),\nusers_tags as (\n select \n u.*\n , tags.tag_value as team\n from \n users_grouped u\n -- it has data delay up to a few hours, so if you change tag assignment, you will not see it immediatelty\n left join snowflake.account_usage.tag_references tags on \n tags.tag_name = 'TEAM'\n and tags.domain = 'USER'\n and tags.object_id = u.user_id\n)\nselect\n u.user\n , u.email\n , roles_count \n , ARRAY_TO_STRING(roles, '\\n') as roles\n\t, date(u.user_created) user_created\n , date(u.last_success_login) last_success_login\n , ARRAYS_OVERLAP(\n ARRAY_CONSTRUCT('SYSADMIN', 'ACCOUNTADMIN', 'SECURITYADMIN'),\n roles\n ) as has_admin_role\n --, IFF(u.last_success_login is null, true, false) Never_Logged_In\n , IFF(u.last_success_login < DATEADD(day, -30, CURRENT_DATE) , true, false) Not_Logged_In_1_month\n , IFF(u.last_success_login < DATEADD(day, -90, CURRENT_DATE) , true, false) Not_Logged_In_3_months \n , u.team\nfrom\n users_tags u\norder by 1\n;\n", "execution_count": null }, { "cell_type": "markdown", "id": "09ed39da-9469-4f34-9a1f-b4a40c3bbd0b", "metadata": { "name": "cell2", "collapsed": false }, "source": "Set team on users" }, { "cell_type": "code", "id": "23f417d2-14d3-471a-8656-b05ba9e54544", "metadata": { "language": "sql", "name": "Create_tag" }, "outputs": [], "source": "-- Create tag\n\nuse database DELPHI_EXPLORATION;\n\nCREATE OR ALTER TAG team\nALLOWED_VALUES 'data-platform', 'fansifter', 'devops', 'sony-music-publishing', 'sme-central-analytics'\nCOMMENT = 'User team';", "execution_count": null }, { "cell_type": "code", "id": "9dee22da-23e0-49af-899b-66b4c6e63c36", "metadata": { "language": "sql", "name": "Assign_tag" }, "outputs": [], "source": "-- data platform\nALTER USER IBURILO SET TAG team = 'data-platform';\nALTER USER ACIKAJ SET TAG team = 'data-platform';\nALTER USER CVALLECILLA SET TAG team = 'data-platform';\nALTER USER DKARNAUKH SET TAG team = 'data-platform';\nALTER USER EGORFEDOROV SET TAG team = 'data-platform';\nALTER USER ETARANENKO SET TAG team = 'data-platform';\nALTER USER KSHRAYBER SET TAG team = 'data-platform';\nALTER USER OSHAINOHA SET TAG team = 'data-platform';\nALTER USER RROY SET TAG team = 'data-platform';\nALTER USER SDUBERG SET TAG team = 'data-platform';\nALTER USER ROBGODWIN SET TAG team = 'data-platform';\nALTER USER BORISUVAROV SET TAG team = 'data-platform';\nALTER USER OKSANAEFANOVA SET TAG team = 'data-platform';\nALTER USER REMCOSTIPHOUT SET TAG team = 'data-platform';\nALTER USER FAROUKUMAR SET TAG team = 'data-platform';\n\n-- fansifter\nALTER USER DIMAURUKOV SET TAG team = 'fansifter';\nALTER USER RAINBOMBERG SET TAG team = 'fansifter';\nALTER USER ALEKSKOZLOV SET TAG team = 'fansifter';\nALTER USER ANTONRUHLOV SET TAG team = 'fansifter';\nALTER USER FUADASADULLAYEV SET TAG team = 'fansifter';\nALTER USER KGODLEVSKAYA SET TAG team = 'fansifter';\nALTER USER OZHOVNUVATYI SET TAG team = 'fansifter';\nALTER USER YULIIADYTYNIAK SET TAG team = 'fansifter';\n\n-- devops\nALTER USER TSHAGAPOV SET TAG team = 'devops';\nALTER USER BBABII SET TAG team = 'devops';\nALTER USER ARTUR_ADMIN SET TAG team = 'devops';\nALTER USER IKORNIIENKO_ADMIN SET TAG team = 'devops';\nALTER USER JOEDENNISS SET TAG team = 'devops';\nALTER USER JULIALIM SET TAG team = 'devops';\nALTER USER NPICONDELCAMPO SET TAG team = 'devops';\nALTER USER TANZEELMOIN SET TAG team = 'devops';\nALTER USER NTURSUNKUL SET TAG team = 'devops';\n\n\n-- sony-music-publishing\nALTER USER ACHIU SET TAG team = 'sony-music-publishing';\nALTER USER CMORIARTY SET TAG team = 'sony-music-publishing';\nALTER USER ETHANFRANA SET TAG team = 'sony-music-publishing';\nALTER USER JYEW SET TAG team = 'sony-music-publishing';\nALTER USER LKEYS SET TAG team = 'sony-music-publishing';\nALTER USER MARCINSLAWINSKI SET TAG team = 'sony-music-publishing';\nALTER USER MIGUELCOLMENARES SET TAG team = 'sony-music-publishing';\nALTER USER MLAU SET TAG team = 'sony-music-publishing';\nALTER USER MLINARES SET TAG team = 'sony-music-publishing';\nALTER USER NEDINBURG SET TAG team = 'sony-music-publishing';\nALTER USER SCARESS SET TAG team = 'sony-music-publishing';\nALTER USER TBRANDT SET TAG team = 'sony-music-publishing';\n\n\n-- sme-central-analytics\nALTER USER FKARIM SET TAG team = 'sme-central-analytics';\nALTER USER HSPIEGEL SET TAG team = 'sme-central-analytics';\nALTER USER SAROTSKY SET TAG team = 'sme-central-analytics';\nALTER USER JAVIREYES SET TAG team = 'sme-central-analytics';\nALTER USER KMCGANNLUDWIN SET TAG team = 'sme-central-analytics';\nALTER USER BELLADODD SET TAG team = 'sme-central-analytics';\n\n", "execution_count": null }, { "cell_type": "code", "id": "b6ab48e2-9200-4a05-bdf2-4969aea714bd", "metadata": { "language": "sql", "name": "cell1" }, "outputs": [], "source": "select\n object_name as user,\n tag_value as team,\nfrom\nsnowflake.account_usage.tag_references tags\nwhere\n tags.tag_name = 'TEAM'\n and tags.domain = 'USER'\norder by 2, 1 \n;", "execution_count": null }, { "cell_type": "markdown", "id": "a174adb9-f370-489c-b412-57e23ddf9292", "metadata": { "name": "cell3", "collapsed": false }, "source": "\nGet accessed databases and schemas for user roles.\nIt will help with understanding which permissions role has and as a result which data can be accessed by user.\n\nFor each user role resolve hierarchy of parent roles." }, { "cell_type": "code", "id": "18092da3-bbbb-474e-a0dd-7092b8a8bd69", "metadata": { "language": "sql", "name": "cell7" }, "outputs": [], "source": "-- Make a copy of GRANTS_TO_ROLES tables to speed up processing\n\ncreate or replace transient table DELPHI_EXPLORATION.public.GRANTS_TO_ROLES as\n select * from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES;", "execution_count": null }, { "cell_type": "code", "id": "9138fc82-b7a1-4bbd-a165-b119c83b6ea1", "metadata": { "language": "sql", "name": "cell6" }, "outputs": [], "source": "WITH RECURSIVE role_hierarchy AS (\n select\n role as root_role,\n null as parent_role,\n role as child_role,\n count(1) as granted_to_users_num\n from\n snowflake.account_usage.grants_to_users\n where\n deleted_on IS NULL\n and role not in ('ACCOUNTADMIN', 'SYSADMIN', 'SECURITYADMIN')\n --and role = 'ORCHARD_STRATEGY_TEAM_ROLE'\n group by ALL\n\n UNION ALL\n\n SELECT\n rh.root_role,\n g.grantee_name AS parent_role,\n g.name AS child_role,\n rh.granted_to_users_num\n FROM\n DELPHI_EXPLORATION.public.GRANTS_TO_ROLES g\n join role_hierarchy rh ON\n g.grantee_name = rh.child_role\n and privilege = 'USAGE'\n and granted_on in ('ROLE', 'DATABASE_ROLE')\n and deleted_on is null\n),\nroles as (\n SELECT\n DISTINCT root_role, child_role as role, granted_to_users_num\n FROM role_hierarchy\n)\n, tables as (\n select\n r.root_role,\n granted_to_users_num,\n grantee_name as role,\n name as table_name, -- table or view\n table_catalog,\n table_schema,\n granted_on table_type,\n case\n when privilege = 'OWNERSHIP' then 'own'\n when privilege in ('INSERT', 'DELETE', 'UPDATE') then 'write'\n when privilege = 'SELECT' then 'read'\n -- use of shared database: grants_to_roles doesn't show tables/views.\n when privilege = 'USAGE' and granted_on = 'DATABASE' and name in (\n 'LUMINATE_DB_LISTING_DETAIL',\n 'FANSIFTER_SNOWFLAKE_SECURE_SHARE_1732194621969',\n 'ORCHARD_FACTS_TO_DELPHI',\n 'STAGE_DELPHI_PUBLIC_DATA_RAW_CHARTS_SPOTIFY',\n 'STAGE_DS_ISNT_DELPHI_EXP_CHARTMETRIC',\n 'DELPHI_PROD_DS',\n 'DS_SONY')\n then 'data_share'\n ELSE 'other'\n end as access_type\n from\n roles r\n left join DELPHI_EXPLORATION.public.GRANTS_TO_ROLES grants on\n r.role = grants.grantee_name\n and DELETED_ON is null\n and (granted_on in ('TABLE', 'VIEW') or access_type='data_share')\n)\n, tables_by_root_role as (\n select distinct\n root_role,\n granted_to_users_num,\n table_name,\n table_catalog,\n table_schema,\n table_type,\n access_type\n from tables\n)\n,distinct_tables as (\n select\n root_role as role,\n granted_to_users_num,\n table_name,\n table_catalog,\n table_schema,\n table_type,\n access_type\n , count(1) over (PARTITION BY role, table_catalog) AS db_objects_num\n from tables_by_root_role\n),\ndata as (\n select\n role\n , table_catalog as db\n , table_schema as schema\n , any_value(granted_to_users_num) as granted_to_users_num\n , any_value(db_objects_num) as db_objects_num\n , count(1) as schema_objects_num\n , array_agg( distinct access_type) schema_acces_types\n , array_agg( distinct table_type) schema_object_types,\n iff(count(1) <= 10, array_agg(distinct table_name), array_construct('...')) as schema_objects\n from distinct_tables\n where\n access_type != 'other'\n group by 1, 2, 3\n)\n, prepared_data as (\n select\n role\n , granted_to_users_num as user_num\n , count(distinct db) over (partition by role) as dbs_num\n , db\n , count(distinct schema) over (partition by role, db) as schemas_num\n , schema\n , db_objects_num\n , schema_objects_num\n , schema_acces_types\n , schema_object_types\n , schema_objects\n , array_agg(distinct db) over (partition by role) as dbs\n from data\n), summary_data as (\n select\n role,\n any_value(user_num) as user_num,\n any_value(dbs_num) as dbs_num,\n sum(db_objects_num) as db_objects_num,\n any_value(schemas_num) as schemas_num,\n any_value(dbs) as dbs\n from\n prepared_data\n group by role\n order by role\n)\n-- select * from prepared_data\n-- --where role = 'EXPLORATION_USER'\n-- order by role, db, schema\n\nselect\n s.*,\n array_agg(distinct u.user) as users\nfrom\n summary_data s\n join (select\n role,\n GRANTEE_NAME as user\n from\n snowflake.account_usage.grants_to_users\n where\n deleted_on IS NULL\n ) u on s.role = u.role\ngroup by s.role, user_num, dbs_num, db_objects_num, schemas_num, dbs\norder by role\n;\n", "execution_count": null }, { "cell_type": "markdown", "id": "772c7480-0081-49f1-822b-54a35167d6ba", "metadata": { "name": "cell8", "collapsed": false }, "source": "Get Row-level security" }, { "cell_type": "code", "id": "73b47008-65c7-44f3-8b89-33db8217b3b4", "metadata": { "language": "sql", "name": "cell9" }, "outputs": [], "source": "-- get initial users from mapping tables\nwith initial_users_and_role as (\n select\n user_name, dsp_name\n from\n DELPHI_EXPLORATION.SYS.DSP_ID_TO_USER_MAPPING\n\n union\n\n select\n u.user_name,\n case u.dsp_name\n when 'ALL' then m.real_dsp_name\n else u.dsp_name\n end as dsp_name\n from\n DELPHI_EXPLORATION.SYS.DSP_SUPER_USER u\n -- handle ALL\n left join (\n select * from values\n ('AE', 'ALL'),\n ('CRM_ECOMMERCE', 'ALL'),\n ('FACEBOOK', 'ALL'),\n ('GOOGLE', 'ALL'),\n ('LINKFIREREPORTING', 'ALL'),\n ('TIKTOK', 'ALL')\n AS t(real_dsp_name, dsp_name)\n ) m on u.dsp_name = m.dsp_name and u.dsp_name='ALL'\n),\n-- start: handle roles from mapping table\n role_hierarchy AS (\n -- input roles\n select\n user_name as root_role,\n null as parent_role,\n user_name as child_role,\n 1 as level,\n dsp_name\n from initial_users_and_role\n\n UNION ALL\n\n SELECT\n rh.root_role,\n g.name AS parent_role,\n g.grantee_name AS child_role,\n rh.level + 1,\n rh.dsp_name\n FROM\n DELPHI_EXPLORATION.public.GRANTS_TO_ROLES g\n join role_hierarchy rh ON\n g.name = rh.child_role\n and privilege = 'USAGE'\n and granted_on in ('ROLE', 'DATABASE_ROLE')\n and deleted_on is null\n),\nroles as (\n select\n distinct root_role, child_role as role, dsp_name\n from role_hierarchy\n),\nextended_users_and_roles as (\n select\n u.grantee_name AS user,\n r.root_role,\n u.role,\n r.dsp_name\n from\n roles r\n join SNOWFLAKE.ACCOUNT_USAGE.grants_to_users u on r.role = u.role\n where\n u.deleted_on is null\n),\n-- end\n\n-- combine initial users with extended users:\nusers_and_role as (\n select user_name, dsp_name\n from initial_users_and_role\n union\n select distinct user, dsp_name\n from extended_users_and_roles\n),\n-- end\n\ndsp_to_table_mapping as (\n select\n policy_name,\n case policy_name\n when 'AE_ROW_POLICY' then 'AE'\n when 'CRM_ROW_POLICY' then 'CRM_ECOMMERCE'\n when 'FACEBOOK_ROW_POLICY' then 'FACEBOOK'\n when 'FACEBOOK_ROW_POLICY_INT' then 'FACEBOOK'\n when 'GOOGLE_ROW_POLICY' then 'GOOGLE'\n when 'LINKFIRE_ROW_POLICY' then 'LINKFIREREPORTING'\n when 'TIKTOK_ROW_POLICY' then 'TIKTOK'\n end as dsp_name,\n concat(ref_database_name, '.', ref_schema_name, '.', ref_entity_name) as object_name\n from SNOWFLAKE.account_usage.POLICY_REFERENCES\n where\n policy_kind='ROW_ACCESS_POLICY'\n and ref_entity_domain in ('TABLE', 'VIEW')\n and dsp_name is not null\n\n UNION ALL\n select * from values\n ('SALESCLOUD_SECURITY_CHECK', 'SALESCLOUD', 'DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.V_FAN_C'),\n ('SALESCLOUD_SECURITY_CHECK', 'SALESCLOUD', 'DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.V_FORM_RESPONSE_C'),\n ('SALESCLOUD_SECURITY_CHECK', 'SALESCLOUD', 'DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.V_FORM_C'),\n ('SALESCLOUD_SECURITY_CHECK', 'SALESCLOUD', 'DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.V_MAILING_LIST_C'),\n ('SALESCLOUD_SECURITY_CHECK', 'SALESCLOUD', 'DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.V_SUBSCRIPTION_C'),\n ('SALESCLOUD_SECURITY_CHECK', 'SALESCLOUD', 'DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.V_SUBSCRIPTION_HISTORY_C'),\n ('SALESCLOUD_SECURITY_CHECK', 'SALESCLOUD', 'DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.V_TLA_C')\n),\nuser_join_mapping as (\n select\n u.user_name, u.dsp_name,\n m.object_name\n from\n users_and_role u\n left join dsp_to_table_mapping m on u.dsp_name = m.dsp_name\n)\nselect\n user_name,\n array_agg(distinct dsp_name) as dsps,\n array_agg(distinct object_name) as tables,\n count(distinct object_name) as tables_count\nfrom user_join_mapping\ngroup by 1\norder by 1;", "execution_count": null }, { "cell_type": "markdown", "id": "a639f03d-d8e5-4a3c-8b68-f5ef706a4c35", "metadata": { "name": "cell10", "collapsed": false }, "source": "Get column level security" }, { "cell_type": "code", "id": "07b93697-5bd1-412f-9de9-cb8c7b2d602e", "metadata": { "language": "sql", "name": "cell11" }, "outputs": [], "source": "Get list of policies", "execution_count": null }, { "cell_type": "code", "id": "ca0aa117-d0b4-446f-a206-c0d5a86172e2", "metadata": { "language": "sql", "name": "cell12" }, "outputs": [], "source": "select\n *\nfrom SNOWFLAKE.account_usage.POLICY_REFERENCES\nwhere\n policy_kind='MASKING_POLICY'\norder by policy_db, policy_schema, policy_name, ref_entity_name\n;\n\n\n-- get policy definition:\n-- select get_ddl('POLICY', 'DELPHI_CRM_DATA.SYS.PERSONAL_DATA_MASK');\n-- select get_ddl('POLICY', 'DELPHI_EXPLORATION.SYS.CRM_PII_MASKING');", "execution_count": null }, { "cell_type": "markdown", "id": "72b18ba8-e839-432f-9530-5e377795a1e9", "metadata": { "name": "cell14", "collapsed": false }, "source": "Get users and CLS policies\n\nNote that I didn't check Fansifter policies" }, { "cell_type": "code", "id": "85855fa3-a3d9-4634-b58a-4bc1137c11a2", "metadata": { "language": "sql", "name": "cell13" }, "outputs": [], "source": "with role_hierarchy AS (\n -- input roles\n select * from values\n -- DELPHI_CRM_DATA.SYS.PERSONAL_DATA_MASK\n ('CRM_ADMIN_USER', null, 'CRM_ADMIN_USER', 1, 'DELPHI_CRM_DATA.SYS.PERSONAL_DATA_MASK'),\n ('DELPHI_CRM_DATA_DB_RAW_SALESFORCE_SALES_CLOUD_SCHEMA_READ', null, 'DELPHI_CRM_DATA_DB_RAW_SALESFORCE_SALES_CLOUD_SCHEMA_READ', 1, 'DELPHI_CRM_DATA.SYS.PERSONAL_DATA_MASK'),\n ('DELPHI_CRM_DATA_DB_RAW_SALESFORCE_SALES_CLOUD_SCHEMA_READWRITE', null, 'DELPHI_CRM_DATA_DB_RAW_SALESFORCE_SALES_CLOUD_SCHEMA_READWRITE', 1, 'DELPHI_CRM_DATA.SYS.PERSONAL_DATA_MASK'),\n ('DELPHI_CRM_DATA_DB_READ', null, 'DELPHI_CRM_DATA_DB_READ', 1, 'DELPHI_CRM_DATA.SYS.PERSONAL_DATA_MASK'),\n ('DELPHI_CRM_DATA_DB_READWRITE', null, 'DELPHI_CRM_DATA_DB_READWRITE', 1, 'DELPHI_CRM_DATA.SYS.PERSONAL_DATA_MASK'),\n ('FIVETRAN_PROD_ROLE', null, 'FIVETRAN_PROD_ROLE', 1, 'DELPHI_CRM_DATA.SYS.PERSONAL_DATA_MASK'),\n ('PC_HIGHTOUCH_DB_PICKER_ROLE', null, 'PC_HIGHTOUCH_DB_PICKER_ROLE', 1, 'DELPHI_CRM_DATA.SYS.PERSONAL_DATA_MASK'),\n -- DELPHI_EXPLORATION.SYS.CRM_PII_MASKING\n ('CRM_ADMIN_USER', null, 'CRM_ADMIN_USER', 1, 'DELPHI_EXPLORATION.SYS.CRM_PII_MASKING')\n AS t (root_role, parent_role, child_role, level, policy)\n\n UNION ALL\n\n SELECT\n rh.root_role,\n g.name AS parent_role,\n g.grantee_name AS child_role,\n rh.level + 1,\n rh.policy\n FROM\n DELPHI_EXPLORATION.public.GRANTS_TO_ROLES g\n join role_hierarchy rh ON\n g.name = rh.child_role\n and privilege = 'USAGE'\n and granted_on in ('ROLE', 'DATABASE_ROLE')\n and deleted_on is null\n),\nroles as (\n select\n distinct root_role, child_role as role, policy\n from role_hierarchy\n),\nusers_and_roles as (\n select\n u.grantee_name AS user,\n r.root_role,\n u.role,\n r.policy\n from\n roles r\n join SNOWFLAKE.ACCOUNT_USAGE.grants_to_users u on r.role = u.role\n where\n u.deleted_on is null\n),\npolicy_tables as (\n select distinct\n concat(ref_database_name, '.', ref_schema_name, '.', ref_entity_name) as object,\n concat (policy_db, '.', policy_schema, '.', policy_name) as policy\n from SNOWFLAKE.account_usage.POLICY_REFERENCES\n where\n policy_kind='MASKING_POLICY'\n and (\n (policy_db = 'DELPHI_CRM_DATA' and policy_schema = 'SYS' and policy_name = 'PERSONAL_DATA_MASK')\n or (policy_db = 'DELPHI_EXPLORATION' and policy_schema = 'SYS' and policy_name = 'CRM_PII_MASKING')\n )\n)\n-- select * from users_and_roles order by user\nselect\n u.user,\n count(distinct p.policy) as policies_count,\n array_agg(distinct p.policy) as policies,\n count(distinct p.object) as tables_count,\n array_agg(distinct p.object) as tables\n from\n users_and_roles u\n left join policy_tables p on u.policy = p.policy\n group by 1\n order by user\n ;", "execution_count": null } ] }