Detecting Incorrect Security in Production
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
into #Folders
From dssmdobjinfo o
join dssmdobjinfo p
on o.project_id = p.object_id
join dssmdobjinfo r
on o.parent_id = r.object_id and o.project_id = r.project_id
where r.object_name = ‘Public Objects’
while @@rowcount > 0
begin
insert into #Folders
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
from dssmdobjinfo o
join #folders f
on o.project_id = f.project_id and o.parent_id = f.folder
join dssmdobjinfo p
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
end
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
from DSSMDOBJINFO o
join DSSMDOBJSECU s
on o.OBJECT_ID = s.OBJECT_ID and o.PROJECT_ID = s.PROJECT_ID
join DSSMDOBJINFO u
on u.OBJECT_ID = s.TRUST_ID
join tbl_object_type ut
on ut.object_type = o.object_type
join DSSMDOBJINFO p
on p.OBJECT_ID = o.project_id
join #folders f
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%’
drop table #folders
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
how to put this multiple SQLs into an FFSQL report.
I think supporting multiple queries in a FFSQL report was added in 9.0.1, though I couldn’t find a reference to it on the KB. I have that exact code in a FFSQL report in my system, and I didn’t have to set any kind of special settings. I do know it didn’t work in 8.x, but I have used that report at least since 9.0.1.
Hi Bryan,
If you don’t mind could you please give explanation about Command Manager, object manager and Enterprise manager tools means what is purpose of each as till now i have not chance to work on ADMIN side.
Thanks,
Charmi
All 3 are stand alone, external tools that help you administer an environment but are not technically required.
Command Manager is a tool that lets you script out specific actions in MicroStrategy, either in a batch style or in a java-like procedure style. There’s nothing in this tool that you can’t manually do through Desktop/Web, but it lets you automate it.
Object Manager is a tool that moves objects from one project to another. It resolves dependency and version differences and is what you need to use to migrate objects between Dev and Production environments.
Enterprise Manager is an external mini-ETL tool that transforms the raw statistics logged by the IServer into a data model for reporting. It also comes with a standard project with a schema and set of reports to use this model and allow you to easily track usage of your system.
Thanks..