¿Cómo puedo conceder permisos de tabla a un usuario?

Sintaxis

1
2
3
4
5
6

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ANALYZE | ANALYSE } [, ...] 
      | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
       | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] 
    [ WITH GRANT OPTION ];

Escenario

Supongamos que hay usuarios u1, u2, u3, u4, y u5 y cinco esquemas con el nombre de estos usuarios. Sus requisitos de permiso son los siguientes:

• El usuario u2 es un usuario de sólo lectura y requiere el permiso SELECT para la tabla u1.t1.
• El usuario u3 requiere el permiso SELECT para la tabla u1.t1.
• El usuario u3 requiere el permiso UPDATE para la tabla u1.t1.
• El usuario u5 requiere todos los permisos de la tabla u1.t1.

1

GRANT SELECT ON u1.t1 TO u2;

1

GRANT INSERT ON u1.t1 TO u3;

1

GRANT SELECT,UPDATE ON u1.t1 TO u4;  

1

GRANT ALL PRIVILEGES ON u1.t1 TO u5;

Procedimiento

Realice los siguientes pasos para conceder y verificar permisos:

1. Conéctese a su base de datos como dbadmin. Ejecute las siguientes instrucciones para crear usuarios u1 a u5. De forma predeterminada, se crearán cinco esquemas con el nombre de los usuarios.

   1 2 3 4 5

   CREATE USER u1 PASSWORD '{password}'; CREATE USER u2 PASSWORD '{password}'; CREATE USER u3 PASSWORD '{password}'; CREATE USER u4 PASSWORD '{password}'; CREATE USER u5 PASSWORD '{password}';

2. Crear tabla u1.t1 en el esquema u1.

   1	CREATE TABLE u1.t1 (c1 int, c2 int);

3. Insertar dos registros en la tabla.

   1 2	INSERT INTO u1.t1 VALUES (1,2); INSERT INTO u1.t1 VALUES (1,2);

4. Conceder permisos de esquema a los usuarios.

   1	GRANT USAGE ON SCHEMA u1 TO u2,u3,u4,u5;

5. Otorgar al usuario u2 el permiso para consultar la tabla u1.t1.

   1	GRANT SELECT ON u1.t1 TO u2;

6. Iniciar una nueva sesión y conéctese a la base de datos como usuario u2. Comprobar que el usuario u2 puede consultar la tabla u1.t1 pero no puede escribir en la tabla ni modificarla.

   1 2 3

   SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

   

7. En la sesión iniciada por el usuario dbadmin, conceda permisos a los usuarios u3, u4 y u5.

   1 2 3

   GRANT INSERT ON u1.t1 TO u3; -- Allow u3 to insert data. GRANT SELECT,UPDATE ON u1.t1 TO u4; -- Allow u4 to modify the table. GRANT ALL PRIVILEGES ON u1.t1 TO u5; -- Allow u5 to query, insert, modify, and delete table data.

8. Inicie una nueva sesión y conéctese a la base de datos como usuario u3. Compruebe que el usuario u3 puede consultar la tabla u1.t1 pero no puede consultar o modificar la tabla.

   1 2 3

   SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

   

9. Inicie una nueva sesión y conéctese a la base de datos como usuario u4. Compruebe que el usuario u4 puede modificar y consultar la tabla u1.t1, pero no puede insertar datos en la tabla.

   1 2 3

   SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

   

10. Inicie una nueva sesión y conéctese a la base de datos como usuario u5. Verifique que el usuario u5 pueda consultar, insertar, modificar y eliminar datos en la tabla u1.t1.

    1 2 3 4

    SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; DELETE FROM u1.t1;

    

11. En la sesión iniciada por el usuario dbadmin, ejecute la función has_table_privilege para consultar los permisos del usuario.

    1	SELECT * FROM pg_class WHERE relname = 't1';

    Compruebe la columna relacl en la salida del comando. rolename=xxxx/yyyy indica que rolename tiene el permiso xxxx en la tabla y el permiso se obtiene de yyyy.

    En la siguiente figura se muestra la salida del comando.

    

    • u1=arwdDxtA/u1 indica que u1 es el propietario y tiene permisos completos.
    • u2=r/u1 indica que u2 tiene el permiso de lectura.
    • u3=a/u1 indica que u3 tiene el permiso de inserción.
    • u4=rw/u1 indica que u4 tiene los permisos de lectura y actualización.
    • u5=arwdDxtA/u1 indica que u5 tiene permisos completos.