MySQL

I recently learned of some odd behavior using MySQL BETWEEN operator queries, filtering by a DATETIME column. I wrote about this over on Medium so I am sharing the post for any readers here who are interested… Image by _Alicja_ from Pixabay  ... [More] Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! I’ve finally started a weekly email, covering SQL/PHP related content. If you’re interested in subscribing, use this sign-up form or the one located at the bottom of the page. Thank you! In my eclectic day job, I cover many tasks. Most of them I do not care for. However, I work extremely hard at those so I can work on the one task I do like; programming a custom internal LAMP stack web application. I have been working on this web application for over a year and it has grown immensely. Anyways, enough yada yada. On to the crux of the matter. Consider making a donation as I continue to provide valuable content here on my blog. Thank you so much!!! Every bit is much appreciated and helps tremendously! While prototyping out a query which retrieves PDF’s stored in the database (check out this post on the intricacies), I learned some interesting aspects of the BETWEEN operator involving DATETIME column values. I’ll leave it there for now and let you dig into the actual article, Are MySQL BETWEEN Operator Queries Inclusive. Feel free to share your thoughts or any comments you may have about these types of queries in the comments section below. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The post MySQL BETWEEN Operator Queries – Are they inclusive? appeared first on Digital Owl's Prose. [Less]

In our previous tutorial, we have explained how to achieve Concurrency with Goroutines in Golang. Here, We will clarify about Channels in Golang and how Goroutines speak with Channels. Channels are the lines that are utilized with Goroutines for ... [More] sending and getting values. We can send the data with Channels from one Goroutines and can […] The post Golang Channels With GoRoutine appeared first on GolangLearn. [Less]

The students needed yet another example of LEFT JOIN, RIGHT JOIN, and FULL JOIN syntax (by combining a left and right join with the UNION set operator). To that end, I put this set of examples together. The example also shows how to order the result ... [More] set from a derived table with the UNION operator. It uses the WITH clause to build a Common Table Expression (CTE), which allows the query to order the UNION set operator’s product based on the left and right join queries. It uses a CASE statement to order the result sets. The left_table is the parent table and the right_table is the child table in the relationship, which means the right_table holds a left_id foreign key column that lets you connect matching rows in the left_table. You build the little model with the following script: -- ----------------------------------------------------------------- -- Drop the demonstration tables. -- ----------------------------------------------------------------- DROP TABLE IF EXISTS left_table, right_table; -- ----------------------------------------------------------------- -- Create left_table. -- ----------------------------------------------------------------- CREATE TABLE left_table ( left_id int unsigned primary key auto_increment , leftstring varchar(10)); -- ----------------------------------------------------------------- -- Create left_table. -- ----------------------------------------------------------------- CREATE TABLE right_table ( right_id int unsigned primary key auto_increment , left_id int unsigned , rightstring varchar(10)); -- ----------------------------------------------------------------- -- Insert five rows to the left table, which holds a -- left_id primary key column. -- ----------------------------------------------------------------- INSERT INTO left_table (leftstring) values ('One'); INSERT INTO left_table (leftstring) values ('Two'); INSERT INTO left_table (leftstring) values ('Three'); INSERT INTO left_table (leftstring) values ('Four'); INSERT INTO left_table (leftstring) values ('Five'); -- ----------------------------------------------------------------- -- Delete row four to create a gap. -- ----------------------------------------------------------------- DELETE FROM left_table where left_id = 4; -- ----------------------------------------------------------------- -- Insert four rows, skipping a foreign key value for the -- left_id primary key value of 2. -- ----------------------------------------------------------------- INSERT INTO right_table (rightstring,left_id) values ('One',1); INSERT INTO right_table (rightstring,left_id) values ('Three',3); INSERT INTO right_table (rightstring,left_id) values ('Four',4); INSERT INTO right_table (rightstring,left_id) values ('Five',5); Here are the join statements: INNER JOIN The INNER JOIN only returns those rows that match between a primary and foreign key column or set of columns. SELECT l.left_id , l.leftstring , r.left_id , r.right_id , r.rightstring FROM left_table l INNER JOIN right_table r ON l.left_id = r.left_id; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 1 | One | 1 | 1 | One | | 3 | Three | 3 | 2 | Three | | 5 | Five | 5 | 4 | Five | +---------+------------+---------+----------+-------------+ 3 rows in set (0.00 sec) LEFT OUTER JOIN The LEFT OUTER JOIN only returns those rows that match between a primary and foreign key column or set of columns and any rows in the table on the lefthand side of the join that fail to match with any row on the righthand side of the join. The non-matching rows are also known as the right complement of the join. SELECT l.left_id , l.leftstring , r.left_id , r.right_id , r.rightstring FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 1 | One | 1 | 1 | One | | 2 | Two | NULL | NULL | NULL | | 3 | Three | 3 | 2 | Three | | 5 | Five | 5 | 4 | Five | +---------+------------+---------+----------+-------------+ 4 rows in set (0.00 sec) Add the following line 8 to the query and you get only those rows in the lefthand table that have no child-related rows in the righthand table. These rows are sometimes called childless parent rows. More or less, the use case for this type of query is to find order headers without order lines. FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id WHERE r.left_id IS NULL; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 2 | Two | NULL | NULL | NULL | +---------+------------+---------+----------+-------------+ 1 row in set (0.00 sec) RIGHT OUTER JOIN SELECT l.left_id , l.leftstring , r.left_id , r.right_id , r.rightstring FROM left_table l RIGHT JOIN right_table r ON l.left_id = r.left_id; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 1 | One | 1 | 1 | One | | 3 | Three | 3 | 2 | Three | | NULL | NULL | 4 | 3 | Four | | 5 | Five | 5 | 4 | Five | +---------+------------+---------+----------+-------------+ 4 rows in set (0.00 sec) Add the following line 8 to the query and you get only those rows in the righthand table that have no parent-related rows in the lefthand table. These rows are sometimes called orphans because they have no parent row. More or less, the use case for this type of query is to find latent order lines after deleting the order header. FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id WHERE l.left_id IS NULL; It produces the following result set: +---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | NULL | NULL | 4 | 3 | Four | +---------+------------+---------+----------+-------------+ 1 row in set (0.00 sec) FULL OUTER JOIN The full outer join doesn’t exist in MySQL, so you combine a LEFT OUTER JOIN and RIGHT OUTER JOIN with the UNION operator. The UNION operator eliminates the duplicate row from the intersection of the joins. Here’s the full query: WITH cte AS (SELECT l.left_id AS primary_left_id , l.leftstring , r.left_id AS foreign_left_id , r.right_id , r.rightstring FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id UNION SELECT l.left_id AS primary_left_id , l.leftstring , r.left_id AS foreign_left_id , r.right_id , r.rightstring FROM left_table l RIGHT JOIN right_table r ON l.left_id = r.left_id) SELECT * FROM cte ORDER BY CASE WHEN 'One' IN (leftstring, rightstring) THEN 1 WHEN 'Two' IN (leftstring, rightstring) THEN 2 WHEN 'Three' IN (leftstring, rightstring) THEN 3 WHEN 'Four' IN (leftstring, rightstring) THEN 4 WHEN 'Five' IN (leftstring, rightstring) THEN 5 END; It produces the following result set: +-----------------+------------+-----------------+----------+-------------+ | primary_left_id | leftstring | foreign_left_id | right_id | rightstring | +-----------------+------------+-----------------+----------+-------------+ | 1 | One | 1 | 1 | One | | 2 | Two | NULL | NULL | NULL | | 3 | Three | 3 | 2 | Three | | NULL | NULL | 4 | 3 | Four | | 5 | Five | 5 | 4 | Five | +-----------------+------------+-----------------+----------+-------------+ 5 rows in set (0.00 sec) Add the following lines 18 and 19 to the query and you get only those rows that are childless parent rows or orphaned child rows. More or less, the use case for this type of query is to find both order headers without order lines and order lines abandoned by deleted order headers. SELECT * FROM cte WHERE primary_left_id IS NULL OR foreign_left_id IS NULL It produces the following result set: +-----------------+------------+-----------------+----------+-------------+ | primary_left_id | leftstring | foreign_left_id | right_id | rightstring | +-----------------+------------+-----------------+----------+-------------+ | 2 | Two | NULL | NULL | NULL | | NULL | NULL | 4 | 3 | Four | +-----------------+------------+-----------------+----------+-------------+ 2 rows in set (0.00 sec) The ORDER BY clause used is a variation on the more common choice of: WHEN leftstring = 'One' OR rightstring = 'One' THEN 1 The position of two string literals in any statement is a bad idea. Inverting the literal on the right and using a IN operator gives you a better and clearer WHEN statement: WHEN 'One' IN (leftstring, rightstring) THEN 1 As always, I hope this helps those looking for syntax examples. [Less]

When organizing things helps to simplify life. In the previous article, we start to explore dynamic privileges and the interaction with static ones. We also saw how to remove SUPER privilege from a DBA account.  What we did was go by subtraction. But ... [More] in real life, we should act differently. We should ADD only what is really needed for the account to work correctly. Adding privilege one by one, and for each user is problematic given the level of interaction they may have, and also prone to mistakes.  Instead, we can use ROLES to group, assign, and revoke the correct privileges in a much easier way. This is becoming even more important in MySQL with the advent of dynamic privileges. What should we do to correctly use ROLES? Well first of all design.   The first step is to identify the ROLES, in doing so we need to keep a basic principle, make it simple, as such let us try to avoid having too many ROLES, or ROLE with too many cross-functional privileges. My proposal:  DBA (The lord of the databases who can do all) MaintenanceAdmin (DBA minions they can perform only some action on the server, and server only) UserAdmin (Can create users assign grants and so on) MonitorUser (See all process and read from performance_schema) DBManager (Can add/drop/modify schemas/tables/triggers/view/routines etc ) DBDesigner (Can modify specific objects mostly with clear identification by schema/table) ReplicationAdmin (Can add/change/remove start/stop replication also GR) BackupAdmin (Can take backup, cannot restore) We have eight administrative ROLES and they should cover ALL we need for administrative tasks. Now let us create them:CREATE ROLE 'DBA', 'MaintenanceAdmin', 'UserAdmin', 'MonitorUser', 'DBManager', 'DBDesigner', 'ReplicationAdmin', 'BackupAdmin' DC2-1(root@localhost) [mysql]>Select user,host from mysql.user where account_locked ='Y' and password_expired='Y' order by 1; +------------------+------+ | user | host | +------------------+------+ | BackupAdmin | % | | DBA | % | | DBDesigner | % | | DBManager | % | | MaintenanceAdmin | % | | MonitorUser | % | | ReplicationAdmin | % | | UserAdmin | % | +------------------+------+ 8 rows in set (0.00 sec)Let us check the roles one by one and see what privileges we need to assign. Our test user does not have any grant:DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost` DBA Well, you may say .. easy GRANT ALL. Wrong! As already indicated in the previous article, doing that will also assign SUPER, which is deprecated from MySQL 8.0.x. Let us start with the right foot and add ONLY what we need:GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `DBA`@`%` WITH GRANT OPTION; GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `DBA`@`%` WITH GRANT OPTION;That should be exactly the same as GRANT ALL but without SUPER.  To assign the ROLE to our test user:GRANT `DBA`@`%` TO `secure_test`@`localhost`Now our user has:DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost` *************************** 2. row *************************** Grants for secure_test@localhost: GRANT `DBA`@`%` TO `secure_test`@`localhost`Correct you now see DBA as grant but that is not active:DC2-2(secure_test@localhost) [(none)]>show grants for DBA@'%'\G ERROR 1142 (42000): SELECT command denied to user 'secure_test'@'localhost' for table 'user'To ACTIVATE a role you need to do it explicitly: SET DEFAULT ROLE DBA TO  secure_test@'localhost';And have the user reconnect! Once a role is activated we can also use:show grants for current_user()\GTo check which privileges are now active for a specific user. We can also control which role is active for which user querying the table mysql.default_roles.  To remove the active role:SET DEFAULT ROLE NONE TO  secure_test@'localhost';Anyhow, NOW we have our DBA role available for all DBA and if we need to change something we can do it just there on the ROLE and not on every single user.  MaintenanceAdmin GRANT EVENT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, RELOAD, SHUTDOWN ON *.* TO `MaintenanceAdmin`@`%`; GRANT BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN ON *.* TO `MaintenanceAdmin`@`%`; GRANT `MaintenanceAdmin`@`%` TO `secure_test`@`localhost` ; UserAdmin GRANT CREATE USER, GRANT OPTION, RELOAD, SHOW DATABASES ON *.* TO `UserAdmin`@`%`; GRANT ROLE_ADMIN  ON *.* TO `UserAdmin`@`%`; GRANT `UserAdmin`@`%` TO `secure_test`@`localhost` ; MonitorUser GRANT PROCESS, REPLICATION CLIENT ON *.* TO `MonitorUser`@`%`; GRANT SELECT ON performance_schema.* TO `MonitorUser`@`%`; GRANT `MonitorUser`@`%` TO `secure_test`@`localhost` ; DBManager GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, INDEX, INSERT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE  ON *.* TO `DBManager`@`%`; GRANT SET_USER_ID, SHOW_ROUTINE ON *.* TO `DBManager`@`%`; GRANT `DBManager`@`%` TO `secure_test`@`localhost` ; DBDesigner GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE VIEW, INDEX, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO `DBDesigner`@`%`; GRANT `DBDesigner`@`%` TO `secure_test`@`localhost` ; ReplicationAdmin GRANT REPLICATION CLIENT ON *.* TO `ReplicationAdmin`@`%`; GRANT REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, SERVICE_CONNECTION_ADMIN ON *.* TO `ReplicationAdmin`@`%`; GRANT SELECT on performance_schema.* TO `ReplicationAdmin`@`%`; GRANT SELECT on mysql.* TO `ReplicationAdmin`@`%`; GRANT `ReplicationAdmin`@`%` TO `secure_test`@`localhost` ; BackupAdmin GRANT EVENT, LOCK TABLES, SELECT, SHOW DATABASES ON *.* TO `BackupAdmin`@`%`; GRANT BACKUP_ADMIN ON *.* TO `BackupAdmin`@`%`; GRANT `BackupAdmin`@`%` TO `secure_test`@`localhost` ;Once all our ROLES are in, we can test them. For instance, we can check our ReplicationAdmin checking the Binary Logs and stopping/starting our Group Replication (or normal Replication):DC2-2(secure_test@localhost) [(none)]>show binary logs; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation DC2-2(secure_test@localhost) [(none)]>stop group_replication; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or GROUP_REPLICATION_ADMIN privilege(s) for this operationAlso if created and assigned the role is not active. Let us now enable the role for the user:SET DEFAULT ROLE ReplicationAdmin  TO  secure_test@'localhost';Remember to reconnect!DC2-2(secure_test@localhost) [(none)]>show binary logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000011 | 113802321 | No | | binlog.000012 | 19278 | No | +---------------+-----------+-----------+ 2 rows in set (0.00 sec) DC2-2(secure_test@localhost) [(none)]>stop group_replication; Query OK, 0 rows affected (5.25 sec) DC2-2(secure_test@localhost) [(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5 | 3306 | OFFLINE | | | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) DC2-2(secure_test@localhost) [(none)]>start group_replication; Query OK, 0 rows affected (3.70 sec) DC2-2(secure_test@localhost) [(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 79ede65d-9797-11eb-9963-08002734ed50 | gr4 | 3306 | ONLINE | PRIMARY | 8.0.23 | | group_replication_applier | 7e214802-9797-11eb-a0cf-08002734ed50 | gr6 | 3306 | ONLINE | SECONDARY | 8.0.23 | | group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5 | 3306 | ONLINE | SECONDARY | 8.0.23 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.01 sec)And these are the privileges active:DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT REPLICATION CLIENT ON *.* TO `secure_test`@`localhost` *************************** 2. row *************************** Grants for secure_test@localhost: GRANT GROUP_REPLICATION_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,SERVICE_CONNECTION_ADMIN ON *.* TO `secure_test`@`localhost` *************************** 3. row *************************** Grants for secure_test@localhost: GRANT SELECT ON `mysql`.* TO `secure_test`@`localhost` *************************** 4. row *************************** Grants for secure_test@localhost: GRANT SELECT ON `performance_schema`.* TO `secure_test`@`localhost` *************************** 5. row *************************** Grants for secure_test@localhost: GRANT `BackupAdmin`@`%`,`DBA`@`%`,`DBDesigner`@`%`,`DBManager`@`%`,`MaintenanceAdmin`@`%`,`MonitorUser`@`%`,`ReplicationAdmin`@`%`,`UserAdmin`@`%` TO `secure_test`@`localhost` 5 rows in set (0.00 sec) Conclusion Using the ROLES allows us to modify the needed privileges in one place, and at the same time will allow us to keep under control the possible proliferation of dynamic privileges due to the use of components or plugins, significantly reducing the complexity of having multiple privileges sources. Roles are normally used in the most common databases and MySQL had implemented them quite late. But using roles is the natural evolution of how we should deal with user grants when moving from small platforms to medium/large.  The time when we assign single user privileges IS GONE, welcome to 2021 MySQLers! For your convenience, I am distributing a simple SQL file with all commands to create the Roles as described in this article. References https://dev.mysql.com/doc/refman/8.0/en/roles.html Some queries related to MySQL Roles MySQL 8.0: Listing Roles   MySQL 8.0 Roles and Graphml [Less]

When trying to make things better, make our life very complicated. I was working on a Security Threat Tool script, when I had to learn more about the interaction between static and dynamic privileges in MySQL 8. Dynamic privileges is a “new” thing ... [More] added in MySQL 8 to easily extend the privileges definition, and at the same time to provide more granularity. For instance the FLUSH operation now has dedicated Privileges and by scope.  Dynamic privileges are assigned at runtime. Most of them are active when the server starts. But they can also change in respect to the components or plugin when activated. (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-dynamic) Static privileges are the classical privileges available in MySQL (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-static) Those are built into the server and cannot be changed. So far all is good. If we can give more flexibility to the security mechanism existing in MySQL, well I am all for it. My first step was to deal with the abuse of SUPER.  About that the manual comes to help with a section: Migrating Accounts from SUPER to Dynamic Privileges (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#dynamic-privileges-migration-from-super) .  Woo perfect! Let us play a bit. First let me create a user: create user secure_test@'localhost' identified by 'secret'; DC2-2(secure_test@localhost) [(none)]>show grants for current_user(); +-------------------------------------------------+ | Grants for secure_test@localhost                | +-------------------------------------------------+ | GRANT USAGE ON *.* TO `secure_test`@`localhost` | +-------------------------------------------------+ As you can see I can connect, but have no permissions. On another terminal with an administrative account, let us do the classical operation to create a DBA: GRANT ALL on *.* to secure_test@'localhost' WITH GRANT OPTION; And now I have : DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION As you can see I have a bunch of Privileges assigned.  To be honest, to have to identify exactly what each privilege does and how it interacts with the others is challenging. Anyhow, the manual tell us: "For each account identified by the preceding query, determine the operations for which it needs SUPER. Then grant the dynamic privileges corresponding to those operations, and revoke SUPER." In our case: revoke SUPER on *.* from secure_test@'localhost'; Which will remove the SUPER privileges, but what else will remain active?  Let us try one of the easiest things, let us modify the variable super_read_only. With super I can change the value of the variable without problems , but if I remove the SUPER privileges, what will happen?  DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION As you can see SUPER is gone.  DC2-2(secure_test@localhost) [(none)]>set global super_read_only=0; Query OK, 0 rows affected (0.00 sec) And I can still modify the global variable. WHY?   The manual says that SYSTEM_VARIABLES_ADMIN from the dynamic privileges allow us to modify "Enables system variable changes at runtime". Well what if I revoke it?  revoke SYSTEM_VARIABLES_ADMIN on *.* from secure_test@'localhost'; DC2-2(secure_test@localhost) [(none)]>set global super_read_only=0; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation Great! So in order to really remove/limit super I need to also remove SYSTEM_VARIABLES_ADMIN. But is that all? Well to make it short, no it is not.  Checking the manual you can see that SUPER is affecting all these: BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN And these are the ones by default. But we can also have others depending on the plugins we have active.  So in theory to be sure we are removing all SUPER related privileges, we should: REVOKE SUPER, BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN on *.* from secure_test@'localhost'; This, should leave us with the equivalent of a user without SUPER: DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SHOW_ROUTINE,SYSTEM_USER,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION CONCLUSIONS In this first blog we have started to explore the usage of Dynamic privileges, and what we need to do to remove the SUPER privilege.  Nevertheless, the list above is still a bit chaotic and unsafe. We still have SHUTDOWN or RELOAD or FILE, all of them are insecure and should be assigned with great care. In the next article we will see how to deal with Dynamic and Static privileges by Role and we also try to have clearer how they affect one another. [Less]

When trying to make things better, make it very complicated. I was working on a Security Threat Tool script when I had to learn more about the interaction between static and dynamic privileges in MySQL 8. Dynamic privileges is a “new” thing added in ... [More] MySQL 8 to easily extend the privileges definition, and at the same time to provide more granularity. For instance, the FLUSH operation now has dedicated Privileges and by scope.  Dynamic privileges are assigned at runtime. Most of them are active when the server starts. But they can also change with respect to the components or plugin when activated. (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-dynamic) Static privileges are the classical privileges available in MySQL. (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-static) Those are built into the server and cannot be changed. So far, all is good. If we can give more flexibility to the security mechanism existing in MySQL, well, I am all for it. My first step was to deal with the abuse of SUPER.  About that –  the manual comes to help with a section called Migrating Accounts from SUPER to Dynamic Privileges.  Woo perfect! Let us play a bit. First, let me create a user:create user secure_test@'localhost' identified by 'secret'; DC2-2(secure_test@localhost) [(none)]>show grants for current_user(); +-------------------------------------------------+ | Grants for secure_test@localhost                | +-------------------------------------------------+ | GRANT USAGE ON *.* TO `secure_test`@`localhost` | +-------------------------------------------------+As you can see I can connect, but have no permissions. On another terminal with an administrative account, let us do the classical operation to create a DBA:GRANT ALL on *.* to secure_test@'localhost' WITH GRANT OPTION;And now I have:DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTIONAs you can see, I have a bunch of privileges assigned.  To be honest, to have to identify exactly what each privilege does and how it interacts with the others is challenging.  Anyhow, the manual tells us: “For each account identified by the preceding query, determine the operations for which it needs SUPER. Then grant the dynamic privileges corresponding to those operations, and revoke SUPER.“ In our case:revoke SUPER on *.* from secure_test@'localhost';Which will remove the SUPER privileges, but what else will remain active?  Let us try one of the easiest things, let us modify the variable super_read_only. With super I can change the value of the variable without problems, but if I remove the SUPER privileges, what will happen? DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTIONAs you can see SUPER is gone. DC2-2(secure_test@localhost) [(none)]>set global super_read_only=0; Query OK, 0 rows affected (0.00 sec)And I can still modify the global variable. WHY?   The manual says that SYSTEM_VARIABLES_ADMIN from the dynamic privileges allow us to modify “Enables system variable changes at runtime“. Well, what if I revoke it? revoke SYSTEM_VARIABLES_ADMIN on *.* from secure_test@'localhost'; DC2-2(secure_test@localhost) [(none)]>set global super_read_only=0; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operationGreat! So in order to really remove/limit super, I need to also remove SYSTEM_VARIABLES_ADMIN. But is that all? Well to make it short, no it is not.  Checking the manual you can see that SUPER is affecting all these: BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN And these are the ones by default. But we can also have others depending on the plugins we have active.  So in theory to be sure we are removing all SUPER related privileges, we should:REVOKE SUPER, BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN on *.* from secure_test@'localhost';This should leave us with the equivalent of a user without SUPER:DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SHOW_ROUTINE,SYSTEM_USER,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION CONCLUSION In this first blog, we have started to explore the usage of Dynamic privileges, and what we need to do to remove the SUPER privilege.  Nevertheless, the list above is still a bit chaotic and unsafe. We still have SHUTDOWN or RELOAD or FILE, all of them are insecure and should be assigned with great care. In the next article in this series, we see how to deal with Dynamic and Static privileges by Role and we also try to have clearer how they affect one another. [Less]

When organizing things helps to simplify life. In the previous article we start to explore dynamic privileges and the interaction with the static ones. We also saw how to remove SUPER privilege from a DBA account.  What we did was go by subtraction. ... [More] But in real life, we should act differently. We should ADD only what is really needed for the account to work correctly. Adding privilege one by one, and for each user is problematic given the level of interaction they may have, and also prone to mistakes.  Instead we can use ROLES to group, assign and revoke the correct privileges in a much easier way. This is becoming even more important in MySQL with the advent of dynamic privileges. What should we do to correctly use ROLEs? Well first of all design.   The first step is to identify the ROLES, in doing so we need to keep a basic principle, make it simple, as such let us try to avoid having too many ROLES, or ROLE with too many cross functional privileges. My proposal:  DBA (The lord of the databases who can do all) MaintenanceAdmin (DBA minions :-) they can perform only some action on the server, and server only) UserAdmin (Can create users assign grants and so on) MonitorUser (See all process and read from performance_schema) DBManager (Can add/drop/modify schemas/tables/triggers/view/routines etc ) DBDesigner (Can modify specific objects mostly with a clear identification by schema/table) ReplicationAdmin (Can add/change/remove start/stop replication also GR) BackupAdmin (Can take backup, cannot restore) We have 8 administrative ROLES and they should cover ALL we need for administrative tasks. Now let us create them: CREATE ROLE 'DBA', 'MaintenanceAdmin', 'UserAdmin', 'MonitorUser', 'DBManager', 'DBDesigner', 'ReplicationAdmin', 'BackupAdmin' DC2-1(root@localhost) [mysql]>Select user,host from mysql.user where account_locked ='Y' and password_expired='Y' order by 1; +------------------+------+ | user | host | +------------------+------+ | BackupAdmin | % | | DBA | % | | DBDesigner | % | | DBManager | % | | MaintenanceAdmin | % | | MonitorUser | % | | ReplicationAdmin | % | | UserAdmin | % | +------------------+------+ 8 rows in set (0.00 sec) Let us check the roles one by one and see what privileges we need to assign. Our test user do not have any grant: DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost` DBA, well you may say .. easy GRANT ALL. Wrong! As already indicated in the previous article, doing that will also assign SUPER, which is deprecated from MySQL 8.0.x. Let us start with the right foot and add ONLY what we need: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `DBA`@`%` WITH GRANT OPTION; GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `DBA`@`%` WITH GRANT OPTION; That should be exactly the same as GRANT ALL, but without SUPER.  To assign the ROLE to our test user: GRANT `DBA`@`%` TO `secure_test`@`localhost` Now our user has: DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost` *************************** 2. row *************************** Grants for secure_test@localhost: GRANT `DBA`@`%` TO `secure_test`@`localhost` Correct you now see DBA as grant but that is not active: DC2-2(secure_test@localhost) [(none)]>show grants for DBA@'%'\G ERROR 1142 (42000): SELECT command denied to user 'secure_test'@'localhost' for table 'user' To ACTIVATE a role you need to do it explicitly:  SET DEFAULT ROLE DBA TO  secure_test@'localhost'; And have the user reconnect! Once a role is activated we can also use: show grants for current_user()\G To check which privileges are now active for a specific user. We can also control which role is active for which user querying the table mysql.default_roles.  To remove the active role: SET DEFAULT ROLE NONE TO  secure_test@'localhost'; Anyhow, NOW we have our DBA role available for all DBA and if we need to change something we can do it just there on the ROLE and not on each single user.  MaintenanceAdmin, GRANT EVENT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, RELOAD, SHUTDOWN ON *.* TO `MaintenanceAdmin`@`%`; GRANT BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN ON *.* TO `MaintenanceAdmin`@`%`; GRANT `MaintenanceAdmin`@`%` TO `secure_test`@`localhost` ; UserAdmin, GRANT CREATE USER, GRANT OPTION, RELOAD, SHOW DATABASES ON *.* TO `UserAdmin`@`%`; GRANT ROLE_ADMIN  ON *.* TO `UserAdmin`@`%`; GRANT `UserAdmin`@`%` TO `secure_test`@`localhost` ; MonitorUser, GRANT PROCESS, REPLICATION CLIENT ON *.* TO `MonitorUser`@`%`; GRANT SELECT ON performance_schema.* TO `MonitorUser`@`%`; GRANT `MonitorUser`@`%` TO `secure_test`@`localhost` ; DBManager, GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, INDEX, INSERT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE  ON *.* TO `DBManager`@`%`; GRANT SET_USER_ID, SHOW_ROUTINE ON *.* TO `DBManager`@`%`; GRANT `DBManager`@`%` TO `secure_test`@`localhost` ; DBDesigner, GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE VIEW, INDEX, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO `DBDesigner`@`%`; GRANT `DBDesigner`@`%` TO `secure_test`@`localhost` ; ReplicationAdmin, GRANT REPLICATION CLIENT ON *.* TO `ReplicationAdmin`@`%`; GRANT REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, SERVICE_CONNECTION_ADMIN ON *.* TO `ReplicationAdmin`@`%`; GRANT SELECT on performance_schema.* TO `ReplicationAdmin`@`%`; GRANT SELECT on mysql.* TO `ReplicationAdmin`@`%`; GRANT `ReplicationAdmin`@`%` TO `secure_test`@`localhost` ; BackupAdmin, GRANT EVENT, LOCK TABLES, SELECT, SHOW DATABASES ON *.* TO `BackupAdmin`@`%`; GRANT BACKUP_ADMIN ON *.* TO `BackupAdmin`@`%`; GRANT `BackupAdmin`@`%` TO `secure_test`@`localhost` ; Once all our ROLES are in, we can test them. For instance we can check our ReplicationAdmin checking the Binary Logs and stopping/starting our Group Replication (or normal Replication): DC2-2(secure_test@localhost) [(none)]>show binary logs; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation DC2-2(secure_test@localhost) [(none)]>stop group_replication; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or GROUP_REPLICATION_ADMIN privilege(s) for this operation Also if created and assigned the role is not active. Let us now enable the role for the user: SET DEFAULT ROLE ReplicationAdmin  TO  secure_test@'localhost'; Remember to reconnect! DC2-2(secure_test@localhost) [(none)]>show binary logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000011 | 113802321 | No | | binlog.000012 | 19278 | No | +---------------+-----------+-----------+ 2 rows in set (0.00 sec) DC2-2(secure_test@localhost) [(none)]>stop group_replication; Query OK, 0 rows affected (5.25 sec) DC2-2(secure_test@localhost) [(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5 | 3306 | OFFLINE | | | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) DC2-2(secure_test@localhost) [(none)]>start group_replication; Query OK, 0 rows affected (3.70 sec) DC2-2(secure_test@localhost) [(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 79ede65d-9797-11eb-9963-08002734ed50 | gr4 | 3306 | ONLINE | PRIMARY | 8.0.23 | | group_replication_applier | 7e214802-9797-11eb-a0cf-08002734ed50 | gr6 | 3306 | ONLINE | SECONDARY | 8.0.23 | | group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5 | 3306 | ONLINE | SECONDARY | 8.0.23 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.01 sec) And these are the privileges active: DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT REPLICATION CLIENT ON *.* TO `secure_test`@`localhost` *************************** 2. row *************************** Grants for secure_test@localhost: GRANT GROUP_REPLICATION_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,SERVICE_CONNECTION_ADMIN ON *.* TO `secure_test`@`localhost` *************************** 3. row *************************** Grants for secure_test@localhost: GRANT SELECT ON `mysql`.* TO `secure_test`@`localhost` *************************** 4. row *************************** Grants for secure_test@localhost: GRANT SELECT ON `performance_schema`.* TO `secure_test`@`localhost` *************************** 5. row *************************** Grants for secure_test@localhost: GRANT `BackupAdmin`@`%`,`DBA`@`%`,`DBDesigner`@`%`,`DBManager`@`%`,`MaintenanceAdmin`@`%`,`MonitorUser`@`%`,`ReplicationAdmin`@`%`,`UserAdmin`@`%` TO `secure_test`@`localhost` 5 rows in set (0.00 sec) Conclusions Using the ROLES allows us to modify the needed privileges in one place, and at the same time will allow us to keep under control the possible proliferation of dynamic privileges due the use of components or plugins, significantly reducing the complexity of having multiple privileges sources. Roles are normally used in the most common Databases and MySQL had implemented them quite late. But using roles is the natural evolution of how we should deal with user grants when moving from small platforms to medium/large.  The time when we assign single user privileges IS GONE, welcome to 2021 MySQLlers!   For your convenience I am distributing a simple SQL file with all commands to create the Roles as described in this article(link to github) References https://dev.mysql.com/doc/refman/8.0/en/roles.html https://lefred.be/content/some-queries-related-to-mysql-roles/ https://lefred.be/content/mysql-8-0-listing-roles/   https://lefred.be/content/mysql-8-0-roles-and-graphml/ [Less]

The practice of running databases in containers continues to grow in popularity.  As a Technical Account Manager at Percona, I get asked regularly about our Percona Distribution for MySQL Operator.  Additionally, I’m asked what I’m seeing in the ... [More] industry in terms of adoption.  In most cases, the questions stem around new deployments.  Our DBaaS tool (currently in Technical Preview) makes launching a new cluster in a Kubernetes deployment trivial.   Once the operator completes and verifies the setup, the UI displays the endpoint and credentials and you are on your way.  Voila!  You now have a cluster, behind a load balancer, that you can access from within your k8s cluster or externally:   This is all well and good, giving developers the opportunity to quickly build new applications.  However, a new question recently came up in a client call: how can I migrate an existing database into Kubernetes?  This got me thinking, so I decided to spin up some test servers and experiment with the backup and restore functionality of the operator. General Migration Process For my testing, I wanted to follow the standard process: Take a binary snapshot of the primary server Restore that snapshot into the new cluster Start replication from the primary server to the new cluster Cutover the application after replication is caught up Looking through the operator documentation, I wanted to leverage the existing restore functionality in my testing.  Through some manual updates to the service created by the DBaaS tool, I defined my S3 credentials and details:kubectl edit perconaxtradbcluster.pxc.percona.com/mbenshoof-pxc-clusterNext, I verified that I could take and restore backups from the command line (using the kubectl tool).  So far, so good.   Finally, it was time to test the main step of a generic migration.  I had a test server set up and took a streaming backup (using xbcloud with the –md5 switch passed) directly into the S3 bucket configured in the operator.  The final step was to define a custom restore resource and kick off the restore:# cat restore-external.yaml apiVersion: pxc.percona.com/v1 kind: PerconaXtraDBClusterRestore metadata: name: restore-external-v1 spec: pxcCluster: migrate-from-external backupSource: destination: s3:///external-80-full_backup s3: credentialsSecret: region: us-east-1After some time, I verified that the restoration succeeded and also verified that my DBaaS cluster was operational again:$ kubectl logs job/restore-job-restore-external-v1-migrate-from-external  If everything is fine, you can clean up the job:$ kubectl delete pxc-restore/restore-external-v1 Completed: 2021-06-04T16:42:58Z State: Succeeded Events: Success!  I was able to restore my external 8.0 instance into a new DBaaS-generated cluster.  The final step in a migration process is to set up replication into the new cluster. Setting up Replication With a replication user-defined, it was trivial to get replication running into the new cluster.  In fact, there was no difference between this test and setting up replication normally.  Once I allowed access to the source server (via VPC Security Groups), I simply started replication and verified that I was picking up new transactions:mysql> show replica status\G *************************** 1. row *************************** Replica_IO_State: Waiting for master to send event Source_Host: 10.1.1.226 Source_User: repl Source_Port: 3306 Connect_Retry: 60 Source_Log_File: binlog.000005 Read_Source_Log_Pos: 679 Relay_Log_File: migrate-from-external-pxc-0-relay-bin.000002 ... Seconds_Behind_Source: 0 ... Retrieved_Gtid_Set: c6a4c262-c4a7-11eb-bed8-0e0d8750f515:84-85 Executed_Gtid_Set: c6a4c262-c4a7-11eb-bed8-0e0d8750f515:1-85 Auto_Position: 1 ... 1 row in set (0.00 sec)Just to ensure things were working as expected, I ran a few queries and sent some write traffic to my primary test server.  Everything worked as expected when setting up replication from a stand-alone instance into the new cluster! Primary Challenge – User Credentials Working through this exercise, I did identify the main limitation in this process.  When I first attempted this exercise, the restore process hung and eventually failed.  The reason: conflicting user credentials in the freshly restored cluster. The operator manages the cluster nodes via credentials defined in a Kubernetes secret.  When new clusters are created, random and unique credentials are generated and used to set up the cluster.  While this is great for a new cluster and keeps it secure upon launch, it isn’t ideal for the migration process. While playing around with various migration test variations, I used one of the following two workaround methods: Modifying the source database with the newly generated cluster credentials Modifying the k8s secret file with credentials from the existing database Modifying the Source Database For this method, the workaround I used was capturing all of the operator generated credentials on the empty cluster via pt-show-grants:pt-show-grants --only=clustercheck,monitor,operator,proxyadmin,root,xtrabackupI then applied these grants to the source server (in practice, I would never do this, but this was just an exercise with dummy data).  Once the credentials were updated and confirmed, I then took the S3 streaming backup and the restore went off without a hitch. Modifying the Secrets File For the second workaround, there are some assumptions: You know all the users created by the operator by default Those users exist (or can be created) on the source database In this workaround, I grabbed the password(s) from the existing source cluster as the first step.  Once I had them base64 encoded, it was just a matter of editing the secrets created with the new cluster.  I only needed to modify the dbaas-* generated secrets as that was kept in sync with all the other secret files. The main thing in the process – ensure the secrets file is updated BEFORE kicking off the restore job.  Secrets are only reloaded when pods are terminated and restarted, so doing that cleanly is important.  The restore job does a few things: Pull the backup from S3 into a new container Prepare and validate the backup Stop the old cluster Restart the cluster with the freshly prepared backup  Assuming you have the secrets file in place before starting the job, the new credentials will be picked up nicely when the cluster restarts and all will go well.  Otherwise, you’ll be looking at lots of manual battles and debugging. Other Limitations The other primary limitation with the current release is that the source needs to also be running 8.0.  As the restore procedure uses xtrabackup-80 for the process, providing a 5.7 backup will result in the following error:This version of Percona XtraBackup can only perform backups and restores against MySQL 8.0 and Percona Server 8.0, please use Percona Xtrabackup 2.4 for this database.To handle a 5.7 -> 8.0 migration, you would need to do a logical dump/reload of the databases in question.  Like the binary restore, the user credentials will be problematic so it would be preferable to omit the user tables.  This process comes with the standard 5.7 -> 8.0 challenges, but it is possible.   Summary As this process is not supported (or even recommended) in the current release, I’ve omitted some details and configuration from this post.  Despite the challenges and the process being a little rough around the edges, it was great to validate xtrabackup restoration and replication into k8s from an external source. I’m excited to see the progress in the coming months as our DBaaS offering approaches GA.  With features like UX management of the process and MySQL user migration on the roadmap, it should be much easier in the future to migrate existing databases into Kubernetes.  With the investments we are seeing around large k8s deployments, it would be a shame to limit it to new applications.  As the  Percona Distribution for MySQL Operator continues to evolve along with the DBaaS interface, the options should be unlimited.  As always, if you need help evaluating an existing architecture or migrating into Kubernetes, don’t hesitate to reach out to our Professional Services team!  [Less]

In this golang tutorial, We will clarify about Golang Goroutines with example. The Goroutines are methods in the Golang that are run simultaneously which implies numerous activities can be dealt with simultaneously. It’s tied in with making and ... [More] executing multiple processes autonomously. We can undoubtedly change sequential programs into simultaneous program without agonizing over threads […] The post Golang Goroutines With Example appeared first on GolangLearn. [Less]

One of the most popular ways in achieving high availability for MySQL is replication. Replication has been around for many years, and became much more stable with the introduction of GTIDs. But even with these improvements, the replication process ... [More] can break due to various reasons - for instance, when master and slave are out of sync because writes were sent directly to the slave. How do you troubleshoot replication issues, and how do you fix them?  In this blog post, we will discuss some of the common issues with replication and how to fix them with ClusterControl. Let’s start with the first one. Replication Stopped With Some Error Most MySQL DBAs will typically see this kind of problem at least once in their career. For various reasons, a slave can get corrupted or maybe stopped syncing with the master. When this happens, the first thing to do to start the troubleshooting is to check the error log for messages. Most of the time, the error message is easily traceable in the error log or by running the SHOW SLAVE STATUS query.  Let’s take a look at the following example from the SHOW STATUS SLAVE: ********** 0. row ********** Slave_IO_State: Master_Host: 10.2.9.71 Master_User: cmon_replication Master_Port: 3306 Connect_Retry: 10 Master_Log_File: binlog.000111 Read_Master_Log_Pos: 255477362 Relay_Log_File: relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: binlog.000111 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 255477362 Relay_Log_Space: 256 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1000 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 1000-1000-2268440 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 We can clearly see the error is related to Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.'. In order words, what the error is telling us essentially is that there is inconsistency in data and the required binary log files have already been deleted. This is one good example where the replication process stops working. Besides SHOW SLAVE STATUS, you can also track the status in the “Overview” tab of the cluster in ClusterControl. So how to fix this with ClusterControl? You have two options to try: You may try to start the slave again from the “Node Action” If the slave is still not working, you may run “Rebuild Replication Slave” job from the “Node Action” Most of the time, the second option will resolve the issue. ClusterControl will take a backup of the master, and rebuild the broken slave by restoring the data. Once the data is restored, the slave is connected to the master so it can catch up.  There are also multiple manual ways to rebuild slave as listed below, you may also refer to this link for more details:  Using Mysqldump to Rebuild an Inconsistent MySQL Slave Using Mydumper to Rebuild an Inconsistent MySQL Slave Using a Snapshot to Rebuild an Inconsistent MySQL Slave Using a Xtrabackup or Mariabackup to Rebuild an Inconsistent MySQL Slave Promote A Slave To Become A Master Over time, the OS or database needs to be patched or upgraded to maintain stability and security. One of the best practices to minimize the downtime especially for a major upgrade is promoting one of the slaves to master after the upgrade was successfully done on that particular node.  By performing this, you could point your application to the new master and the master-slave replication will continue to work. In the meantime, you also could proceed with the upgrade on the old master with peace of mind. With ClusterControl this can be executed with a few clicks only assuming the replication is configured as Global Transaction ID-based or GTID-based for short. To avoid any data loss, it’s worth stopping any application queries in case the old master is operating correctly. This is not the only situation that you could promote the slave. In the event the master node is down, you also could perform this action.  Without ClusterControl, there are a few steps to promote the slave. Each of the steps requires a few queries to run as well: Manually take down the master Select the most advanced slave to be a master and prepare it Reconnect other slaves to the new master Changing the old master to be a slave Nevertheless, the steps to Promote Slave with ClusterControl is only a few clicks: Cluster > Nodes > choose slave node > Promote Slave as per the screenshot below: Master Becomes Unavailable Imagine you have large transactions to run but the database is down. It does not matter how careful you are, this is probably the most serious or critical situation for a replication setup. When this happens, your database is not able to accept a single write, which is bad. Besides, your application(s), of course, will not work properly. There are a few reasons or causes that lead to this issue. Some of the examples are hardware failure, OS corruption, database corruption and so on. As a DBA, you need to act quickly to restore the master database. Thanks to the “Auto Recovery” cluster function that is available in ClusterControl, the failover process can be automated. It can be enabled or disabled with a single click. As the name goes, what it will do is bring up the entire cluster topology when necessary. For example, a master-slave replication must have at least one master alive at any given time, regardless of the number of available slaves. When the master is not available, it will automatically promote one of the slaves. Let’s take a look at the screenshot below:   In the above screenshot, we can see that “Auto Recovery” is enabled for both Cluster and Node. In the topology, notice that the current master IP address is 10.10.10.11. What will happen if we take down the master node for testing purposes? As you can see, the slave node with IP 10.10.10.12 is automatically promoted to master, so that the replication topology is reconfigured. Instead of doing it manually which, of course, will involve a lot of steps, ClusterControl helps you to maintain your replication setup by taking the hassle off your hands. Conclusion In any unfortunate event with your replication, the fix is very simple and less hassle with ClusterControl. ClusterControl helps you recover your replication issues quickly, which increases the uptime of your databases. Tags: MySQLclustercontrolreplication [Less]