Placing my customers individual front ends in Sharepoint Synced Folder

[Edgar_]

data breach

It depends on how you secure your app. If you and your developer follow at least the top ten recommendations from OWASP, your app will have a good level of protection. Here's the link:
https://owasp.org/www-project-top-ten/

The developer you've hired should already know a bunch of good practices that can help mitigate most of the risks there, but you're the one paying for it, so it's nice if you can supervise that. Take into account that a number of these recommendations are architecture-specific, some architectures have issues that others don't. Also, it's not that you have to check them one by one, there are design patterns that force you to do things a certain way to avoid most of these problems, there are also libraries that help protect your app with a few lines of code and there are components for React that help with a lot of these things. But most of the stuff will have to be secured in the back-end.

You have to keep an eye on the data entered through your front-end as your user types it. Once it's typed, you have to validate in your front-end whether the data is fit for the database.

If it looks good, great. Now ensure its transportation is through https, don't ever send data through http, there are techniques and programs that can intercept these packages, so if someone intercepts yours, it better be encrypted.

You should also educate your users not to use your app through insecure wifi connections (and if you can monitor that, good for you).

If transportation looks good, great, now receive it properly in the back-end, sanitize it, yes, you might have already sanitized the data in the front-end, but since that can be forged, you should also sanitize it as it's received, AND also ensure the users who sent it have the credentials to access the resources they're requesting.

If all is well, send the data, if anything fails, send a nasty 4xx error. No URL should ever match any ID in your database, if possible, send these encrypted with the request and don't show any ID in a URL, functions that manage sensitive information should be obfuscated, the React project map should also be obfuscated, failed attempts should ban users temporarily, etc.

As you can see, there are a lot of things you have no control when you use Access, but you have a lot of control in web applications. That also means it's easy to forget to include something, so I suggest you take a course on penetration testing, or you hire someone that can do it at some point.

Yes, it can be hard to do all of that, so the type of architecture you use for your web app should allow you to incorporate more security as it grows.

 

[medihub_guy]

It depends on how you secure your app. If you and your developer follow at least the top ten recommendations from OWASP, your app will have a good level of protection. Here's the link:
https://owasp.org/www-project-top-ten/

The developer you've hired should already know a bunch of good practices that can help mitigate most of the risks there, but you're the one paying for it, so it's nice if you can supervise that. Take into account that a number of these recommendations are architecture-specific, some architectures have issues that others don't. Also, it's not that you have to check them one by one, there are design patterns that force you to do things a certain way to avoid most of these problems, there are also libraries that help protect your app with a few lines of code and there are components for React that help with a lot of these things. But most of the stuff will have to be secured in the back-end.

You have to keep an eye on the data entered through your front-end as your user types it. Once it's typed, you have to validate in your front-end whether the data is fit for the database.

If it looks good, great. Now ensure its transportation is through https, don't ever send data through http, there are techniques and programs that can intercept these packages, so if someone intercepts yours, it better be encrypted.

You should also educate your users not to use your app through insecure wifi connections (and if you can monitor that, good for you).

If transportation looks good, great, now receive it properly in the back-end, sanitize it, yes, you might have already sanitized the data in the front-end, but since that can be forged, you should also sanitize it as it's received, AND also ensure the users who sent it have the credentials to access the resources they're requesting.

If all is well, send the data, if anything fails, send a nasty 4xx error. No URL should ever match any ID in your database, if possible, send these encrypted with the request and don't show any ID in a URL, functions that manage sensitive information should be obfuscated, the React project map should also be obfuscated, failed attempts should ban users temporarily, etc.

As you can see, there are a lot of things you have no control when you use Access, but you have a lot of control in web applications. That also means it's easy to forget to include something, so I suggest you take a course on penetration testing, or you hire someone that can do it at some point.

Yes, it can be hard to do all of that, so the type of architecture you use for your web app should allow you to incorporate more security as it grows.

This is precisely why I value this forum. Your input is invaluable and I will do as suggested with taking some courses to get up to speed.
After much introspect, I realized I'm not equipped to handle this venture on my own which is why I hired a developer with an existing team. He was kind enough to take me under his wing to "show me the ropes".
Being the original developer and owner of the company, I will certainly need to know a little bit of everything, but hiring a team was my best move yet. I'm a devoted learner so I should be up to speed or at the very least be able to cross check the developers work by the time we are ready to launch the web app.
Thanks Edgar for your advice. It means so much!

 

[Edgar_]

Happy to help, keep an eye on the money you throw at it, ensure there is enough money flowing in before investing too much. Keep in mind that most apps in the world have problems, a lot of apps have 0-day bugs nobody knows about, I know a few myself and also remember memes like this exist for a reason:

 

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

 

[Pat Hartman]

How can I facilitate interoperability between these databases?

If you were in the US, the method for communicating between pharmacies and insurers would almost certainly be the EDI system. Electronic Data Interchange. This is a set of pre-defined transactions that are used for business-to-business communication. I know enough about this to be dangerous so you need to find an expert. But, rather than reinvent the wheel for communication between pharmacies, you should probably use something that already exists. As you can see by the map in one of the videos, this method is used world wide. Is it used in the Bahamas? I don't know. You could certainly introduce it. Sometimes trading partners will make their own documentation that greatly simplifies the initial work you have to do. In my case, our trading partner specified what parts of each transaction he was interested in. So, the formats never deviated but what data was required and what was optional could be decided by trading partners. So, even though my transactions were intended for the State of Connecticut (for the 835 and 837), any other provider would understand the file if they received it because the formatting of each part of the transaction was always fixed. I don't know enough about what your "exchanges" would entail to know if the defined EDI codes cover your requirements. You would need to review all the EDI codes in the Healthcare and Insurance section to see if they can handle what you need.

You can work with an intermediary (if any service the Bahamas) or you can create and process your own transactions in VBA. The EDI transactions are complicated and you need to purchase the documentation from the regulatory group but once you understand how it works, the coding is not difficult, just simply tedious. I've created/processed EDI transactions for the example in the video and also for medical payments (835 and 837). I did it by reading the manual. In both cases, the file transfer was done using FTP but there are other methods that involve clearing houses or middle-men that reformat your transaction to an actual EDI but this just adds a perpetual transaction fee. So, if you can handle the coding, that's a one time charge and you have a fee for the FTP site unless you can host it yourself on either end. Dropbox would probably work also. At the end of the day, Each Pharmacy creates the send/reply transactions for other pharmacies and puts them in the appropriate FTP site. Third party clearing houses become more appropriate when you are dealing with thousands of exchanges between hundreds of different customers every day. If you were to use a clearing house, all your outgoing transactions would go to the clearing house. They would read the transaction and forward it to its target. You would get a file to download with all your transactions. Using a clearing house might give you real time exchanges rather than batch exchanges.

Electronic data interchange - Wikipedia

en.wikipedia.org

A simplistic video of how it works. This is part of a sales pitch from a middle-man.

What is EDI (Electronic Data Interchange)? | DataTrans Solutions

EDI stands for electronic data interchange and is defined as computer-to-computer exchange of business information in a standard and structured format.

datatrans-inc.com

https://www.opentext.com/file_source/OpenText/en_US/PDF/opentext-ebook-edi-basics-en.pdf

What is EDI (Electronic Data Interchange)? | EDI Basics

What is EDI (Electronic Data Interchange)? The computer-to-computer exchange of business documents in an electronic format between business partners

www.edibasics.com

Complete EDI Guide | Electronic Data Interchange | (11,725 Words)

Complete EDI guide. This EDI guide covers all topics related to Electronic Data Interchange such as history, getting started guide and more

www.commport.com

Attached is a list of the defined EDI transactions.
Here is some documentation on Pharmacy use of the 835

https://ncpdp.org/NCPDP/media/pdf/X12N835_5010PaymentReferenceGuide.pdf

​

 

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