Oracle OLAP User Privileges Explained

Mark Rittman -

A recent poster on the OTN OLAP Forum asked the question "What are the minimum privileges needed by an Oracle user when viewing BI Beans reports against an analytic workspace?". This was a question I was thinking about as well, so I decided to do a bit of investigation.

There are three main Oracle roles that concern us in the Oracle 9i OLAP world;

  • SELECT_CATALOG_ROLE (the least privileged role)
  • OLAP_USER
  • OLAP_DBA (the most privileged role)

According to the Oracle OLAP Developers Guide, the SELECT_CATALOG_ROLE role allows users to view and use existing metadata in the OLAP Catalog, giving them enough system privileges to view a report. By granting this role, and the CONNECT role, users will be able to log in and view reports against data held in their own schema.

If the data they are going to view is in another schema, you'll also need to GRANT SELECT ON <AW$xxx> TO <user>; to enable them to view the other schema's analytic workspace. Metalink note 231381.1 explains this in more detail.

If you want users to be able to create and manage OLAP Catalog metadata, plus standard form analytic workspaces in their own schema, they should be granted the OLAP_USER role (as well as the CONNECT role as before). According to the Oracle OLAP Developers Guide, "Any OLAP user who will be performing these tasks should have the OLAP_USER role or equivalent privileges"

Lastly, if users need to create and manage OLAP Catalog metadata, plus standard form analytic workspaces in any schema, they need the OLAP_DBA role. According to the Developer Guide, "The OLAP_DBA role is granted with the DBA role. Care should be taken in extending this privilege to additional users." This is important because the OLAP_DBA role allows users to create and drop tables in any schema, which could be used as a way of tampering with non-OLAP objects in any schema in the database.

If you're interested in the particular system and object privileges that come with each role, you can load up Enterprise Manager and view each role's details using the 'Security' > 'Roles' navigator node.

To summarise then;

  • The minimum system privileges required by a BI Beans OLAP user are CONNECT and SELECT_CATALOG_ROLE
  • If the analytic workspace is in a different schema, they'll need SELECT privileges on the AW$ table concerned
  • If they want to create new OLAP_Catalog entries, or standard form AWs in their own schema, they'll need the OLAP_USER privilege
  • If they want to create new OLAP_Catalog entries, or standard form AWs in *any* schema, they'll need the OLAP_DBA privilege, but use this with care.

Many thanks to Oracle Support in Germany for their assistance with this issue.