This page needs a newer browser to run its tools. You can still read everything here.

Security and access control interview questions

Interview questions on security and access control: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

8 questions: 3 warm-ups and 5 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

mysql_native_password and caching_sha2_password both check a password. How do they differ, and why does an upgrade past MySQL 8.4 care?

Answered in the lesson MySQL accounts, authentication and TLS
Mid

The mysql client's --ssl-mode has five values. What does each one check, and which stops a client from talking to an impostor server?

Answered in the lesson MySQL accounts, authentication and TLS
Fresher to mid

What makes a privilege dynamic rather than static?

Answered in the lesson Privileges, roles and least privilege in MySQL

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

An online store runs 40 application accounts on MySQL 8.4 with mysql_native_password=ON in my.cnf, and the team plans to move to 9.7 LTS. How do you find, migrate and verify those accounts so that no application loses its database connection, and in what order do the server changes happen?
What it tests

Knowing that 9.x has no server-side native plugin, tying each account to the application behind it, checking client libraries, ordering the moves on 8.4, and proving nothing needs the old plugin before the upgrade.

First step

Start on 8.4, not on 9.7. List the accounts on the old plugin from mysql.user and tie each one to the application and the client library that use it. The upgrade itself cannot rescue any of them: 9.7 has no server-side native plugin and will not even start with the option, so every account must already be on caching_sha2_password when the new version first starts.
Follow-ups
- How do you tell which application still logs in with the old plugin when the deprecation warning names no account?
- Why can you not convert the stored hashes, and how do you avoid changing every application's settings?
- What breaks after the move for a client that connects over TCP without TLS, and when does it show?
- How do you prove that nothing depends on mysql_native_password before 9.7 starts?
From the lesson MySQL accounts, authentication and TLS
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 6 minutes
Mid to staff

A payments service reaches its MySQL 8.4 primary across a data-centre network, and every client runs with the default connection settings. Security now asks that every connection be encrypted and that clients prove they reached the real server. Plan the change on the server, the accounts and the clients, and say how you confirm it worked.
What it tests

Telling encryption apart from server verification, issuing certificates whose names match how clients connect, ordering client, account and server changes so nothing breaks, and checking from the server which sessions use TLS.

First step

Find out how every client names the server (DNS names, addresses, a virtual IP) and issue a server certificate from an authority you control whose subjectAltName lists all of them. Until clients can verify that certificate, rules on the server could only force encryption, not prove which server answered.
Follow-ups
- Why does the default client setting protect against eavesdropping but not against a fake server?
- In what order do you change the clients, the accounts and the server, and why?
- How do you see from the server which connections still arrive without TLS?
- How do you replace the server certificate later without restarting the server?
From the lesson MySQL accounts, authentication and TLS
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Mid to senior

A new team of five developers needs database accounts on a MySQL 8.4 server that holds application rows, a reporting replica of them and a payroll schema. Design the privilege set each account carries, the roles that group the privileges, and the one line that keeps payroll out of the operators' global SELECT.
What it tests

Picking the account shape per job, grouping privileges into roles, keeping SYSTEM_USER on DBAs, and knowing where a partial revoke replaces a one-off exclusion.

First step

Draw the matrix before writing any GRANT: list the job kinds down the side (application, migrator, reporting, backup, operator) and the schemas across the top. Decide at each cell whether the account reads, writes, both or nothing, so a role is a column of that matrix and SHOW GRANTS only has to confirm what the design already says. The replication account stays outside it: it reads the binary log, not rows.
Follow-ups
- How do you keep the operator accounts out of mysql and out of payroll with one setting?
- Which accounts hold SYSTEM_USER, and why not every DBA?
- How do you make a role active at every login, without relying on activate_all_roles_on_login?
- What does the replication account need, and nothing else?
From the lesson Privileges, roles and least privilege in MySQL
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Senior to staff

A compliance review asks that no application account read payroll schemas, even though a handful of accounts inherited a global SELECT from a long-gone migration. You cannot drop and recreate every account during the week of the review. Plan the roll-out of partial revokes across one source and six replicas, and the proof you can hand to the auditor.
What it tests

Enabling partial_revokes safely, writing the revokes once on the source and letting replication copy them, checking on every replica that each restriction landed, and showing the auditor a before-and-after.

First step

Start with the audit, not the fix. Run the lesson's over-broad check against every replica (every account with a global SELECT and no payroll restriction), save the list and the row count, and agree with the auditor that reducing the list to zero is the proof.
Follow-ups
- What has to be true on every replica before you can run the first revoke on the source?
- How do the revokes travel to the replicas, and what breaks if a replica is on an older release?
- Which rows of mysql.user do you read on each replica to prove the restriction landed?
- How do you roll one revoke back on one account without touching the others?
From the lesson Privileges, roles and least privilege in MySQL
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Mid to senior

During an incident, an operator asks you which accounts can read salaries. The server has twenty accounts, a handful of roles, a few partial revokes and three definer views on payroll.salaries. Write the audit that answers the question completely, with the queries you run and the way you read them.
What it tests

Covering the four access paths (own grants, active roles, definer views, administrators) with the seven queries, reading each row, and knowing which query is evidence for which path.

First step

Settle the question "what can read salaries" before you look at any account. A row is reached through the account's own grants, an active role's grants, a definer view it may select from, or an administrator's global privileges: four paths, and the audit must cover all four. Run the seven queries of audit_grants.sql in order and keep the outputs beside you.
Follow-ups
- Which query answers 'the account has SELECT on the base table through a role'?
- How do you tell apart an account with SELECT ON payroll.* and one with a global SELECT and a payroll restriction?
- Which rows prove that a definer view exposes aggregates and nothing more?
- What does mysql.default_roles add that mysql.role_edges does not?
From the lesson Privileges, roles and least privilege in MySQL
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 7 minutes

All MySQL & MariaDB in Depth interview questions · The MySQL & MariaDB in Depth track

Warm-up questions

mysql_native_password and caching_sha2_password both check a password. How do they differ, and why does an upgrade past MySQL 8.4 care?

The mysql client's --ssl-mode has five values. What does each one check, and which stops a client from talking to an impostor server?

What makes a privilege dynamic rather than static?

Questions with model answers

An online store runs 40 application accounts on MySQL 8.4 with mysql_native_password=ON in my.cnf, and the team plans to move to 9.7 LTS. How do you find, migrate and verify those accounts so that no application loses its database connection, and in what order do the server changes happen?

During an incident, an operator asks you which accounts can read salaries. The server has twenty accounts, a handful of roles, a few partial revokes and three definer views on payroll.salaries. Write the audit that answers the question completely, with the queries you run and the way you read them.