User authentication using MySQL and GRANT or custom software solution?

Question

I'm a long time programmer but have done very little database design. So I'm seeking some feedback on a solution I'm developing.

Description

I will have several (10 to 20) customers storing sensitive data on my remote server. A customer is an organization that has users. Access is done via piece of java software running on the server accessing a MySQL server. A separate client java program accesses the server and does the interchange.

Data privacy is very important, so I am planning on a separate DB (schema) for each customer. Within each database there are three user levels: input, query, and admin. A user fits into one of those levels and gets various permissions on the DB. I'm planning to do everything with stored procedures, so really they are getting access to a set of stored procedures on a specific DB.

The biggest challenge is that the customer's Admin users have to be able to add (or remove) additional users that only have access to the customer's database. Users should be unique to the DB, but not to the MySQL instance. Each customer should be about to have a user named 'bob' for example.

Question

So I have two ways currently of approaching this. The one I currently prefer is to have each user be a real MySQL user that can have privileges GRANTed. This makes sure they can only execute what their role specifies right at the MySQL level. The problem is name collision since MySQL usernames are global to all DBs. I solved that by having a "root" table that maps a User's customerID (unique to each customer, not each user) to a DB, then a table in that DB maps the user's name to a userID, which is then the actual MySQL user. The java server software would connect to the DB using the mapped userID and user's password, and go about business. It's a good bit of effort and hoop jumping to do this, but I like it from a security standpoint.

The other way is just to have a couple of base users in the DB for the java software to connect and then write our own auth by storing usernames and passwords in each customer's DB. It would be up to the software to make sure only the right DBs are modified, only the right functions are called, etc since nothing in MySQL would be able to prevent it. The base users would have to have all permissions for all user types, so a software glitch or compromise in the java software could lead to bad things for the DB. Makes the DB easier but the software somewhat more complex.

Feedback

So do any DB veterans have some feedback on this? Has anyone solved an issue like this in a better way? Trying to get some ideas before we commit to something.

Thanks, Matt

Answer 1

The other way is just to have a couple of base users in the DB for the java software to connect and then ...

Use LDAP to store users, schema associations, privileges, roles, authorizations, etc., etc.

It is always up to the software -- via authorization rules -- to make sure only the right DBs are modified, only the right functions are called, etc

The base users would have to appropriate permissions for specific user types,

so a software glitch or compromise in the java software could lead to bad things for the DB.

That's always true. So use testing to prevent this mysterious "glitch".

The database is not magically "more secure" or magically "less subject to software glitches". DB security is just as trustworthy as application security. Both are just as trustworthy as the administrative procedures and human factors.

Use application-level authorization. Use LDAP.

Don't impute magical powers to RDBMS authorization schemes. They're not "better" or "more trustworthy".

Application authorization checks -- in most languages and frameworks -- are single line-of-code decorators (or if statements) that explicitly define what group of users has access to the functionality. It's not difficult. It's not subject to "glitches".