0

We have a client who is determined to keep their data in our cloud VM separate from other client data. That is, we have a centralized MySQL database where we store all of our client data and access the data depending on the id etc. The clients are now requesting that their data is separated one from the other. Meaning that if the database is hacked the hacker can't jump from one users data to see the others. I have never heard of this type of functionality especially for MySQL databases (you can create users and allocate to tables but not to specific data in a table) as far as I know. Possibly this is a functionality of Azure databases or something.

Has anyone encountered something like this request/solution?

Thanks

user19019404
  • 55
  • 5
  • As far as I am aware there isn't native row level security for MySQL like there is for some other RDMBS (e.g. [SQL Server](https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16)), however there are ways around this, for example by removing all access to tables for users, and only enabling access through views which are configured to filter based on user. e.g. [Article: Implementing row level security in MySQL](https://www.sqlmaestro.com/resources/all/row_level_security_mysql/) – GarethD Dec 15 '22 at 18:54

1 Answers1

1

I did work for a notification service. We stored each client's data in a separate schema, but on the same MySQL instance. The reason was to keep PII (Personally Identifiable Information) separate, so on any given application request, it was not possible that it could accidentally read data for another client.

The application first connected to a special schema that stored a table listing all the client schemas and the username & password for each client schema. The app reads this table to query for one specific client, then opens a new connection using that username & password.

It added a little bit of overhead to every session to do this two-step connection, but it wasn't too much.

I'm not sure how this eliminates the possibility of being hacked. That's still a risk. If an attacker hacks the primary database, why couldn't they also hack the specific client's database?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828