Privilegios de usuario

Clase 12 de 22 • Curso de PostgreSQL 2017

Gabriel Cabrera

student•

buen video. excelente

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [,...] | ALL [ PRIVILEGES ] }  
    ON { [ TABLE ] table_name [, ...]  
         | ALL TABLES IN SCHEMA schema_name [, ...] }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )  
    [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }  
    ON [ TABLE ] table_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { USAGE | SELECT | UPDATE }  
    [,...] | ALL [ PRIVILEGES ] }  
    ON { SEQUENCE sequence_name [, ...]  
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }  
    ON DATABASE database_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON FOREIGN DATA WRAPPER fdw_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON FOREIGN SERVER server_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { EXECUTE | ALL [ PRIVILEGES ] }  
    ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]  
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON LANGUAGE lang_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }  
    ON LARGE OBJECT loid [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }  
    ON SCHEMA schema_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { CREATE | ALL [ PRIVILEGES ] }  
    ON TABLESPACE tablespace_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

Andres Felipe Naranjo Giraldo

student•

Les recomiendo el siguiente link para que se documenten muchos mas sobre el tema de privilegios que es bien extenso con lo mencionan en el video.

RODRIGO NOREÑA ARTEAGA

student•

Buenos días,

Como un contenido extra, sería una buena opción mirar la administración de tablespaces cosas como agrandarlos, reducirlos, métricas, etc.

Ramiro Perez Zamorano

student•

GRANT privilage TO role WIH GRANT OPTION

Alberto Higginson

student•

Genial!!

Jaime Alexander Uribe Uribe

student•

Excente

Ramiro Perez Zamorano

student•

GRANT SELECT ON ALL TABLES IN SCHEMA "nombre schema" TO "usuario"

Ricardo Portillo

student•

Me gustaría que profundizara como crear roles y privilegios para un app y como manejar lo.
En mysql puede manejar con un usuario toda la base de datos se puede lo mismo en un app o no se recomienda.

JIMMY ALEXANDER CRUZ QUINTERO

student•

Lo recomendable es un único usuario de base de datos que se configure en la aplicación, adicionalmente este usuario solo debe tener acceso a la gestión de datos (select, update, delete, insert) y no debe poder modificar la estructura de la base de datos.

Cesar David Ramírez Dimaté

student•

La pregunta del millón, dónde puedo visualizar qué permisos tiene actualmente un rol?

Cesar David Ramírez Dimaté

student•

Lo puedo visualizar para login roles pero no para group roles

-- Visualizar en detale los permisos que tiene un rol.
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'postgres';

Diego Forero

Team Platzi•

Puedes ejecutar el comando \du

Luis Alfredo Villalta López

student•

Agregand

REATE SCHEMA video;

GRANT privilege TO role WHITH GRAN OPTION 

GRANT ALL ON ALL TABLES IN SCHEMA video TO platzi

GRANT SELECT  ON ALL TABLES IN SCHEMA video TO platzi;

o Privilegios a Roles

Usuario anónimo

user•

😎👍

Tecno Motum

student•

como puedo otorgar privilegio select a todos los schemas de una bd? o tiene que ser schema por schema?

Eduardo Rasgado Ruiz

student•

GRANT SELECT ON ALL TABLES IN SCHEMA schema1, schema2,schema3, … TO rol_name;

Marco Elizalde

student•

Asignar privilegio a todas las tablas de un schema

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [,...] | ALL [ PRIVILEGES ] }  
    ON { [ TABLE ] table_name [, ...]  
         | ALL TABLES IN SCHEMA schema_name [, ...] }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )  
    [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }  
    ON [ TABLE ] table_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { USAGE | SELECT | UPDATE }  
    [,...] | ALL [ PRIVILEGES ] }  
    ON { SEQUENCE sequence_name [, ...]  
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }  
    ON DATABASE database_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON FOREIGN DATA WRAPPER fdw_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON FOREIGN SERVER server_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { EXECUTE | ALL [ PRIVILEGES ] }  
    ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]  
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON LANGUAGE lang_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }  
    ON LARGE OBJECT loid [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }  
    ON SCHEMA schema_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { CREATE | ALL [ PRIVILEGES ] }  
    ON TABLESPACE tablespace_name [, ...]  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

Privilegios de usuario

Introducción a Postgresql

PostgreSQL: qué es

Lo nuevo en Postgresql

¿Por qué es PostgreSQL?

Entendiendo el Core de Postgresql

Archivos de configuración

Manejo De Conexiones

Preguntas y Respuestas

[Extra] Cómo instalar PostgreSQL en Linux, Windows y OS X (Mac).

Roles, Tipos de Datos, Rangos y JSON en Postgresql

Uso de roles en Postgresql

Creando una base de datos

Arquitectura de la información usando esquemas