The ability to write and run your own scripts for information that you want on a regular basis is very useful. The idea behind this was a discussion with a community member who had issues with a locked account that unknowingly locked and knowing when passwords where changed. This is a typical bit of work for a DBA that would be better automated and saved as a script for future uses.
The Query
The query collects several vital facets of information for dealing with passwords and accounts.
SELECT concat(User, '@',Host) as User,
password_expired as Expired,
password_last_changed as Changed,
password_lifetime as Lifetime,
account_locked as Locked,
User_attributes FROM mysql.user
Nothing too exotic as far as a query goes.
The Code
Most of the Python code deals with registering the registering the passwordData function that houses the query as report named userList. The query itself is in purple below and the registration 'boilerplate is in green. The shell.register_report is what ties the new function to the extension called userList.
# Define a passwordData function that generates a MySQL Shell report
def passwordData(session):
query = "SELECT concat(User, '@',Host) as User, password_expired as Expired, password_last_changed as Changed, password_lifetime as Lifetime, account_locked as Locked, User_attributes FROM mysql.user"
result = session.sql(query).execute();
report = []
if (result.has_data()):
report = [result.get_column_names()]
for row in result.fetch_all():
report.append(list(row))
return {"report": report}
# Register the userList function as a MySQL Shell report
shell.register_report("userList", "list", passwordData, {"brief":"Lists the user password status on the target server."})
# Check if global object 'ext' has already been registered
if 'ext' in globals():
global_obj = ext
else:
# Otherwise register new global object named 'ext'
global_obj = shell.create_extension_object()
shell.register_global("ext", global_obj,
{"brief":"MySQL Shell extension plugins."})
# Add the 'userList' extension object as a member of the 'ext' global object
try:
plugin_obj = global_obj.process
except IndexError:
The Report
The output is pretty much as expected and could use some careful tailoring. It would be nice to add information on how many days until a password expires and parsing the User_attributes field for key/values in a pretty fashion. But in a few minutes work I had a quick little utility that will save me time in the future.
JS > \show userList
+----------------------------+---------+---------------------+----------+--------+----------------------------------------------------------------------------------+
| User | Expired | Changed | Lifetime | Locked | User_attributes |
+----------------------------+---------+---------------------+----------+--------+----------------------------------------------------------------------------------+
| demo@localhost | N | 2020-04-09 10:42:06 | NULL | N | NULL |
| foobar@localhost | N | 2020-04-06 09:38:50 | NULL | N | {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 2}} |
| mysql.infoschema@localhost | N | 2020-01-13 11:57:33 | NULL | Y | NULL |
| mysql.session@localhost | N | 2020-01-13 11:57:33 | NULL | Y | NULL |
| mysql.sys@localhost | N | 2020-06-12 10:20:33 | NULL | Y | NULL |
| root@localhost | N | 2020-01-13 11:57:37 | NULL | N | NULL |
+----------------------------+---------+---------------------+----------+--------+-----------------------------------------------------------------------------
You Try
I recommended grabbing LeFred's Github repo and reading through his code. You can create your own directory under your ~/.mysqlsh/plugins/ext/ directory and using the above code saved as init.py as an exercise.
I would love to see what similar scripts or reports others are creating so please share!
