Problem statement: Data and logic often exist as close to each other as possible. Take MySQL server, for instance, or any relational database server and there is convenience to query and process the data stored. In fact, SQL is not just a language but a standard for structured storage and data that does not fit in a spreadsheet continues to be saved in tables. Many SQL server instances were created on-premises and usually in a central department or company headquarters but with the move to cloud they became universally reachable and demanded more from authentication and authorization. The de facto directory in the cloud for any organization continues to be the active directory aka AD and most SQL logins saved in the MySQL server, are now replaced by users and groups in the AD so that they can authenticate as themselves. But the onboarding of every user continues to draw up chores that an administrator must take in the form of SQL statements such as for audit or assignment. How do we automate this?

 

Solution: Every runtime, host, and cloud, provide management features for authentication and authorization that determine principal, role, and permissions to allow the control to pass over these barriers successfully before data is accessed. Out of these, the inner most circle has the data and logins saved so any automation can be successfully applied there. One of the features of the MySQL runtime is that it supports triggers that can execute INSERT, REPLACE, UPDATE and DELETE. System events where a trigger can run include STARTUP, SHUTDOWN, SERVERERROR, LOGON, LOGOFF, CREATE, DROP and ALTER. MySQL provides hooks for init_connect, init_file, and init_slave. In the my.cnf file, we can add a SQL script file which can be executed on database startup (init_file). These three hooks can be used to build the LOGON and the STARTUP trigger.

The implementation of a logon trigger requires writing a procedure that will execute on each LOGON event.

 

-- DROP PROCEDURE test.logon_trigger;

DELIMITER //

CREATE PROCEDURE test.logon_trigger()

     SQL SECURITY DEFINER

BEGIN

CREATE AADUSER CURRENT_USER()

flush privileges;

-- Create roles

CREATE ROLE 'ReadOnlyGroup';

-- Grant permissions to that role

GRANT SELECT ON testdb.* TO 'ReadOnlyGroup';

-- Assign roles to the AD Service Principal.

GRANT 'ReadOnlyGroup' TO CURRENT_USER()

END;

//

DELIMITER;

 

The CURRENT_USER() / CONNECTION_PROPERTY(‘user’) can be replaced with a caller supplied parameter.

 

The trigger can be tested with:

SHOW GLOBAL VARIABLES LIKE 'init%';

CALL test.logon_trigger;

 

When everything works fine, the hook must be connected to the procedure to build the trigger:

SET GLOBAL init_connect=’CALL test.logon_trigger()’;

 

A caveat here must be mentioned that just like all stored procedures, permissions must be granted to execute the logon trigger.

This can be done with:

 

GRANT EXECUTE TO PROCEDURE test.logon_trigger TO ‘*’;

 

The hook in the my.cnf file must be fixed otherwise it is not activated in the next system restart.

 

It is also possible to put the call procedure statement in a .sql file so that this script can be accessed from remote.

 

Finally, if the administrator centric duties must not be invoked as part of every logon event, the same trigger can be run as a procedure over a batch of user accounts belonging to the team as a one-time event and invoked specifically by the administrator on an adhoc or scheduled basis.

 

Automation with a cloud runbook that call azure cli commands on the target mysql instance is also possible and provides convenience to the end users but the SQL statements cannot be avoided and must be passed to the runbook in some form of script/file that can be run as part of the runbook. This makes logon triggers and such stored procedures convenient to encapsulate and run the statements.

 

Previous articles: https://1drv.ms/w/s!Ashlm-Nw-wnWhNNf6kdqJbe7IHHQEA?e=wqZK1g