Force a "where" to a user

security Supervisor (4.x - 6.x)
1

Hi all, is there some way to force a “where” to a user, eventhough the query of the user doesn’t include the tables of the “where” condition??? What I want is no matter what the user includes in his report, that a “where” condition appears in his SQL code.
Is this possible?
Thanks.

Roberto Domínguez

reverke (BOB member since 2004-05-13)

2

I think you need to give us a bit more info because if the table for the ‘where’ isn’t in the SQL, the SQL won’t be valid. Are you trying to enforce a subselect against a security table?

Cindy Clayton :us: (BOB member since 2002-06-11)

3

Thanks Cindy, let me explain you the situation.

I have this user who needs row level security but it’s not as simple as that. What I need is an “OR” condition. In my tables I have clients, accounts, and lines. Each client, account and line has a region, and not necessary has to be the same, for example, one client can be in region 9 and his line can be en region 6. And for this user I need that he can see the information where the region of the line is different from region 9, or the client region is different from 9 or the account region is different from 9.

This condition would be my “where” condition:
line_region!=‘9’ or account_region!=‘9’ or client_region!=‘9’

If I added the 3 row level conditions in the supervisor, it would be like an “AND”.

So what I thought is this: I’m gonna add the join of the tables in the universe in all my contexts, so the tables of the “where” condition appear in the query. Then that’s why I need to force the “where” condition to appear always.

I am not trying the security table because I am not sure if a security table would help me with this.

Thank you very much,

Roberto Domínguez

reverke (BOB member since 2004-05-13)

4

Can you put some of your logic into a view instead? And then return a set of ID values from one specific table based on your complex logic?

BTW, for performance reasons, you might want to consider an alternative to the logic you posted. Instead of

line_region!='9' or account_region!='9' or client_region!='9'

try to invert the logic, as in

! (line_region='9' or account_region='9' or client_region='9' )

instead. Moving the “not” ! outside of the logic allows you to use “positive” matching (equal to instead of not equal to) which is much more efficient. Ultimately you then “not” or negate the rows that would match, leaving only those rows that don’t match.

I don’t have time to validate the idea completely at the moment, but it’s something to consider. If it is logically equivalent it might end up being faster.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Thanks Dave, I will consider that. But now my main issue is how to force the “where” condition on every query of the user.
Do you know how to do this?

Thanks again.

reverke (BOB member since 2004-05-13)

6

Sure, go into every object definition in your universe and select the tables that you need. It’s not pretty, it’s very time consuming, and you have to remember to do it for every object. But that will force the selected tables into your query for every object in the universe.

Specifics: Select an object and double-click on it. Click on the Tables button. Scroll through and with the CTRL key highlight any additional tables that you want to include. That’s it, but it has to be done on every object.

Note that even if you have 20 objects in your query, the tables will only be included once, not 20 times.

Dave Rathbun :us: (BOB member since 2002-06-06)

7

Sorry if some of my question are obvious but I am pretty new with B.O. :oops:

What I understand here is that by adding the tables to the objects, I will force the joins between the tables.

But I don’t get it :confused:, how do I force the “where” condition on the sql of every query of this user (I only need this for one user). All I want is that no matter what is the query that this user creates, it has the “where” condition on it.

reverke (BOB member since 2004-05-13)

8

When a query uses a table, it will automatically use any self-restricting joins on that table. So if all your objects are forced to use a table, the join will be used.

Is this any use?

Paul Williams :uk: (BOB member since 2002-07-10)

9

If you only have it for 1 particular user, then none of this will help.

Be more specific with your requirement. What are you trying to restrict?

In general, its the data in 1 table that you want to restrict, not every table.

Steve Krandel :us: (BOB member since 2002-06-25)

10

I am trying to restrict the information in all tables for 1 user, without affecting the other users. :roll_eyes:

The restriction of this user is that he can only see the information where line_region!=‘9’ or account_region!=‘9’ or client_region!=‘9’ .

And these are 3 different tables.

I have an idea :idea: , maybe it will work if I create some views of these tables and they have all the regions, except ‘9’. And I make this user tu use this views instead of the tables.

reverke (BOB member since 2004-05-13)

11

I’ve seen a complex OR type row-restriction imposed, but it’s very inefficient. The condition is built in a hidden object in Designer. Then it’s imposed in Supervisor.

And the condition in effect runs a subquery – it uses a WHERE EXISTS clause that ORs the conditions together. I would expect that you could also force the joins on all of the tables in question.

Anita Craig :us: (BOB member since 2002-06-17)

12

You can do this using the Table Mapping feature in Supervisor.

Steve Krandel :us: (BOB member since 2002-06-25)

13

I think that Table Mapping can be a not so complex solution :yesnod: , I’ll see if it does what I want, if not… :hb:

Thanks everybody.

reverke (BOB member since 2004-05-13)

14

Cindy posted this a few days ago:

And this is the main idea of what I want, it doesn’t matter if the SQL is not valid, I just want to force that whatever the user do, he has the “where” condition in his SQL. Even if the tables are not related.

Is this possible? :roll_eyes:

thanks for all the comments, they have really help me a lot.

reverke (BOB member since 2004-05-13)

15

I’m confused. If the table is not used in the query at all, then what value is there to enforcing a condition?

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

16

This is the thing: I know I can force the joins in the objects and then force the “where” condition in the supervisor. But this joins will affect all users, and I don’t want that.

What I want is that if there is some way to first force the “where” condition, then just by adding the joins in my contexts the restriction would apply only to this user and not to everyone.

Now what I am thinking is make a copy of the universe just for this user. Of course this will imply more work in the universes administration, but at least I won’t affect other users.

This situation turned a little bit more complicated than I though at the beginning. :nopity:

reverke (BOB member since 2004-05-13)

17

The SQL has to be valid – meaning that if a table.column is reference in a WHERE clause – that table has to be in the FROM clause. So, you’ve got to do whichever steps are necessary – either in a view, or in a complex row-level security statement – that forces the table(s) needed to be in the FROM clause.

Anita Craig :us: (BOB member since 2002-06-17)