MySQL Password Security Changes for PHP Developers

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 Complexity

5.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 means use the server default value. The lifetime is measured in days and stored in the password_last_changed column of the nysql.users table. If the password is expired, you are put into sandbox mode where the only command you can execute is to change the password. That works great for interactive users. But what about your application? It uses a username password pair to talk to the database but it is very unlikely that anyone planned on changing passwords upon expiration. I seriously doubt anyone has set up the exception routine to handle an expired password properly. And if so, how do you notify all involved about this new password --- securely.

What to do

The best thing would be to set the default password lifetime for accounts used by applications to zero. It simply does not expire. QED & out.

But what if your company wants ALL password changed on a regular basis? And they do mean ALL. Earlier there was a listing of the defaults. The test system are set to a password length of eight characters minimum, requires mixed case, requires at least one upper case letter, one special (nonalphanumeric) character, and is of MEDIUM complexity.

MEDIUM complexity means that passwords need one numeric, one lower case, one upper case, and one special character. LOW tests the password length only. And STRONG adds a condition that sub strings of the length of four characters or long do not match entries in a specified password file (use to make sure swear words, common names, etcetera are not part of a password).

Lets create a dummy account.

CREATE USER 'foobar'@'Localhost' IDENTIFIED BY 'Foo@Localhost1' PASSWORD EXPIRE;

Checking the entry in the user table, you will find that the account's password is expired. For extra credit notice what the authentication string is set to. We can't have just a password string as some authentication tokens or hashes are not really password.

So login as foobar and you will get a notice that the password must be reset before we can do anything else.

ALTER USER 'foobar'@'localhost' IDENTIFIED By '1NewP@assword';

Corporate Standard

Your corporate rules may require you to rotate password every N days and set the corresponding complexity. With MySQL 5.7 you can follow what their model is. If you do not have a standard and want to create one, be sure to DOCUMENT well what your standard is and make sure that standard is well known.

There are ways to use packages like PAM or LDAP for authentication but that is for another day.

Generated MySQL Columns and Changing Values

I was speaking at PHP[Tek] this week on the JSON Data Type and using generated columns. JSON columns can not be indexed but data from a JSON column can be extracted via a generated column and that column can be indexed. All was going well until someone asked me about modifying data in a generated column. Was it possible?

I blinked hard. I have not tried that! I had not seen any mention of that in the documentation. So I had to admit that I did not know and would have to try that.

The Test

mysql> CREATE TABLE gentest (a INT, b INT AS (a + 1) STORED, INDEX(b));
...
mysql> INSERT INTO gentest VALUES (1),(2),(3),(4);
...

So now we have a table with data to test. So lets try to modify the value of one of the generated columns.

mysql> UPDATE gentest SET b = 9 WHERE a = 1;

And what did the server do?

It returned an error and told me The value specified for generated column 'b' in table 'gentest' is not allowed.

Lesson Learned

So now I know that the server will not allow you to mangle, er, change the value of generated columns away from their definition. And yes, I find the same thing with both the VIRTUAL and STORED versions of generated columns.

Oracle Linux 7, MySQL 5.7.12 and YUM for JSON Document Store Part 1

Okay, I will admit that I grew up professionally without package management. Well, we did have SSCS and RCS under Unix and a lot of very brilliant shells scripts but nothing modern programmers would deign to touch. Then came Linux Torvalds (and why hasn't he been given a Nobel prize?!?!) and I blundered into the Redhat world with their RPM package manager. RPMS are great but over time I was seduced by Ubuntu and became and became an apt-get fiend.

Now I need to do a demo of the new MySQL 5.7.12 Document store features on an RPM based distro, Oracle 7.1. I could have used a Vagrant or Docker image but ended up using VirtualBox. You can even use a fresh install on hardware even though that seems to be becoming passe in a virtual & containerized world. So if you are following along please adjust to your platform.

After installing the latest and greatest Virtualbox from VirtualBox.org, I pulled the ISO image from Oracle. Then I set up a virtual machine using that ISO. And now ends the hard part.

The next step is to get the MySQL YUM repository configured on the system. And this is done by downloading a RPM with the needed info inside. The instructions show you how to use the rpm program to load this download. Then fire up your text editor of choice to pry open /etc/yum.repos.d/mysql-community.repo file. Make sure the enabled lines under the [mysql57-community] is set to 1 and that the other [mysql5X-community] have enabled lines set to zero. We can only use the new Docstore feature start with 5.7.12, so the earlier versions will not do.

Now set the enabled line to 1 under [mysql-tools-preview]. This loads the docstore software.

Now sudo yum install mysql-community-server mysql-shell to get all the software loaded. Then sudo service mysqld start will bring up the server. Please note the secure root password is in your log file and you will have to look in there in order to login the first time with a quick grep 'temporary password' /var/log/mysqld.log. Then login to the server and change the ugly generated password with your new password ALTER USER 'root'@'localhost' IDENTIFIED BY 'LessUglySecret1!';

So the server is set up as a regular relational server but we need to get the document store features setup. Simply type mysqlsh -u root -p --classic --dba enableXProtocol. Then it is a simple mysqlsh -u root -p --sql to have a SQL session on the server.

Next time: More document store explorations

What happens when you create a MySQL Document Store

The MySQL Document Store introduced with version 5.7.12 allows developers to create document collections without have to know Structured Query Language. The new feature also comes with a new set of terminology. So let us create a collection and see what it in it (basically creating a table for us SQL speakin' old timers).

So start the mysqlsh program, connect to the server, change to the world-x schema (database) switch to Python mode, a create a collection (table).

What did the server do for us? Switching to SQL mode, we can use describe to see what the server has done for us.

We have a two column table. The first is named doc and is used to store JSON. And there is also a column named _id and please notice this column is notated as STORED GENERATED.

The generated column extracts values from a JSON document and materializes that information into a new column that then can be indexed. But what did the system extract for us to create this new column?

Lets use SHOW CREATE TABLE to find out.

mysql-sql> SHOW CREATE TABLE foobar;
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
| Table  | Create Table

                                                                      |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
| foobar | CREATE TABLE `foobar` (
  `doc` json DEFAULT NULL,
  `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'
))) STORED NOT NULL,
  UNIQUE KEY `_id` (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql-sql>

So the 5.7.12 document store is creating an index for us on a field named _id in our JSON document. Hmm, what if I do not have an _id field in my data. So I added two records ("Name" : "Dave" and "Name" : "Jack") into my new collection and then took a peek.

mysql> select * from foobar;
+-------------------------------------------------------------+-----------------
-----------------+
| doc                                                         | _id
                 |
+-------------------------------------------------------------+-----------------
-----------------+
| {"_id": "819a19383d9fd111901100059a3c7a00", "Name": "Dave"} | 819a19383d9fd111
901100059a3c7a00 |
| {"_id": "d639274c3d9fd111901100059a3c7a00", "Name": "Jack"} | d639274c3d9fd111
901100059a3c7a00 |
+-------------------------------------------------------------+-----------------
-----------------+
2 rows in set (0.00 sec)

mysql>

But what if i do have a _id of my own?

The system picked up the _id for the Dexter record. Remember that the index on the _id field is marked UNIQUE which means you can not reuse that number.

So we know the document store wants is creating an unique identification number (that we can also use).

Update: The client generates the identification number, the server can not due to possible conflicts in future sharding projects.

The new MySQL X DevAPI Protocol and what it means for PHP

The relational database world just changed and you didn't notice, did you? Rather than speaking SQL to your data, what if you could talk to it in PHP directly? No longer do you have to struggle to remember arcane structured query language syntax and just grab data with PHP. You can still write bad queries full of N+1 errors and a whole host of other problems. But you are speaking PHP to your data. And this started with MySQL 5.7.12 and its new Document Store Functionality.

The Document Store functionality expands on the new JSON data type in MySQL 5.7 with a new server plugin, a new API, and a suite of new components designed to make MySQL accessible for users who are not familiar with the SQL language or prefer to use a schemaless data store. This MySQL Server plugin enables communication using the X Protocol. And clients that implement X DevAPI and enables using MySQL as a document store easily. So you can talk to your data in the language of your choice (say Python, Javascript, or SQL) via a new shell or use a new connector to use these features. For those in the PHP world I must warn you that the new connector to support the new X DevAPI is on the way -- not here yet but on the way.

Trying the XDevAPI

You will need MySQL 5.7.12 with the X plugin enabled and the new mysqlsh. Details on the installation of the shell and plugin can be found under Using MySQL as a Document Store in the MySQL 5.7 Reference Manual. Fire up the shell and you will see something like this:

That mysql-js> prompt lets you know that your shell is currently in Javascript more. Currently the shell has modes for Javascript, Python, or SQL. To connect to a database simply type

db = session.getSchema('world_x')

It is then easy to use Javascript to create, remove, update or delete data.

The X Plugin extends MySQL Server to be able to function as a document store and the X Protocol, which is designed to expose the ACID compliant storage abilities of MySQL as a document store. Documents are stored in JSON format and enable schema-less storage. Using the X DevAPI you can use a NoSQL-like syntax to execute Create, Read, Update, Delete (CRUD) operations against these documents. The server will be listening to port 33060 (configurable) for communications in the new protocol.

And any data you drop into the document store is avail from the document store in JSON format AND is accessible at the same time from SQL. And by using the new connectors (Java, .NET, Node.JS for now) that support the X protocol, your code hits the database without have to fidget and grumble about using SQL. You are talking in your language to a relational database using NoSQL.

And you data you add via the X protocol in Node.JS can be used by someone with Python, or Java, or SQL at the same time.

Digging Down into JSON data with the MySQL Functions -- A Question from Peter Zaitsev -- Follow Up

Last time this blog covered digging into a JSON document in a MySQL 5.7 table. The goal was to pull certain records matching a particular criteria. Both Peter Zaitsev and Morgan Tocker get my thanks for their kind comments. My example was a little contrived in that an application would be used to fine tune seeking for a particular key value pair. I was trying to pull single records which is kind of silly when it is much easier to use PHP to parse the data. What follows below is a sample PHP script to grab out the matching records and then feed the results, the JSON document, into an array.

#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$foo = array();
$query0 =
"SELECT* FROM restaurant WHERE json_contains(data, '{\"grade\": \"A\"}', '$.grades')";
echo "$query0\n";
if ($result = $mysqli->query($query0)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n\n", $row[0]);
    $foo =  json_decode($row[0]);
        var_dump($foo);
} else {
        printf("Errormessage: %s\n", $mysqli->error);

}


$mysqli->close();

In this case we get the data into an array and the the processing is limited to a var_dump().

Digging Down into JSON data with the MySQL Functions -- A Question from Peter Zaitsev

How do you dig down into the JSON data, say like in comments on a blog post? This is a badly paraphrased question from Peter Zaitsev of Percona at the end of a recent presentation of mine at the Great Wide Open conference. I have to admit I had not done like that with JSON data but it had to be able to be done, right? Surely it can be done. Can't it? Time to dig. I could not find an example of JSON data on a blog with comments. But the good folks at Mongo had an example of a restaurant with grades.

{
  "address": {
     "building": "1007",
     "coord": [ -73.856077, 40.848447 ],
     "street": "Morris Park Ave",
     "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
     { "date": { "$date": 1393804800000 }, "grade": "A", "score": 2 },
     { "date": { "$date": 1378857600000 }, "grade": "A", "score": 6 },
     { "date": { "$date": 1358985600000 }, "grade": "A", "score": 10 },
     { "date": { "$date": 1322006400000 }, "grade": "A", "score": 9 },
     { "date": { "$date": 1299715200000 }, "grade": "B", "score": 14 }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
}

So how do we get the grade information and just the grades (A,A,A,A, and B) from grades?

I created a quick little table named restaurant with a single column named data for this test and then added the above document to the table. Yeah, real creative on the table and columns names there. Let's look at the fields, or keys.

mysql> select json_keys(data) from restaurant;
+----------------------------------------------------------------------+
| json_keys(data)                                                      |
+----------------------------------------------------------------------+
| ["name", "grades", "address", "borough", "cuisine", "restaurant_id"] |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

So can we see the path to the grades key? Yes, but not the date, grade, or score keys from under the grades tag. Note that in the case the 'one' or 'all' second argument returns the same result.

mysql> select json_contains_path(data,'one','$.grades') from restaurant;
+-------------------------------------------+
| json_contains_path(data,'one','$.grades') |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

Grab the grades.

mysql> select json_extract(data,'$.grades') from restaurant\G
*************************** 1. row ***************************
json_extract(data,'$.grades'): [{"date": {"$date": 1393804800000}, "grade": "A",
 "score": 2}, {"date": {"$date": 1378857600000}, "grade": "A", "score": 6}, {"da
te": {"$date": 1358985600000}, "grade": "A", "score": 10}, {"date": {"$date": 13
22006400000}, "grade": "A", "score": 9}, {"date": {"$date": 1299715200000}, "gra
de": "B", "score": 14}]
1 row in set (0.00 sec)

This is all the info from the grades tag. Now to whittle this down to just the individual grade entries. We can get the individual grades as $.grades.[N] such as:

mysql> select json_extract(data, '$.grades[1]') from restaurant\G
*************************** 1. row ***************************
json_extract(data, '$.grades[1]'): {"date": {"$date": 1378857600000}, "grade": "
A", "score": 6}
1 row in set (0.00 sec)

mysql> select json_extract(data, '$.grades[4]') from restaurant\G
*************************** 1. row ***************************
json_extract(data, '$.grades[4]'): {"date": {"$date": 1299715200000}, "grade": "
B", "score": 14}
1 row in set (0.00 sec)

Much closer!

And then the individual grade from the 4th entry:

mysql> select json_extract(json_extract(data, '$.grades[4][0]'),'$.grade') from
restaurant;
+--------------------------------------------------------------+
| json_extract(json_extract(data, '$.grades[4][0]'),'$.grade') |
+--------------------------------------------------------------+
| "B"                                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

I need to go read up on JSON pathing and experiment so I can grab all the grades at once but right now I fell like I am halfway done with the 'homework' Peter assigned.