Writing desktop app that uses database. Suggestions for how to manage user access to tables?

Question

I'm writing a depsktop application (in Java) that interacts with a database which stores mostly requirements documents,but I have a bit of a dilemma. Specifically, a problem with managing user access.

To illustrate, I store all details on the folder structures in a single table. However, I would like to institute a user-group mechanism similar to Linux/Unix systems where you can only add/mod/del the folders that you have permissions for. Of course, I can only assign database permissions to a table or columns, not individual rows which represent the folders they have access to.

One solution to this is to give each folder its own table, and then only give update/insert/delete access to certain users but that would be nothing short of a nightmare as the # of tables would explode to an unmanageable level.

The second option is to create a server side process that sits between the database and clients, which would return the list of folders that the user is stated to have (removing the whole issue of table privileges, buy requiring now that I write a network protocol to talk with this process, instead of just using the jdbc driver directly)

Final option is triggers, though the database I have to support (mysql) doesn't make it easy for me to reject. I was also hoping, given the frequency of access, to avoid triggers due to the added computation and slower performance.

None are ideal but I'm running out of ideas.

Recommendations?

Answer 1

You should not conflate DB rights and permissions with user rights and permissions. User code should go through a DAL or service layer which implements the access restriction functionality. Where you store information about rights depends largely on your authentication mechanism. If you have an existing user authentication system like active directory or LDAP, you can integrate either authentication & authorization into that or integrate only aUthentication, and push authorization into the DB.

Basically for your model it sounds like you should have a table for authenticated entities, tables for users and groups which both have a FK into it, and then a permissions table that has a FK relationships into the auth entities table.

Answer 2

How secure does this app need to be?

If you are just trying to protect naive users from accidentally screwing each other's folders up, and you really want your client program to have direct database access, it sounds like you need to handle the folder permissions in the desktop client itself. Yes, this means that a clever "hacker" could connect to the DB directly after decompiling your java code and discovering the connection info for the database, but for many small intranet apps that is ok.

For any app that you expect to grow, or that needs real fine grained security, it's probably worth the effort to implement a server of some sort between the DB and your desktop client.

Answer 3

Why don't you manage access within the app itself instaed of relying on the rdbms to do it? You could just have a table of users versus tables and access levels and consult it before access.

Answer 4

In PostgreSQL, this is a fairly common approach to your dilemma. While I've not tried it in MySQL sepecifically, it may be worth considering.

That being said, it may well be preferable to manage this in your application rather than MySQL. Read on.

---

You can use a mixture of permission tables, views, and the user() function.

For example, say you had a table called Document:

Document_ID | Name | Content
------------+------+--------
1234        | Doc1 | Bla bla
2345        | Doc2 | Bla bla
3456        | Doc3 | Bla bla
------------+------+--------

And you had a permission table called Document_User.

Document_ID | User
------------+------+--------
1234        | smith@'%'
2345        | smith@'%'
3456        | smith@'%'
1234        | jones@'%'
2345        | jones@'%'
1234        | white@'%'
------------+------+--------

From the above structure, it is obvious that User Smith has access to all three documents, User Jones has access to the first two, and User White only has access to the first one.

Finally, create a view like this:

CREATE VIEW 
SQL SECURITY DEFINER
    `User_Document`
AS
    SELECT *
    FROM `Document`
    WHERE Document_ID IN 
        (SELECT Document_ID FROM Document_User WHERE User = USER())

The currently logged in user will see a set of records in the User_Document table that are only the records that they have permission to see.