MySQL connections and multiple Users from different Departments?

Question

I have 2 departments--

• Marketing with 100 users (They only work with the Marketing table.)
• Factory with 200 users (They only work with Factory table.)

I have made 2 connections in MySQL.

The users from each department will use their respective connection from a Java client from their respective locations.

1. How may I have all the marketing people use the same connection string from their Java client?

   At any moment there will be at least 100 people online, each one connecting from their own laptop. I only ask with respect to MySQL; on the Java side the threading isn't the issue.

2. How may I disallow MarketingDept users from viewing/reading/writing the Factory table and disallow FactoryDept users from viewing/reading/writing the Marketing table?

3. How may I allow a person from the MarketingDept to edit their details in their record in the MarketingTable?

   E.g. some kind of row-level security. I find this troublesome because the only sort of authentication at the time of connection is department level and not user level.

Answer 1

GRANTs and ROLEs and VIEWs. Here are some tips to get you started.

A "Role" is a "named collection of privileges". It allow you to give each member of a Dept their own login, plus the "role" for that Dept.

Each Dept "role" (one for MarketingDept, etc) would have granted or denied read or write access to various VIEWs and columns of those views.

The Views would be crafted to, for example, exclude a column of the underlying TABLE, thereby preventing that connection from even reading the contents of that column.

A GRANT on a view or column can be for SELECT, INSERT, UPDATE, DELETE. These correspond to what is being allowed the grantee.

"Roles" were added in MariaDB 10.0.5 (2012) and MySQL 8.0 (2016). Upgrade if necessary. Study the documentation.