Oracle security view

oracle-view-security.sql

CREATE OR REPLACE VIEW VIEW_SECURITY_ALL AS
WITH allprojbus AS (
     SELECT project_id, business_id, updated_on FROM projBus pb
), alluserrights AS (
     SELECT * FROM userright
     WHERE RIGHT_ID <> 'NONE' AND RIGHT_ID <> 'EXTER'
), rank1 AS ( --specific project and specific business
     SELECT ur.sso_id, ur.project_id, ur.business_id, allpb.updated_on
     FROM alluserrights ur
     JOIN allprojbus allpb ON (ur.project_id = allpb.project_id AND ur.business_id = allpb.business_id)
     WHERE ur.project_id IS NOT NULL AND ur.business_id is NOT NULL
), rank2 AS ( --specific project and all business
     SELECT ur.sso_id, ur.project_id, allpb.business_id, allpb.updated_on
            FROM alluserrights ur
            JOIN allprojbus allpb ON (ur.project_id = allpb.project_id)
            WHERE ur.project_id IS NOT NULL AND ur.business_id is NULL
     MINUS SELECT * FROM rank1
), rank3 AS ( --all project and specific business
     SELECT ur.sso_id, allpb.project_id, ur.business_id, allpb.updated_on
            FROM alluserrights ur
            JOIN allprojbus allpb ON (ur.business_id = allpb.business_id)
            WHERE ur.project_id IS NULL AND ur.business_id is NOT NULL
     MINUS (SELECT * FROM rank1 UNION SELECT * FROM rank2)
), rank4 AS ( --all project and all business
     SELECT ur.sso_id, allpb.project_id, allpb.business_id, allpb.updated_on
            FROM alluserrights ur, allprojbus allpb
            WHERE ur.project_id IS NULL AND ur.business_id is NULL
     MINUS (SELECT * FROM rank1 UNION SELECT * FROM rank2 UNION SELECT * FROM rank3)
), last_modified_date as (
   select sso_id, max(updated_on) as updated_on from tcom_security_userright group by sso_id
)

SELECT r1.sso_id, r1.project_id, r1.business_id, ur.right_id, ur.is_privileged, lmd.updated_on AS ur_date, r1.updated_on AS pb_date,
       r1.sso_id||r1.project_id||r1.business_id ID, '1' AS RANK
FROM rank1 r1, alluserrights ur, last_modified_date lmd
WHERE ur.sso_id = r1.sso_id AND ur.project_id = r1.project_id AND ur.business_id = r1.business_id AND r1.sso_id = lmd.sso_id

UNION
SELECT r2.sso_id, r2.project_id, r2.business_id, ur.right_id, ur.is_privileged, lmd.updated_on AS ur_date, r2.updated_on AS pb_date,
       r2.sso_id||r2.project_id||r2.business_id ID,'2' AS RANK
FROM rank2 r2 JOIN alluserrights ur
ON (ur.sso_id = r2.sso_id AND ur.project_id = r2.project_id AND ur.business_id IS NULL)
JOIN last_modified_date lmd ON (r2.sso_id = lmd.sso_id)

UNION
SELECT r3.sso_id, r3.project_id, r3.business_id, ur.right_id, ur.is_privileged, lmd.updated_on AS ur_date, r3.updated_on pb_date ,
       r3.sso_id||r3.project_id||r3.business_id ID,'3' AS RANK
FROM rank3 r3 JOIN alluserrights ur
ON (ur.sso_id = r3.sso_id AND ur.project_id is NULL AND ur.business_id = r3.business_id)
JOIN last_modified_date lmd ON (r3.sso_id = lmd.sso_id)

UNION
SELECT r4.sso_id, r4.project_id, r4.business_id, ur.right_id, ur.is_privileged, lmd.updated_on AS ur_date, r4.updated_on pb_date,
       r4.sso_id||r4.project_id||r4.business_id ID,'4' AS RANK
FROM rank4 r4 JOIN alluserrights ur
ON (ur.sso_id = r4.sso_id AND ur.project_id is NULL AND ur.business_id IS NULL)
JOIN last_modified_date lmd ON (r4.sso_id = lmd.sso_id);