Multi-tier application (1 Viewer)

Is it possible to implement a multi-tier application with Access?

The application is multi-user with users organized in groups that should have exclusive access to their own data.

The application is same for all users, I do not want to create different database for each group.

Yes it is. You would just need to set up your own area permissions as it's not built in. That said, is the object to keep them out *just because* or is there data sensitive information they should never see?

Groups belong to different companies, so each group must not have access to data of other groups.

Hmm, then you might want to consider using an SQL Server backend because that level of *security* is not available in Access. You can let the SQL Server handle the permissions.

Just had a thought, there might be a way to use Active Directory, have a look here...
http://www.accesssecurityblog.com/author/tvanstiphout.aspx

I am thinking of adding foreign key companyID to every table and relate all tables to company table, then all queries will be restricted by the specific companyID field.

Database: MySQL or MSSQL

Yes, that was actually what I was suggesting by adding areas. However, if you really want to keep people from *peeking* you will need something a bit more in depth.

In your situation, I would give each company their own BE (SQL or Access) but with the same FE in use across all companies.
If you use DSN-less connection strings it's easy for each company admin to link the FE to their own BE.

BAH, beat me by 5 minutes.

Pretty much what Colin said. If you're not familiar with setting up a DSNless connection, we can show you how to do that.

I feel that managing multiple BE databases will be a nightmare. I will test it and see how it goes.

I thing the single database with a guid 128 bit companyID is hard enough to break.

I will test each method.

The following is a method for record level lock - per user - regarding mySQL. Looks promising.


https://www.sqlmaestro.com/resources/all/row_level_security_mysql/

Whilst it may be possible to do this, I think it will be far more complex than each company having its own separate BE.

I also would be VERY concerned about the possibility of data breaches.
If I was one of your client companies, I would immediately terminate any contract where my company data was stored in the same tables as data for other unrelated companies. The recent implementation of GDPR should give you significant further cause for concern.

The article you cite was written in 2009. Issues and policies related to data security have changed fundamentally since then.

The real question is HOW SECURE do you want this data? Then is it data the is REQUIRED to be secure? What are the CONSEQUENCES of a data breach?

Now, that you have thought about the above, as Colin posted, a lot has changed with data security. Citing anything that was not published in the past two months is subject to inaccuracies as to what is going on now. You MUST also consider the policy of the organization you are building this for...

The same method is embedded in mssql 2016. Looks like a low level secure method.

https://channel9.msdn.com/Events/DataDriven/SQLServer2016/Row-Level-Security

I understand that "physical" separation into several distinct databases is the most secure one. I search for alternatives making compromises on some issues.

ridders said:

Whilst it may be possible to do this, I think it will be far more complex than each company having its own separate BE.

I also would be VERY concerned about the possibility of data breaches.
If I was one of your client companies, I would immediately terminate any contract where my company data was stored in the same tables as data for other unrelated companies. The recent implementation of GDPR should give you significant further cause for concern.

The article you cite was written in 2009. Issues and policies related to data security have changed fundamentally since then.

Click to expand...

I believe all SaaS solutions use a common database for all customers. I do not know for sure, I guess they do that.

lpapad said:

I believe all SaaS solutions use a common database for all customers. I do not know for sure, I guess they do that.

Click to expand...

Yep, as far as I know they do and so do some cloud based companies. Customers are identified by a Key or Token.

The most secure, lease overhead, and most portable for clients would be separate back ends.

Which would be easier for you to add, procedure level security to ensure only the correct datasets are returned (and some way to keep an end user from running their own FE against your back end without a care as to YOUR security), table level security on an SQL sever, or spend a little money to get each client their own small NAS and set permissions based on user login?

To me, I'd spend a weeks worth of junk food on the NAS and have each client have NO VISIBILITY on other's data. The first time you get questioned about security / ability to provide the end user their data, you'll have saved more than a few orders of fries by saying "Here it is". This also means you can use disconnected networks to add additional security. Nothing stops a hacker like an air gap.

Why are different companies working on the same LAN using the same Jet/ACE BE? That would have to be extremely unusual.

Unless you have a need to have consolidated reporting, there is no advantage to having a single BE and many disadvantages starting with the extra complexity of the code and queries needed to keep company data from being commingled.

If you get some of the FMSINC.com tools, they will handle a lot of the backup and compacting that needs to be done on a regular basis so multiple BE's won't be an issue.

lpapad said:

I believe all SaaS solutions use a common database for all customers. I do not know for sure, I guess they do that.

Click to expand...

Not the ones I build.

We made the decision not to do this (for many reasons) and I've always felt it was the right decision. Having separate backends/deployments per customer seems to be the generally accepted practice by most others who are building SaaS apps as well.

Set yourself up with a different backend for each client, be disciplined in your control of changes to the backend, and you'll thank yourself later, I'd bet.

Since Access security is "roll your own" or "none" (on a native solution), and you WILL want to keep things separate as a matter of fiduciary responsibility, I'd say that separate BE files would be the way to go. First, it is FAR easier to assure that users cannot overlap if you can use Windows domain-level security as a factor in that solution.

However, another factor pops up in my mind. If you are going to have multiple users from multiple companies on a single BE, you have VASTLY expanded your exposure to the random true hacker or hacker "wanna-be" types that are in every company.

There are the "curious George" types who will browse anything that isn't nailed down. There are the "grumpy Gus" types who will SHOW you that you can't keep THEM out of a system. There are the "helpful Harry" types who will tell you "Your security would be better if...." followed "... because I just found THIS on the alternate paths." Not to mention "Carmen the corporate spy" who will work assiduously to find some tidbit of saleable corporate "insider" knowledge.

If you can keep them physically separate because they don't have rights to anyone else's folders all the way up and down the path from the BE server to their particular database, you are better off. And this is what firewalls are meant to do. With THAT kind of security and physical separation of the sub-nets, your security is not going to be so much of an issue. Besides which, most customers want to believe that they have a dedicated system all to themselves.

If you are hell-bent on a single-BE solution, just remember that some products have size limits. If you have something that can go up to 10 GB databases, just remember that if you have as few as six customers, you just crossed the line in BE size. Because with "pure" Access and a split FE/BE you can have 2 GB of data, but with a unified BE and a 10 GB limit, six customers only have an average of 1.666 GB each.

Not only that: You will have to arrange for maintenance times and with five or six different corporations, you will have PURE HELL to get them to agree on a regular schedule of down times.

Here's a thought: If you are going into this in a serious way, look into something like VMWare or some other provider of virtual host management. Their cross-client security is excellent and with some network-attached storage to bolster available data space, you can have a pretty good-sized server farm. Yes, NAS is quite capable of partitioning itself to keep multiple projects and data separate.

My last job before I retired was with the U.S. Navy's Enterprise Data Center in New Orleans. At the NEDC NO site, we had a few dozen VMWare systems hosting several HUNDRED virtual systems spread out to service over 80 different Navy and other government groups. We also had TERABYTES worth of NAS. Not only that, but we had systems that maintained SECRET-level security. If you think about the stringent security needs of the USA armed forces, you would realize that our setup was under constant scrutiny for holes, leaks, and breaches. But we WERE considered a secure site. And we found that keeping projects on separate sub-nets and servers from the moment they came online was the best way to keep their data separate AND SAFE.