Placing my customers individual front ends in Sharepoint Synced Folder

[medihub_guy]

!!

Tbh I find comfort in knowing no application is perfect. It gives me affirmation that I can get there and I'm allowed to make mistakes.

 

[GPGeorge]

The better question is, do you have concerns?

You've taken an important step by moving the data into a more secure database; I assume that's SQL Server or SQL Azure or one of the other options available, given the move to a web-based interface.

Any time you take possession of customer data, you have to be concerned about data breaches, IMO. You are handling prescription information for potentially thousands of patients. I don't know what the laws are in the Bahamas, so I can't say they are more, or less, strict, than in Europe or the US. All I know for sure is that security is a higher level of concern when that kind of information is under your control.

Interoperability and data security are probably always going to be in tension. It seems to me that having 100% security and 100% interoperability at the same time is not realistic. So, the goal, I think, is to provide adequate security and as much interoperability as you can achieve under that context.

It also occurs to me to wonder what interoperability means, and not just in this context. Does that mean Pharmacy A should be able to directly retrieve prescription information for a customer from pharmacy B? If so, the whole idea of segregating data is out the window anyway. Does it mean being able to retrieve that data via your intervention as the administrator? What are the implications of that? What responsibility do you have to ensure that Pharmacy A has a legitimate reason to get that information?

How else might that interoperability be defined and implemented? My Personal Care Provider seems to have the ability to communicate directly with my pharmacy. I doubt that the pharmacy has access to my health records, and I doubt the PCP has access to the pharmacy's records. They both know enough about me, though, to make prescribing meds a simple phone call or email, or perhaps some other method of electronic communication. Is that the interoperability we're aiming for?

I'm just noodling ideas here, to be honest. This started as a bit of concern over commingling what seems to be, at least in the US and probably in most jurisdictions, very sensitive data. And wondering what liability you might be incurring by doing so.

I apologize, though, for not taking time to fully explain the reason for my original question to you.

 

[GPGeorge]

The better question is, do you have concerns?

You've taken an important step by moving the data into a more secure database; I assume that's SQL Server or SQL Azure or one of the other options available, given the move to a web-based interface.

Any time you take possession of customer data, you have to be concerned about data breaches, IMO. You are handling prescription information for potentially thousands of patients. I don't know what the laws are in the Bahamas, so I can't say they are more, or less, strict, than in Europe or the US. All I know for sure is that security is a higher level of concern when that kind of information is under your control.

Interoperability and data security are probably always going to be in tension. It seems to me that having 100% security and 100% interoperability at the same time is not realistic. So, the goal, I think, is to provide adequate security and as much interoperability as you can achieve under that context.

It also occurs to me to wonder what interoperability means, and not just in this context. Does that mean Pharmacy A should be able to directly retrieve prescription information for a customer from pharmacy B? If so, the whole idea of segregating data is out the window anyway. Does it mean being able to retrieve that data via your intervention as the administrator? What are the implications of that? What responsibility do you have to ensure that Pharmacy A has a legitimate reason to get that information?

How else might that interoperability be defined and implemented? My Personal Care Provider seems to have the ability to communicate directly with my pharmacy. I doubt that the pharmacy has access to my health records, and I doubt the PCP has access to the pharmacy's records. They both know enough about me, though, to make prescribing meds a simple phone call or email, or perhaps some other method of electronic communication. Is that the interoperability we're aiming for?

I'm just noodling ideas here, to be honest. This started as a bit of concern over commingling what seems to be, at least in the US and probably in most jurisdictions, very sensitive data. And wondering what liability you might be incurring by doing so.

I apologize, though, for not taking time to fully explain the reason for my original question to you.

And now I see more detailed information on implementing security posted by Edgar.

 

[Edgar_]

You should definitely chat with a tech-savvy lawyer in your app's launch area. George's concerns are something you should stay ahead of, and it's pretty likely that potential buyers will want to know about them. So, being well-prepared is key.

As a matter of fact, I recently got hit with a question about the legal stuff for one of my apps. Not from a buyer, but a potential distributor who happens to have about 40% of my target audience. Lucky for me, I had a lawyer next to me, so we kinda sidestepped the issue for a couple of weeks. But when we were back, we were armed with all the answers. Although we're not 100% done with all the legal stuff, the software is already out there in the wild. According to my sources, stuff like this happens all the time, so don't be so afraid, but keep it in the radar.

 

[medihub_guy]

The better question is, do you have concerns?

You've taken an important step by moving the data into a more secure database; I assume that's SQL Server or SQL Azure or one of the other options available, given the move to a web-based interface.

Any time you take possession of customer data, you have to be concerned about data breaches, IMO. You are handling prescription information for potentially thousands of patients. I don't know what the laws are in the Bahamas, so I can't say they are more, or less, strict, than in Europe or the US. All I know for sure is that security is a higher level of concern when that kind of information is under your control.

Interoperability and data security are probably always going to be in tension. It seems to me that having 100% security and 100% interoperability at the same time is not realistic. So, the goal, I think, is to provide adequate security and as much interoperability as you can achieve under that context.

It also occurs to me to wonder what interoperability means, and not just in this context. Does that mean Pharmacy A should be able to directly retrieve prescription information for a customer from pharmacy B? If so, the whole idea of segregating data is out the window anyway. Does it mean being able to retrieve that data via your intervention as the administrator? What are the implications of that? What responsibility do you have to ensure that Pharmacy A has a legitimate reason to get that information?

How else might that interoperability be defined and implemented? My Personal Care Provider seems to have the ability to communicate directly with my pharmacy. I doubt that the pharmacy has access to my health records, and I doubt the PCP has access to the pharmacy's records. They both know enough about me, though, to make prescribing meds a simple phone call or email, or perhaps some other method of electronic communication. Is that the interoperability we're aiming for?

I'm just noodling ideas here, to be honest. This started as a bit of concern over commingling what seems to be, at least in the US and probably in most jurisdictions, very sensitive data. And wondering what liability you might be incurring by doing so.

I apologize, though, for not taking time to fully explain the reason for my original question to you.

@GPGeorge thank you so much for offering your advice. You've given me a lot to think about.

This is indeed sensitive data that should have security at the forefront. I was insisting on illustrating scenarios where there would be a breach in confidentiality regardless of data being comingled. With queries in place, how can Pharmacy A see Pharmacy B's data?

Kindly indulge me. I am using Azure SQL as my backend. Each customer gets a "locked down" MS Access file front end that queries data based on their location (PharmacyID) and patient's pharmacy preference. What do I mean by "Patient's pharmacy preference"? So, there is a patient portal being developed that will allow patients to select which pharmacies they want to have access to their data. This is where interoperability kicks in for my application.

To further explain, patients will be able to manipulate their profile using the the patient portal to select the pharmacy they want to access their data. There is a table with a many to many relationship (we'll call it Table1) with PatientID, PharmacyID and GovernmentIssuedNumber. This table is used to generate a query with the criteria PatientID =" & Forms!SplashScreen!PharmacyID. So lets say Patient A, that the record's PatientID=1, has 3 records in Table1 with 3 different PharmacyIDs matching the same PatientID, the query will only show the record that matches the PharmacyID of that pharmacy. Now, when a patient wants to add a pharmacy to his/her network via the patient portal, Adding a pharmacy will add a record to Table1 with the matching PatientID and PharmacyID. When they want to remove the pharmacy, this record will be deleted in Table1.

Duplicates are prevented using GovernmentIssuedNumber when the patient profile is being created at any pharmacy.

Essentially what we're doing here is allowing the patient's drug history and information "follow" them wherever pharmacy they want to go. This eliminates the need for pharmacies having to call one another to discuss records over the phone or internet.

Now I must admit, there are some scenarios that need further attention using this model.

I hope I didnt lose you with my explanation. Looking forward to your feedback.

 

[GPGeorge]

Both Pat and Edgar know more about this area than I do. I will defer to them, therefore, on specifics. I will only comment that you've described the scenario where an "average user" follows the rules. I brought up the potential risk from a malicious invader getting to the data of one customer in one of your client accounts and from there being able to find all other customers' data in all other client accounts.

 

[medihub_guy]

Both Pat and Edgar know more about this area than I do. I will defer to them, therefore, on specifics. I will only comment that you've described the scenario where an "average user" follows the rules. I brought up the potential risk from a malicious invader getting to the data of one customer in one of your client accounts and from there being able to find all other customers' data in all other client accounts.

Hi George,

Thanks for still offering your opinion. Again though, this goes for anyone willing to answer, can you paint the picture of how exactly the hacker can get in? Theoretically, what are the weak points of an application as i described considering all security steps are taken?

 

[The_Doc_Man]

How can I facilitate interoperability between these databases?

Globally, there is a huge push towards interoperability between pharmacy management applications within a given jurisdiction. To date, the United States has difficulty achieving this despite government incentivized initiatives and laws.

My market is The Bahamas. Interoperability is a lot more achievable/manageable here as there are <100 pharmacies that are potential clients for my application. Perhaps this can give you a better insight of why I've taken the route I've taken.

Data security is one of the reasons why Access is less ideal for my application and why I have decided to utilize a web-based application. All available advice pertaining to locking down a front end file comes with a clause that "any experienced access developer would be able to bypass your front end security". This is why, in my opinion, Access is not an option for a large scale enterprise. Boy do I wish it was though. I've spent a great deal of hours learning VBA when i could have been learning another programming language. BUT this was the advantage Access had over the others - it was easy and developing an application was relatively quick. So, Access has served its purpose i.e. being a functional prototype. I would recommend Access to any developer just getting started for that reason alone. Get in quick with your prototype and adjust your application to industry feedback. Once you have a design that works for most, make the switch!

So now, given that an Access front end file is no longer in the equation, do you still have concerns of a data breach?

A variant comment:

This thread is incredibly valuable as an example and learning/teaching discussion of feeling your way through a virtual minefield of difficulties starting from Access and growing to encompass a larger (and different) style of application. Thank you, @medihub_guy - you have contributed greatly to the value of the forum by openly sharing the progress of your project with us. Using Access as a prototype but evolving into something else is a typical path, but you have explained WHY you made the choices. Thank you from a long-time forum member and more recently nominated moderator.

 

[GPGeorge]

Hi George,

Thanks for still offering your opinion. Again though, this goes for anyone willing to answer, can you paint the picture of how exactly the hacker can get in? Theoretically, what are the weak points of an application as i described considering all security steps are taken?

What you probably should do is hire someone who specializes in security to evaluate your system directly. Moreover, It seems inappropriate to try to do that in an open forum. My point was never to try to pin down specifics, but to try to raise awareness of the risk I see.

I echo The_Doc_Man's sentiment. Discussing issues objectively is far better than debating entrenched positions.

 

[Edgar_]

this goes for anyone willing to answer, can you paint the picture of how exactly the hacker can get in? Theoretically, what are the weak points of an application as i described considering all security steps are taken?

The weakpoints of a web application depend entirely on the architecture and technologies chosen to make it work. You could connect via ODBC drivers (you are familiar with these, for Access), with an ORM, with a direct user/password connection, Serverless (like Firebase, AWS, etc), among others, of which the most common may very well be REST API. Each has its pros and cons. As a web developer, I've used all of the ones I mentioned, but I have a tendency to design using REST API and Serverless the most, so I could tell you about these.

I know, this is a highly generic response, but it is certainly impossible to tell you where your app is weak when I don't have it open in my browser, so I can do some testing on it. I wish I could tell you what a typical hacker would do, but unfortunately there are many kinds of attacks. I'll enumarate a few that I can picture at the moment:

1. Some guy is just sending requests from his network of zombie computers looking for vulnerabilities via brute force to MANY different random IP addresses until he receives clues that one has a weak point. This attack might be the most common, because they'll just look for common mistakes, like "Oh, let's see if I get access using root/admin as username and password", but remember that this is automated, and he's testing users and passwords from a dictionary of common users and passwords... ALL servers receive these as soon as they're live, because it's targeting IP addresses and they're numbers he can predict, so yours won't be a difference. Lots more can be said, but it goes down to attacking common mistakes.

2. Some "script kiddie" gained access to some L337 software that can perform attacks, usually denial of service attacks (DoS) by spamming your server. If the kid has friends and they can perform this attack at the same time, it becomes a distributed attack (DDoS), which will make your server unable to respond. It's a common attack, where you don't lose data but they can use this to make you lose money (it will annoy your users) or distract you from other attacks that do lead to data breach.

3. This one is frightening, imagine you have some legit user who can access your systems, this user might gain control of other resources because he's authenticated, so one layer of security is exposed to him, if your design does not account for possible attempts from this user to access the data of other authenticated users, if you don't restrict him to only see his data, and not that of others, then you have a massive problem. THIS IS THE MOST COMMON PROBLEM, check the link I gave you previously.

4. This one is even more frightening, imagine you have a clueless legit user, but he is not careful of how he connects to your service and so someone steals his credentials! if your system is well designed, the attacker will only steal the data from this clueless user, but if you have the problem described in point 3, brace yourself, consider that data stolen.

5. Now consider you don't validate/sanitize user input, that could lead to SQL injection. Imagine you have some query that does this:
SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';
Now, consider a scenario where the attacker enters the following values for the username and password:
Username: attacker' OR '1' = '1
Password: password

The query, after inserting these values, would become:
SELECT * FROM users WHERE username = 'attacker' OR '1' = '1' AND password = 'password';

The SQL query will evaluate to true because '1' = '1' is always true. And so, the query retrieves all rows from the users table, effectively bypassing the authentication process and allowing the attacker to log in as any user, even without knowing their password.

The list can go on and on, so the best answer you can get is in the link I posted previously:
https://owasp.org/www-project-top-ten/

The best you can do is ask the developer you hired to tell you how he addressed each point in the top ten from OWASP. Either that or check it out yourself via direct testing.

This is not meant to inflict fear on you, it's just a set of possibilities. Most hackers will give up when they find out you're using strong passwords, NOT default usernames, NOT default configurations, etc. Don't leave anything by default, the attackers know these vulnerabilities very well, so be creative in your credentials and configurations. Some configurations considered "good practices" may become "good possibilities" for a hacker, the best aid in that case is NOT naming things commonly and obfuscating exposed code.

 

[The_Doc_Man]

Most hackers will give up when they find out you're using strong passwords, NOT default usernames, NOT default configurations, etc. Don't leave anything by default, the attackers know these vulnerabilities very well, so be creative in your credentials and configurations. Some configurations considered "good practices" may become "good possibilities" for a hacker, the best aid in that case is NOT naming things commonly and obfuscating exposed code.

When I was still a U.S. Navy contractor, this was sometimes called the "low hanging fruit" discussion. (Mental Image: A herd of herbivores grazing near a fruit tree whose branches are drooping from the weight of the low-hanging fruit, just ripe for being picked by the beasts of the wild.)

You DO need to check that you don't have any low-hanging fruit in your app. Edgar_ gave a decent checklist of common things to avoid.

 

[Edgar_]

this was sometimes called the "low hanging fruit"

That's a great analogy, the more difficult it is for the hacker to exploit your application, the less likely they are to stay and search for other vulnerabilities. They'll give up, unless there's monetary incentive.

 

[The_Doc_Man]

I'll add a couple of other items to your list. Besides these:

1. Strong passwords. (Military standard used to be 14+ characters, or 15+ for administrators, not less than 2 upper case, NLT 2 lower case, NLT 2 digits, and NLT 2 specials.)

2. No default usernames (See #7 below for a specific example).

3. No default configurations

Add to this:

4. Always patch/update your products. To include: Windows patching (despite the fact that sometimes they are buggy), browser patching, security software signature and code patching, utility patching (for those that you actually use), and any 3rd-party network software. Explanation: The SAMS Institute (you can look them up) used to rate an unpatched system as #2 on the list of most common causes for being compromised.

5. Where you have the option (you don't always), set your browser's internal firewall to "stateful". That is, it only allows inbound traffic from places where YOU initiated a very recent connection. I.e. no "unknown" inbound connections. They may have other names for this concept as each vendor can call their features any name they want. But to the military it is a "stateful" firewall.

6. Where you have the option, NEVER develop an application as an administrator. You can publish it to your users as administrator ... but never develop as an administrator. And ABSOLUTELY never TEST it as an administrator. You want to see what your USERS can do with it, so you have to be a user to think like a user. Doing it this way stops you from accidentally building a privilege dependency in your app, i.e. something worked for you because you were running with admin privileges or permissions at the time.

7. If you don't have an IT shop to take over the admin accounts, there is a trick to perform: Create an account to be your administrator but spelled differently than the default admin account. Like ADMIN vs. ADMINISTRATOR. Then if you are GOING to do an admin function, do it from the alternate account. There are ways to make the default admin account login locally only. Exercise that option, which turns off network access, i.e. no remote logins. NOTE that if you have an IT shop, they might use a group policy override on that account anyway, in which case you can let the IT people do their jobs. This "local lockout" is NOT recommended (and might not even be possible) if you are working on WinXX Home, but should be possible with higher versions of Windows. Look it up online if you want to exercise this option as it can be tricky.