Join Log in
Score:1
blur avatar Drupal

For which tables should the MariaDB user have CRUD permissions for browsing the site?

bn flag
blur

I'm trying to lock down the database on my Drupal 9 install so that people can browse the website, but can't write to it.

I have 2 users in MariaDB with access to the database. I want to set one account to only be able to write to the needed tables for browsing and give the other account full permissions. To Which tables should the first account have CRUD permissions?

To determine which user to use, I'm going to set an environment variable that will detect the subdomain (admin.site.com) and assign database settings accordingly. I have blocked access to Drupal's admin/login pages on the main domain while allowing full access on admin.site.com. The subdomain is on a separate IP than the main domain and the firewall blocks access based on the connecting user IP.

53
0 + 8
9
Jaypan avatar
de flag
Jaypan
I don't know why someone downvoted this question - it's a reasonable question. It's Drupal related, explains the issue, and personally I would like to know the answer myself. I upvoted this question.
2
Reply
cn flag
Clive
I was just writing literally the same thing when the last comment popped up - it's almost impossible to answer this exhaustively (because of contrib/custom modules), but an answer addressing the general theory for core at least would surely be useful to a lot of people
1
Reply
apaderno avatar
us flag
apaderno
I would say that every database table created from Drupal is used to write data, when the site is accessed from an administrator user. If we exclude the user #1, or administrator users with full permissions on the site, there could be database tables that aren't written when those users access the site. We cannot give a complete list because which database tables are never written when administrator users access the site depends from the permissions they have, from which modules are installed, from their versions, the Drupal core version, and which Drupal core features are enabled.
2
Reply
blur avatar
bn flag
blur
Thank you for the comments. I am mainly concerned about core right now and just blocking the public DB user from inserting into the database. Maybe I'll just go though the tables one-by-one adjusting permissions to see what happens. I'll try to take notes and post back here if nobody has found a solution. Isn't there a module that shows that SQL queries used per page?
0
Reply
ru flag
Hudri
If everything on your frontend is mostly static and read-only for anonymous end-users, I personally would use [Tome](https://www.drupal.org/project/tome) or Gatsby. This is even more secure and more performant. Especially Tome is a low-effort to setup (if you don't have dynamic content like views with exposed freetext-filters) and nothing is more secure than plain HTML.
0
Reply
apaderno avatar
us flag
apaderno
The database *public* account is used for those users who visit the site, who aren't allowed to create comments, post content, and create accounts, while the *private* account is used for administrator users who access the site using a specific sub-domain. Did I understand that correctly?
0
Reply
blur avatar
bn flag
blur
That is correct.
0
Reply
apaderno avatar
us flag
apaderno
I was going to post an answer, but with just two database tables it would be incomplete. It's quite hard to understand, reading the code, which tables are used to write data when the site is accessed only to read data. It's easier to say which modules should not be enabled to avoid data is written when the site is used only to read data from anonymous users.
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.