Security Interview Questions

LOCAL INFILE on the client plus local_infile=ON on the server lets LOAD DATA read the client's filesystem, which is a data-exfil path from a malicious server or a compromised client library. secure_file_priv restricts non-LOCAL FILE reads/writes on the server. Disable local_infile unless an admin ETL path is locked down. The FILE privilege is a separate escalation.

The SUPER privilege (or the 8.0 dynamic privileges that replaced chunks of it) lets a user SET GLOBAL, kill threads, and manipulate replication. Combined with FILE it is close to host compromise. MySQL 8 splits SUPER into BINLOG_ADMIN, CONNECTION_ADMIN, and friends so you stop granting SUPER to humans. Audit mysql.user for leftover SUPER on old upgrades.

sql_mode is a session variable attackers love: turning off STRICT_TRANS_TABLES re-enables truncated writes; NO_BACKSLASH_ESCAPES changes escape behavior. A DEFINER procedure captures sql_mode at create time, which can bypass the caller’s strict mode. SET SESSION sql_mode should be reserved for migration scripts, not granted to app users. Audit init_connect as well—it runs as the user.

mysql.user plugin mysql_native_password vs caching_sha2_password changes how hashes sit on the wire and in mysql.user. Native password is a SHA1-era scramble that tools still request; caching_sha2 needs RSA key exchange or TLS for the first auth. Forcing old clients via default_authentication_plugin weakens the estate. Dual users during migration beat turning the default back globally.

The audit plugin or MySQL Enterprise Audit logs CONNECT and QUERY events; without it you scrape the general log (too heavy) or binlog Query events. PERFORMANCE_SCHEMA is not a durable audit trail. Binlog ROW will not show the SQL text of a DROP the way STATEMENT/MIXED Query events do. Plan audit before the incident; mysqlbinlog after is a poor substitute.

Data masking (Enterprise) or generated views that wrap AES_ENCRYPT hide PII from analysts who get SELECT on the view only. Masking via views is defeated if the analyst has SELECT on the base table. Column-level grants (8.0) help but fail for SELECT *. Keep encryption keys in the keyring, not in a procedure body. Hashing for join keys is not encryption.

FILE privilege plus secure_file_priv='' is a classic privilege-escalation path: SELECT ... INTO OUTFILE writes a web shell, or LOAD_FILE reads /etc/passwd if OS perms allow. Even with a directory jail, overwriting a .cnf that mysqld will include is a threat. Revoke FILE from app users; use an ETL role and a locked directory. InnoDB tablespaces are not a FILE substitute.

DEFINER views and procedures owned by root@localhost are a hidden SUPER path when SQL SECURITY DEFINER and EXECUTE/SELECT are granted to a weaker user. The routine runs with root’s table rights, including mysql.user if the body allows dynamic SQL. Recreate objects as a least-privilege definer. mysqldump --set-gtid-purged dumps preserve DEFINER clauses you must rewrite.

TLS certificates for replicas (SOURCE_SSL_CA) prevent a MITM from injecting ROW events into the IO thread. SOURCE_SSL_VERIFY_SERVER_CERT should be ON. Replication user should have only REPLICATION SLAVE / REPLICATION_SLAVE_ADMIN, not SUPER. GTID plus a mismatched uuid still applies forged events if they stream on an encrypted-but-unverified channel.

The mysql system schema itself needs backups and least-privilege: GRANT PROXY and tablespace encryption keys in mysql.user dumps are credentials. Restoring mysql.user onto another host with different TLS material breaks logins. Partial dumps that skip mysql leave GRANTs behind. Encrypt dump files; they contain hashes and DEFINER clauses that are production secrets.

MySQL accounts are user@host pairs in mysql.user; omitting the host defaults to '%' which matches any client host. app@localhost and app@10.0.0.% are different rows with different plugins and privileges. People GRANT to app and wonder why 10.0.0.8 still fails. SHOW GRANTS FOR CURRENT_USER() is the session truth, not the table you think you edited.

Roles (CREATE ROLE, GRANT role TO user, SET ROLE) group privileges so you stop copy-pasting GRANT ALL on every app account. activate_all_roles_on_login controls whether roles apply at connect. Mandatory roles can surprise audits. A role is not a login—mysql.user still needs the user@host that wears the role.

REQUIRE SSL on CREATE USER plus require_secure_transport=ON reject plaintext clients even if they know the password. Replica SOURCE_SSL_CA pins the source certificate so a MITM cannot inject ROW events. Self-signed server certs still encrypt; VERIFY_IDENTITY needs a proper CN. PERFORMANCE_SCHEMA status_by_account shows Ssl_cipher per thread.

Partial revokes (8.0.16+) let you GRANT on *.* then REVOKE a schema, which is how you give a DBA almost-global rights without mysql.* . Without them, GRANT ALL ON *.* is all-or-nothing. Enabling the feature is a one-way metadata change—read the docs before flipping it on a replica fleet. Combine with roles so the revoke set is named.

Password validation component (validate_password) plus password_history stop reuse; password_require_current blocks SET PASSWORD without the old one. It does not rotate leaked hashes in mysql.user by itself. caching_sha2_password still needs TLS or RSA. Combine with FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME on CREATE USER (8.0.19+).

Connection-control plugin and max_connect_errors slow brute-force on mysql.user; firewall or admin_port isolation is better. FAILED_LOGIN_ATTEMPTS locks a human account, not a connection-pooled app user sharing one account. Separate the app user@subnet from DBA user@bastion. PERFORMANCE_SCHEMA host_cache shows the blocked hosts.

Dynamic privileges (CLONE_ADMIN, BINLOG_ADMIN, AUTHENTICATION_POLICY_ADMIN) should be treated like SUPER fragments. CLONE_ADMIN can copy the instance; BINLOG_ADMIN can purge logs a replica still needs. Grant them to named DBA roles, not to the ORM user. SHOW PRIVILEGES and mysql.global_grants list the current set after upgrades.

A user with PROCESS can SHOW PROCESSLIST and read other sessions' SQL; combine with SELECT on PERFORMANCE_SCHEMA.events_statements_history and you leak literals. Older MySQL showed unhashed passwords in processlist for SET PASSWORD. Restrict PROCESS to DBAs; apps should not diagnose neighbors. sys.session is the same data with prettier views.

Row-based binlog of GRANT is still a statement; leaking SUPER on a replica admin account lets someone GRANT themselves on the source after a promotion. replication filters that skip mysql.* skip GRANTs and desynchronize privileges. Treat mysql.user as production data: backup, replicate, and restrict. clone/XtraBackup copies privilege tables too.

Masking via views is defeated if the analyst has SELECT on the base table; lock that table down and GRANT only on the view. SQL SECURITY DEFINER on the view then becomes the controlled window. WITH CASCADED CHECK OPTION does not hide columns. Column encryption at rest (tablespace ENCRYPTION=Y) still decrypts for any SELECT that is allowed.