Pam authentication plugin dialog.dll and case sensitive

I setup a MySQL Server to use the Pam authentication plugin as described in these two blog posts:

https://www.percona.com/doc/percona-server/LATEST/management/pam_plugin.html

https://www.percona.com/blog/2017/04/21/how-to-setup-and-troubleshoot-percona-pam-with-ldap-for-external-authentication/

I came across a couple issues.

1. The username for logging into MySQL with Active Directory credentials is case sensitive. I kept trying to login with lowercase and kept getting "ERROR 1045 (28000): Access denied for user...". After switching to uppercase it finally worked.

2. I wasn't able to login from my Windows system using a GUI tool like MySQL workbench. It would error when trying to logon mention "dialog". I downloaded and installed MariabDB onto my Windows System. I found the dialog.dll file and copied it.
Copied from here:
C:\Program Files\MariaDB 10.3\lib\plugin\dialog.dll
To this location:
C:\Program Files\MySQL\MySQL Server 5.7\lib\plugin\dialog.dll

How to grant view access to stored procedures

I have a client that uses a lot of triggers and stored procedures. Normally when I create a MySQL application user, that user has limited privileges such as SELECT, EXECUTE, SHOW VIEW, Insert, Update, Delete. When you login with this user and try to view the code inside of a stored procedure or a trigger, you will not be able to see it. It will show up as null. It is interesting that MySQL has a privilege for CREATE ROUTINE, ALTER ROUTINE but there isn't any "SHOW ROUTINE" privilege.

The work around to allow a user to view the code inside a stored procedure is to grant SELECT access to the mysql.proc table. Here is an example:

GRANT Select ON mysql.proc  TO 'MyUser'@'%';

If the user wants "Read Only" access but also needs to see triggers then I would grant the user these privileges:

GRANT Select, execute, SHOW VIEW, Trigger ON `MyDatabase`.* TO 'MyUser'@'%';

Be sure the user already exists and has a password before running these grant statements! On MySQL version before 5.7, running a grant statement like this without a password or password hash will auto create the user with a blank password.

Fixing definers for users that do not exist

Far too often, I get called to troubleshoot a problem because writes or some functionality is no longer working. When you manage thousands of database servers with thousands of databases all with different applications, you run into this problem now and again. When a developer or other DBA is terminated, their MySQL user will also get dropped on all our databases. Sometimes they have created views, events, stored procedures, functions, triggers and the definer for those objects became the user that created them by default. Unless you have monitoring step up to let you know when this happens or some kind of process to fix or prevent it, this can become very wide spread.

Fixing stored procedures, events and functions can be very easy because you can directly manipulate the values in the mysql.events table and mysql.proc table.


 UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='me@%';

 UPDATE `mysql`.`event` p SET definer = 'root@localhost' WHERE definer='me@%';

https://dev.mysql.com/doc/refman/5.6/en//stored-routines-privileges.html

The MySQL manual warns against this with this text:

"The server manipulates the mysql.proc table in response to statements that create, alter, or drop stored routines. It is not supported that the server will notice manual manipulation of this table."

I've tested this by creating a basic stored procedure and changing the definer:

------------------------------------------------------------------
GRANT ALL PRIVILEGES ON *.* TO 'me'@'%' IDENTIFIED BY 'testPASS!';

DROP TABLE t1;
CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS `WriteToTable`;

DELIMITER ;;
CREATE DEFINER=`me`@`%` PROCEDURE `WriteToTable`()
BEGIN
INSERT INTO `t1` (`id`, `timestamp`) VALUES (NULL, NULL);
END;;
DELIMITER ;

call WriteToTable();

DROP USER 'me'@'%';

SELECT * FROM `mysql`.`proc` p  WHERE  name = 'WriteToTable';

UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='me@%' AND name = 'WriteToTable';

call WriteToTable();

------------------------------------------------------------------

When I run this in the same session that was used to update the mysql.proc table, it fails with error because me@% does not exist anymore. However, if I close the session and log back in again then it works fine.

For updating triggers and views, there isn't an easy way to do it without dropping and re-creating and object.

Possible Mitigation for system performance after 'meltdown' bug patching

This is an interesting read on database performance (not specific to MySQL) after patching to secure data on multi tenant system.

https://blog.appoptics.com/visualizing-meltdown-aws/

Copied from the above article:

Applications that make frequent systems calls to read/write data either over network sockets or from disk systems will need to be better tuned for batching. Incurring small I/O operations is now more costly, and engineers will need to optimize their code to reduce the frequency of such calls. Finding the sweet spot between larger batch sizes and latency is difficult and will require software that adapts for multiple variables simultaneously. It was promising to see that the Kafka consumer libraries were able to optimize for this dynamically as network call latency increased.

Using triggers to audit database changes

I cannot count on people on my team to inform me of changes they are making to our databases so I've had to create some very basic monitoring which checks the status of certain system variables at a certain interval and saves this into a few tables. I've added triggers to these tables to audit changes and deletions.

This is an old but useful (and free) way of keeping historical information on changes in a MySQL databases. The triggers copy the old and the new value to a generic change log table (sometimes called audit log). In this manner multiple tables can use the same change log table.

In this example I have two tables, one called MyCluster and one called Tag. The Tag table uses key/value to store data while the MyCluster has specific attributes stored in columns for each cluster. Here is an example table structure for the two tables (actual table has many more columns):

CREATE TABLE `MyCluster` (
  `Profile` varchar(100) NOT NULL COMMENT 'Account where data came from',
  `DBClusterIdentifier` varchar(255) NOT NULL DEFAULT '',
  `Endpoint` varchar(255) DEFAULT NULL COMMENT 'Cluster Writer End Point Address',
  `EndpointIPAddress` varchar(50) DEFAULT NULL COMMENT 'Cluster Writer End Point IP Address',
  `ReaderEndpoint` varchar(255) DEFAULT NULL COMMENT 'Cluster Reader End Point Address',
  `ReaderEndpointIPAddress` varchar(50) DEFAULT NULL COMMENT 'Cluster Reader End Point IP Address',
  `ClusterCreateTime` timestamp NULL DEFAULT NULL COMMENT 'UTC time',
  `CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Profile`,`DBClusterIdentifier`),
  KEY `ix_Endpoint` (`Endpoint`),
  KEY `ix_EndpointIPAddress` (`EndpointIPAddress`),
  KEY `ix_ClusterCreateTime` (`ClusterCreateTime`),
  KEY `ix_ReaderEndpoint` (`ReaderEndpoint`),
  KEY `ix_ReaderEndpointIPAddress` (`ReaderEndpointIPAddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cluster Specific info';


CREATE TABLE `Tag` (
  `Profile` varchar(100) NOT NULL COMMENT 'Account where data came from',
  `Type` varchar(255) NOT NULL DEFAULT '',
  `Identifier` varchar(255) NOT NULL DEFAULT '',
  `Key` varchar(100) NOT NULL DEFAULT '',
  `Value` varchar(1000) DEFAULT NULL,
  `ResourceARN` varchar(255) DEFAULT NULL,
  `CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Profile`,`Type`,`Identifier`,`Key`),
  KEY `ix_Identifier` (`Identifier`),
  KEY `ix_Type` (`Type`),
  KEY `ix_Key` (`Key`),
  KEY `ix_Value` (`Value`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Instance Tag info.';

-- This is the audit table or log table:

CREATE TABLE `Audit` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Profile` varchar(100) DEFAULT NULL,
  `Identifier` varchar(100) NOT NULL DEFAULT '',
  `TableName` varchar(100) DEFAULT NULL,
  `FieldName` varchar(100) DEFAULT NULL,
  `OldValue` varchar(100) DEFAULT NULL,
  `NewValue` varchar(100) DEFAULT NULL,
  `Type` varchar(100) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data comes from triggers on other tables';

-- Here are the example triggers...

-- Example of saving information from a change to column called EndpointIPAddress:

DROP TRIGGER IF EXISTS MyCluster_BU;

DELIMITER $$
CREATE DEFINER=`root`@`%` TRIGGER MyCluster_BU BEFORE UPDATE ON MyCluster
  FOR EACH ROW BEGIN
    IF (OLD.EndpointIPAddress <> NEW.EndpointIPAddress AND OLD.EndpointIPAddress <> '' AND NEW.EndpointIPAddress <> '') THEN
        INSERT INTO Audit (
        `Profile`,
        `Identifier`,
        `TableName`,
        `FieldName`,
        `OldValue`,
        `NewValue`,
        `Type`,
        `timestamp`
        )
        VALUES (
        OLD.Profile,
        OLD.DBClusterIdentifier,
        'MyCluster',
        'EndpointIPAddress',
        OLD.EndpointIPAddress,
        NEW.EndpointIPAddress,
        'change',
        NOW()
        );
    END IF;

END$$
DELIMITER ;

-- Example of saving information after a delete occurs:

DROP TRIGGER IF EXISTS MyCluster_AD;

DELIMITER $$

CREATE DEFINER=`root`@`%` TRIGGER MyCluster_AD
AFTER DELETE
   ON MyCluster FOR EACH ROW

BEGIN

  INSERT INTO Audit (
  `Profile`,
  `Identifier`,
  `TableName`,
  `FieldName`,
  `OldValue`,
  `NewValue`,
  `Type`,
  `timestamp`
  )
  VALUES (
  OLD.Profile,
  OLD.DBClusterIdentifier,
  'MyCluster',
  'EndPoint',
  OLD.EndPoint,
  '',
  'delete',
  NOW()
  );

END; $$

DELIMITER ;


-- Example of saving information after a delete occurs on a key value table. The key is StackName and the value can be anything:


DROP TRIGGER IF EXISTS Tag_AD;

DELIMITER $$

CREATE DEFINER=`root`@`%` TRIGGER Tag_AD
AFTER DELETE
   ON Tag FOR EACH ROW

BEGIN

IF (OLD.Key = 'StackName') THEN
  INSERT INTO Audit (
  `Profile`,
  `Identifier`,
  `TableName`,
  `FieldName`,
  `OldValue`,
  `NewValue`,
  `Type`,
  `timestamp`
  )
  VALUES (
  OLD.Profile,
  OLD.Identifier,
  'Tag',
  'StackName',
  OLD.Value,
  '',
  'delete',
  NOW()
  );
END IF;

END; $$

DELIMITER ;


-- Example of saving information from a change to key value. The key is StackName and the Value can be anything.

DROP TRIGGER IF EXISTS Tag_BU;

DELIMITER $$
CREATE DEFINER=`root`@`%` TRIGGER Tag_BU BEFORE UPDATE ON Tag
  FOR EACH ROW BEGIN
  IF (OLD.Key = 'StackName' AND OLD.VALUE <> NEW.VALUE) THEN
        INSERT INTO Audit (
        `Profile`,
        `Identifier`,
        `TableName`,
        `FieldName`,
        `OldValue`,
        `NewValue`,
        `Type`,
        `timestamp`
        )
        VALUES (
        OLD.Profile,
        OLD.Identifier,
        'Tag',
        'StackName',
        OLD.VALUE,
        NEW.VALUE,
        'change',
        NOW()
        );
    END IF;

END$$
DELIMITER ;


-- Now add some data into the tables, make changes and delete some rows.

INSERT INTO `MyCluster` (`Profile`, `DBClusterIdentifier`, `Endpoint`, `EndpointIPAddress`, `ReaderEndpoint`, `ReaderEndpointIPAddress`, `ClusterCreateTime`, `CreateTime`, `UpdateTime`) VALUES ('test', 'test', 'test', '123', NULL, NULL, NULL, CURRENT_TIMESTAMP, '0000-00-00 00:00:00');
UPDATE `MyCluster` SET `EndpointIPAddress` = '456' WHERE `Profile` = 'test' AND `DBClusterIdentifier` = 'test';
DELETE FROM `MyCluster` WHERE (`Profile` = 'test' AND `DBClusterIdentifier` = 'test');

INSERT INTO `Tag` (`Profile`, `Type`, `Identifier`, `Key`, `Value`, `ResourceARN`, `CreateTime`, `UpdateTime`) VALUES ('test', 'Cluster', '123', 'StackName', 'MyTest', NULL, CURRENT_TIMESTAMP, '0000-00-00 00:00:00');
UPDATE `Tag` SET `Value` = 'MyTestIsDone' WHERE `Profile` = 'test' AND `Type` = 'Cluster' AND `Identifier` = '123' AND `Key` = 'StackName';
DELETE FROM `Tag` WHERE (`Profile` = 'test' AND `Type` = 'Cluster' AND `Identifier` = '123' AND `Key` = 'StackName');

Values that were changed or Deleted for the columns that have triggers setup will now appear in the Audit table.


PROS:

1.     Easy to implement.
2.     Very simple triggers
3.     Only one table needed to keep history for any number of tables

CONS

1.     Data type for old and new values is very generic, all data no matter what type it originally was is stored as TEXT
2.     There are no foreign key constraints between the tables. The columns in the change log table can refer to anything. Without  constraints, there is nothing to stop accidental or intentional manipulating of the numbers to values that don’t exist in the source table.
3.     Triggers add additional overhead to the system which could slow performance
4.     Writing queries to revert data is not simple

Setting up consumers - events_statements_history for Aurora instances with performance schema

I am wanting to setup Percona Monitoring Manager (PMM) for a client which uses Aurora. To do so, the documentation says you need to turn on the consumer in the performance schema so that events_statements_history os enabled. There isn't an option in the AWS console to do this and there isn't a parameter group setting to modify this so it must be done directly on the instance.


Here I will show you from the command line:

MySQL [(none)]> show global variables like 'performance_schema';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| performance_schema | ON    |

+--------------------+-------+

1 row in set (0.00 sec)

MySQL [(none)]> use performance_schema

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MySQL [performance_schema]>

MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';

+---------------------------+---------+

| NAME                      | ENABLED |

+---------------------------+---------+

| events_statements_history | NO      |

+---------------------------+---------+

1 row in set (0.00 sec)

MySQL [performance_schema]> update setup_consumers set enabled='yes' WHERE name = 'events_statements_history';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';

+---------------------------+---------+

| NAME                      | ENABLED |

+---------------------------+---------+

| events_statements_history | YES     |

+---------------------------+---------+

1 row in set (0.00 sec)

However, after an instance restart, the changes performed to setup_consumers table will be reversed. 

MySQL [performance_schema]> show global variables like 'performance_schema';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| performance_schema | ON    |

+--------------------+-------+

1 row in set (0.00 sec)

MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';

+---------------------------+---------+

| NAME                      | ENABLED |

+---------------------------+---------+

| events_statements_history | NO      |

+---------------------------+---------+

1 row in set (0.01 sec)

MySQL [performance_schema]> update setup_consumers set enabled='yes' WHERE name = 'events_statements_history';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';

+---------------------------+---------+

| NAME                      | ENABLED |

+---------------------------+---------+

| events_statements_history | YES     |

+---------------------------+---------+

1 row in set (0.00 sec)

I could setup a Zabbix trigger that turns it back whenever it detects a server restart or some other script. I think the easier route it to create an event that keeps turning it back on. Here is a simple example:

DROP EVENT IF EXISTS enable_statement_history;

CREATE

DEFINER=`root`@`localhost`

EVENT IF NOT EXISTS enable_statement_history

ON SCHEDULE EVERY 60 SECOND

    STARTS NOW()

DO

   update performance_schema.setup_consumers set enabled='yes' WHERE name = 'events_statements_history';

Encrypting the defaults file for logging into mysql

MySQL has an option to store credentials in a file so that you don't have to enter them at the command line when connecting to MySQL.

For example the "normal" way of connecting to MySQL from the command line would be like this:

# mysql -u<my_user> -p -h<server name>
Enter password:

OR

# mysql -u<my_user> -p<MYPassword>  -h<server name>

In the first example you have to enter in your password which won't work for scripts. In the second example you would have to hard code the password into your script or pull it out of a variable but it would get stored in the command line making it visible by anyone who can see what is running on the system. This is bad from a security perspective.

Instead you can use a defaults file and reference the file to logon to MySQL like this:

mysql --defaults-file=location_of_my_default_file.cnf -h<server name>

The defaults file only needs to contain these three lines:

[client]
user=my_user
password='123#_BLABLA'

You can also add a line for host if you want to limit the file to only be used by one server.

If your password is going to have special characters like # then make sure it is surrounded by single quotes like the above example.

The problem with this is now the password is stored in plain text and the security team at your company is not going to like it. This is better than having it in the command line history and visible in the process list but still too easy to discover. You could lock the permissions down so that only the root user can view it and only people with root access should theoretically ever be able to see it but that may still give several teams the possibility to view it and other indexing applications to easily discover it.

In MySQL 5.6, a new feature was added to encrypt this file with mysql_config_editor.

From the manual (https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html):

The encryption used by mysql_config_editor prevents passwords from appearing in .mylogin.cnf as cleartext and provides a measure of security by preventing inadvertent password exposure. For example, if you display a regular unencrypted my.cnf option file on the screen, any passwords it contains are visible for anyone to see. With .mylogin.cnf, that is not true. But the encryption used will not deter a determined attacker and you should not consider it unbreakable. A user who can gain system administration privileges on your machine to access your files could decrypt the .mylogin.cnf file with some effort.

Here is how you would create the encrypted file:

mysql_config_editor set --login-path=my_encrypted_defaults_file.cnf --host=localhost --user=root --password

And then to use it:

mysql --login-path=my_encrypted_defaults_file.cnf

OR

mysql --login-path=my_encrypted_defaults_file.cnf -h<server name>

You won't be able to view the file at my_encrypted_defaults_file.cnf. This may be "good enough" to satisfy your security team but we can do even better by using GPG. I do something similar to what is described in this Percona blog post using GPG on my laptop. I will leave that for another blog post:

https://www.percona.com/blog/2016/10/12/encrypt-defaults-file/