Access vs Comercially available databases

[BlueSpruce]

Many years ago a client decided to implement a well known online crm system. I was involved with integrating it with finance, commissions and hr systems.

I discovered that if you knew the login details (of a sales person for example), you could not only access the data from excel, that access was not limited to the data the login allowed - you could see and edit pretty much anything.

The crm company initially reassured my client that it was not possible and that I was making it up (echoes of Horizon?) So (with the permission of my client) I went in and made some changes.

The crm company had to about face and took something like 3 months to come up with a solution

An online app is automatically less secure than an on premise app. Then you also have to worry about the outfit that's hosting your data. No thanks.

 

[AccessBlaster]

It's all moot, some of the most robust systems on earth get hacked. I'm part of a large class action law suit right now. Keenan & Associates was hacked in 2023. Millions of people effected, potentially billions of dollars on the line. Security is an illusion like insurance.

 

[gemma-the-husky]

It's good to see a lot more answers on this, but I thought the real question the OP was asking is whether the functionality of a commercial system would be better than the system that was already in place.

 

[BlueSpruce]

It's all moot, some of the most robust systems on earth get hacked. I'm part of a large class action law suit right now. Keenan & Associates was hacked in 2023. Millions of people effected, potentially billions of dollars on the line. Security is an illusion like insurance.

I've already cashed in on several settlements. Makes me want to put more of my personal info out there so I can have another source of steady income

 

[BlueSpruce]

It's good to see a lot more answers on this, but I thought the real question the OP was asking is whether the functionality of a commercial system would be better than the system that was already in place.

Agreed, I did mention that towards the end of this post. If I were the OP, I would defend the current Access app for all the merits mentioned in this thread.

 

[The_Doc_Man]

The problem I have with 3rd party packages is that they were probably developed by a senior designer and a bunch of junior coders. I.e. without knowing the details of how it was actually developed and implemented, you have no way of knowing whether it is better, the same as, or worse than Access for security details.

 

[RonPaii]

"But you can not hide the server name, and database in connection string. Can you?"

You can't hide the server with or without Access. The server handles data security, not the FE.

BTW, I know of at least 1 very large account / payroll / buisness system with a base option of a file based db, just like Jet which makes it just as hackable. You can build passthough queries to that db usable with the corrent login form an Access FE.

 

[Isaac]

If your FE is Access, you can open a blank database, write a simple code to return all tables and then theri fields in your FE. You can also run an update or insert query from a database outside your FE. You can hide/unhide tables in a FE using a remote database, or change FE's properties like shift+double click or .....

As long as your FE is Access, you have a big hole in your security and it doesn't matter your BE is sql server or cloud.

Keep in mind that most of these implementations are already behind the safety of a network. If you trust your users to give them access to the data then trust them. If someone gained access to your network and your file servers, they would have access to everything not just Microsoft Access. Microsoft Access is not any specialer than any other file nor more dangerous

 

[BlueSpruce]

That is why you don't use Windows to secure your SQL Server. My users never have their credentials for the server database. While it is true that you can view linked tables, as long as you don't store the password with the link, you can't retrieve data that way or use the table names and server name to log into the server.

Why not use Windows Authentication, which leverages existing Active Directory credentials? That eliminates the need to manage separate logins and passwords for SQL Server.

 

[Isaac]

That is why you don't use Windows to secure your SQL Server. My users never have their credentials for the server database. While it is true that you can view linked tables, as long as you don't store the password with the link, you can't retrieve data that way or use the table names and server name to log into the server.

In my opinion it's the opposite, using Windows active directory groups to secure sql server access is a great option

 

[BlueSpruce]

Because, using Windows Authentication allows knowledgeable users to get directly into SQL Server without using your Access application. If you're OK with that, then you have a very large security hole that I hope will never bite you.

Please explain how exactly that's possible. There are many enterprises that use Windows Authentication for SQL Server connections. If what you say is true, no one would use that method.

 

[BlueSpruce]

Once you know your credentials, create a new, empty Access database and link to tables on the Server. Can you do it? You won't have the credentials to manipulate objects but you can update data because that is what you need to allow the Access app to function.

That implies a person who already has Windows login credentials, and enough knowledge to set that up. I have yet to hear about a case where someone who is authorized to login to a workstation has perpetrated a SQL Server hack using that method. If you can't trust your own users, then what's the sense in conducting business?

 

[The_Doc_Man]

If you can't trust your own users, then what's the sense in conducting business?

Trust is a continuum just like lots of other things. It isn't "do you trust user X?" it is "how much do you trust user X?"

That's why role-based security is so important.

 

[BlueSpruce]

it is "how much do you trust user X?" That's why role-based security is so important.

... and that's why SQL Server role based permissions can be setup for Active Directory groups. Even the healthcare applications I work with at major hospitals, which are HIPAA regulated, use Windows Authentication.

 

[Isaac]

That implies a person who already has Windows login credentials, and enough knowledge to set that up. I have yet to hear about a case where someone who is authorized to login to a workstation has perpetrated a SQL Server hack using that method. If you can't trust your own users, then what's the sense in conducting business?

I agree. If you're going to give them access that means you trust them.
If you make an app that uses service accounts there are downsides to that too but anyway for all of these there are trade-offs

 

[Edgar_]

You're being cynical. If you feel Access is so insecure, then why do you develop with it, or even use it?

Although the system is definitely insecure, it offers a really efficient way to build UIs quickly. Sure, there are limitations, and the UI doesn't exactly meet security standards. But it's still functional enough to get the job done. It's a usable UI in the sense that it lets you interact and perform a variety of tasks, but let's be clear: it's nowhere near a robust, safe environment.

Just think about it: you're basically hoping your users don't do anything malicious. And honestly, do you really think there won't be any bad actors eventually? I mean, think about it: what if some users aren't supposed to see sensitive stuff, like company profits or individual salaries? What if they're able to view how much vacation a certain person is allowed to take, payroll details, or confidential project budgets? Think: subcontractors.

Spoiler: It does not stop there

But it doesn't stop there. What if they gain access to even more critical data, like HR files, customer credit card info (yikes!), or system admin passwords? Imagine if private email communications, client contracts, and trade secrets were exposed. What about internal audit results, sensitive medical data, or legal documents that should remain strictly confidential?

Then there are things like personnel evaluations, marketing strategies, intellectual property, or even database connection strings that could end up in the wrong hands. What if employees can access financial transactions they shouldn't see, or view projects in development that haven't been announced yet, where they were not included? Even worse, what if someone gains access to the company's entire client list, email marketing campaign data, or product pricing models to replicate the whole business somewhere else?

The list goes on, and it's a serious concern. With a system this insecure, it's hard to feel safe knowing that so much sensitive data is just waiting to be mishandled, intentionally or otherwise. Wanna consider a lawyer? you better do.

On top of that, Access can be pretty unpredictable when you try to create custom workflows or add features to components that really need them. What works smoothly in one scenario can suddenly break when conditions change, and often, it's in ways you don't expect. These issues can be tough to predict because the underlying causes are often hidden, undocumented, and deeply ingrained in how the system operates. This lack of transparency makes troubleshooting a serious headache, since the root cause is rarely clear.

Despite all the issues, we usually find workarounds, like using MSSQL to some extent. The core system may be unstable when pushed beyond its basic limits, but there's often a temporary fix, even if it feels like a quick patch. Ultimately, you're relying on hope that things will go smoothly. As a developer, though, you can't ignore these shortcomings, they're always there. But then again, more robust systems require more robust specs and a significant extra development time, so, it's a trade-off.

 

[Isaac]

Just because you trust them doesn't mean you need to leave gaping security holes that can easily be closed.

Your Access FE probably locks the users out of design view on anything and doesn't give them options like - delete all tables? But are you willing to allow them to use their SQL Server credentials outside of your Access application is the question?

Grant them EXECUTE permission only on the specific stored procedures they are allowed to use, problem solved In some cases with read access to the tables.
I don't particularly like SA's as I want the database to be aware of who is who.

 

[BlueSpruce]

Grant them EXECUTE permission only on the specific stored procedures they are allowed to use, problem solved In some cases with read access to the tables.
I don't particularly like SA's as I want the database to be aware of who is who.

You can grant them DBA privileges and the db will still be safe because none of the authorized users would know how to, or would dare mess around with something they're not supposed to touch. The db audit log would reveal who did what, when, and they would lose their jobs and face criminal charges.

 

[The_Doc_Man]

none of the authorized users would know how to, or would dare mess around with something they're not supposed to touch.

That is one of the most tenuous assumptions you can make.

When I was doing U.S. Navy work, one of the Senior Chiefs made it clear to all users of our databases that we had a ship waiting at the port of embarkation that still had the equipment required for keelhauling. Which would be doubly bad because besides being an easy way to drown, it would be through Mississippi River water, which isn't necessarily that clean.

 

[Isaac]

Anyway, it's a very common method and it keeps people's database-identities separated and individual, as they ought to be. The extensive use of service accounts leaves you outside the 'know' when it comes to who did what - and in that case, YOU'RE relying on your FE to audit who did what and log that, but if they do something outside the db .... You'll never know.