Grant Statement with Wildcard Characters in Cloud SQL - MySQL 8.0

[Marivishnu]

Providing Grant Statement with Wildcard Characters is not working in CloudSQL - MySQL 8.0

For Example:

If I have, 3 DBs named test_1, test_2, test_3 and I want my user to have SELECT privileges on these DBs, the GRANT statement I provided in Self Hosted MySQL 8.0 would be

GRANT SELCET ON `test\_%`.* TO 'myuser'@'%';

And that user can see the databases starting with 'test_'.

But providing the same in Cloud SQL doesn't seem to be working.

But "SHOW DATABASES LIKE `test\_%`" in Cloud SQL shows the database starting with "test_".

Am I missing anything in Cloud SQL with regards to the GRANT privileges?

[ms4446]

Cloud SQL for MySQL introduces specific nuances in the handling of wildcard grants, particularly when compared to self-hosted MySQL environments. While wildcard characters like % are effective for specifying hostnames (e.g., 'myuser'@'%'), their behavior can be unpredictable when applied to database or table names in Cloud SQL. This discrepancy arises from the unique way Cloud SQL manages database name resolution during the authorization process.

In practical terms, this means that a grant statement using a wildcard for database names, such as GRANT SELECT ON test_%.* TO 'myuser'@'%', which works seamlessly in a self-hosted MySQL setup, might not function as expected in Cloud SQL. Despite the SHOW DATABASES LIKE 'test\_%' command confirming the existence of matching databases, the wildcard grant may not apply the intended permissions.

Given the limitations, a straightforward and reliable approach is to explicitly grant permissions to each database. For instance:

GRANT SELECT ON `test_1`.* TO 'myuser'@'%';
GRANT SELECT ON `test_2`.* TO 'myuser'@'%';
GRANT SELECT ON `test_3`.* TO 'myuser'@'%';

This method ensures that each database receives the correct permissions without relying on wildcard interpretation. For environments with numerous databases, using stored procedures can automate and simplify the process of applying grants. A stored procedure can iterate through databases that match a specified pattern and dynamically apply the necessary permissions. 

DELIMITER //

CREATE PROCEDURE grant_select_to_test_dbs(user_name VARCHAR(255), host_name VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE db_name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'test_%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO db_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @Sql_stmt = CONCAT('GRANT SELECT ON `', db_name, '`.* TO ''', user_name, '''@''', host_name, ''';');
        PREPARE stmt FROM @Sql_stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END//

DELIMITER ;

To execute this procedure, simply call:

CALL grant_select_to_test_dbs('myuser', '%');

When encountering issues with wildcard grants, it is crucial to meticulously review any error messages generated by the GRANT statement. These messages can offer insights into why the expected permissions are not being applied. Additionally, examining the MySQL error logs within the Cloud SQL instance can provide more detailed information about authorization failures.

Consulting the Google Cloud SQL documentation for MySQL 8.0 can also be invaluable. The documentation may include specific guidelines and best practices for managing permissions in a Cloud SQL environment.

When using wildcards, exercise caution as they can inadvertently grant broader access than intended. Moreover, while stored procedures can be a powerful tool for managing permissions dynamically, they also introduce additional complexity and potential performance overhead. Therefore, use them judiciously to balance ease of management with system performance and security.

[Marivishnu]

Got it, thanks for the reply.