Alumno 4 - Comandos Oracle.sql

alter session set "_ORACLE_SCRIPT"=true;
set linesize 32000;
set pagesize 400;
set serveroutput on;

CREATE USER PRAC5 IDENTIFIED BY PRAC5;

CREATE TABLE TABLAPRAC5 (
    ID NUMBER(10) NOT NULL,
    NOMBRE VARCHAR2(100) NOT NULL,
    PRIMARY KEY (ID)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLAPRAC5 TO PRAC5;

CREATE OR REPLACE PROCEDURE P_DUMMY IS
BEGIN
    NULL;
END P_DUMMY;
/

CREATE ROLE R1_PRAC5;
CREATE ROLE R2_PRAC5;
CREATE ROLE R3_PRAC5;

GRANT EXECUTE ON P_DUMMY TO R1_PRAC5;
GRANT ALTER ON TABLAPRAC5 TO R2_PRAC5;
GRANT READ ON TABLAPRAC5 TO R3_PRAC5;

GRANT R1_PRAC5, R2_PRAC5 TO PRAC5;

GRANT R3_PRAC5 TO R2_PRAC5;

CREATE OR REPLACE PROCEDURE P_USUARIO_EXISTE (P_USUARIO IN VARCHAR2, P_RESULTADO OUT NUMBER) IS
    CURSOR C_USUARIOS IS
        SELECT * FROM DBA_USERS
        WHERE USERNAME = P_USUARIO;
    VC_USUARIOS C_USUARIOS%ROWTYPE;
BEGIN
    OPEN C_USUARIOS;
    FETCH C_USUARIOS INTO VC_USUARIOS;
    IF C_USUARIOS%NOTFOUND THEN
        P_RESULTADO := '0';
    END IF;
    CLOSE C_USUARIOS;
END P_USUARIO_EXISTE;
/

DECLARE
    VN_RESULTADO NUMBER;
BEGIN
    P_USUARIO_EXISTE('PRAC5', VN_RESULTADO);
    DBMS_OUTPUT.PUT_LINE('El usuario PRAC5 existe, por lo que no devuelve nada' || VN_RESULTADO);
END;
/

DECLARE
    VN_RESULTADO NUMBER;
BEGIN
    P_USUARIO_EXISTE('NOEXISTE', VN_RESULTADO);
    DBMS_OUTPUT.PUT_LINE('- El usuario no existe, valor devuelto: ' || VN_RESULTADO);
END;
/

DECLARE
    VN_RESULTADO NUMBER;
BEGIN
    P_USUARIO_EXISTE('R1_PRAC5', VN_RESULTADO);
    DBMS_OUTPUT.PUT_LINE('- El usuario no existe, es un rol, valor devuelto: ' || VN_RESULTADO);
END;
/

CREATE OR REPLACE PROCEDURE P_BUSCAR_ROLES (P_USUARIO IN VARCHAR2, RC_ROLES OUT SYS_REFCURSOR) IS
BEGIN
    OPEN RC_ROLES FOR
        SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
        FROM DBA_ROLE_PRIVS
        START WITH GRANTEE = P_USUARIO
        CONNECT BY GRANTEE = PRIOR GRANTED_ROLE;
END P_BUSCAR_ROLES;
/

DECLARE
    RC_ROLES SYS_REFCURSOR;
    VV_GRANTED_USER DBA_TAB_PRIVS.GRANTEE%TYPE;
    VV_GRANTED_ROLE DBA_TAB_PRIVS.GRANTEE%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('+ Roles del usuario PRAC5:');
    P_BUSCAR_ROLES('PRAC5', RC_ROLES);
    LOOP
        FETCH RC_ROLES INTO VV_GRANTED_USER, VV_GRANTED_ROLE;
        EXIT WHEN RC_ROLES%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('- ' || VV_GRANTED_ROLE);
    END LOOP;
    CLOSE RC_ROLES;
END;
/

CREATE OR REPLACE PROCEDURE P_BUSCAR_OBJETOS_ACCESIBLES_DIRECTA (P_USUARIO_ROL IN VARCHAR2) IS
    CURSOR C_OBJETOS_ACCESIBLES IS
        SELECT * FROM DBA_TAB_PRIVS
        WHERE GRANTEE = P_USUARIO_ROL;
    VC_OBJETOS_ACCESIBLES C_OBJETOS_ACCESIBLES%ROWTYPE;
BEGIN
    OPEN C_OBJETOS_ACCESIBLES;
    LOOP
        FETCH C_OBJETOS_ACCESIBLES INTO VC_OBJETOS_ACCESIBLES;
        EXIT WHEN C_OBJETOS_ACCESIBLES%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('- Nombre del objeto: ' || VC_OBJETOS_ACCESIBLES.TABLE_NAME || CHR(10) || '- Privilegio concedido: ' || VC_OBJETOS_ACCESIBLES.PRIVILEGE || CHR(10) || '- Propietario del objeto: ' || VC_OBJETOS_ACCESIBLES.OWNER || CHR(10) || '- Cedido por: ' || VC_OBJETOS_ACCESIBLES.GRANTOR || CHR(10) || '=================================================');
    END LOOP;
    CLOSE C_OBJETOS_ACCESIBLES;
END P_BUSCAR_OBJETOS_ACCESIBLES_DIRECTA;
/

BEGIN
    P_BUSCAR_OBJETOS_ACCESIBLES_DIRECTA('PRAC5');
END;
/

BEGIN
    P_BUSCAR_OBJETOS_ACCESIBLES_DIRECTA('R1_PRAC5');
END;
/

CREATE OR REPLACE PROCEDURE P_MOSTRAR_OBJETOS_ACCESIBLES (P_USUARIO IN VARCHAR2) IS
    VN_RESULTADO NUMBER;
    RC_ROLES SYS_REFCURSOR;
    VV_GRANTED_USER DBA_TAB_PRIVS.GRANTEE%TYPE;
    VV_GRANTED_ROLE DBA_TAB_PRIVS.GRANTEE%TYPE;
    EX_USUARIO_NO_EXISTE EXCEPTION;
BEGIN
    P_USUARIO_EXISTE(P_USUARIO, VN_RESULTADO);
    IF VN_RESULTADO = 0 THEN
        RAISE EX_USUARIO_NO_EXISTE;
    END IF;
    DBMS_OUTPUT.PUT_LINE('==== OBJETOS ACCESIBLES POR EL USUARIO ' || P_USUARIO || ' ====' || CHR(10) || '=================================================');
    P_BUSCAR_OBJETOS_ACCESIBLES_DIRECTA(P_USUARIO);
    P_BUSCAR_ROLES(P_USUARIO, RC_ROLES);
    LOOP
        FETCH RC_ROLES INTO VV_GRANTED_USER, VV_GRANTED_ROLE;
        EXIT WHEN RC_ROLES%NOTFOUND;
        P_BUSCAR_OBJETOS_ACCESIBLES_DIRECTA(VV_GRANTED_ROLE);
    END LOOP;
    CLOSE RC_ROLES;
EXCEPTION
    WHEN EX_USUARIO_NO_EXISTE THEN
        RAISE_APPLICATION_ERROR(-20001, 'El usuario ' || P_USUARIO || ' no existe.');
END P_MOSTRAR_OBJETOS_ACCESIBLES;
/

EXEC P_MOSTRAR_OBJETOS_ACCESIBLES('PRAC5');
EXEC P_MOSTRAR_OBJETOS_ACCESIBLES('JULIÁN');

CREATE OR REPLACE PROCEDURE P_BUSCAR_PRIVILEGIO (P_USUARIO_ROL IN VARCHAR2, P_PRIVILEGIO IN VARCHAR2, P_OBJETO IN VARCHAR2, P_RESULTADO OUT NUMBER) IS
    CURSOR C_PRIVILEGIOS IS
        SELECT *
        FROM DBA_TAB_PRIVS
        WHERE GRANTEE = P_USUARIO_ROL
            AND PRIVILEGE = P_PRIVILEGIO
            AND TABLE_NAME = P_OBJETO;
    VC_PRIVILEGIOS C_PRIVILEGIOS%ROWTYPE;
BEGIN
    OPEN C_PRIVILEGIOS;
    FETCH C_PRIVILEGIOS INTO VC_PRIVILEGIOS;
    IF C_PRIVILEGIOS%FOUND THEN
        P_RESULTADO := '1';
    END IF;
    CLOSE C_PRIVILEGIOS;
END P_BUSCAR_PRIVILEGIO;
/

DECLARE
    VN_RESULTADO NUMBER;
BEGIN
    P_BUSCAR_PRIVILEGIO('PRAC5', 'INSERT', 'TABLAPRAC5', VN_RESULTADO);
    DBMS_OUTPUT.PUT_LINE('- El usuario PRAC5 tiene el privilegio INSERT sobre la tabla TABLAPRAC5 de forma directa, valor devuelto: ' || VN_RESULTADO);
END;
/

DECLARE
    VN_RESULTADO NUMBER;
BEGIN
    P_BUSCAR_PRIVILEGIO('PRAC5', 'READ', 'TABLAPRAC5', VN_RESULTADO);
    DBMS_OUTPUT.PUT_LINE('- El usuario PRAC5 no tiene el privilegio READ sobre la tabla TABLAPRAC5, al menos de forma directa, por lo que no devuelve nada' || VN_RESULTADO);
END;
/

DECLARE
    VN_RESULTADO NUMBER;
BEGIN
    P_BUSCAR_PRIVILEGIO('R3_PRAC5', 'READ', 'TABLAPRAC5', VN_RESULTADO);
    DBMS_OUTPUT.PUT_LINE('- El rol R3_PRAC5 sí tiene el privilegio READ sobre la tabla TABLAPRAC5, valor devuelto: ' || VN_RESULTADO);
END;
/

CREATE OR REPLACE PROCEDURE P_TIPO_PRIVILEGIO (P_USUARIO IN VARCHAR2, P_PRIVILEGIO IN VARCHAR2, P_OBJETO IN VARCHAR2) IS
    VN_RESULTADO NUMBER;
    RC_ROLES SYS_REFCURSOR;
    VV_GRANTED_USER DBA_TAB_PRIVS.GRANTEE%TYPE;
    VV_GRANTED_ROLE DBA_TAB_PRIVS.GRANTEE%TYPE;
    EX_USUARIO_NO_EXISTE EXCEPTION;
    EX_PRIVILEGIO_DIRECTO EXCEPTION;
    EX_PRIVILEGIO_POR_ROL EXCEPTION;
    EX_PRIVILEGIO_NO_EXISTE EXCEPTION;
BEGIN
    P_USUARIO_EXISTE(P_USUARIO, VN_RESULTADO);
    IF VN_RESULTADO = 0 THEN
        RAISE EX_USUARIO_NO_EXISTE;
    END IF;
    P_BUSCAR_PRIVILEGIO(P_USUARIO, P_PRIVILEGIO, P_OBJETO, VN_RESULTADO);
    IF VN_RESULTADO = 1 THEN
        RAISE EX_PRIVILEGIO_DIRECTO;
    END IF;
    P_BUSCAR_ROLES(P_USUARIO, RC_ROLES);
    LOOP
        FETCH RC_ROLES INTO VV_GRANTED_USER, VV_GRANTED_ROLE;
        EXIT WHEN RC_ROLES%NOTFOUND;
        P_BUSCAR_PRIVILEGIO(VV_GRANTED_ROLE, P_PRIVILEGIO, P_OBJETO, VN_RESULTADO);
        IF VN_RESULTADO = 1 THEN
            RAISE EX_PRIVILEGIO_POR_ROL;
        END IF;
    END LOOP;
    CLOSE RC_ROLES;
    RAISE EX_PRIVILEGIO_NO_EXISTE;
EXCEPTION
    WHEN EX_USUARIO_NO_EXISTE THEN
        RAISE_APPLICATION_ERROR(-20001, 'El usuario ' || P_USUARIO || ' no existe.');
    WHEN EX_PRIVILEGIO_DIRECTO THEN
        DBMS_OUTPUT.PUT_LINE('SÍ, DIRECTO');
    WHEN EX_PRIVILEGIO_POR_ROL THEN
        DBMS_OUTPUT.PUT_LINE('SÍ, POR ROL');
    WHEN EX_PRIVILEGIO_NO_EXISTE THEN
        DBMS_OUTPUT.PUT_LINE('NO');
END;
/

EXEC P_TIPO_PRIVILEGIO('PRAC5', 'SELECT', 'TABLAPRAC5');

EXEC P_TIPO_PRIVILEGIO('JULIAN', 'SELECT', 'TABLAPRAC5');

EXEC P_TIPO_PRIVILEGIO('PRAC5', 'READ', 'TABLAPRAC5');

EXEC P_TIPO_PRIVILEGIO('PRAC5', 'SELECT', 'TABLAPRAC6');