Row Restrictions in Supervisor

I asked this question on 12/30 and didn’t receive a response. I suppose that’s because…

  1. There is no answer
  2. The answer is so obvious that everyone thought someone else would tell me
  3. The question has been asked so often that the answer is in the archives and everyone thought someone would point that out to me 4. Nobody understood the question
  4. I’m invisible :slight_smile:

So if there’s anyone who can help me I would appreciate it and hopefully return the favor one day!

Cindy

From: Clayton, Cindy, HRGBM

Hi all,

I’m BO 4.1 Oracle 7.3. I’m adding row restrictions to a user. When I click on the ‘Table’ button, I see a list of available tables to use to place restrictions. Why is my list void of any table columns when I press the ‘Where clause’ button?

Cindy Clayton - Business Objects Consultant AT&T
336.698.2144

Give people more than they expect and do it cheerfully. Talk slowly but think quickly.
Smile when picking up the phone. The caller will hear it in your voice. Mind your own business.
Learn the rules then break some.
Judge your success by what you had to give up in order to get it.


Listserv Archives (BOB member since 2002-06-25)

Cindy,
If your table has an alias name, then this is a bug which is fixed in version 4.1.6.

Vantive case CS00302094659 Bug Number 32738
Alias table does not display the column names when adding a row restriction.

Davia

I’m BO 4.1 Oracle 7.3. I’m adding row restrictions to a user. When I click on the ‘Table’ button, I see a list of available tables to use to place restrictions.

***************************************************************************** The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorized.

If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. When addressed to our clients any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing KPMG client engagement letter. *****************************************************************************


Listserv Archives (BOB member since 2002-06-25)

Hi Cindy,
To show that you are not invisible, I will make a feeble attempt to help. :stuck_out_tongue:
We came across this problem a long time ago when we were using SQL 6.5 (we are now using SQL 7.0). I am trying to rack my brain as to what we did to get around it, and I remember that it had something to do with the ownership of the tables. I think that we had to rename the tables so that the owner was stripped off it in the Designer piece. For example, dbo.TableName was renamed to simply TableName. The catch is that when you did this in Designer so you could see the tables in Supervisor, you prevented Designer from being able to refresh the table structure. I also think that this was specific to SQL 6.5 and not SQL 7.0, and that when I had called tech support this was the first time they had become aware of this.
That is as much as my little brain can remember on that subject: hope it helps. Simon


Listserv Archives (BOB member since 2002-06-25)

Cindy,
I’ve noticed this too.
For us, it’s not a show stopper (and in my experience even time consuming).

I just select the table, then cut and paste the table name into the where clause and manually type in the column name and value (you probably do the same thing). I use the check option allows for syntax checking on possible typo’s.

Cut and paste is a blessing if you need to repeat for many folders/users, but as I said before time wise I’ve found in Designer having automatic selection panels is more time consuming than just typing and running checks

I’m sounding like an apoligist, but there for me, Business Objects have much bigger fish to fry (like being able to do inter column calculations in Webi)

Simon


Listserv Archives (BOB member since 2002-06-25)

Be careful relying on the Check option. It only checks to see if the table is valid in the universe, it does not check the syntax of the where clause. (at least not in 4.1.3)

Shelley


Listserv Archives (BOB member since 2002-06-25)

Thanks for all the responses. I guess I’m NOT invisible after all.

I agree that it is slower to use the >> button to create the where clause. It was mostly just my insane, insatiable curiosity that made me ask in the first place. I had hoped that the reason my where clauses weren’t checked was BECAUSE the field names weren’t appearing. I would have written the where myself anyway because it’s so darn slow to bring up the where clause box. The only benefit I could see would have been the syntax check but since you say that 4.1.3 doesn’t check that anyway and I’m on 4.1…

Thanks all!

Cindy


Listserv Archives (BOB member since 2002-06-25)

In a message dated 00-01-05 10:52:10 EST, you write:

Be careful relying on the Check option. It only checks to see if the
table is valid in the universe, it does not check the syntax of the where clause. (at least not in 4.1.3)

Shelley

It doesn’t even do that, in my experience. The “check” option in row restrictions is NOT the same as “Parse” in Designer. In fact, I’ve not been able to figure out what it does. No matter what I do in the where clause, the check always returns “OK”.

Try it - put in a table name that you know is invalid, and try using the check button. Or leave quotes out of a text condition. Or spell a column name wrong. Any of these cases (using the Island Resorts Markting universe as a check point) results in an “OK” from the check button for me. So I don’t ever use it. :slight_smile:

As to the original question: I can’t reproduce the problem, nor have I ever seen it at a client site. So I don’t have any words of wisdom (or otherwise) to share.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com


Listserv Archives (BOB member since 2002-06-25)

I know the check option works in one instance ;

I’ve renamed a core lookup table in designer (from the actual table name to a restricted view name).
After exporting the change
Supervisor found the the restiction on the “physical table name” (even though it’s still in the DB) is no longer valid (so I updated the restriction to the view name)
I guess it makes sure that the Universe that is being restricted has a reference to the Table name thats being restricted

Re - Check option :
It doesn’t even do that, in my experience. The “check” option in row restrictions is NOT the same as “Parse” in Designer. In fact, I’ve not been able to figure out what it does. No matter what I do in the where clause, the check always returns “OK”.


Listserv Archives (BOB member since 2002-06-25)

You are correct. As I stated before, the “check” option does not check the syntax of the where clause. It does; however, check that the table you selected in the table field (not the where clause) exists in that universe. If you manually type in a table that doesn’t exist in the universe, when you click on check, the status will read “Not in Universe”. Again, this is independent of the where clause.

It doesn’t even do that, in my experience. The “check” option in row restrictions is NOT the same as “Parse” in Designer. In fact, I’ve not been able to figure out what it does. No matter what I do in the where clause, the check always returns “OK”.

Try it - put in a table name that you know is invalid, and try using the check button. Or leave quotes out of a text condition. Or spell a column name wrong. Any of these cases (using the Island Resorts Marketing universe as a check point) results in an “OK” from the check button for me. So I don’t ever use it. :slight_smile:


Listserv Archives (BOB member since 2002-06-25)

For what it’s worth, I found a case where it’s not “OK” – when the table restricted is no longer in the universe!

Now if anyone can tell me some clever way to parse hundreds of row restrictions, I’ll be an exceptionally happy camper. :wink:


JennFisher :us: (BOB member since 2002-06-25)

I can think of a way, but it might not be nice or fast.

If you pull out OBJ_M_UNIVDBCST.M_UNID_C_ITEMSRC and OBJ_M_UNIVDBCST.M_UNID_C_ITEMDST, you have a list of restricted tables and the restrictions based on them.

With a bit of fiddling, you could probably create something like…

select * from <Restricted Table In Here> where <Restriction In Here> and rownum < 11

If the query returns rows, the restriction is valid. If it bombs, there’s a problem.

I’m not sure how you’d run a load of these at once and pick up which are running/failing though.

Which database are you using?


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

We’re on Oracle 8i.

I ended up writing a report against the Security universe, pulling back each row restriction. I came up with a few things to check like number and location of parathesis, making sure I typed IS NULL instead of NOT IS NULL and other characters that made us feel 99.44% sure the restrictions were good.

Complicating our issue is that while some of the tables are restricted the restrictions don’t really work just yet. We’re restricted on a disease_id but some of the tables are only used by one disease. We decided to go with the “best practice” of restricting the table for all diseases just in case, one day, it becomes a multi-disease table.


JennFisher :us: (BOB member since 2002-06-25)