queries - redshift sql commands

¿Cómo veo las subvenciones en Redshift?

Question

Me gustaría ver las subvenciones en los desplazamientos al rojo.

Encontré esta vista para postgres :

CREATE OR REPLACE VIEW view_all_grants AS 
SELECT 
  use.usename as subject, 
  nsp.nspname as namespace, 
  c.relname as item, 
  c.relkind as type, 
  use2.usename as owner, 
  c.relacl, 
  (use2.usename != use.usename and c.relacl::text !~ (''({|,)'' || use.usename || ''='')) as public
FROM 
  pg_user use 
  cross join pg_class c 
  left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
  left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE 
  c.relowner = use.usesysid or 
  c.relacl::text ~ (''({|,)(|'' || use.usename || '')='') 
ORDER BY 
  subject, 
  namespace, 
  item 

Lo que no funciona porque la ::text de ::text de relacl falla con lo siguiente:

ERROR: cannot cast type aclitem[] to character varying [SQL State=42846] 

Modificando la consulta para

CREATE OR REPLACE VIEW view_all_grants AS 
SELECT 
  use.usename as subject, 
  nsp.nspname as namespace, 
  c.relname as item, 
  c.relkind as type, 
  use2.usename as owner, 
  c.relacl 
  -- , (use2.usename != use.usename and c.relacl::text !~ (''({|,)'' || use.usename || ''='')) as public
FROM 
  pg_user use 
  cross join pg_class c 
  left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
  left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE 
  c.relowner = use.usesysid 
  -- or c.relacl::text ~ (''({|,)(|'' || use.usename || '')='') 
ORDER BY 
  subject, 
  namespace, 
  item 

Permite crear la vista, pero me preocupa que esto no muestre todos los datos relevantes.

¿Cómo puedo modificar la vista para que funcione en el desplazamiento al rojo o hay una manera mejor / alternativa de ver las concesiones en el desplazamiento al rojo?

ACTUALIZACIÓN: Redshift tiene la función HAS_TABLE_PRIVILEGE para verificar las concesiones. (ver here )

Answer 1

Algo a lo largo de las líneas de apagado:

select tablename, 
   HAS_TABLE_PRIVILEGE(tablename, ''select'') as select,
   HAS_TABLE_PRIVILEGE(tablename, ''insert'') as insert,
   HAS_TABLE_PRIVILEGE(tablename, ''update'') as update,
   HAS_TABLE_PRIVILEGE(tablename, ''delete'') as delete, 
   HAS_TABLE_PRIVILEGE(tablename, ''references'') as references 
from pg_tables where schemaname=''public'' order by tablename;

me da todo lo que necesito.

Answer 2

Aquí hay otra consulta útil para ver las concesiones en el esquema (uso, creación) por usuario que creé en base a la consulta anterior por :

SELECT * 
FROM 
    (
    SELECT 
        schemaname
        ,usename
        ,HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, ''usage'') AS usg
        ,HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, ''create'') AS crt
    FROM
        (
        SELECT distinct(schemaname) FROM pg_tables
        WHERE schemaname not in (''pg_internal'')
        UNION
        SELECT distinct(schemaname) FROM pg_views
        WHERE schemaname not in (''pg_internal'')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY schemaname
    )
WHERE (usg = true or crt = true)
--and schemaname=''<opt schemaname>''
--and usename = ''<opt username>'';

Answer 3

La función has_table_privilege es práctica, pero no siempre ayuda en la administración cuando desea administrar grupos. Transformé su consulta original para crear scripts de concesión para usuarios o grupos específicos. Esta consulta de muestra puede modificarse fácilmente para satisfacer sus necesidades

select namespace||''.''||item as tablename , 
''grant '' || substring(
                case when charindex(''r'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',select '' else '''' end 
              ||case when charindex(''w'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',update '' else '''' end 
              ||case when charindex(''a'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',insert '' else '''' end 
              ||case when charindex(''d'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',delete '' else '''' end 
              ||case when charindex(''R'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',rule '' else '''' end 
              ||case when charindex(''x'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',references '' else '''' end 
              ||case when charindex(''t'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',trigger '' else '''' end 
              ||case when charindex(''X'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',execute '' else '''' end 
              ||case when charindex(''U'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',usage '' else '''' end 
              ||case when charindex(''C'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',create '' else '''' end 
              ||case when charindex(''T'',split_part(split_part(array_to_string(relacl, ''|''),''group dw_developers='',2 ) ,''/'',1)) > 0 then '',temporary '' else '''' end 
           , 2,10000)
 || '' on ''||namespace||''.''||item ||'' to group dw_developers;'' as grantsql
from 
(SELECT 
  use.usename as subject, 
  nsp.nspname as namespace, 
  c.relname as item, 
  c.relkind as type, 
  use2.usename as owner, 
  c.relacl 
 FROM 
  pg_user use 
  cross join pg_class c 
  left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
  left join pg_user use2 on (c.relowner = use2.usesysid)
 WHERE 
  c.relowner = use.usesysid  
  and  nsp.nspname NOT IN (''pg_catalog'', ''pg_toast'', ''information_schema'')
 ORDER BY 
  subject,   namespace,   item 
 ) where relacl is not null
 and array_to_string(relacl, ''|'') like ''%group dw_developers%'' order by 1

Answer 4

Luché mucho con esto y finalmente se me ocurrió una solución que me da justo lo que quiero ver.

WITH tabledef as (
    SELECT schemaname,
        ''t'' AS typename,
        tablename AS objectname,
        tableowner as owner,
        schemaname + ''.'' + tablename AS fullname
    FROM pg_tables
    UNION 
    SELECT schemaname,
        ''v'' AS typename,
        viewname AS objectname,
        viewowner as owner,
        schemaname + ''.'' + viewname AS fullname
    FROM pg_views
),
res AS (
    SELECT t.*,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''select'')
    WHEN true THEN u.usename
    ELSE NULL END AS sel,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''insert'')
    WHEN true THEN u.usename
    ELSE NULL END AS ins,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''update'')
    WHEN true THEN u.usename
    ELSE NULL END AS upd,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''delete'')
    WHEN true THEN u.usename
    ELSE NULL END AS del,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''references'')
    WHEN true THEN u.usename
    ELSE NULL END AS ref
    FROM tabledef AS t
    JOIN pg_user AS u
    ON HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''select'') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''insert'') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''update'') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''delete'') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, ''references'') = true
        OR t.owner = u.usename
    WHERE t.schemaname = ''analytics''
)
SELECT schemaname, objectname, owner, sel, ins, upd, del, ref FROM res
WHERE sel not in (''rdsdb'', ''<superuser>'')
ORDER BY schemaname, objectname;

Las dos líneas importantes: una que señala qué esquema se debe buscar para acceder

WHERE t.schemaname = ''analytics''

Y - Segundo, que descarta los permisos de superusuario (tienen permiso completo de todos modos) de los resultados.

WHERE sel not in (''rdsdb'', ''<superuser>'')

Answer 5

Otra variación será como:

SELECT * 
FROM 
    (
    SELECT 
        schemaname
        ,objectname
        ,usename
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ''select'') AND has_schema_privilege(usrs.usename, schemaname, ''usage'')  AS sel
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ''insert'') AND has_schema_privilege(usrs.usename, schemaname, ''usage'')  AS ins
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ''update'') AND has_schema_privilege(usrs.usename, schemaname, ''usage'')  AS upd
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ''delete'') AND has_schema_privilege(usrs.usename, schemaname, ''usage'')  AS del
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ''references'') AND has_schema_privilege(usrs.usename, schemaname, ''usage'')  AS ref
    FROM
        (
        SELECT schemaname, ''t'' AS obj_type, tablename AS objectname, schemaname + ''.'' + tablename AS fullobj FROM pg_tables
        WHERE schemaname not in (''pg_internal'')
        UNION
        SELECT schemaname, ''v'' AS obj_type, viewname AS objectname, schemaname + ''.'' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in (''pg_internal'')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY fullobj
    )
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
and schemaname=''<opt schema>''
and usename = ''<opt username>'';

Answer 6

Un desarrollo en la respuesta de mike_pdb se me ocurrió lo siguiente

 WITH object_list(schema_name,object_name,permission_info)
 AS (
    SELECT N.nspname, C.relname, array_to_string(relacl,'','')
    FROM pg_class AS C
        INNER JOIN pg_namespace AS N
        ON C.relnamespace = N.oid
    WHERE C.relkind in (''v'',''r'')
    AND  N.nspname NOT IN (''pg_catalog'', ''pg_toast'', ''information_schema'')
    AND C.relacl[1] IS NOT NULL
  ),
  object_permissions(schema_name,object_name,permission_string)
  AS (
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',1) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',2) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',3) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',4) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',5) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',6) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',7) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',8) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',9) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,'','',10) FROM object_list
  ),
  permission_parts(schema_name, object_name,security_principal, permission_pattern)
  AS (
      SELECT
          schema_name,
          object_name,
          LEFT(permission_string ,CHARINDEX(''='',permission_string)-1),
          SPLIT_PART(SPLIT_PART(permission_string,''='',2),''/'',1)
      FROM object_permissions
      WHERE permission_string >''''
  )
SELECT
    schema_name,
    object_name,
    ''GRANT '' ||
    SUBSTRING(
        case when charindex(''r'',permission_pattern) > 0 then '',SELECT '' else '''' end
      ||case when charindex(''w'',permission_pattern) > 0 then '',UPDATE '' else '''' end
      ||case when charindex(''a'',permission_pattern) > 0 then '',INSERT '' else '''' end
      ||case when charindex(''d'',permission_pattern) > 0 then '',DELETE '' else '''' end
      ||case when charindex(''R'',permission_pattern) > 0 then '',RULE '' else '''' end
      ||case when charindex(''x'',permission_pattern) > 0 then '',REFERENCES '' else '''' end
      ||case when charindex(''t'',permission_pattern) > 0 then '',TRIGGER '' else '''' end
      ||case when charindex(''X'',permission_pattern) > 0 then '',EXECUTE '' else '''' end
      ||case when charindex(''U'',permission_pattern) > 0 then '',USAGE '' else '''' end
      ||case when charindex(''C'',permission_pattern) > 0 then '',CREATE '' else '''' end
      ||case when charindex(''T'',permission_pattern) > 0 then '',TEMPORARY '' else '''' end
    ,2,10000
    )
    || '' ON '' || schema_name||''.''||object_name
     || '' TO '' || security_principal
     || '';'' as grantsql
FROM permission_parts

;

Hay 3 expresiones de tabla comunes usadas aquí.

• object_list: tablas y vistas con su matriz de permisos como una cadena delimitada por comas.
• object_permissions: esquema / objeto (tabla o vista) y un registro por cadena de permisos. Tenga en cuenta que la función SPLIT_PART no permite una posición dinámica de la pieza, por lo que se supone que no hay más de 10 usuarios o grupos con permisos directos asignados.
• permission_parts El esquema / objeto, el principal de seguridad a quien se otorgan los permisos y los atributos de seguridad que se establecen.

Según la solución de mike_pdb, los caracteres de permiso individuales se convierten en una lista concatenada de concesiones. Como no sabemos qué subvenciones se utilizarán, usamos SUBSTRING de la posición 2 para descartar la primera coma de la lista.

Puede usar exactamente el mismo enfoque para la creación de scripts fuera de los permisos de esquema

WITH schema_list(schema_name, permission_info)
AS (
    SELECT nspname, array_to_string(nspacl,'','')
    FROM pg_namespace
    WHERE nspacl[1] IS NOT NULL
    AND nspname NOT LIKE ''pg%'' AND nspname NOT IN (''public'',''information_schema'')
),
schema_permissions(schema_name,permission_string)
AS (
    SELECT schema_name,SPLIT_PART(permission_info,'','',1) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,'','',2) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,'','',3) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,'','',4) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,'','',5) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,'','',6) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,'','',7) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,'','',8) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,'','',9) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,'','',10) FROM schema_list
),
permission_parts(schema_name, security_principal, permission_pattern)
AS (
    SELECT
        schema_name,
        LEFT(permission_string ,CHARINDEX(''='',permission_string)-1),
        SPLIT_PART(SPLIT_PART(permission_string,''='',2),''/'',1)
    FROM schema_permissions
    WHERE permission_string >''''
)
SELECT
    schema_name,
    ''GRANT '' ||
    SUBSTRING(
        case when charindex(''r'',permission_pattern) > 0 then '',SELECT '' else '''' end
      ||case when charindex(''w'',permission_pattern) > 0 then '',UPDATE '' else '''' end
      ||case when charindex(''a'',permission_pattern) > 0 then '',INSERT '' else '''' end
      ||case when charindex(''d'',permission_pattern) > 0 then '',DELETE '' else '''' end
      ||case when charindex(''R'',permission_pattern) > 0 then '',RULE '' else '''' end
      ||case when charindex(''x'',permission_pattern) > 0 then '',REFERENCES '' else '''' end
      ||case when charindex(''t'',permission_pattern) > 0 then '',TRIGGER '' else '''' end
      ||case when charindex(''X'',permission_pattern) > 0 then '',EXECUTE '' else '''' end
      ||case when charindex(''U'',permission_pattern) > 0 then '',USAGE '' else '''' end
      ||case when charindex(''C'',permission_pattern) > 0 then '',CREATE '' else '''' end
      ||case when charindex(''T'',permission_pattern) > 0 then '',TEMPORARY '' else '''' end
    ,2,10000
    )
    || '' ON SCHEMA '' || schema_name
     || '' TO '' || security_principal
     || '';'' as grantsql
FROM permission_parts;