Oracle BI EE 11g - Security Auditing

BI EE Web Catalog security is an area that generally requires a lot of understanding especially while migrating Web Catalogs. There is not a lot available in the documentation that goes into detail on how .ATR files store security. Its a known fact that doing a file system copy of reports and dashboards from one web catalog to another can potentially corrupt the security. But unless we know how .ATR files store the security, it will be very difficult to know how corruption can happen just by doing a file system copy. This is what i covered as part of my Open World Presentation yesterday. You can download the full set of slides from here. I will quickly go through the important parts of the presentation here.

Binary Representation of Web Catalog Permissions:

In BI EE web catalog we can assign quite a lot of permissions(shown below) for each user/role. Its important to understand how the permissions are represented internally. BI EE follows a binary representation (similar to the unix folder permissions - 777,775 etc). BI EE uses a total of 16 bits to represent the Access Control List (basically the permissions) as shown below

So basically each permission setting like Full Control, Open, Modify etc will have a corresponding decimal representation which is given below.

Binary Representation of Accounts Structure:

While assigning users/roles to a catalog object, they are stored internally in a binary format. The screenshot below shows how they are actually stored.

As you see the above uses a total of 7 bits to represent accounts.

Decompiling .ATR files:

As i mentioned above, all the accounts, their corresponding permissions that are relevant to a catalog object are stored within the .ATR file. The above binary representation is also stored in the byte code of the .ATR file. But we need to know how to interpret the file and look at the contents without opening up catalog manager.

Understanding Application Role & Permissions Storage:

While assigning permissions to an application role on a catalog object, both the permission and the account get pushed inside the .ATR file. So to understand how they are stored internally lets open up the .ATR file of a sample folder called Permissions Test in a HEX editor.

As you see, the application roles are stored in ASCII within the .ATR file. But permissions are not stored in normal ASCII. Instead they are represented in HEX (basically the 2 bytes immediately after the Application Role names as shown below)

Understanding User & Web Catalog Groups Storage:

Unlike Application Roles which are stored in ASCII format, users and web catalog groups are stored in an encrypted format. So, it is not straightforward to understand how they are stored internally.

If you notice, the user analyst has a code of 5DCF9000EEDA1E1BFF0D99317CAD3C9. This is basically the code for the user. It is a reference to a file that exists under /system/security/accountids/xxx folder in the webcatalog. In addition to that there will also be a separate analyst user object under the /system/security/users/xxx folder.

If we now look at the HEX code of the encrypted file itself, you will notice that it has the analyst user reference as shown below.

The permissions are stored in the same format as the application roles (2 bytes after the encrypted code). Same will be the case for application roles as well.

In addition to the above, i had covered how user permissions get applied through application roles through various scenarios. I will not be covering that here. Instead you can download the slides directly from the link above.