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;