Access vs Comercially available databases

[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.

 

[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.

 

[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.

 

[Isaac]

Then you can't build an application using bound forms.

That is true, my suggestion would be best on an unbound form, but at least it avoids using service accounts where nobody knows who did what EXCEPT your fe app

 

[Edgar_]

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?

I don't know what you're talking about.

Spoiler

Here's an accde file viewed with the notepad

Here's an accdb file viewed with the notepad

Stuff isn't very safe here, but hey, I built this in like 10 minutes, that's valuable

 

[Isaac]

I'm not sure who you are talking to. It can't be to me.

Yes, it was to you. If you use service accounts you don't know who did what unless your FE logs it

 

[DickyP]

Much of the discussion on this thread has been, quite sensibly, about security issues; however, the initial question was far more fundamental - can the commercial available offering do the job that the current system does?

The simplest answere I'd give is to ask the people who use the current system what they are willing to do without? I virtually guarantee it will be nothing! And is it the user's or the managers driving the change? If it is the latter ask them how much they are willing to pay (in perpetuity) to have the commercial system customised?

My (bitter) experience is that it will at least treble the up-front cost!

 

[gemma-the-husky]

Much of the discussion on this thread has been, quite sensibly, about security issues; however, the initial question was far more fundamental - can the commercial available offering do the job that the current system does?

The simplest answere I'd give is to ask the people who use the current system what they are willing to do without? I virtually guarantee it will be nothing! And is it the user's or the managers driving the change? If it is the latter ask them how much they are willing to pay (in perpetuity) to have the commercial system customised?

My (bitter) experience is that it will at least treble the up-front cost!

And from the sound of it, they already have a working system.

 

[The_Doc_Man]

Much of the discussion on this thread has been, quite sensibly, about security issues; however, the initial question was far more fundamental - can the commercial available offering do the job that the current system does?

The simplest answere I'd give is to ask the people who use the current system what they are willing to do without? I virtually guarantee it will be nothing! And is it the user's or the managers driving the change? If it is the latter ask them how much they are willing to pay (in perpetuity) to have the commercial system customised?

My (bitter) experience is that it will at least treble the up-front cost!

I've told a part of this story before, but it becomes relevant again relating to the fundamental question, as DickyP reminded us. The question is "roll your own" vs. "over-the-counter."

The USA uniformed military services in 2009 wrote a specification for a new personnel management system that was supposed to unify all of the military services, because at the time, each service had its own separate personnel system - and most of them had a separate system for active military vs. reserve military. That meant that EACH SERVICE had to build transactions for the "purple" services (i.e. all services combined, or "joint services") could talk to the civilian side of military management - like Defense Financial Accounting Systems (DFAS) and other agencies that had to serve ALL of those services. My Navy Reserve personnel management machine (which would have been replaced... except read the next paragraph) talked to 18 different agencies that were civilian and cross-service in scope.

The intent was to buy OTC and then get the vendor to do any required customization. That requirement WAS in the spec. What happened was, first they had to quell the not-so-quiet rebellion. Army and Air Force were almost the same in personnel rules and they were ready to go along with the idea as long as THEY didn't have to adapt to too many changes. (Change what WE were doing? No, let the other guys adapt to us.)

Navy, Marine Corps, and Coast Guard? You would think someone had kidnapped their youngest children into overseas slavery. After several presentations, PeopleSoft won the contract, beating out ORACLE by about 1%. (The delicious irony is that before the project was over, ORACLE blithely BOUGHT PeopleSoft.) PeopleSoft had these "personnel support" modules that if you had specific types of employees, it was like a library of handling code. Analogous but not identical to having specific data types and individual rules to handle each type - except a LOT more complex.

As each service dug in their heels over specific features, it got crazier. For instance, of all of the services, the U.S. Navy Reserve was unique because Navy Reservists have contracts that, if breached, would release the reservist of his/her military obligations. That's right - Navy Reservists are technically NOT enlisted personnel. Any other service would discharge a reservist with a gripe - probably with a less-than-honorable separation. But not for the Navy Reserve. Anyway, after seven years and 10 billion US dollars, it got shut down due to cost overrun after cost overrun because none of the services was willing to budge on most of their core requirements.

In detail, what happened is that "over-the-counter" morphed into "roll-your-own" because the OTC software was estimated at only 12% compliant with the actual requirements and NONE of the services would adapt. So ... core software change orders for the non-compliant 88% abounded. BUT it was done with contractors doing the retrofits, upgrades, remodeling, and customization. And these change orders were full-traffic time-and-materials costs, no discounts. The best they could do before the plug was pulled was that a personnel cycle that was supposed to occur every 24 hours took a couple or three days per cycle using the fastest multi-threaded CPUs available on the market. (Ironically, that was when ORACLE also made processors.) If there was a leading, bleeding edge CPU available at the time, they tried it.

Thus, when Congress wanted to upgrade personnel management in a standardized way, the intended users of the proposed system were opposed. And as a result, we STILL have almost the same exact system that was used before the DIMHRS project was started. "Almost" - because since DIMHRS started and subsequently tanked, the U.S. Space Force came into existence - another uniformed regular service and reserve units - 12 groups instead of 10.

The moral of the story? Change orders are like vampires. They suck your company's blood until it is dry. And buying OTC almost invariably leads to change orders. If your company has the ability to "roll its own" it might end up cheaper in the long run.

 

[Isaac]

I never said to use service accounts. No one ever asked me what I do, you all just poo-pooed the issue so I didn't bother.

Okay humor me. You don't use Windows authentication for SQL server backends the only other option is SQL server authentication which means you do use a service account.

 

[KitaYama]

I stepped back for a while to see what experts have to say.
I'm not expert in Access, not even close to one. But I believe the MVPs I know, the experts I've talked to, the books and articles I've read.
From the first Acces bible that I read with Access 97, down to every book that I've read ever since, they have something in commen :
"Access Security is weak. Don't trust your data with Access if it's sensitive."

In either of the following situations:
FE uses DSN file to connect to BE
FE is connected via DSNLess connection
BE is Access
BE is sql server or other cloud BEs

1- Open your FE and start using it.
2- Open a blank database, write a code to read tableDef of FE
3- Read the connection string of any table
4- Create new link tables in your blank database using the same connection string
5- Do whatever you want to do with the tables.

Possibilities:

• You use Windows Authentication: Since your PC is a client of the domain, any database can connect to sql server. Above steps can be used.
• You use sql server authentication: Passward is either in DSN file, which is a text file and can be read with notepad, or you're using DSNLess connection, which password is in the connection string. Still above steps can be used.
• What @Pat Hartman suggested. (seperate passwords for differnt users). I don't have any experience on this, and will try to see how it works as soon as I'm back to my desk. But for now I can say as soon as a user logs in to FE and is connected to BE, password can be read from connection string from a remote database. Hence, the above 5 steps can be used.
• I've seen articles from Isladogs that suggested encrypting data in tables. Again I don't have this experience, but my insticts says data can not be read and understood, but can be updated. Use above steps and then run an update query to change quantity of all orders to 1.
• You don't use linked tables. You Open in memory recordsets or disconnected recordsets and work from there. FE is accde and no one has access to VBE. This solution may work, but since you don't have linked tables, your forms are not bound. And as a result, most of the events (beforeupdate, afterupdate,.....) can not be used. Can we still call it an Access FE? Because Access is all about bound forms.
  This may work.

If you can't trust your own workers, then how can you operate?

If this is correct in your situation, I really envy you. Because you're in a trustworthy society.
Here, the first step in security is don't trust anyone. Inbound or outbound.
I'm just happy that the security team of the banks I use daily, don't think this way. Just imagine Amazon's security is was built on : Let's trust our users.

And I see some experts who have been able to berak in some out-of-shelf apps.
I never said other apps are not breakable. Any app is breakable. I said Access is too loose with security of data and any beginner can break into data with several hours of googling. And if you found an out-of-shelf app that was too easy to break in, it doesn't mean Access is secure. It means your organization moved from Access to a less secure app.
It makes a difference if you can access data with several hours of googling, or you need years and years of experience to bring down an app,

I'm not here to prove I'm right and you are wrong.
I'm here to learn something and would be more than happy and thankful if you proove the above is not correct, or there's other ways to secure Access.

Thanks.

 

[KitaYama]

Security Is A Myth! Anything can be broken into because the weakest link in security are the people entrusted to use SQL Server, Access, Excel, Oracle, Web Apps . . . ANYTHING!

Since you were in a hurry to reply, you may have missed a very important part of what I said.
Here that section;

I never said other apps are not breakable. Any app is breakable. ............
It makes a difference if you can access data with several hours of googling, or you need years and years of experience to bring down an app,

Yes, there was a time a group of hackers, hacked CIA. But none of them started with several hours of googling. They had years and years of hacking experience.

 

[KitaYama]

here are many objects that have no security at all, but you protect them by preventing someone else from gaining access to that object.
Your wallet has no security, but you are always on alert to make sure no one can get it, unless they hold you up at gunpoint, or hit you over the head and take it from you. So you have information stored in Access that anyone as you said can easily learn how to extract the data, but you encrypt the backend and make it harder for the average person to use a blank frontend to connect to that backend because they don't have the pasword to open it. You can store the encrypted backend in a safe and hope someone doesn't crack the safe, or you can take it home every day and hope someone doesn't break into your house and steal it, or a fire destroys it. So you minimize risk by taking preventive measures to protect anything, like your wallet, keys, Access backend, your family, and so forth. Nothing is guaranteed 100% safe, but you minimize risk by implementing preventive measures and guarding it as best as you can.

I'm sorry, but I don't understand why you're repeating my words and feed it back to me.
Nothing is 100% safe. We try to prevent data leakage as far as possible. And moving from Access to an out-of-shelf app, is one step toward more secured data.
It's all what I said from my first post on this topic and you're repeating them. So what are you exactly trying to tell me. (In simple words please. I'm not too good in English)

Here's my first post on this topic:

While all the the mentioned pros of an in-house solution is correct, you guys forgetting about the security.
If you own a business, security of your data stands ahead of everything else, and well....all of us know that Access is amoung the least secure apps.

......., I simply said Access is not secure enough (comparing to out-of-box apps) and maybe that's why the OP's company is thinking about another solution like ALIS.

 

[KitaYama]

There's no need to spend lots of money and time migrating from a customized Access app to an off the shelf app that doesn't do everything that the Access app does just because it's "safer" than Access. That's certainly not a justifiable reason.

For whom? me? you? or OP?

In case of the other parts of your reply, I think we have some communication problems. Because we're going in circles.
I think I'll stop here one more time, because, it's kind of wasting our time, and other readers

Thanks for your replies.

 

[KitaYama]

but I absolutely do not recommend the OP follow your advice.

Now I'm sure you have problems reading. Because I didn't suggest anything. Show me where I suggested anything.
Maybe if I make the fonts bold you can understand what I mean:

KitaYama said:
......., I simply said Access is not secure enough (comparing to out-of-box apps) and maybe that's why the OP's company is thinking about another solution like ALIS.

 

[KitaYama]

have a good night, or day, wherever you are.

Thanks. You too.

 

[The_Doc_Man]

With Access and Windows, it's all about environment. I'll try to say it without too many stumbling points. It is ALWAYS about risk/reward. You ALWAYS should buy the protections you need to control the risk, and the reward is a working tool to help your organization go forward.

The U.S. Navy had a BuPers (Bureau of Personnel) application that was used to manage medical school scholarships for potential Navy medical doctors. (Navy pays for your degree, full-ride scholarship, if you serve as a medical officer for 10 years.) It was implemented with Access as FE manager and SQL Server as BE manager. It sat behind an isolation firewall that led to a network called NMCI - the Navy/Marine Corps Internet - essentially a private enterprise network - that was not fully exposed to the big-I Internet i.e. "the world." That subject matter meant that they were subject to the USA Privacy Act and, because it was a personnel system, was also subject to Navy regulations regarding security clearances.

It was basically SBU - Sensitive But Unclassified - and FOUO - For Official Use Only. It wasn't quite sensitive enough to be classified as Secret. The lesser level is called "Public Trust" and for much of my career with them, that is the clearance I held. Access to NMCI required Public Trust and was accomplished via two-factor authentication - a physical smart-card reader and a separate PIN. AND it worked over a VPN, which meant secure remote access was possible. As to range, NMCI at one time held the distinction of having the second-largest OUTLOOK address book in the world. I'm not allowed to tell you how many people were served by it (because the Secret-clearance NDA is lifetime), but it was a bunch. NMCI was available in Rota, Spain and Seoul, South Korea plus a few Aussie sites, something from Qatar, and I forget how many other international sites were served by NMCI - but it was massive. In fact, I met Nautical Gent while we were both on NMCI.

This situation passed security requirements because of (a) sub-net isolation (b) higher-level login requirements (c) I don't know how the SQL Server was set up but it was not a passive part of the security. I got called in for front-end diagnosis and trouble-shooting; another team member handled the BE machine. The point being that as paranoid an organization as the U.S. Navy still entrusted Access to drive the FE of a money-and-personal-data app. They did so because it was shielded. It didn't matter that Access intrinsic security was limited. Its external security was top-notch.

On the other hand, even OTC apps could sometimes get fooled when going out through a stateful firewall. One of my colleagues visited the New York Times (on the Web) site using whatever Microsoft was using as a browser in about 2014 - probably Edge - for a news article only to find it had been hacked and contained a malicious link that then downloaded porn to his machine. Took the IT team two days to wipe his laptop and reload it.

There are multiple kinds of hackers. Fortunately for most of us, the most common hacker is the opportunistic "grazer" - looking for any system with weak security. This is the kind of hacker you can dissuade by having decent security. The worst kind of hacker is the "targeted attention" operative, who knows or believes that behind your protections is a treasure trove of some kind. This is the hacker you can never stop; you can only slow him/her down. State-sponsored hackers (Russia, China, and Iran come to mind) are paid professional black-hat hackers whose attention you don't want.