Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

[Jausan]

Before I bore anyone with the particulars of trying all of the previously outdated posts regarding the earlier versions of Alpha, I am in the midst of developing a system that will allow client companies to login in and manage their employees. Currently, I am using a Company Code through ulink at login as per the advice of Steve Woods in the post for v10. What I would like to do is to make sure that other company managers, employees etc. cannot access the records of other firms, ie through grids etc. What are some of the best practices to achieve this goal? So far everything I have both read and used is outdated and ineffective. My web security tables are Actively Linked to the MySQL database. Any suggestions?

[J.R. Noe]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

Hi James, I don't know if the way we do it is the best way, but this has been working great for us.
We include a field called "agencyid" in each of our MySQL tables. It is a character field, length of 12. The "web_users" table has a field called companyid, which is the same as our agencyid.
When a user is logged in, we use their companyid variable and save that value when a record is saved, into the agencyid field of the current record.
We filter each grid, dialog and report to show only the records of the agencyid of the user currently logged in, so personnel only see the data for their own agency/company.
So far, we have not had any trouble using this method.

At first we only put the agencyid field only in the parent tables, figuring the sub forms and report data will only show with the parent record, but we soon realized that this is not always true. So now we save the agencyid variable into each tables agencyid field. So I recommend the agencyid (or what ever you are calling yours), field be included in every table.
Also, double check all your grids, dialogs and reports are filtered to only show the records for the agencyid of the person logged in. It's very easy to forget one of them, which will allow others to see the records for another agency.

[Pat Bremkamp]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

James,

I use the same technique as JR, but I have a few additional suggestions.
First, be concerned that the User code and Company code cannot be guessed by a user (they will try). If you are not using it already, use UUID codes for the User Ids and Company Ids. The field is long, but MySQL handles it beautifully. In fact, MySQL includes a UUID() function that you can place in an insert trigger to ensure one is created whenever you create a new User or Company.

Second. Usually, those codes are assigned to session variables, so you need to test each page and component so that, when the session variable expires, it drops back to showing nothing instead of everything.

Thinrd, if you haven't already, you will soon see that each company will have a hierarchy of users who want to see records. The headquarters person will want to see everyone in each branch, but the branch manager should only see the branch employees. Gets worse if they have regions. Plus, as administrator, you will need to be able to see everyone so you can clean up the messes your creative clients will make. So, you need to decide if you will use different components or pages for each level or use employee codes to indicate the level. If you have a lot of components, the multiple form version is a lot of work. If you use codes, you will need to write code to handle, especially, the dropdowns and lists to show only the right people, locations, and job descriptions for each level.

Been there and it is much easier if you figure all that out at the beginning.

[Peter.Greulich]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

Just to tag onto what Pat said, if you are using a tabbed_ui it becomes a little simpler. The tabbed_ui allows you to filter a grid, for example, right in the tabbed buttons. Also: you can have several buttons for each component, each with a different filter. This works because you can set security groups to the button so no matter who logs in they only see a single button per component for their particular security group.

[J.R. Noe]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

Pat, you have me concerned with the user will "try to guess the company code". We are still learning Alpha, so just to make sure we have not made an error setting this up, let me ask you if this was done correctly please.

The companyid/agencyid field I use is not shown anywhere in the application. The user never sees it and don't even know it exists. The self registration process assigns the correct agency ID to them when they register because their agency admin gives them a password for their agency alone. Once they register, they change their password. Each agency admin can only see users for their agency and can set their agency personnel access permissions.
When the user logs in, the agencyid is now a variable to be used through out the application to filter the dialogs, grids and reports until they logout.
I do not think there is an issue since the field is not available to be tinkered with, and even if it were, they would not be listed as a user for the other agency and could not access anything except the public pages.
Just wanted to make sure we didn't overlook something. Need some reassurance.

Peter, that is also good advice. I found it much easier to use the tabbed_ui and have different buttons with different security levels assigned to each. Much, Much easier than doing it the other way.

[Jausan]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

Thanks guys so much for your professionalism and expertise. J.R. I have one question on filtering according to the agency-id or in our case the company _id in regards to grids, reports, and dialogues. Where would I go to do this? Also, one question for Pat. Should I place the UUID in each table like I did with company_id?

[J.R. Noe]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

James, I use the "OnDialogInitialize / Get primary keys for parent table". In there is a Filter setting. I use something like this in that filter.
complaints.agencyid = :coid

The complaints is the table name, agencyid is the field in the complaints table which captures the agencyid when a new record is created, and coid is a session variable which is set when a user logs in.
If you call it from a tabbed_ui then Peters suggestion works great.

For reports and grids, I put "WHERE complaints.agencyid = :coid" in the sql statement.

[Jausan]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

Thanks so much J.R. giving it a try.

[Jausan]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

J.R. Thanks again. One more question. Could this be done on Grids also?

[Jausan]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

J.R. I have a question in respect to where to load session._protected_company_id=
sql_lookup("conn","websecurity_users","ulink="+session._protected_ulink,company_id) in the system so that once users have logged in the security is then applied across the application. Used it in XBasic nothing, between <a5%> tags nothing. javascript however it states undefined variable. Also, I hear a lot about filtering grids, I assume this means under look-ups or SQL statements that the company_id for instance must be included?

[J.R. Noe]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

James, yes it can be done in grids just as easy. I set the variables when a user logs in with sql lookups, something like this:

session.fullname = sql_lookup(cn,"web_users","userid='"+pUser.userid+"'","concatenate(alltrim(fname),' ',mname,' ',lname,' ',namesuffix)")
session.coid = sql_lookup(cn,"web_users","userid='"+pUser.userid+"'","companyid")

[CharlesParker]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

sql_lookup(cn,"web_users","userid='"+pUser.userid+"'","companyid")

what does the ' stand for in the included item "'" between puserid and companyid ?

[J.R. Noe]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

I wish I could answer that Charles, but I do not know the granular details on how it works, just that it does work. The code was provided by Steve Wood, and I have continued to use it and expand on it to set other session variables.

[Peter.Greulich]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

what does the ' stand for in the included item "'" between puserid and companyid ?

IOW's the variable is converted to a literal value and then the single quote, quotes it - as typically used in a string concatenation.

[GGandhi]

Re: Setting up Multiple Company Accounts Hosted by on Database in MySQL using Alpha Anywhere

"userid=' "+pUser.userid+" ' ","companyid")

isn't that the other part of the single quote right after the userid.
i expanded to show the single quote from the post, in order to use the pUser.userid with single quotes around it.
isn't it?

edit: i noticed while i am writing, peter has pointed out the same idea.