Trying to make a long story short, we are considering, as a security solution, creating a SQL server view that limits access to certain rows in a table based on user ID. That is, something like

SELECT *
FROM base_table
WHERE authorization_table.user_id = (dynamically-passed user-id) and top_secret_flag = ‘Y’
;

Put another way, a “static” view contains a variable!

Is there any way that WebFOCUS can pass a Logon ID as a parameter to this kind of view? I’m doubtful.

The way I see it, the WF Master will not contain a logon ID (because it's not a column in the view), so there is no way that WF can pass the info to the SQL server. And, even if it was a column on the table, and visible via the Master, there is no way we could keep a user from hard coding an 'authorized' logon ID. Can anyone prove me wrong? (please!)

Hi,

Since a stored procedure is accepts parameters and returns a set of data as would do a view please consider using a stored proc.

Regards.

Thanks, Majid. I'll pass that suggestion on to the SQL server folks.

quote:

SELECT *
FROM base_table
WHERE authorization_table.user_id = (dynamically-passed user-id) and top_secret_flag = ‘Y’
;

Jeff,

You should be able to do this.

  

SELECT *
FROM base_table
WHERE authorization_table.user_id = (&IBIMR_user) and top_secret_flag = ‘Y’
;

Sayed

Thanks for your interest, Sayed.
Please note that the select statement is not being passed through WebFOCUS. It is the definition of the SQL server view. WebFOCUS will see only the filtered data from the base table, through the view, which does not contain user ID as a field.

But, you highlight the exact issue-- can I get SQL server to recognize &IBIMR_user as the user ID behind the WebFOCUS call to the view? My current opinion is that I can't, and that Majid's stored procedure suggestion is the best alternative so far. Unless you can describe a better way...

Jeff,

&IBIMR_user is a WebFOCUS internal value. Unless the SQL is passed from WF, &IBIMR_user will not be recognized. Stored proc is the way to go.

Sayed

Jeff,

We have been set up for some time to do exactly as you wish.

Instead of passing the WebFOCUS userid into SQL as a parameter to use as a variable in the view's filter statement, we use the WebFOCUS userid as the SQL logonid that creates the SQL session. Each WF userid has a like-named SQL userid [it helps that we're a single-sign-on shop (an employee's network ID is used at initial sign on, and he/she is never asked for credentials again). MS/Active Directory is our primary authenticator of credentials.]

With this setup, we had to use kerberos authentication to make the 3rd 'hop' to pass the ID into SQL. A/D only allows two passes of the credentails: #1 is the network logon, and #2 is the logon to WF. The logon to SQL would be #3, so we had to use kerberos technology to make that work.

The WebFOCUS data adapter referenced by synonyms specifies 'pass through' security.

Once the user logs onto SQL as 'himself' putting the SQL UserID into the view's IF filter is a snap!

We evolved a work-around for testing and high-powered 'top secret clearance' persons:
Two synonyms exist for the SQL tables. Ordinary users get the default search path that uses the above mentioned synonymns with the 'pass through' data adapter. 'Special' persons have a WF profile that puts an special appdir into high up in their their search path. That appdir contains identically named synonyms, but these use a different data adapter, one that's set for 'explicit' access to SQL using a special unrestricted userid/password.

Chris

Thanks, Chris!!
Though I don't know whether or not we use Kerberos here, this is the kind of automatic, user-transparent (and un-hackable) solution I'm looking for. I'll see what our tech folks have to say about it.

I don't know if this will help you at all, Jeff, but there is a reporting server security option called DBMS which forces the user to authenticate to the data base directly before a fex will run. We use it here on one server for Teradata. Check the Server Administration manual for your platform for more details.