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
Celvin you are just great 🙂
can you pls let us know what is parent folder?
what do you mean by Replace hssdbuser with your Shared Services relational user id?
parent folder is the folder where the report is under. you'll have to start with 'Root'
use your shared services registry user id – this is the user id which is the owner of shared services relational database
Hi Celvin, great post, can you please advise on how do we get all users including ones authenticated via LDAP. CSS tables do not hold info on those users.
Thanks
Amit
Amit
You can use NUMSys to pull that information, check the menu of this blog and you can download it from here.
Else you can get the GUID from css tables and query ldap
Celvin, are "reports included in a book" stored in the relational tables as well?
Thanks,
-Vince