We never implemented any specific security in WF. If you get access to the portal - you get everything.

We now have a situation where we are acquiring companies. The good news is Company was pretty much always a dropdown list item on our reports....it just always had ONE value before.

What would be the simplest method to start to secure a given user, to only see THEIR particular company on all the reports we have in production.

Ex: I would think we will need a security table outlining each user logon and their company.

What else would we need to do to implement something like this?

This message has been edited. Last edited by: FP Mod Chuck,

Using a custom security table per user is an option.

You will then need a fex that will extract the security value based on the &IBIMR_user (the logged in user) and have a variable such as &CIE setup with the user profile. The fex could reside in the _universal_profile or server profile.
Then you will need to have your Cie drop down fex filtered using the &CIE parameter. That way it will only show what the user is allow to see.
You may also need to add the &CIE parameter to other fex to insure that only the authorized cie is selected.

Another option could be to use master file DBA feature. Many document are available on the subject.

since we prompt for company on just about every report could we perhaps bypass all this and simply adjust our Company Prompts to ONLY populate with the companies the user is secured to see...sort of feed the Company dropdown from the list of companies in the users profile...or something like that...

quote:

simply adjust our Company Prompts to ONLY populate with the companies the user is secured to see

This is exactly what I've suggested

1- Have a fex that will retrieve the user security profile
2- Assign a variable (parameter) with the user cie profile
3- Have your CIE dropdown fex filtered (WHRE clause) using above cie parameter

Pseudo sample :

-* To extract user security profile
-DEFAULTH &ALLOWED_CIE = ''
SET ASNAMES = ON
TABLE FILE SECFILE
PRINT ALLOWED_CIE
WHERE USER_ID EQ '&IBIMR_user';
ON TABLE HOLD AS UCIE
END
-RUN
-READFILE UCIE
-SET &USERCIE = &ALLOWED_CIE;

-**************************
-* To extract Cie drop down list values
TABLE FILE CIELIST
SUM CIE
WHERE CIE EQ &USERCIE;
ON TABLE PCHOLD FORMAT XML
END
-RUN

Above is just a pseudo code, many other things may be needed depending on how your data exist.
Above will work for a single row per user from SECFILE.
I do use that a lot, and have one row per user. But each user may have multiple values. In that case the column have multiple value in.

SECFILE Sample:

USER_ID ALLOWED_CIE
abc     1
def     1,2
ghi     ALL

Then
- if the ALLOWED_CIE is ALL the variable (&USERCIE) is assigned to FOC_NULL (or _FOC_NULL)
- if the ALLOWED_CIE contains coma (,), replace the coma by : " OR ". Which result in : 1 OR 2
- otherwise take ALLOWED_CIE as is.

Be aware that if you are processing alphanumeric value you will need to put quotes around each value ('1' OR '2')

Use DBA security. Create a process to set a "password" for the user and then do a value_where restriction on the master file that has your companies in it. That way anytime you join to the company table the user is always restricted to only their company.

Robert

From the WebFOCUS Reporting Server console create a new application folder called wf_retail and then right mouse click on it. Under new there is an option called tutorials. Choose the WebFOCUS Retail Demo and for Data Security (DBA) choose Enterprise (By Product) and it will create a working example of what Martin has talked about. The connection you use will have to have an ID that can create tables in the DBMS for this to work.

thanks all...to be honest I created a quick sql table with my userid and a few companies, created a master file, created a generic security fex using Martin's code, pointed my company dropdown on my html page to the fex and....it worked! I had to remove the ALL option from the Company control but the user can CTRL Click if they have access to multiple companies and want to see them combined. I must test further but this seems promising.

Of course, ideally we would only put the folks requiring security in the table...for everyone else they would get all companies.

I am thinking that somehow I can enhance Martin's code such that if NO VALUE is
returned, the user simply would be assigned each of our 5 companies in the fex..ie they get ALL...otherwise they get what they are secured for. I'll have to try to see if that is possible.

quote:

I am thinking that somehow I can enhance Martin's code such that if NO VALUE is
returned, the user simply would be assigned each of our 5 companies in the fex..ie they get ALL...otherwise they get what they are secured for. I'll have to try to see if that is possible.

Sure it is possible. I did it all times.

You need two parameters :

1- From the security fex : Getting back to my previous sample you have set the &USERCIE
2- From the user selection : you drop down list from which the user will make his/her selection. Let say that this parm is named &SELCIE

Then in your report fex you will do something such as this

TABLE FILE abc
SUM ...
BY ...
WHERE CIE EQ &USERCIE;
WHERE CIE EQ &SELCIE;
END

So since the user can't select a Cie that he/she doesn't have access, even if the ALL option is available from the drop list, he/she will be limited to his/her security profile.

Note : I do suggest that you keep the ALL option available to the user. It's painless to choose ALL (one click) then multi-select all the options when you want all of them.

I probably did not make it clear. The Company drop down is chained to the Dept then Employee. So the value chosen in the company limits the Depts the user can choose from etc.

Lets say we have 3 companies: 20,50 and 90.
Lets say the user is secured for 30 and 90.
Lets say we leave the ALL option on the company control.
When the user runs the report he/she sees ALL, 30,90 as possible choices.
Lets say they select all.
The Dept table, that has every company's Depts in it is no longer limited...thus the user may pick Depts from ALL companies.

If I remove the ALL option, this is no longer the case.

That's one issue.

We'd also like to only put folks in the security table who need to be secured so it feels like I need a NULL check against the security table and if NULL just dummy up the 3 companies to the Company Control otherwise let the table provide the companies...hope this makes sense....

quote:

The Dept table, that has every company's Depts in it is no longer limited...thus the user may pick Depts from ALL companies.

You also need to add the filter in the Dept fex :

TABLE FILE DEPT
BY DEPT
WHERE CIE EQ &USERCIE;
WHERE CIE EQ &SELCIE;
ON TABLE PCHOLD FORMAT XML
END

The user security profile variables HAVE to be at every places where a limitation can be applied based on Cie.

quote:

We'd also like to only put folks in the security table who need to be secured so it feels like I need a NULL check against the security table

In you security fex, you assign a default value that any security parameter, so if the user does not exist it has the default

-DEFAULTH &ALLOWED_CIE = ''
-DEFAULTH &USERCIE     = _FOC_NULL
SET ASNAMES = ON
TABLE FILE SECFILE
PRINT ALLOWED_CIE
WHERE USER_ID EQ '&IBIMR_user';
ON TABLE HOLD AS UCIE
END
-RUN
-IF &LINES EQ 0 THEN GOTO NOSECURITY;
-READFILE UCIE
-SET &USERCIE = &ALLOWED_CIE;
-NOSECURITY

RE:
The user security profile variables HAVE to be at every places where a limitation can be applied based on Cie.


Well, we are looking for the Company to delimit so we do not have to add that criteria to subsequent controls.

I am not sure I followed your comment on the user who does not exist in the table. However, The code below seems to work. It may be 'more' than what was required. I think it provides the security necessary...will need to further test.


-* To extract user security profile if it exists
SET ASNAMES = ON
TABLE FILE TBLMSTR_IBIMRUSERCOMPANY
PRINT IBIMR_USER COMPANY
WHERE IBIMR_USER EQ '&IBIMR_user';
ON TABLE HOLD AS HOLD_ALLOWABLECOMPANIES
END


-IF &LINES EQ 0 THEN GOTO GENERICSECURITY ELSE GOTO USERSECURITY;

-USERSECURITY
TABLE FILE HOLD_ALLOWABLECOMPANIES
PRINT
HOLD_ALLOWABLECOMPANIES.HOLD_ALL.IBIMR_USER
HOLD_ALLOWABLECOMPANIES.HOLD_ALL.COMPANY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=6.000000,
$
ENDSTYLE
END
-GOTO FINISHLINE


-GENERICSECURITY
SET ASNAMES = ON
TABLE FILE TBLMSTR_IBIMRUSERCOMPANY
PRINT IBIMR_USER COMPANY
WHERE IBIMR_USER EQ '0';
ON TABLE HOLD AS HOLD_ALLOWABLECOMPANIES
END
TABLE FILE HOLD_ALLOWABLECOMPANIES
PRINT
HOLD_ALLOWABLECOMPANIES.HOLD_ALL.IBIMR_USER
HOLD_ALLOWABLECOMPANIES.HOLD_ALL.COMPANY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=6.000000,
$
ENDSTYLE
END
-GOTO FINISHLINE
-FINISHLINE
END

quote:

The Dept table, that has every company's Depts in it is no longer limited...thus the user may pick Depts from ALL companies.

quote:

Well, we are looking for the Company to delimit so we do not have to add that criteria to subsequent controls.

I'll let you do your tests, but as you mentioned the Depts table has all company's departments, so you need to filter based on the company to show only the "proper" Depts for a user based on his allowed Cies.

As a remark : you don't need the ELSE when the ELSE goes to the line right after the IF. But some programmer prefer to always code IF...THEN...ELSE, it's ok.

-IF &LINES EQ 0 THEN GOTO GENERICSECURITY ELSE GOTO USERSECURITY;

One last thing, I don't understand why you are requesting two more read from tables when you already know that you need to set the default values because the user haven't been found at the first step.
The GENERICSECURITY step is doing no more than what a -DEFAULTH can do and you are not requesting system resource to read a table.

But, it's your call the way you are doing your code. I'm just saying that it's more simple and efficient to reduce the number coding lines, steps and GOTOs when they are not essentials. Also make it easier to debug.

Re:
I'll let you do your tests, but as you mentioned the Depts table has all company's departments, so you need to filter based on the company to show only the "proper" Depts for a user based on his allowed Cies.

Our Controls are chained so the Company will limit the depts...unless: ALL is there! I'll work thru that.

Yes, I likely should clean up the extra code!

quote:

Our Controls are chained so the Company will limit the depts

I understood that it was like that.
I don't know how you've built your fex to populate list box values. Is that one fex per type of value (1 for Cie, 1 for Dept, ...) or one fex that extract data for Cie/Dept/... and creates all possible association ?

If the answer is the first case (1 fex per type of value), to make your ALL option work and avoid the situation where the user as access only to Cie 30 & 90 but has selected the ALL Cie from the drop list and then have all Cie's depts displayed you will also need to have the WHERE CIE EQ &USERCIE in the Dept fex.

If your situation in the second option, it should be good.
But you will still need to have the security filters inserted in your fex that generates the report.

I think that we both agree on what it is needed where.

Robert,

I think you are looking at this all wrong. Instead of focusing on the drop down, you should be focusing on your metadata. By adding DBA security into your master file, you ensure that the user is secured to only the Departments/Companies that they have access to without having to adjust each report now or in the future. Its one time setup and works continuously. It solves your problem with the "Select All" option and is more secure in that, if these users have the ability to write their own reports, they will still be secured against the data that you don't want them to see. Take a look at this documentation here: http://infocenter.informationb...Fsource%2Fdba100.htm.

If you look at the "Limiting Data Source Access: The Restrict Attribute" section, you will see the value_where restriction. That's what you will want to use. You would then want to take your table that you created and write a query off of that in the MFD_PROFILE for your master file. Use this to generate a list of all the companies that the user has access to

Here is an example

MFD_PROFILE

-* To extract user security profile if it exists
SET ASNAMES = ON
TABLE FILE TBLMSTR_IBIMRUSERCOMPANY
PRINT COMPANY
IF IBIMR_USER EQ &IBIMR_user.QUOTEDSTRING
ON TABLE SAVE AS HOLD_ALLOWABLECOMPANIES
END

-SET &USR_PASS = IF &LINES EQ 0 THEN 'GOD' ELSE 'COMPANY_USER';

SET PASS = '&USR_PASS'
 

DBA_FILE

FILENAME=[any_name], SUFFIX=FIX     , $
  SEGMENT=DUMMY, SEGTYPE=S0, $
    FIELDNAME=DUMMY, USAGE=A1, ACTUAL=A1, $
END    
USER=GOD, ACCESS=R, $
USER=COMPANY_USER, ACCESS=R, $
RESTRICT=VALUE_WHERE, NAME=SYSTEM, VALUE=COMPANY_ID EQ (HOLD_ALLOWABLECOMPANIES);, $
-* Please note that in 8.1.x, NAME=SYSTEM will enforce the join in the query every time. 
-* This is regardless of whether or not you need the table joined. 
-* You will need to provide the specific segment in which the field lies if you do not want this behavior.
  

So basically, now anytime you join your company table to any other table, the user will be restricted to only things within their company.

This message has been edited. Last edited by: eric.woerle,

You can also take this and expand it to utilize &FOCSECGROUPS instead of &IBIMR_user. Then you can check peoples group assignments instead of individual users. This means then that you don't need to maintain your security table when you have a new hire, only when you add a new company and create a new group. Also keep in mind that &IBIMR_user is provided from the application server. This means that RC (which bypasses the Application Server and goes through the Reporting Server) doesn't use &IBIMR_user. I believe that is changing in that RC will go through the Application Server first, but I don't know if thats already been implemented or if it is still on the roadmap.

This message has been edited. Last edited by: eric.woerle,

I'd also agree you need to start looking at this from the Groups POV. OK, so now you have users with company only, but what about corporate? What about auditors who want to see all companies? Using the Group as the security and then adding an user into a group that has the access defined. Its also easier to check who has access to what, as you eventually will have someone come up with that question... did I mention audit

I'm back!
Re:
MFD_PROFILE
-* To extract user security profile if it exists
SET ASNAMES = ON
TABLE FILE TBLMSTR_IBIMRUSERCOMPANY
PRINT COMPANY
IF IBIMR_USER EQ &IBIMR_user.QUOTEDSTRING
ON TABLE SAVE AS HOLD_ALLOWABLECOMPANIES
END

-SET &USR_PASS = IF &LINES EQ 0 THEN 'GOD' ELSE 'COMPANY_USER';

SET PASS = '&USR_PASS'



DBA_FILE
FILENAME=[any_name], SUFFIX=FIX , $
SEGMENT=DUMMY, SEGTYPE=S0, $
FIELDNAME=DUMMY, USAGE=A1, ACTUAL=A1, $
END
USER=GOD, ACCESS=R, $
USER=COMPANY_USER, ACCESS=R, $
RESTRICT=VALUE_WHERE, NAME=SYSTEM, VALUE=COMPANY_ID EQ (HOLD_ALLOWABLECOMPANIES);, $
-* Please note that in 8.1.x, NAME=SYSTEM will enforce the join in the query every time.
-* This is regardless of whether or not you need the table joined.



That sounds like it has some advantages. I think we need to set up a call with BI to determine what is best for us.

Re:
I understood that it was like that.
I don't know how you've built your fex to populate list box values. Is that one fex per type of value (1 for Cie, 1 for Dept, ...) or one fex that extract data for Cie/Dept/... and creates all possible association ?


One table, one row per user per company. One security fex.

All,
Thanks. It was easier when no security was needed! I am net sure which path we will follow. At least I can say t can be done. We would likely speak with BI regarding what may be best for us.

quote:

We would likely speak with BI regarding what may be best for us.

I have used a no security, a fex security (code in the report to decide if a user can execute or not), custom security table (such as my previous sample), WF 8 security model and DBA security.
One solution doesn't mean that it will be "the" solution. Actually moving from WF7 to WF8 and I'm going from a DBA security model with PTH access to a mix of WF8 Security model, custom security file and SSO.

IB may help you to take a decision for your organisation by giving you options, as we did, and gives you the pros and cons of each solution, but the decision will be yours.

Another Question..we have one portal tab: MISC, All it contains is hyperlinks to Excel Sheets on a share. We now have a tab on that portal page for each dept and appropriate links to departmental reports therein.

Is there some sort of quick way I can use the USERID and my new table to evaluate the user and only show them the tab they should have access too...

...maybe I can have a Dept Dropdown with a label: Click your Dept to access your Excel Reports.....


some HTML (?) script could be executed using the Dept number to direct them to the html page for their company???

Of course I know nothing of writing html!

quote:

Is there some sort of quick way I can use the USERID and my new table to evaluate the user and only show them the tab they should have access too

Using the WF8 Security Model you can hide/show tab according to Group security. But each of your users must be assigned in a custom "Dept" Group.

right now we are trying to avoid implementing the security module..but rather just controlling who sees what by looking at the userid....it may not be the best way, we may have to redo it, but for now its the only option open to me. I was hoping to somehow use the on click option of the control to open a particular html page based on the Dept the user chooses...

quote:

I was hoping to somehow use the on click option of the control to open a particular html page based on the Dept the user chooses...

I'm not saying that it could not be possible to do it the way you're trying.
But the easiest way is to use your custom security model (using a table file usersecurity) with the WF Security model.
It maybe a long way and may need many coding such as jscript and/or HTML to perform such a thing without using WF Security model that does it for you.

I appreciate the insight...will pass it along...not an option for me right now...was just hoping I could somehow grab the value from a control and jump to a new HTML page based on the control's value...TX.