How to generate a Security report for EPM Workspace


I think we might have (I’ve done it) done this while preparing documentation. Pull a list of users “who has access” and “what type of access” in Workspace.
I don’t think there is an option to create such a report from Workspace. (I might have missed it)
The below given SQLs can be used to generate a report of all objects under a folder.
Users
 select c.name as "User Name", rpt.name as "File Name",   
(CASE d.name
WHEN 'role.modify' THEN 'Modify'
ELSE d.name
END) "Access"
from v8_access_ctrl a, v8_css_user b, hssdbuser.css_users c, v8_identity d, (select
a.name, a.container_uuid
from 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
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 '%/My Reports' ----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;

Replace hssdbuser with your Shared Services relational user id.
Groups
 select c.name as "Group Name", rpt.name as "File Name",
(CASE d.name
WHEN 'role.modify' THEN 'Modify'
ELSE d.name
END) "Access"
from v8_access_ctrl a, v8_css_group b, hssdbuser.css_groups c, V8_IDENTITY d, (select
a.name, a.container_uuid
from 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
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 '%/My Reports' ----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;

Replace hssdbuser with your Shared Services relational user id.

Note: Not tested with external users/groups

HTH


About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 thoughts on “How to generate a Security report for EPM Workspace