Even the most seasoned administrator can run into unforeseen side effects with MicroStrategy Object Manager. The scariest is wrangling object security. Despite how careful you are or the defaults you’ve selected, it seems developers gaining full access to objects in production always seems to happen somehow. One of my favorite tricks is getting a daily summary report from MicroStrategy Enterprise Manager that gives me basic statistics for the environment, including any security issues. Today, I’ll share the code for the security check report that has saved me on multiple occasions.
Create a Freeform SQL Report in your Enterprise Manager project (or anywhere you wish) with the following code:
select o.project_id, p.object_name as project_name, o.project_id as parent, p.object_name as parent_name, o.object_id as folder, o.object_name as folder_name
on o.project_id = p.object_id
on o.parent_id = r.object_id and o.project_id = r.project_id
where r.object_name = ‘Public Objects’
select o.project_id, f.project_name as project_name, o.parent_id as parent, p.object_name as parent_name, o.object_id as folder, o.object_name as folder_name
on o.project_id = f.project_id and o.parent_id = f.folder
on o.parent_id = p.object_id and o.project_id = p.project_id
where o.object_id not in(select distinct folder from #folders)
and o.object_type = 8 and o.subtype = 2048 and o.hidden = 0
select p.object_id as Project_ID, p.object_name as Project_Desc, o.object_id, o.object_name, ut.object_type, ut.object_desc, u.object_id as UserGroupID, u.object_name as UserGroupDesc, case s.rights when 255 then ‘Full’ when 199 then ‘View’ when 223 then ‘Modify’ else ‘Custom’ end as rights
on o.OBJECT_ID = s.OBJECT_ID and o.PROJECT_ID = s.PROJECT_ID
on u.OBJECT_ID = s.TRUST_ID
on ut.object_type = o.object_type
on p.OBJECT_ID = o.project_id
on f.folder = o.PARENT_ID and f.project_id = o.project_id
where p.OBJECT_NAME in(‘PRODUCTION PROJECT 1′, ‘PRODUCTION PROJECT 2′) and RIGHTS in(255,223) and u.object_name not like ‘%PERSON TO EXCLUDE%’ and u.object_name not like ‘%Administrator%’
At a high level, we first have to narrow down to just the Public Objects folder. The reason is because all users are going to have Modify or Full Control over their own personal objects. Since the Metadata tables store objects recursively, we have to first build a list of folders that are under the Public Objects folder. This is done through the first 2 segments of code and we end up with a temporary table called #folders that contains a list of all folders under Public Objects. That is an extremely handy piece of code that I reuse in lots of scripts. You could also modify this screen to check Schema Objects if necessary by simply adding it to the the first portion of the script.
The last section of code is where we’re looking for users that have Modify or Full Control over any objects. There are lots of different configurations that could lead to having edit access over an object, and the best way to find all of them is to simply set up an object with custom ACL and look at it (stored in DSSMDOBJSECU).
One piece that you’ll be missing is the table tbl_object_type
. You’ll need to create this yourself, and you can get the full list from this tech note
The final piece of the query is excluding anyone who really should have access to those objects in Production, including the Administrator user or any other admin users.
Once you’ve got this report, simply subscribe to it to send to yourself on whatever interval you’d like. If there are no security exceptions, you won’t receive an email. If there are some, you’ll get the alert so that you can resolve it before anyone knows. You can also run the report on ad-hoc to double check that a recent migration didn’t introduce any issues.
UPDATE: Object IDs are handled differently in 9.2+, so be sure to check out Changes to Object IDs in 9.2