1

I am very new to databases and Web design, but I understand enough to know that designing a good database is crucial, so I would like to prepare for that conceptually. However, I was unable to find any answers on my level on the web/in tutorials.

I am working towards a developing a database (MariaDB, to be exact) that will be accessed by my website (written in Django). I want the website to serve as an API for several people to work on the database. Naturally, I want to set some use- or group-specific privileges.

What are the possible ways of doing that, and what are their upsides and downsides?

I can only think of two generals approaches:

  1. Connecting the website to the database as root, and letting the website API limit user capacity to edit certain tables.
  2. Using some sort of server-side user system directly in a web API.

This is important to me both in the context of limited access to certain tables within a database and syncing multiple people editing the same table. Is there an approach generally considered as a good practice?

closed as primarily opinion-based by LowlyDBA, Colin 't Hart, RLF, mustaccio, Kin Jun 13 '17 at 1:23

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

2

Don't connect as root/super user

Read this: Multi-Tenant Data Architecture

Since it's a web app, you're going to want to use connection pooling, for performance

This means you need to use impersonation, i.e., login as one user then switch to a different user. Because connection pooling requires that everyone in the pool connect with the same connection string.

For Maria/MySQL, read up on https://mariadb.com/kb/en/mariadb/grant/#proxy-privileges

MySQL/Maria doesn't yet do row security as far as I know, so you'd need to do your access control (via SQL) in the application layer (your web app)

In terms of multiple people editing the same rows at the same time, read up on Optimistic Concurrency Control

Personally, I'd consider Postgres, as it does row security

  • A lot of terms I have never heard before, so it's definitely the right track—thanks! – MrVocabulary Jun 13 '17 at 6:46
1

You want to definitely limit access on the SQL side. You can create a login and then create a user on the SQL database and grant select on the table you want to allow access to. Here is an old StackOverflow answer.

Not the answer you're looking for? Browse other questions tagged or ask your own question.