BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

FAQ: Designer
44 members found this topic helpful
Goto page Previous  1, 2
 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Wed Aug 11, 2004 10:28 pm 
Post subject: How can I implement row level security?

icon_question.gif How can I implement row level security?

icon_idea.gif How to Implement Row-Level Security

First -- what is Row-Level Security? When you want to specify which rows of data each userid is permitted to retrieve from the database, based on some column of data, or combination of columns of data -- that is row-level security.

Row-level security can be implemented in BusinessObjects in several possible ways. If you have a tech support login, you can look at some of the techniques and the pros and cons in Steve Krandel's 2002 International User Conference presentation Using Designer to Implement Row-Level Security.

Here are some of the ways possible:

1.) By letting the database do it. This is usually the most secure, although for logistic reasons, many sites do not find it feasible. To make this work, individual logins have to be set up in the database for each user, and those logins need to correlate to the BusinessObjects login and password. A possible way is to have a security table that is joined to the fact table(s) -- via a view -- and use the view in BusinessObjects. For this to work, the universe connection will need to specify "Use BusinessObjects username and password". (This has sometimes been called "Advanced Login Strategy" in the past.)

2.) In BusinessObjects Supervisor -- for a given group or user, select the universe. Go to the Rows tab of Universe Properties. Click the Add button, and select the table to be protected. Enter the appropriate WHERE clause to be imposed. However, only 256 characters of a WHERE clause can be entered in this panel, which many folks find very restrictive.

3.) A workaround to this is to define the WHERE clause in a special object in Designer, and impose that object in Supervisor, using @WHERE(special_object) for the WHERE clause.

4.) You can impose the security restrictions in Designer, by using self-joins and a maintained lookup table. You might end up with self-joins along the lines of:
Code:
DataTable.Region in ( Select Region From SecurityLookup Where User = @BOUSER)



Using Designer to Implement Row-Level Security in BOB's Downloads has more information.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Fri Aug 31, 2007 10:00 am 
Post subject: Re: FAQ: Designer

icon_question.gif How can I use the current system date in an object?

icon_idea.gif You use a different function for each database. For example, Oracle users would typically use sysdate while Sybase or SQL Server users would use getdate(). Teradata uses DATE, and DB2 uses CURRENT_DATE. Ask your DBA if you are unsure which method is appropriate for you.

icon_question.gif Ok, so I have done that, but my object does not parse. How can I fix it?

icon_idea.gif These functions or pseudo-columns do not need to reference a table. Yet when Designer parses these and other objects it will create a full SQL statement as:
Code:
SELECT object FROM table

When you don't reference a table in your object definition, the SQL is not valid:
Code:
SELECT object FROM

Don't worry about it, the object will still work in a query as long as you include at least one "real" object with it. Do not be tempted to include the DUAL table (Oracle specific tip) as you will add more problems to the universe than you solve.

icon_question.gif What about other databases?

icon_idea.gif See the Designer Database Specific FAQ entry here.
Back to top
Anita Craig
Forum Groupie
Forum Groupie



Joined: 17 Jun 2002

Posts: 8541
Location: Palo Alto, California, U.S.A.


flag
PostPosted: Fri May 23, 2008 5:46 pm 
Post subject: Re: FAQ: Designer

How should I impose a WHERE clause on my object(s) in the Universe?

This question comes up very often, and the answer is that you usually don't want to do this if there's any chance that more than one object will have a WHERE clause on the same database column -- because the conflicting WHERE clauses will be ANDed together, causing you to get No Results.

From a recent post by Dwayne Hoffpauir, where a person wanted 10 objects on the same database column each with different WHERE clauses:

Dwayne Hoffpauir wrote:
Use a CASE syntax on the Select portion instead. Something like this:
Code:
CASE Table1.Column2 = 'String1' THEN Table1.Column1 END

Note that NULL will be returned if the condition is not met. You can specify an ELSE if you like. Assuming Table1.Column1 is numeric:
Code:
CASE Table1.Column2 = 'String1' THEN Table1.Column1 ELSE 0 END


Of course, this specific syntax only works if your particular database's SQL supports the CASE syntax. If it doesn't, then you should work with your DBA or co-workers to identify which alternate syntax to use instead.

_________________
Anita Craig Image link
Institutional Research & Decision Support
Stanford University Image link
____________________
Search is Your Friendô
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Tue Jul 15, 2008 9:23 pm 
Post subject: Re: What is an isolated join and how do I fix it?

icon_question.gif How can I provide a default value for a prompt?

icon_idea.gif Using standard code that is portable for all products you cannot do this as of XI 3.0. The standard prompt syntax via the @Prompt() function does not provide for a default value option. If your universe will only be used in a web environment then you can opt to make use of the extended syntax which will allow you to specify a prompt order, whether the prompt is persistent or not, and even include a default value. Note that these objects will probably not parse successfully as Designer is not aware of the extended syntax.

icon_question.gif Okay, so if I use the extended syntax, how can I make the prompt default to another object? or a formula, like today's date?

icon_idea.gif You can't. icon_smile.gif The extended syntax only supports having a constant value as a default. You cannot use a formula or a reference to another object.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 2 of 2 All times are GMT - 5 Hours
Goto page Previous  1, 2
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0344 seconds using 17 queries. (SQL 0.0080 Parse 0.0010 Other 0.0254)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo