• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Restrict MySQL User Creation with Blank Password

by admin

Question: How can we not allow user creation with a blank password in MySQL?

Using a blank password for a user is always a no go. In order to restrict user creation with blank password follow the steps given below:

Before MySQL 5.6

In case of MySQL version below 5.6, this can not be achieved completely. But there are settings that can be used to come closer to our objective.

Set the below SQL mode on the MySQL instance:

SET SQL_MODE = NO_AUTO_CREATE_USER

Using NO_AUTO_CREATE_USER mode it is expected that – the statement must specify a NONEMPTY password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH.

Because, when the clause IDENTIFIED BY is specified, it is a clear indication that one purposely wants to create a new user. We cannot prevent admin accounts from creating users with a blank password. Hence if you want to setup a monitoring rule to identify such users periodically, enable the below MySQL Enterprise Monitor (MEM) version 2.3 Advisors that would alert you of such accounts and that tells you what to do next:

Advisor 1: Server Has Accounts Without A Password
Advisor 2: Server Has Anonymous Accounts

MySQL 5.6 and above

Starting from MySQL 5.6, this can be achieved using Password Validation Plugin’s ‘validate_password_policy‘ function. Use the below options to cover most cases:

plugin-load = validate_password.so
validate-password = FORCE_PLUS_PERMANENT
validate-password-policy = X
sql_mode = NO_AUTO_CREATE_USER

Filed Under: mysql, MySQL Cluster

Some more articles you might also be interested in …

  1. CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
  2. How to Set Space limits for MySQL for database/schema/table
  3. MySQL: how to figure out which session holds which table level or global read locks
  4. How to Add New Nodes To an Existing MySQL Cluster Setup
  5. Beginners Guide to MySQL Data Types
  6. MySQL : Stored Procedure For Rotating the Audit Log
  7. Examples of mysqldump partial backups
  8. Multi-Versioning in MySQL Database
  9. MySQL – How to undo (rollback) a set of SQL statements
  10. How to find Which Process Is Killing mysqld With SIGKILL or SIGTERM on Linux

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright