Business Intelligence – Oracle

Archive for June 30th, 2008

Oracle BI EE 10.1.3.3.3/2 – Public Reports and Dashboards – Bypassing authentication

Posted by Venkatakrishnan J on June 30, 2008

Another common question that keeps circulating is, “how do we bypass login to certain BI EE Dashboards?”. The most obvious answer to this question is to pass the username and password through the url. But certain security requirements/standards in some companies do not allow passing of passwords through the URL. So, lets look at a simple approach wherein some dashboards can be seen without passing the passwords through the url. The idea is pretty simple. Identify a user, for example PUBLIC, that would get direct access to certain dashboards. This user should not exist in the BI EE repository. All the other users would exist in the BI EE repository. So, the first step to achieve this is to create a simple init block with the below shown sql.

select ':USER' from dual where upper( ':USER' ) = upper( 'Public' )

Make the above init block to set the USER system session variable. Also, check the “Required for Authentication” check box. The init block can connect to any oracle database connection pool.

    

Now, if you use the below shown go url, the dashboards would be shown. As you see we are not passing the passwords through the go url.

http://localhost:9704/analytics/saw.dll?Dashboard&nquser=Public

    

The above approach uses the concept of external table authentication. Basically, the init block checks for the PUBLIC user and ensures that the user is authenticated without even checking for the password. All the reports/dashboards that can be viewed by the “Everyone” privilege can be accessed by this PUBLIC user. Very simple but can be used in certain situations.

Advertisements

Posted in All Posts, OBI EE Plus | 3 Comments »

Oracle BI EE 10.1.3.3.3/2 – Row Level Security and Row-wise Intialized Session Variables

Posted by Venkatakrishnan J on June 30, 2008

As you would probably know, BI EE provides the capability to do row-level security from the Business Model layer. There was a question the other day wherein a user wanted to know how to do row-level security, wherein more than 1 filter value come from some other table. For example, lets consider the standard SH schema that comes with Oracle Database. The BM for this schema would look like the one shown below

    

Consider another table PROD_SECURITY containing the columns USERNAME and PROD_CATEGORY.

    

So, basically, the above table provides the list of users and their corresponding product category for which the users have access to. Now, in our BM, the sales and the product table are joined at the Product level. Each product category can have multiple products and hence we would have to use row-wise initialized session variables to achieve this security. So, start with creating an init block which would populate a session variable PROD_FILTER with all the prod ids belonging to the product category for which the user has access to. The init block would use the sql below

SELECT 'PROD_FILTER',PROD_ID
FROM PRODUCTS A, PROD_SECURITY B
WHERE A.PROD_CATEGORY = B.PROD_CATEGORY

    

Once this done, use the below statement in the where clause of the content tab of the Sales data table.

ORCL."".SH.SALES.PROD_ID IN ( VALUEOF(NQ_SESSION."PROD_FILTER"))

    

So, basically the row wise initialized variable would be initialized to (100,200,….) etc. Lets check whether the security is getting properly applied from answers. Since, we have not secured on the dimension table, lets first include the products column in our report and see what happens to our report

    

As you see, it would list down all the product categories. Now lets include the AMOUNT_SOLD column from the fact table into our report.

    

As you see, the security has been applied and the user Administrator would be able to see only 2 categories for which he has access to.

Posted in All Posts, OBI EE Plus | 6 Comments »