Verifying Your Integration

After all the files have been configured, you can verify your integration using either of the following.

Identifying the Encrypted Tablespaces

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';

Encrypting a table using keyring_okv plugin

1. Create a new user and grant all privileges to the user.

   CREATE USER 'TestUser'@'localhost' IDENTIFIED BY '********';
   GRANT ALL PRIVILEGES ON *.* TO 'TestUser'@'localhost';
   

2. Log on to MySQL as “TestUser”, and create a database called Players and a table named BaseballPlayers.

   mysql -u TestUser –p
   create database Players;
   use Players;
   CREATE table BaseballPlayers (lastname varchar(16), firstname varchar(12), position varchar(16));
   

3. Insert some sample data into the table BaseballPlayers.

   INSERT INTO BaseballPlayers ( firstname, lastname, position ) values ('John','Doe','Outfield' );
   INSERT INTO BaseballPlayers ( firstname, lastname, position ) values ('Joe','Smith','Pitcher' );
   INSERT INTO BaseballPlayers ( firstname, lastname, position ) values ('Willie','Mays', 'Outfield' );
   

4. Encrypt the table.

   ALTER TABLE BaseballPlayers ENCRYPTION='Y';
   

This generates your master encryption key on the CipherTrust Manager and completes your integration. Move to the next section to verify your integration.

Rotating Master Key

The master encryption key should be rotated periodically or whenever you suspect that the key has been compromised.

To rotate the master key:

1. Run the following query:

   ALTER INSTANCE ROTATE INNODB MASTER KEY;
   

2. Check the server records on the CipherTrust Manager to verify the status of the operation.