SQL REQUIRE

What is the SQL REQUIRE clause in MySQL?

Clause in MySQL GRANT/CREATE USER statements that forces connecting clients to satisfy specific SSL or X.509 conditions.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL REQUIRE: Supported: MySQL 5.0+, MariaDB 5.2+ Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Snowflake

SQL REQUIRE Full Explanation

REQUIRE is a security-focused clause that appears inside GRANT and CREATE USER statements in MySQL and MariaDB. It lets administrators mandate that any session established by the account use an encrypted connection (SSL) and, optionally, present an X.509 certificate with matching attributes such as SUBJECT, ISSUER, or CIPHER. If the client fails to meet the declared requirement, the server rejects the connection during the TLS handshake. REQUIRE is evaluated at connection time only and does not retroactively affect already-established sessions. Because REQUIRE is non-standard, it is unavailable in PostgreSQL, SQL Server, Oracle, or SQLite. Instead, those systems rely on server-level SSL enforcement or parameter-based authentication.

SQL REQUIRE Syntax

GRANT priv_type ON db.table TO 'user'@'host'
    REQUIRE { NONE | SSL | X509 | 
              SUBJECT 'subject_pattern' | 
              ISSUER  'issuer_pattern'  | 
              CIPHER  'cipher_name' };

CREATE USER 'user'@'host'
    REQUIRE { NONE | SSL | X509 | 
              SUBJECT 'subject_pattern' | 
              ISSUER  'issuer_pattern'  | 
              CIPHER  'cipher_name' };

SQL REQUIRE Parameters

SSL (keyword) - Connection must be encrypted with TLS but no certificate attributes are checked.
X509 (keyword) - Connection must use TLS with a valid client certificate signed by any trusted CA.
SUBJECT 'pattern' (string) - Permitted X.509 Subject Distinguished Name (supports wildcards).
ISSUER 'pattern' (string) - Permitted X.509 Issuer Distinguished Name (supports wildcards).
CIPHER 'cipher_name' (string) - Exact name of the allowed TLS cipher.
NONE (keyword) - Removes previously set REQUIRE options for the account.

Example Queries Using SQL REQUIRE

-- Require any TLS connection
GRANT SELECT ON sales.* TO 'analyst'@'%' REQUIRE SSL;

-- Require valid client cert signed by any CA
CREATE USER 'reporter'@'10.%' IDENTIFIED BY 's3cret' REQUIRE X509;

-- Pin both subject and issuer patterns
GRANT ALL ON prod.* TO 'svc_app'@'192.168.%' IDENTIFIED BY 'pwd'
    REQUIRE SUBJECT '/CN=svc_app/*' ISSUER '/C=US/O=Corp/OU=CA';

-- Remove all SSL requirements
GRANT USAGE ON *.* TO 'legacy'@'localhost' REQUIRE NONE;

Expected Output Using SQL REQUIRE

The specified account is created or altered
Future connection attempts must negotiate TLS and present certificates matching the stated REQUIRE options, otherwise ERROR 1045 (28000): Access denied is returned

Use Cases with SQL REQUIRE

Enforce encrypted client connections to protect data in transit
Meet compliance needs (PCI, HIPAA) by requiring X.509 authentication
Restrict service accounts to a known certificate subject/issuer pair
Phase out legacy, unencrypted logins without changing application code

Common Mistakes with SQL REQUIRE

Assuming REQUIRE works in non-MySQL databases
Forgetting quotes around SUBJECT, ISSUER, or CIPHER patterns
Combining NONE with other REQUIRE options
Believing REQUIRE applies to already active sessions

First Introduced In

MySQL 4.0.3

Frequently Asked Questions

What happens if the client certificate does not match the REQUIRE SUBJECT or ISSUER?

The server terminates the TLS handshake and returns ERROR 1045 (28000): Access denied for user because the certificate attributes do not satisfy the REQUIRE constraints.

Can I combine multiple REQUIRE options?

Yes. For example, REQUIRE SUBJECT 'subj' ISSUER 'issuer' forces the client to present a cert whose subject and issuer both match. If any option fails, the connection is rejected.

Does REQUIRE impact performance?

There is minimal overhead beyond the standard TLS handshake, which is typically negligible compared to query execution time.

How do I verify an account's REQUIRE settings?

Query the mysql.user table columns ssl_type, x509_issuer, x509_subject, and ssl_cipher or use SHOW CREATE USER 'user'@'host';