Access vs Comercially available databases

[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

 

[BlueSpruce]

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.

That's why Windows Authentication with no service accounts is the preferred method for connecting to SQL Server, Oracle, PostgreSQL, and other db servers. If you can't pinpoint the culprit, then you have to detain all users and hang them upside down by their toe nails until someone confesses.

 

[BlueSpruce]

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

Web apps use stateless unbound forms, but Access doesn't. Service accounts should be avoided because it's difficult, or impossible, to trace who did something.

 

[BlueSpruce]

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

Me: You can grant them DBA privileges and the db will still be safe...

I kno it was a flimsy weak statement. I was being sarcastic, but users have to be trusted to a certain extent, without crippling their productivity.

 

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

 

[BlueSpruce]

So document your Access app well, including technical and end user documentation, and bring another Access developer, or firm, on board. Explain to management the pros of your Access app, and the cons of COTS software.

You can also integrate instruments with your Access app.

Once again, I stand by my earlier statements. Defend your existing app for all of its merits. If they're worried about you being the only one that knows how to maintain and run the app, there's plenty other experienced Access developers available.

 

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

 

[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

 

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

 

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