Is it feasible to pass a business objects user login (currently we are using Active Directory) into a sql query restriction in the Universe?
where username = @BO_USER? or something to that affect?
Thank you.
TheSource (BOB member since 2006-01-13)
Yes.
@variable(‘BOUSER’) is the appropriate syntax.
You should read: Using Designer to Implement Row-Level Security
Steve Krandel 
(BOB member since 2002-06-25)
Works perfect. Thanks for the help!
TheSource (BOB member since 2006-01-13)
we are facing a similar situation. Because we are using active directory, does BO also evaluate the A/D group/s that the user is a member of?
If so, should attempt to design a mapping table that defines the relationship between the A/D group and the values that we are filtering per group?
vault (BOB member since 2009-05-28)
Can the BOUSER variable leverage Active Directory to enforce row-level security? For example, we need to have only a certain group of users see sensitive rows of data in a report, and everyone else see regular data in the same report.
There is a data flag on only one database table. Derived tables have been set up in the universe so that the common objects that render sensitive data will check that flag, but the possibility exists that users can circumvent that table and get the sensitive data by not including an object from that table. So we thought of BOUSER, but are not sure if it can leverage Active Directory to make sure only certain users see certain data rows. If not, is there another way to implement this without creating a security table in the database that must be manually maintained?
Thanks in advance for any suggestions.
BOSleuth (BOB member since 2005-05-05)
You have to create the security somewhere. I don’t think there is any ability to have AD deal with the details of row-level security. It could be possible if you create groups in AD that would mimic the restrictions you have. I believe restrictions can be OR’d together now, so that may help. The bottom line is that you have to do the work somewhere.
Steve Krandel (BOB member since 2002-06-25)
Thanks Steve. I agree we need to implement it somewhere and we are at the point of either trying to leverage something already in place or creating something new. Looks like I’ll need to meet with the DBA and AD teams to figure out the best way to do this. We already have Access Restrictions set up that points two different groups to their respective database instances but also need to apply this row-level security to everyone, regardless of the instance to which they are pointing. This is a bit more challenging than I thought it would be…
Thanks,
Sleuth
BOSleuth (BOB member since 2005-05-05)
Hi BOSleuth,
If you’re using XI3, there is a new feature that might help you. Predefined conditions can be set to “Apply on Universe” or “Apply on Class”. These cause the condition’s WHERE clause to automatically be added to queries.
Keep in mind, however, that any implementation of BOUSER for row-level security can easily be cracked by modifying the report’s SQL.
Joe
joepeters (BOB member since 2002-08-29)
While I agree with Joe that it can be cracked…
End-users should never have access to the SQL. We don’t even allow them to view it. Only developers can view or modify the SQL being used.
Yes, I know they can always use the DataProviderSQL function to get to it. But, even if they could figure that out, they can’t modify it from there.
Steve Krandel (BOB member since 2002-06-25)
My only issues with BO applied security are:-
a) it only applies to the BO application, unlike a security table which can be used for more than one BI app.
b) Although I’m sure this is avoidable if done correctly, a lot of people lose their restrictions, either when they migrate, or when they upgrade.
My 10 cents…
In Oracle you can look at VPD - Virtual Private DB, although I understand this relies on views, maybe someone can confirm, so there must be a performance overhead?
Mak 1 
(BOB member since 2005-01-06)
Thanks. We are using XIR2. Any neat security tricks for that version to avoid having to manually maintain a DB security table? I think Steve’s thoughts are how we’ll need to go if the security can be circumvented in any way whatsoever…
BOSleuth (BOB member since 2005-05-05)
Oracle Views are an option, but you’d have to use passthrough authentication in that case.
Or self-joins, which perform the same (well, similar) function as the universe-level Predefined Condition that I referred to.
joepeters (BOB member since 2002-08-29)
Thanks for the suggestions. However, we are still stuck. Turns out that we cannot implement a security table on the reporting database because the entire contents are overwritten nightly by an ETL job. This reporting database is near-transactional (within 10 minutes of an update to the transactional database.) Are there any other suggestions? Every solution I’ve considered does not seem practical or manageable and I want to see if I may be mising something. Here’s what I considered:
-
Row-level restrictions (under manage security in Designer): I restricted the one table that has the flag, but if that table isn’t used in the query, the security doesn’t work.
-
Restricting every table that contains the objects in question: The number of tables and objects are growing daily, so this would not be practical to include in the overloads. There are over 100 objects and 40 tables that need to be restricted.
-
BOUSER - Ruled out because the reporting database gets wiped out and copied over nightly. The security table could not be maintained there.
-
Duplicating all the objects, putting them in their own folder and applying object-level security to all objects in that folder, then creating a group in the CMC with the same level of access as the objects: This is tedious considering the ever-growing nature of this universe (I am NOT the Designer of this universe, just the BO Administrator).
Can anyone suggest any other options? Anything would be much appreciated. Thank you.
BOSleuth (BOB member since 2005-05-05)
Couldn’t you put this security table in it own schema(Oracle) or database(SQL Server), to stop it being overwritten?
Also, your ETL / Loading / Replication coders don’t have to drop / overwrite every table in the schema / DB, it would just require work, to stop the dropping of, the security table, on their part…;).
Mak 1 (BOB member since 2005-01-06)
If you are on 3.0 or higher, you can create a “universal” flag condition that will be applied to every single query from the universe.
Designer XI 3 New Feature: Class Restrictions
Dave Rathbun (BOB member since 2002-06-06)
Sorry, but I don’t see the problem. As suggested, put the security table in a different schema (Oracle) or database (SQL Server). You can join tables across these with no problems. As long as they are on the same instance.
There are lots of ways to solve the security problem, but the reality is that you have to have the data somewhere. You either build it manually in BO, or put it in a table. You just can’t get around this.
Steve Krandel (BOB member since 2002-06-25)
Thanks for the suggestions. Dave, we are on XIR2, so we don’t yet have the universal flag option, unfortunately. This is a really tough one. So far the security table seems to be it.
Question: Can a security table be created with a derived universe table? I’ve never seen it done, but I know you guys are B.O. heavy-hitters so if anyone would know, you would.
Thanks,
Sleuth
BOSleuth (BOB member since 2005-05-05)
Well, as a derived table is really just an inline view, you could only do this if the actual security table(s) existed somewhere.
Also, security queries are quite heavy hitters, performance wise, I would always go with a standard table.
Mak 1 (BOB member since 2005-01-06)
Thank you!
Steve, I read your presentation on implementing a security table in version 6x. It looked really good, but like most presentations, the details are lost when you are just reading the document and were not there to hear the verbal information. So I’m trying to see if I interpreted it correctly:
-
We would need to create a Security_Table in the database schema that would list the following:
USER_ID Department Data_Indicator
Mary Sales 1
Sue Support 0
John Sales 0
Tracey Support 1
(In our case, if a user has a data indicator of 1, they can see the sensitive data, if they have an indicator of 0, they cannot).
- (And this is where I am unclear): In Designer, for each of the derived tables that are associated with the objects in question, we would include a subquery that references the security table, such as:
Select object1
from derived table1
where (joins)
and Security_Table.data_indicator in (select data_indicator from security_table where user_id=@variable(‘BOUSER’))
Is that correct?
One last question: We have Restrictions (overloads) set up in the universe where one group points to DB schema1 and the other points to schema2. The schema1 group should never see the sensitive data. The schema2 group contains users who should or should not see the sensitive data. I understand that a security table would be needed for schema2, but could we manage schema1 somehow without a security table that contains all zeros in the data_indicator field? Not sure how this would work.
Thanks a million for your knowledge and assistance.
Sleuth
BOSleuth (BOB member since 2005-05-05)
In the above, I meant database ‘instance’. Thanks.
BOSleuth (BOB member since 2005-05-05)