Gestión de Roles, Permisos y Jerarquías en PostgreSQL: Práctica con Proveedores y Monedas

Economía3,82 KB

Ejercicios prácticos de SQL en PostgreSQL

En este documento se recogen ejercicios prácticos sobre modificación de tablas, consultas recursivas, gestión de usuarios y permisos, y creación de vistas en PostgreSQL.

1. Añadir una relación jerárquica a la tabla proveedor

--1
ALTER TABLE proveedor ADD codpj CHAR(8);
ALTER TABLE proveedor ADD FOREIGN KEY (codpj) REFERENCES proveedor(codp);

2. Asignación de proveedores jefes

--2
UPDATE proveedor SET codpj = 'P10' WHERE codp = 'P1';
UPDATE proveedor SET codpj = 'P4' WHERE codp = 'P2';
UPDATE proveedor SET codpj = 'P12' WHERE codp = 'P3';
UPDATE proveedor SET codpj = 'P7' WHERE codp = 'P4';
UPDATE proveedor SET codpj = 'P6' WHERE codp = 'P5';
UPDATE proveedor SET codpj = 'P10' WHERE codp = 'P6';
UPDATE proveedor SET codpj = 'P4' WHERE codp = 'P8';
UPDATE proveedor SET codpj = 'P6' WHERE codp = 'P9';
UPDATE proveedor SET codpj = 'P7' WHERE codp = 'P10';
UPDATE proveedor SET codpj = 'P10' WHERE codp = 'P11';
UPDATE proveedor SET codpj = 'P4' WHERE codp = 'P12';
UPDATE proveedor SET codpj = 'P12' WHERE codp = 'P13';
UPDATE proveedor SET codpj = 'P10' WHERE codp = 'P14';
UPDATE proveedor SET codpj = 'P14' WHERE codp = 'P15';

3. Consulta recursiva de la jerarquía de proveedores

--3
WITH RECURSIVE jefe (codp, nombre, codpj, nivel) AS (
SELECT codp, nombre, codpj, 1 FROM proveedor WHERE codpj IS NULL
UNION
SELECT proveedor.codp, proveedor.nombre, proveedor.codpj, nivel + 1
FROM jefe, proveedor
WHERE proveedor.codpj = jefe.codp
)
SELECT *
FROM jefe;

4. Creación de usuarios y gestión de permisos

--4
CREATE ROLE john WITH LOGIN PASSWORD 'john';
--psql -d Practicas -U john
--No deja visualizar
GRANT SELECT, INSERT, UPDATE ON monedas TO john;
--psql -d Practicas -U john
--Se pueden visualizar las filas de la tabla monedas, pero de las otras no
CREATE INDEX ipais ON monedas (pais);
--No deja crear índices al usuario john
ALTER TABLE monedas OWNER TO john;
--Ahora sí deja crear índices

5. Restitución de la propiedad de la tabla

--5
ALTER TABLE monedas OWNER TO postgres;

6. Creación de una vista y concesión de permisos con GRANT OPTION

--6
CREATE VIEW p4e6
AS SELECT articulo.*, cliente.codc, cliente.nombre AS nombre_cliente, factura.ctd AS cantidad, factura.precio, factura.fecha
FROM (articulo INNER JOIN factura ON articulo.coda = factura.coda) INNER JOIN cliente ON factura.codc = cliente.codc
WHERE factura.fecha > '19900101';
GRANT SELECT ON TABLE p4e6 TO john WITH GRANT OPTION;

7. Revocación de permisos

--7
REVOKE SELECT ON TABLE p4e6 FROM john;

8. Tabla pública y permisos para PUBLIC

--8
CREATE TABLE publica (
no NUMERIC(3,0) NOT NULL,
asunto CHAR(16) NOT NULL,
desarrollo CHAR(60) NOT NULL
);
INSERT INTO publica VALUES (1, 'libro', 'se han vendido 5 libros');
INSERT INTO publica VALUES (2, 'vendido', 'se ha vendido todo');
INSERT INTO publica VALUES (3, 'en stock', 'ya hay artículos en stock');
INSERT INTO publica VALUES (4, 'ordenadores', 'se han vendido 5 ordenadores');
INSERT INTO publica VALUES (5, 'total', 'total ventas 2018');
GRANT ALL ON TABLE publica TO PUBLIC;
REVOKE UPDATE, CREATE ON TABLE publica FROM PUBLIC;

Descargar

PDF Word HTML