• 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. How To Install MySQL RPM packages in a different location to allow multiple versions (versions < 5.6.10)
  2. How to gather information on the MySQL 8 Roles and Privileges assigned to a user without using SHOW GRANTS
  3. MySQL: How To Find Queries Taking Longer Than N Seconds
  4. Understanding the REVOKE statement in MySQL
  5. MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
  6. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  7. How To Shut Down a Node in MySQL Cluster
  8. How To Purge Audit Logs in MySQL
  9. How to List and Set SELinux Context for MySQL Server
  10. MySQL: how to figure out which session holds which table level or global read locks

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright