Access vs Comercially available databases (2 Viewers)

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

 

[BlueSpruce]

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.

I once dealt with a biz that only used service accounts and granted dba to public because they didn't want to bother with security obstacles. They went bankrupt in a couple of years

 

[Pat Hartman]

Each user has an individual account but I pick the password so he doesn't know it. His windows login won't work. He logs into the Access app using whatever password he choose and the app logs into the server and links the tables using a password that only the DBA and I know. There are two ways to do this. Either a hardcoded password which the DBA and I must change frequently or a generated password. I give the DBA a database he can use to generate a password for the user and I use the same code. There are lots of ways to generate a password that is based on a string that is the user's login. Corporate logins are assigned by IT and so I start with that value. Then I make the password 30 characters and append a string to the end of the login string with enough characters to bring it up to 30. Then I pick some method of choosing characters from that string in some order to create the password. So even from the left and odd from the right or whatever. The appended string should contain numbers and any special characters that the server password rules allow.

When the app closes, the tables are deleted to remove the links although the password isn't saved in the link.

 

[BlueSpruce]

Each user has an individual account but I pick the password so he doesn't know it. His windows login won't work. He logs into the Access app using whatever password he choose and the app logs into the server and links the tables using a password that only the DBA and I know. There are two ways to do this. Either a hardcoded password which the DBA and I must change frequently or a generated password. I give the DBA a database he can use to generate a password for the user and I use the same code. There are lots of ways to generate a password that is based on a string that is the user's login. Corporate logins are assigned by IT and so I start with that value. Then I make the password 30 characters and append a string to the end of the login string with enough characters to bring it up to 30. Then I pick some method of choosing characters from that string in some order to create the password. So even from the left and odd from the right or whatever. The appended string should contain numbers and any special characters that the server password rules allow.

When the app closes, the tables are deleted to remove the links although the password isn't saved in the link.

So the appended string is salted. I use a passwordless Yubico security key to login.

 

[Pat Hartman]

If the client wants to provide hardware keys that's fine but it doesn't solve the problem we were discussing.

 

[BlueSpruce]

If the client wants to provide hardware keys that's fine but it doesn't solve the problem we were discussing.

The point I make with security keys is the client doesn't know the authentication key value. The issue your discusing is the use of service accounts, which can easily fall into the wrong hands and then how do you know who perpetrated a hack?

 

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

 

[BlueSpruce]

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

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.

Let me explain something to you. First of all . . . 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! So it's unfair for you to single out Access as the weakest tool of them all, when major data breaches have occurred at, yes, Banks, Credit Card companies, Health Insurance, and many other Fortune 500 enterprises that have our personal information. There are expert state backed hackers at work 24/7 reverse engineering the most hardened systems out there. It's just you don't hear about many breaches anymore because they all cover up it up, sweeping it under the rug to avoid fines, embarassment, and legal liabilities. Even if someday someone drops an EMP bomb that wipes out all electronic devices that stores data, there will still be information breaches because most people are not disciplined enough in keeping information private. It's a human thing! If you want to keep something private, don't use any electronic device to communicate it to someone else, don't discuss it with anyone else, keep it to yourself and hope you don't talk in your sleep so your wife or pet can hear it. Can you keep a secret?

 

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

 

[BlueSpruce]

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


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.

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

 

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

 

[BlueSpruce]

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.

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. Just protect the "non secure" Access app better by encapsulating it in a protective shell, and implement preventive measures to keep unauthorized persons from accessing it. That's all there's to it, minimize the risk by protecting your defenseless wallet!

 

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

 

[BlueSpruce]

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.

You can do whatever you want, but I absolutely do not recommend the OP follow your advice. I previously suggested the OP aggressively defend the current Access app for all it's merits and you detoured this thread with over 30 posts about Access not being secure which is not a high priority for the OP, so I really have nothing further to say about security.

 

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

 

[BlueSpruce]

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.

I'm done arguing with you, have a good night, or day, wherever you are.

 

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

 

[DickyP]

Security is all relative. Access is weak but probably good enough in most situations, whereas keeping all your financial details on your mobile phone is never safe.

As we were told at GCHQ - security doesn't need to be good - it needs to be good enough. At its simplest if your threat horizon is for 30 minutes then anything you can complete in 20 is OK.

 

[BlueSpruce]

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.

This is the point I was making to @KitaYama via my analogy of how we do our best to protect our: family, life, health, wallet, keys, money . . .