# MySQL and MariaDB access control and query approval > How to give developers MySQL or MariaDB production access without sharing a password, and what QueryProxy blocks, approves and masks on the way. - Source: https://queryproxy.com/mysql-mariadb-access-control/ - Language: en-US - Author: Muhammet Şafak --- MySQL and MariaDB decide which accounts can reach which schemas and tables. They do not decide whether *this particular statement*, typed by a person at 3 a.m., should run. QueryProxy adds that decision: developers submit SQL, the statement is parsed and checked before it can enter the queue, a DBA approves it, and the result is masked before it is stored. The database password stays in QueryProxy's vault and is never handed to a developer. ## What does GRANT leave open? `GRANT` scopes an account to schemas, tables and columns, and it should be the first thing you tighten. It still leaves three gaps that matter in production: - An account that may `UPDATE` a table can run an `UPDATE` with no `WHERE` (see [how to stop `UPDATE` and `DELETE` without `WHERE`](/blog/prevent-update-delete-without-where/), which also covers MySQL safe updates). - An account that may `SELECT` the `users` table receives every email address in it, unmasked, on a laptop. - Nobody else sees the statement before it runs. The MySQL 8.4 reference shows how far one privilege can reach. The [`FILE` privilege](https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html) lets an account read and write files on the server host, through `LOAD DATA`, `SELECT ... INTO OUTFILE` and `LOAD_FILE()`, and `secure_file_priv` limits where (read on 2026-10-13). Those are the statements QueryProxy refuses outright. [What database access control is](/what-is-database-access-control/) explains why roles alone do not close these gaps. ## What does QueryProxy block on MySQL? Every submission is parsed before it reaches the approval queue, and a request that breaks a guard is rejected at submission with an explicit message. The [SQL guards reference](/docs/sql-guards/) lists the rules per dialect. The ones that matter most here: - `UPDATE` and `DELETE` without a `WHERE`, or with one that is always true (`WHERE 1 = 1`), are rejected. A `SELECT` with no `LIMIT` gets `LIMIT 1000` injected, and a limit above `10000` is clamped. - `SELECT ... INTO OUTFILE`, `INTO DUMPFILE`, `LOAD DATA` and `LOAD_FILE()` are blocked. `SELECT ... INTO @var` is a plain read. - `CREATE FUNCTION ... SONAME`, which loads a UDF from a shared library, is blocked, as are `CREATE USER`, `ALTER USER`, `DROP USER` and `SHUTDOWN`. - `SET GLOBAL`, `SET PERSIST` and `SET PERSIST_ONLY` on guarded settings such as `general_log`, `plugin_dir`, `secure_file_priv` and `local_infile` are refused. A `SESSION` write to these settings is not guarded. - `get_lock`, `release_lock`, `sleep` and `benchmark` are classified as writes, so they go through approval instead of passing as reads. - `PREPARE s FROM ''` is blocked, and `PREPARE s FROM @q` is blocked because the text sits in a variable. - `SET sql_mode` in any scope is refused. ```sql DELETE FROM orders; -- rejected at submission DELETE FROM orders WHERE id = 42; -- allowed, still needs a DBA SELECT * FROM users INTO OUTFILE '/tmp/u'; -- rejected at submission ``` The table names are examples; the verdicts follow the documented rules. ## Why does sql_mode matter? The guard reads quotes and backslashes the default way. If a session's `sql_mode` contains `NO_BACKSLASH_ESCAPES` or `ANSI_QUOTES`, the server would read the same text differently, and a string the guard saw as harmless could be code. So before QueryProxy runs SQL on a `mysql` or `mariadb` connection it reads `@@SESSION.sql_mode`, and if either flag is present the request fails and nothing runs, with a message naming the flag. QueryProxy sets a strict `sql_mode` when it connects; if the check still fires, look for something that changes the mode after connect, such as a proxy in between. The [configuration guide](/docs/configuration/) describes this. ## What is different on MariaDB? Register a MariaDB server with the `mariadb` driver, not the `mysql` driver. The [SQL guards reference](/docs/sql-guards/) gives the reason: the driver decides how executable comments are read. - **Executable comments.** `/*M!100000 ... */` is code on MariaDB. On the `mariadb` driver the guard reads the body as SQL; on the `mysql` driver it is a plain comment. When the target is unknown, `/*M!` is rejected outright. Two MariaDB-specific statement forms are checked on both drivers, so they are not a reason to pick one: - **`ANALYZE` statements.** MariaDB's `ANALYZE UPDATE ...` is classified as a write and `ANALYZE DELETE FROM t` without a `WHERE` is blocked, while `ANALYZE SELECT ...` is judged as the `SELECT` inside it. `ANALYZE DROP TABLE`, `ANALYZE TRUNCATE` and `ANALYZE SET` are blocked. - **`SET STATEMENT ... FOR `.** The statement after `FOR` is judged by its own rules. Without one, the request is blocked. Everything else in the previous section applies to MariaDB as well. ## How do you connect a MySQL or MariaDB database? A DBA registers the database under **Connections** with the `mysql` or `mariadb` driver. The default port is `3306` ([`DbDriver.php`](https://github.com/QueryProxy/QueryProxy/blob/v0.2.5/app/Enums/DbDriver.php), v0.2.5). The default Docker image ships `pdo_mysql`, so no image change is needed. Credentials are encrypted at rest with AES-256, and a developer sees a connection only after a DBA grants it. For read-only teams, register a separate connection with a database user that has `SELECT` privileges only, and point it at a replica where you can. The [production hardening guide](/docs/production-hardening/) recommends this. ## What happens to the result? Rows are masked while they stream from the cursor to the result file, so an unmasked copy never exists on disk. Masking rules match a column name (such as `*email*`) or the content of a value, and a value that no rule matches comes back unchanged. [Dynamic data masking](/dynamic-data-masking/) explains the idea, and the [data masking documentation](/docs/data-masking/) lists the rule types and strategies. Error messages go through masking too, because messages such as `Duplicate entry '...'` can echo a value. Every step, from submission to download, is written to an immutable audit log. ## What are the limits on MySQL and MariaDB? - **The denylist is incomplete.** It names dangerous syntax known today, and a spelling it does not name passes. The [SQL guards reference](/docs/sql-guards/) says so itself: what authorizes a statement is the DBA approval, not the guard. - **Not yet run against live servers.** The reference states that the MySQL and MariaDB behaviour is verified against the guard's unit tests, and that these dialects have not been run against live servers. - **No read-only transaction or statement timeout on the database side yet.** Both are listed as planned for v0.3.0. Until then, use a `SELECT`-only database user for read connections. - **Code stored on the server is not covered.** A stored procedure or function that already exists in the database runs what it contains. - **Masking is rule-based.** A value that no rule matches is returned as it is, so QueryProxy does not claim to mask all personal data. ## When do you not need QueryProxy? If one person owns the database and is the only one who connects, there is no second person to approve anything, and `GRANT` plus a read-only account is enough. The same holds for a development database that holds no real data. If your problem is only that applications keep passwords in config files, a secrets manager or [IAM database authentication](/compare/aws-iam-database-authentication/) solves that without an approval queue. QueryProxy earns its place when people need ad-hoc access to data that matters, and you want a second pair of eyes and a record of what ran. Read the [approval workflow](/sql-query-approval-workflow/) for how a request moves, or [compare QueryProxy with a bastion host](/compare/bastion-host/). PostgreSQL teams can start from [PostgreSQL access control](/postgresql-access-control/). ## Frequently asked ### How do I give developers access to a production MySQL database without sharing the password? Register the database once in QueryProxy, where the credential is stored AES-256 encrypted, and grant developers the connection instead of the password. They submit SQL, a DBA approves it, and a worker runs it with the stored credential. Developers never see the password. ### Does QueryProxy support MariaDB as well as MySQL? Yes. Register a MariaDB server with the mariadb driver and a MySQL server with the mysql driver. The drivers differ in how the guard reads executable comments (/*M!100000 ... */), and the guard checks MariaDB statements such as ANALYZE UPDATE and SET STATEMENT ... FOR on both drivers. ### Can a developer export a table to a file through QueryProxy? Not with SQL the guard recognizes. SELECT ... INTO OUTFILE, INTO DUMPFILE, LOAD DATA and LOAD_FILE() are refused at submission. Results come back through the portal, masked, as a viewer or a CSV download. ### Why does a request fail with a sql_mode message? Before it runs SQL on a mysql or mariadb connection, QueryProxy reads the session sql_mode. If it contains NO_BACKSLASH_ESCAPES or ANSI_QUOTES, the guard would read quotes differently from the server, so the request fails and nothing runs. ### Is a database-side read-only transaction available for MySQL and MariaDB connections? Not yet. A database-side read-only transaction and statement timeout are listed as not covered and planned for v0.3.0. Until then, give read connections a database user with SELECT privileges only.