My DBA told me that an account I use to talk to my MySQL database instance has TWO passwords! How does that happen? Do I have to provide both passwords every time?
A Confused User Who Does Not Want to Type Two Passwords All The Time
Dear Confused User,
Dual Password Support was added in MySQL 8.0.14 and it is a very handy thing. User accounts can now have a primary password and a secondary password. Image a scenario where you are rotating passwords as directed by your company policy but it takes a while to change the credentials in all your code despite your best EMACS magic. The ability to keep the servers up and active as your do your security due diligence is very helpful.
9 Older Entries »
You don’t need this tutorial if you have access to the
root user or another one with
The following instructions works for MySQL 5.7. You will need to
stop the MySQL server and start it with
with the option
sudo service mysql stop sudo mysqld_safe --skip-grant-tables & mysql -u root mysql
If you get an error on start, chances are there is no folder
created for the
mysqld_safe executable to run, on my
tests I was able to solve by doing:
sudo mkdir /var/run/mysqld sudo chown -R mysql:mysql /var/run/mysqld
And then trying to start the
After this, the MySQL console will pop up, and you need to set up
a new password for
root. The second line is
necessary due to a MySQL bug # …
One of Oracle's tenets is the focus on security. For this reason, when it took over the stewardship of MySQL, it started addressing the most common issues. It was not quick acting, but we have seen real progress:
- MySQL 5.7 has removed the anonymous accounts, which was the greatest threat to security. Because of those accounts, and the default privileges granted to them, users without any privileges could access the "test" database and do serious damage. Additionally, because of the way the privilege engine evaluates accounts, anonymous users could hijack legitimate users, by preventing them to work properly.
- The "root" account now comes with a password defined during …
Have you ever forgotten your WordPress administrator password and did not have access to your e-mail account or haven’t configured your e-mail account properly in WordPress? Fear not, because in today’s tutorial we are going to learn how to reset the WordPress administrator password through the MySQL command prompt. Resetting WordPress Admin Password via MySQL Command […]
What is the solution if I don’t want to give password in command line (i.e mysql -uroot -p ) OR don’t want to store password in files(in .my.cnf) and still can login into MySQL/MariaDB without password ? I was also bit curious to know but finally I found very easy solution called “unix_socket plugin” provided by MariaDB.
This plugin allows to use operating system user credentials while connecting to MariaDB via Unix socket. When we try to connect with OS user, it will retrieve uid of the process which has connected to the socket and allow it to connect to MariaDB with the same user.
You can simply install that plugin with command,
MariaDB [(none)]> INSTALL PLUGIN unix_socket SONAME 'auth_socket'; Query OK, 0 rows affected (0.02 sec)
After, then you need to identify the user which you want to use to login into MariaDB. Like for me,
MySQL 5.7 introduced many new facets to password security. The first thing most notice is that you are assigned a random root password at installation time. You then have to search the log file for this random password, use it to login, and then change it. For the examples on the post I am using a fresh install of 5.7.13 on Oracle Linux 7.1 and was provided with the easy to remember password of nLvQRk7wq-NY which to me looked like I forgot to hit escape when trying to get out of vim. A quick ALTER USER to change the password and you are on your way. Defaults Password Lifetime and Complexity5.7.13 now has the default password lifetime set to 0 or 'never expire'. My fresh install shows that the value of mysql.user.password_lifetime is set to NULL which …[Read more]
I was surprised to find on one of my websites the message “Connect failed: Your password has expired. To log in you must change it using a client that supports expired passwords.
Not knowing that I was using a MySQL password expiry policy I reviewed the 5.7 documentation quickly which *clearly* states “The default default_password_lifetime value is 0, which disables automatic password expiration.”.
I then proceeded to investigate further, my steps are below the following comment.
However, it is always important with MySQL documentation and a new feature (in this case a 5.7 feature) to review release notes when installing versions or to least read ALL the documentation, because you may miss important information, such as.
Complimenting the expanded
CREATE USER syntax introduced in
MySQL Server 5.7.6 is more useful
syntax. Before MySQL Server 5.7.6,
could only be used to expire a user’s password. That’s pretty
limited. With changes made in MySQL Server 5.7.6, a better
distinction is made between privilege-level attributes (those
which are managed via
statements) and account-level attributes (those managed using
CREATE USER and
statements). MySQL has a long history of confusing these –
for example, requiring a
MySQL has provided support for proxy users since version 5.5, but the roles-like capabilities offered have been largely unnoticed until recently. Part of that has been due to limitations on which types of accounts could leverage proxy user capabilities. This changes with the release of MySQL Server 5.7.7 (Release Candidate), which includes support for proxy user mapping for the standard mysql_native_password and sha256_password authentication plugins. This post will introduce the new functionality and explain how to leverage it to emulate certain features …[Read more]
Mermaids have the same probability of fixing
your permission problems, but people continue believing in the
FLUSH PRIVILEGES myth.I see suggesting the usage of
PRIVILEGES every time someone writes a tutorial or a
solution to a problem regarding creating a new account or
providing different privileges. For example, the top post on
as of the writing of these lines, “MySQL:The user specified as a definer does not
exist (error 1449)-Solutions” has multiple guilty cases of
this (Update: the user has corrected those lines
after I posted this article).
It is not my intention to bash that post, but I have seen …[Read more]
9 Older Entries »