How-to: Direct Database Request Configuration OBIEE 11g

The ability to execute SQL statements directly against the data warehouse, also known as 'Direct Database Requests' is not new to OBIEE 11g. Usually used a last resort, the direct database request has many limitations including:

• Physical SQL statement is always executed
• Does not utilize OBIEE 11g's BI Server engine for maximum query optimization
• Increased run time

None the less, if a client has a specific reporting requirement that cannot easily be tied into an existing data model, then the direct database request becomes a viable alternative.

As most OBIEE Architects know, to enable Direct Database Requests is done within Answers in the 'Manage Privileges' Section:

After applying the above security privilege, the 'expected result' is for a member of the applicable application role to be granted the ability  execute direct database reports. Which in turn leads to a surprise when the user is still unable to execute a direct database request.

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 13017] User or application role has not been granted the Direct Database Access privilege to access the database. Please verify the User/Group Permissions in the Oracle BI Administration Tool.Please have your System Administrator look at the log for more details on this error. (HY000)
Please have your System Administrator look at the log for more details on this error. 

Why?

What many developers forget to remember is that components of security are still managed within the repository, even in OBIEE 11g. 

The ability to control who can execute direct database requests is managed within the Admin Tool's Identity Manager. For every applicable application role, you must grant direct database requests to each specific subject area:

Manage -> Identity -> Application Roles Tab -> Permissions

Note that by default 'Execute Direct Database Requests' is set to ignore, which inherits privileges granted by its parent security role. This normally implies 'disallow'. By setting the  the permission to 'allow' , you are granting the user the ability to run a direct database request against a specific subject area.

From a security perspective, granting access to specific subject areas is the preferred method of authorization when the alternative is a blank 'grant' or 'deny' via Manage Privileges.

As outlined in my primer on object level security & inheritance , inheritance applies but direct approval or denial supersedes any implied security rules.

keywords: identity manager, direct database requests, obiee 11g, answers, manage privileges, security, OPR4ONWY:U9IM8TAC:OI2DL65P