how to grant the GRANT OPTION privilege to root

Google Cloud Database
1

As root, as well as with user SRTDB, I am attempting an IMPORT to a database:

The IMPORT is failing with when I use root or srtdb user account:

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

As root when I do:

grant SESSION_VARIABLES_ADMIN on . to ‘srtdb’@‘%’;

I get the below error:

ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation

On MySQL Workbench I granted the GRANT OPTION privilege.

mysql> show grants for ‘root’@‘%’;
±---------------------------------------------------+
| Grants for root@% |
±---------------------------------------------------+
| GRANT USAGE ON . TO root@% WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON srtadmin.* TO root@% |
| GRANT cloudsqlsuperuser@% TO root@% |
±---------------------------------------------------+

But I still cannot do the IMPORT and I still cannot do the following command:

grant SESSION_VARIABLES_ADMIN on . to ‘srtdb’@‘%’;

How do I set a global variable in Cloud MySQL.

I need to get that done first, then can attempt the IMPORT again.

Thanks

Gillian

2

Hi @Gillian ,

Cloud SQL unsupported features:

  • Any features that require SUPERUSER privileges
    An exception to this rule is made for the CREATE EXTENSION statement, but only for supported extensions.
  • Custom background workers
  • The psql client in Cloud Shell doesn’t support operations that require a reconnection, such as connecting to a different database using the \c command.
  • Low-Level Virtual Machine (LLVM) Just-in-Time (JIT) compilation.
    Superuser restrictions

In Cloud SQL, customers cannot create or access users with superuser attributes.

Because Cloud SQL for MySQL is a managed service, it restricts access to certain system procedures and tables.

Thanks

3

Thank Paula for your response.

The objective was to set the global variables SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN. In order to do that, root needed the GRANT OPTION privileges.

So just to be clear in my mind.

Is there a way to set global parameters?

(1) SYSTEM_VARIABLES_ADMIN

(2) SESSION_VARIABLES_ADMIN

Thanks

Gillian

1 Like