Utilizzare un client della riga di comando, ad esempio un client MySQL o una shell MySQL, per elencare i privilegi concessi a un utente.

1. Per elencare i privilegi concessi a un utente, effettuare una delle operazioni riportate di seguito.
   • Per visualizzare i privilegi concessi all'utente corrente, eseguire il comando seguente:

     SHOW GRANTS \G

   • Per visualizzare i privilegi concessi a un utente denominato, eseguire il comando seguente:

     SHOW GRANTS FOR <Username> \G

   Se il comando è stato eseguito per un utente amministratore, User001, il comando restituisce i seguenti elementi:

   *************************** 1. row ***************************
   Grants for User001@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
   PROCESS, 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 ROLE, DROP ROLE ON *.* TO `User001`@`%` WITH GRANT OPTION
   *************************** 2. row ***************************
   Grants for User001@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,
   CONNECTION_ADMIN,EXPORT_QUERY_RESULTS,FLUSH_OPTIMIZER_COSTS,FLUSH_PRIVILEGES,
   FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,OPTION_TRACKER_OBSERVER,
   REPLICATION_APPLIER,ROLE_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,TRANSACTION_GTID_TAG,
   VECTOR_STORE_LOAD_EXEC,XA_RECOVER_ADMIN ON *.* TO `User001`@`%` WITH GRANT OPTION
   *************************** 3. row ***************************
   Grants for admin@%: GRANT CREATE TEMPORARY TABLES ON `sys`.* TO `admin`@`%` 
   WITH GRANT OPTION
   *************************** 4. row ***************************
   Grants for User001@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
   INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW,
    CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `User001`@`%`
   *************************** 5. row ***************************
   Grants for User001@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
   CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, 
   ALTER ROUTINE, EVENT, TRIGGER ON `mysql_audit`.* FROM `User001`@`%`
   *************************** 6. row ***************************
   Grants for User001@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
   INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, 
   CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_option`.* FROM `User001`@`%`
   *************************** 7. row ***************************
   Grants for User001@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, 
   ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, 
   ALTER ROUTINE, EVENT, TRIGGER ON `mysql_rest_service_metadata`.* FROM FROM `User001`@`%`
   *************************** 8. row ***************************
   Grants for User001@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
   CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
   EVENT, TRIGGER ON `mysql_tasks`.* FROM FROM `User001`@`%`
   *************************** 9. row ***************************
   Grants for User001@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, LOCK TABLES, 
   CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `sys`.* FROM `User001`@`%`
   *************************** 10. row ***************************
   Grants for User001@%: GRANT PROXY ON ``@`` TO `User001`@`%` WITH GRANT OPTION
   *************************** 11. row ***************************
   Grants for User001@%: GRANT `administrator`@`%` TO `User001`@`%` WITH ADMIN OPTION
   8 rows in set (0.11 sec)

   Le righe corrispondono alle seguenti:

   • Riga 1: Privilegi globali statici concessi.
   • Riga 2: Privilegi globali dinamici concessi.
   • Riga 3: Privilegi concessi nello schema sys
   • Riga 4: Privilegi revocati dallo schema mysql.
   • Riga 5: Privilegi revocati dallo schema mysql_audit.
   • Riga 6: (MySQL solo 9.1.0 o versioni successive) Privilegi revocati dallo schema mysql_option.
   • Riga 7: (MySQL 9.3.1 o versione successiva con solo privilegi abilitati per REST) Privilegi revocati dallo schema mysql_rest_service_metadata
   • Riga 8: (MySQL solo 9.3.1 o versioni successive) Privilegi revocati dallo schema mysql_tasks
   • Riga 9: Privilegi revocati dallo schema sys.
   • Riga 10: utenti proxy.
   • Riga 11: Ruoli concessi.

Alcuni privilegi MySQL statici globali vengono concessi all'amministratore del sistema DB.

Alcuni privilegi MySQL dinamici globali vengono concessi all'amministratore del sistema DB.

Alcuni privilegi vengono concessi nello schema sys all'amministratore del sistema DB.

Alcuni privilegi vengono revocati dallo schema mysql. Non è possibile concedere questi privilegi ad alcun utente del sistema DB.

Alcuni privilegi vengono revocati dallo schema mysql_audit. Non è possibile concedere questi privilegi ad alcun utente del sistema DB.

In MySQL 9.1.0 o versioni successive, determinati privilegi vengono revocati dallo schema mysql_option. Impossibile concedere questi privilegi a qualsiasi utente del sistema DB.

In MySQL 9.3.1 o versione successiva con REST abilitato, determinati privilegi vengono revocati dallo schema mysql_rest_service_metadata. Non è possibile concedere questi privilegi ad alcun utente del sistema DB.

In MySQL 9.3.1 o versioni successive, determinati privilegi vengono revocati dallo schema mysql_tasks. Non è possibile concedere questi privilegi ad alcun utente del sistema DB.

Alcuni privilegi vengono revocati dallo schema sys. Impossibile concedere questi privilegi a qualsiasi utente del sistema DB.

Alcuni ruoli utente MySQL vengono concessi all'amministratore del sistema DB.

SHOW GRANTS FOR administrator \G
*************************** 1. row ***************************
Grants for administrator@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
PROCESS, 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 ROLE, DROP ROLE ON *.* TO `administrator`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for administrator@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,
CONNECTION_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,
OPTION_TRACKER_OBSERVER,REPLICATION_APPLIER,ROLE_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,
TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `administrator`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for administrator@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, 
CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `administrator`@`%`
*************************** 4. row ***************************
Grants for administrator@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
EVENT, TRIGGER ON `mysql_audit`.* FROM `administrator`@`%`
*************************** 5. row ***************************
Grants for administrator@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, 
ALTER ROUTINE, EVENT, TRIGGER ON `mysql_option`.* FROM `administrator`@`%`
*************************** 6. row ***************************
Grants for administrator@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
EVENT, TRIGGER ON `sys`.* FROM `administrator`@`%`
6 rows in set (0.00 sec)

Utilizzare un client della riga di comando come il client MySQL o la shell MySQL per creare amministratori di sistema DB aggiuntivi.

1. Connettersi al sistema DB con le credenziali di un amministratore del sistema DB utilizzando un client della riga di comando, ad esempio il client MySQL o la shell MySQL.
2. Eseguire il comando seguente per creare un altro amministratore del sistema DB:

   CREATE USER <admin_user_name> IDENTIFIED BY '<password>' DEFAULT ROLE 'administrator';
   GRANT PROXY ON ``@`` TO <admin_user_name> WITH GRANT OPTION; 

3. (Facoltativo) Eseguire il comando seguente per elencare i privilegi concessi al nuovo account utente:

   SHOW GRANTS FOR <admin_user_name> \G

   Viene visualizzato il seguente output:

   *************************** 1. row ***************************
   Grants for <admin_user_name>@%: GRANT USAGE ON *.* TO `<admin_user_name>`@`%` WITH GRANT OPTION
   *************************** 2. row ***************************
   Grants for <admin_user_name>@%: GRANT PROXY ON ``@`` TO `<admin_user_name>`@`%` WITH GRANT OPTION
   *************************** 3. row ***************************
   Grants for <admin_user_name>@%: GRANT `administrator`@`%` TO `<admin_user_name>`@`%`
   3 rows in set (0.00 sec)

4. (Facoltativo) Connettersi al sistema DB con le credenziali del nuovo amministratore utilizzando un client della riga di comando ed eseguire le operazioni riportate di seguito per elencare i privilegi.

   SHOW GRANTS \G

   Viene visualizzato il seguente output:

   *************************** 1. row ***************************
   Grants for <admin_user_name>@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
   PROCESS, 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 ROLE, DROP ROLE ON *.* 
   TO `<admin_user_name>`@`%` WITH GRANT OPTION
   *************************** 2. row ***************************
   Grants for <admin_user_name>@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,
   BACKUP_ADMIN,CONNECTION_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,
   FLUSH_USER_RESOURCES,OPTION_TRACKER_OBSERVER,REPLICATION_APPLIER,ROLE_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,
   TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `<admin_user_name>`@`%` WITH GRANT OPTION
   *************************** 3. row ***************************
   Grants for <admin_user_name>@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, 
   REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, 
   CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `<admin_user_name>`@`%`
   *************************** 4. row ***************************
   Grants for <admin_user_name>@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
   CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
   EVENT, TRIGGER ON `mysql_audit`.* FROM `<admin_user_name>`@`%`
   *************************** 5. row ***************************
   Grants for <admin_user_name>@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
   INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
   EVENT, TRIGGER ON `mysql_option`.* FROM `<admin_user_name>`@`%`
   *************************** 6. row ***************************
   Grants for <admin_user_name>@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
   CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
   EVENT, TRIGGER ON `sys`.* FROM `<admin_user_name>`@`%`
   *************************** 7. row ***************************
   Grants for <admin_user_name>@%: GRANT PROXY ON ``@`` TO `<admin_user_name>`@`%` WITH GRANT OPTION
   *************************** 8. row ***************************
   Grants for <admin_user_name>@%: GRANT `administrator`@`%` TO `<admin_user_name>`@`%`
   8 rows in set (0.00 sec)