SQL+CITI

-- Grupo Usuario

Select DISTINCT M.NAME

From Epm11.Css_Group_Members G

INNER JOIN EPM11.CSS_MEMBER_TYPE T ON (G.MEMBER_TYPE = T.MEMBER_TYPE_ID)

INNER JOIN EPM11.CSS_GROUPS M ON (G.GROUP_IDENTITY = M.IDENTITY_ID)

Where G.Member_Type = 1

ORDER BY M.NAME; -- USER

SELECT c.name AS "User Name", path, rpt.name AS "File Name",

( CASE d.name

WHEN 'role.modify' THEN 'Modify'

ELSE d.name

END ) "Access"

FROM epm11.v8_access_ctrl a,

epm11.v8_css_user B,

epm11.css_users C,

epm11.v8_identity D,

(SELECT A.name,

A.container_uuid,

b.path

FROM epm11.v8_container a,

(SELECT v8_container.name,

parent_folder_uuid,

container_uuid,

connect_by_isleaf "IsLeaf",

Sys_connect_by_path(v8_container.name, '/') AS Path

FROM epm11.v8_container

WHERE meta_type_uuid = 'ID03'

CONNECT BY PRIOR container_uuid = parent_folder_uuid

START WITH container_uuid = 'REPORTMART') B

WHERE A.parent_folder_uuid = B.container_uuid

--AND b.path LIKE '%/Reportes_BI' Mention parent folder here

ORDER BY A.meta_type_uuid ASC,

path ASC) rpt

WHERE b.subject_id = a.accessor_id

AND c.identity_id = b.css_identity

AND Rpt.container_uuid = A.resource_id

AND a.grant_id = d.id;

-- GROUP

SELECT c.name AS "Group Name",

path,

--rpt.name AS "File Name",

( CASE d.name

WHEN 'role.modify' THEN 'Modify'

ELSE d.name

END ) "Access"

FROM epm11.v8_access_ctrl a,

epm11.v8_css_group B,

epm11.css_groups C,

epm11.v8_identity D,

(SELECT A.name,

A.container_uuid,

b.path

FROM epm11.v8_container a,

(SELECT v8_container.name,

parent_folder_uuid,

container_uuid,

connect_by_isleaf "IsLeaf",

Sys_connect_by_path(v8_container.name, '/') AS Path

FROM epm11.v8_container

WHERE meta_type_uuid = 'ID03'

CONNECT BY PRIOR container_uuid = parent_folder_uuid

START WITH container_uuid = 'REPORTMART') B

WHERE A.parent_folder_uuid = B.container_uuid

--AND b.path LIKE '%/Reportes_BI' Mention parent folder here

ORDER BY A.meta_type_uuid ASC,

path ASC) rpt

WHERE b.group_id = a.accessor_id

AND c.identity_id = b.css_identity

AND Rpt.container_uuid = A.resource_id

AND A.grant_id = D.id;

-- Group 2

SELECT c.name as "Group Name",rpt.name AS "File Name", path,

( CASE d.name

WHEN 'role.modify' THEN 'Modify'

ELSE d.name

END ) "Access"

FROM epm11.v8_access_ctrl A

INNER JOIN epm11.v8_css_group B ON (B.group_id = A.accessor_id)

INNER JOIN epm11.css_groups C ON (C.identity_id = B.css_identity)

INNER JOIN epm11.v8_identity D ON (D.id = A.grant_id)

INNER JOIN

(SELECT A.name,

A.container_uuid,

b.path

FROM epm11.v8_container a,

(SELECT v8_container.name,

parent_folder_uuid,

container_uuid,

connect_by_isleaf "IsLeaf",

Sys_connect_by_path(v8_container.name, '/') AS Path

FROM epm11.v8_container

WHERE meta_type_uuid = 'ID03'

CONNECT BY PRIOR container_uuid = parent_folder_uuid

START WITH container_uuid = 'REPORTMART') B

WHERE A.parent_folder_uuid = B.container_uuid

--AND b.path LIKE '%/Reportes_BI' Mention parent folder here

ORDER BY A.meta_type_uuid ASC, path ASC) rpt

ON (Rpt.container_uuid = A.resource_id)

ORDER BY path ASC

-- Seguridad Planning

SELECT GU.object_name AS Usuario,

HO.object_name AS Grupo,

UI.object_name AS Objeto,

CASE flags

WHEN 0 THEN 'member'

WHEN 5 THEN 'children'

WHEN 6 THEN 'IChildren'

WHEN 8 THEN 'Descendants'

WHEN 9 THEN 'IDescendants'

ELSE 'none'

END AS flags,

CASE access_mode

WHEN 1 THEN 'Read'

WHEN 3 THEN 'Write'

WHEN -1 THEN 'Deny'

WHEN 4 THEN 'Launch Calc'

ELSE 'none'

END AS Nivel_Acceso,

type_name

FROM hsp_object HO

inner join hsp_access_control AC

ON ( HO.object_id = AC.user_id )

inner join hsp_object UI

ON ( AC.object_id = UI.object_id )

inner join hsp_usersingroup UG

ON ( HO.object_id = UG.group_id )

inner join hsp_object GU

ON ( GU.object_id = UG.user_id )

inner join hsp_object_type OT

ON ( HO.object_type = OT.object_type )

ORDER BY GU.object_name -- Combinado

select 'Usuario' as Tipo, c.name as "User Name", path, rpt.name as "File Name",

( CASE d.name

WHEN 'role.modify' THEN 'Modify'

ELSE d.name

END ) "Access"

FROM EPM11.v8_access_ctrl a,

Epm11.v8_css_user B,

Epm11.css_users C,

Epm11.V8_Identity D,

(Select A.Name, A.Container_Uuid, b.path

FROM EPM11.v8_container a,

(SELECT v8_container.name,

parent_folder_uuid,

container_uuid,

connect_by_isleaf "IsLeaf",

Sys_Connect_By_Path(V8_Container.Name, '/') As Path

FROM EPM11.v8_container

WHERE meta_type_uuid = 'ID03'

Connect By Prior Container_Uuid = Parent_Folder_Uuid

Start With Container_Uuid = 'REPORTMART') B

Where A.Parent_Folder_Uuid = B.Container_Uuid

--AND b.path LIKE '%/Reportes_BI' Mention parent folder here

Order By A.Meta_Type_Uuid Asc,

path ASC) rpt

where b.subject_id=a.accessor_id

and c.identity_id=b.css_identity

And Rpt.Container_Uuid = A.Resource_Id

and a.grant_id = d.id

union all

SELECT 'Grupo' as Tipo, c.name AS "Group Name", path, rpt.name AS "File Name",

( CASE d.name

WHEN 'role.modify' THEN 'Modify'

ELSE d.name

END ) "Access"

FROM EPM11.v8_access_ctrl a,

Epm11.V8_Css_Group B,

Epm11.Css_Groups C,

Epm11.V8_Identity D,

(Select A.Name, A.Container_Uuid, b.path

FROM EPM11.v8_container a,

(SELECT v8_container.name,

parent_folder_uuid,

container_uuid,

connect_by_isleaf "IsLeaf",

Sys_Connect_By_Path(V8_Container.Name, '/') As Path

FROM EPM11.v8_container

WHERE meta_type_uuid = 'ID03'

Connect By Prior Container_Uuid = Parent_Folder_Uuid

Start With Container_Uuid = 'REPORTMART') B

Where A.Parent_Folder_Uuid = B.Container_Uuid

--AND b.path LIKE '%/Reportes_BI' Mention parent folder here

Order By A.Meta_Type_Uuid Asc,

path ASC) rpt

WHERE b.group_id = a.accessor_id

AND c.identity_id = b.css_identity

And Rpt.Container_Uuid = A.Resource_Id

AND a.grant_id = d.id; -- Carpetas bajo la carpeta Users

Select A.Name, A.Container_Uuid, b.path

FROM EPM11.v8_container a,

(SELECT v8_container.name,

parent_folder_uuid,

container_uuid,

connect_by_isleaf "IsLeaf",

Sys_Connect_By_Path(V8_Container.Name, '/') As Path

FROM EPM11.v8_container

WHERE meta_type_uuid = 'ID03'

Connect By Prior Container_Uuid = Parent_Folder_Uuid

Start With Container_Uuid = 'REPORTMART') B

Where A.Parent_Folder_Uuid = B.Container_Uuid

AND b.path LIKE '%/Users' Mention parent folder here

Order By A.Meta_Type_Uuid Asc,

path ASC