March 13th, 2012 by Mark Rittman
In the previous posting in this series, we looked at an overview of OBIEE 11g security, and then looked at row-level security, which allows us to deploy the same analysis or dashboard out to a set of users, but then restrict what each can see based on permissions. But what if you wanted to stop people seeing whole subject areas, or just individual tables or columns within a subject area, or indeed certain catalog objects or folders? What if you wanted to restrict them to using only certain OBIEE front-end products, but not the analysis editor, for example? Let’s look at these two scenarios now.
To restrict access to a particular subject area, or to certain tables or columns in a subject area, there are actually two main approaches you can take; one that involves restricting access to data at the repository (RPD) level, and the other at the catalog, Presentation Services level. Repository-level subject area restrictions can be as coarse-grained as a whole subject area, or they can be as granular as individual presentation tables or columns. Catalog-level security only works at the whole subject area-level, though sometimes you might want to use this approach even over repository-level restrictions. Let’s walk through a scenario to see what approach works best and when.
Consider a situation where you have a repository with two subject areas, both of which then map back to a single business model. One of the subject areas contains sales data on stores and products, whilst the other contains survey (quality assurance) on your store network. In general, everyone in the organisation can see the sales data, whilst only QA Managers can see the survey data, and to facilitate this you’ve created an LDAP group, and corresponding application role, that you can grant to users who perform this role.
Now unlike the application roles that come pre-created with OBIEE 11g, such as BIAdministrator, BIAuthor and BIConsumer, QA Manager in this case is a special type of role that is just used to grant access to objects; users that are granted this role will also be granted more general roles such as BIAuthor or BIConsumer which will actually grant them more general access to system functionality (we’ll cover this topic more in the next couple of posts). For now though, we want to set up our system so that only users granted the QA Manager role will be able to get access to the data in the Sales – Store Quality subject area shown in the repository screenshot below.
So the first way that you can do this, is to double-click on the Sales – Store Quality subject area in the Presentation Layer of the repository, and then with the General tab selected, press the Permissions… button.
This then brings up the Permissions dialog for this presentation layer object. By changing the permissions of the BIAuthor and BIConsumer roles from the Default value (read/write) to No Access, and then setting the permissions of the new QA Manager role to Read/Write, only users who have been granted this role will be able to view data from this subject area in their analyses.
So let’s try it out. I’ll start by creating a new analysis as the biadmin user, which exclusively uses subject area columns from this repository subject area. Here’s what the analysis results look like:
If I then log back in as the user qmorton, who has been granted the QA Manager role beforehand, the analysis and dashboard display with no problems, as that role has been explicitly granted access to that subject area in the background. But what happens if another user, in this case sfrancesco who only has the BIAuthor role granted, tries to view this dashboard?
In this case, they see nothing, as they don’t have permission to view tables and columns within this subject area in the repository. What happens then if the analysis is subsequently amended to include columns from a subject area that the user does have permission to use; in this case, the Sales – Fact Sales subject area? Let’s add the Quarter column from that subject area to the analysis, which also features in the restricted subject area, and where both subject area columns in the end map-back to the same logical column jn the Business Model and Mapping layer of the repository.
So, this is working pretty well. The BI Server is stopping any user who doesn’t have the required permissions from viewing data from the repository in their analyses, and will give you a reduced results set if you can see some of the columns, but not others. And this approach works pretty well especially if your repository sources data from multiple databases, OLAP cubes and so on, where it may not be practical to enforce subject-area security at the database level, but it’s relatively straightforward to enforce it at the combined, repository level.
You can, of course, take this type of security down to the individual presentation table and column level, so that, for example, you could create an HR Manager role that is the only one, apart from administrators, that can see the Date of Birth column for staff:
Users with this role granted, along with administrators, can see this column in their analyses, like this:
Whereas users without permissions on this column can see the analysis, but just not that particular column.
Those users without permissions on that column, or table if you’d configured one this way, wouldn’t even see the column or table in the Subject Area pane in the analysis editor, which makes this a pretty good way of hiding data from users who don’t need to see it (or aren’t allowed to see it).
But what if, instead of restricting data at the repository level, you wanted to create a situation where users still couldn’t create their own analyses against data they shouldn’t see, but they can view analyses and dashboards created by others, who do have the required permissions, if one of those users shared the analysis or dashboard with them? On other words, can we control rights to make user of subject areas in the analysis editor, but still leave open the possibility of viewing reports using this data that are shared? To do this, you’d enforce subject-area security not at the repository level, but at the Presentation Server level instead.
Let’s take off the subject area restriction currently in the repository for the Sales – Store Quality subject area, so it’s now back to the default settings:
At this point, all users who have one of these roles granted can access data from this subject area. Now, though, let’s move across to the Administration page on the Oracle Business Intelligence website, where there’s a link under the Security heading called Manage Privileges.
By clicking on this link, you can control access, through the analysis editor interface, to individual subject areas. As you can see, all users with the BIAuthor role down can make use of all subject areas in their analyses.
If you wanted to make it, though, so that only users with the QA Manager role could create new analyses using the Sales – Store Quality subject area, along with users with the BIAdministrator role and the system account BISystem, you’d click on the existing BI Author Role link and then change the settings so that:
- The BIAuthor role is removed from the list
- The QA Manager role is added, with the Granted permission, and
- The BIAdministrator and BISystem roles are also added with this permission
The reason that you don’t just set the BIAuthor permission to Denied (it’s other setting), is that any users with the BIAuthor role, even if they’ve also got the QA Manager role, will be explicitly denied access to this subject area, something you probably don’t want – this may well be an option you do want to use in other situations though, when you explicitly don’t want a certain group of users to be able to create analyses against a certain subject area. For now though, you just want to remove this role’s default ability to use this subject area, so you just remove it from the list.
The reason that you have to also explicitly add the BI Administrator and BI System roles to the dialog is that normally, they’d inherit the permissions of the BI Author role, so you wouldn’t need to list them separately. As we’ve now removed this role from the permission list, we need to add them in their own right.
So now when a user with the QA Manager role logs in, they’ll be able to see the subject area when they go to create a new analysis, or add an additional subject area into an existing analysis, like this:
Whereas users without this role granted, won’t see the subject area as an option in this type of situation, in this case seeing nothing as there are no other subject areas in the catalog and repository that share the same underlying business model.
But … and here’s the difference between this, and setting permissions at the repository level; this user can still access analyses and dashboards created using columns from this subject area, they just can’t create or edit analyses that use this data.
If such a user does try and edit an analysis using these columns, they get an error message instead, like this:
So really, how you enable subject-area security is down to your particular circumstances:
- If you want to ensure only users with the correct role assigned can see this subject area’s data, right down to the table and column level, use repository-level permissions, whereas
- If you just want to restrict users’ ability to create and edit analyses against a particular subject area, but you don’t mind if they view a shared analysis that includes this data, then set the restrictions at the catalog/Presentation Services level.
The other restrictions that you can apply at the Presentation Server level are at the product-functionality level, and at the catalog object level. Consider a situation, say, where you wanted to restrict only certain users to be able to create KPIs and Scorecards, because for example you were evaluating their use within the BI team before deciding on whether to upgrade to Oracle BI Foundation. By default, the BI Author role can access the KPI Builder, whilst the BIConsumer role can access scorecards:
If, however, you changed these permissions so that only a new KPI Builder application role, plus the BIAdministrator and BISystem roles, could access this feature:
and did the same for the Scorecard privilege, then any user that didn’t have this role granted wouldn’t see these options available when they went to create a new BI object.
They would though, as with the subject area privileges, be able to view KPIs and scorecards created by others that had then been shared with them.
Finally, you can of course set permissions on individual catalog objects, including folders of objects. From the Catalog page in the Oracle BI website, from the Home page or when using the Catalog Manager utility in online mode, the default permissions on objects can be altered to grant or deny access to individual users, application roles, or for backwards compatibility, catalog groups.
If, for whatever reason you end up setting these permissions wrong and locking yourself out of a particular part of the catalog, remember that the Catalog Manager utility can also open catalogs in offline mode, which ignores object permissions. However, this is really an administration-only task, and is typically used when migrating catalog objects between environments particularly when working with the users’ private folders area of the catalog, which in online mode even administrators don’t have access to.
Anyway, that’s it for the basics of OBIEE security now. Tomorrow, we’ll start to take a look at the tricky stuff, starting off with managing the WebLogic Server LDAP server, the application roles and policies that come with Fusion Middleware 11g, and how we migrate and move all of these security artefacts between environments.