How we will do data level security?if we have 4 (A,B,C,D)groups?in each group we have 10 users?if A group user entered if he run a report he can see 5000 records in report,if b group user entered he can see 8000,C group user entered he can see 10000 records,if D group member entered he can see 12000 records?How we will do?
Please follow the below steps to configure Data Level Security in OBIEE11g.
1. Login to Console and try to use the existing groups BIConsurems, BIAUthors and BIAdministartors if suited for your requirements. If not create the new groups based on your requirement
2. Add the users to the group
3. Log in to EM, Use the existing roles if applicable, if not create new roles and assign proper roles like BIAuthor, BIConsumer and add the correposnding groups created at step 1
4. Now open RPD in online mode and goto Manage -> Identity and then Action -> Synchronize Application Roles, Now in the Applciation Roles tab in Identity manager windows you will see the new Application Roles created at EM are present
5. Now open properties of Application Roles and click Permissions and then Data Filters and specify the Data Filters for the corresponding cols in the corresponding Subject Areas
6. In the Data Filters you can have hard coded value in the right side, if not you can have a Session initialization block and can have a SQL query which gets the corresponding site/region information from a database table and store the value into non system session variable as a single value of row wise initialization variable
Once you are done with this save ur changes in RPD and then reload metadata services in Administration tab in analytics and re-login. Now you'll see Role Based Dashboards. You can verify the roles and groups that you are part of in the My Account -> Catalog groups and Catalog Roles...
Generally If User 'A' want to display 5000 records...and User 'B' want to display 8000 records.... you will have a constraint
Say, Group 'A' users should see only Region 'America' records....(ie general sql, u will write select * from table where group='A' and region ='America')
Say, Group 'A' users should see both Region 'America' & Austalia records....(ie general sql, u will write select * from table where group='B' and region ='America' &'Australia')
you can do this at RPD level filters..for thsi u can create a new Group 'A' & 'B'...... in manage-->security--> new group...
then......click on 'permissions'.............navigate to 'filters' tab........... add the FACT table SQL condition as per your required explained above...
for this...yes...you need to create a Session varaible....called 'Region'..