OBIEE 11g Security Week : Subject Area, Catalog and Functional Area Security

March 13th, 2012 by

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.

Sshot 1

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.

Sshot 2

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.

Sshot 3

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.

Sshot 4

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:

Sshot 5

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?

Sshot 6

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.

Sshot 7

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:

Sshot 8

Users with this role granted, along with administrators, can see this column in their analyses, like this:

Sshot 9

Whereas users without permissions on this column can see the analysis, but just not that particular column.

Sshot 10

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:

Sshot 11

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.

Sshot 12

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.

Sshot 13

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

Sshot 15

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:

Sshot 16

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.

Sshot 17

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.

Sshot 18

If such a user does try and edit an analysis using these columns, they get an error message instead, like this:

Sshot 19

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:Sshot 20

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:

Sshot 21

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.

Sshot 22

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.

Sshot 23

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.

Comments

  1. Scott Powell Says:

    Thanks Mark – great post. This also points out a question I haven’t been able to get an answer to – namely how to get access to a user’s personal reports, etc. if that user is terminated. Was confused that the admin users couldn’t access them through either the web interface or the catalog manager (in online mode), but catalog manager offline should work. I’ll give it a try.

    Thanks for the great info!
    Scott

  2. Mini Says:

    Mark

    Thanks for the post explaining the basic security features in obiee 11g. Looking further on it…

  3. Michal Says:

    2 Scott Powell: To see privat reports of other users online you can use the so called Impersonate user access. Only if you have admin rights, of course.
    It is described here: https://blogs.oracle.com/pa/entry/how_to_use_obiee_impersonate

  4. Michal Says:

    Hi Mark,
    thank you for this handy guide.
    I think we should mention a possibility that users can access BI server not only by Answers but also by an ODBC client (such as MS Excel).
    It means if you restrict an access to some subject areas at the catalog level, it can be too late. Users can bypass this restriction.

  5. Karan Chadha Says:

    Mark,

    Refering to your point – “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”, cant we achieve this at repository level itself by selecting the ‘Read’ option for that particular role?

  6. Jolin Says:

    Hi Mark,

    I tried to set repository level access rights follows your guide. But I encounter a problem:

    A Role has “read/write” rights of a table; But don’t has rights to some columns.
    When display analysis result, these columns can be hidden to the Role; But when create analysis, all columns can be seen and avaliable for selection.
    Any setting needed? Just want to hide these columns when creating analysis, too.

    Thanks a lot

  7. Jolin Says:

    Hi Mark,

    I encountered a problem after I set up repository level access rights:
    A role has “read/write” rights of a table, but has no rights to some columns of the table. Data of these columns can be hidden when see result. But these columns are still visible and available for selection when creating analysis.
    I use 11.1.1.6. Would you help advise?

    Thanks a lot:)

  8. Ashok Says:

    Hi Mark,

    I have created an user and associated with BIAuthors Group. It does not allow to login and it works when I associated to BIAdministrators Group.

    What would be the issue exist with this?

    Regards,
    Ashok

  9. Julio Sanchez Becker Says:

    Mark, your articles really helps! We have a problem with Security in OBIEE 11.1.1.6.2
    We migrate from OBIEE 10 to this version and now we are using LDAP services to manage policies, roles and group users. The problem is that for a specific group users we need to grant read access to the group user on a specific dashboards and denied them access to edit anlayses against that subject area but we don’t have success with that. From the Repository we grant access to the group user to that subject area and deny access from OBIEE (via Manage Privilegies) in the specific Subject Area. In this scenario we can see the subject area for edit analyses against the subject area and we can execute the dashboard. When we deny access in repository and deny access from OBIEE in that specific Subject Area (via Manage Privilegies) we didn’t see the subject area when we want to edit analyses but we couldn’t execute the dashboard. Any suggestions ? Thanks in advance. Regards, Julio Sanchez Becker.

  10. Pal Says:

    Hi Mark,
    This is a good article. Thanks.
    Can you please talk about managing security using “Catalog page in the Oracle BI website, from the Home page” ?
    Specially restricting users from certain tables from certain subject areas.

  11. Marjorie1 Says:

    Hi,
    Dashboard –> Subject Area –> Fact

    I’ve created a new fact table and brought it into my subject area but I can’t expand the columns…the permissions are okay because I’m the server admin. Any ideas?

    Marjorie

  12. obieefl Says:

    currently Troubleshooting an agent.. What’s the easiest way to view a catalog group in the OBIEE toolset?

  13. Lior Says:

    Does anybody knows how do I find out what permissions (dashboards / analysis / folders) have been set for each Application role?

  14. Olivia Says:

    Thanks just foг thiѕ post, І’m a big big fan using this web site would like to embark on updated.

  15. HPR Says:

    Hi Mark,

    Great Post!!
    I have created a KPI and then I have a user “Demo” with BI Consumer Role.
    Now I need to give the “Analyze” permission on the KPI to this user. can you please let me know how do I do that?

  16. Doug Rosenfeld Says:

    You can’t believe how many times Google brings back your excellent website. We always find your posts extremely helpful. This one was perfect! Thanks!

  17. JW Adendorff Says:

    Hi,

    Is there any way to set a condition on an entire dashboard and not just the section within a dashboard?

    We have a custom security model that we want to apply on dashboard level.

    Any feedback would be much appreciated.

    Regards

Website Design & Build: tymedia.co.uk