ALL PRIVILEGES Privilege
This page is part of MariaDB's Documentation.
The parent of this page is: Privileges for MariaDB Xpand
Topics on this page:
Overview
Grants all available privileges at the given privilege scope.
As an example of scope, granting all privileges on a particular table does not grant any privileges on the table's database scope.
DETAILS
Scope: Global, Database, Table, Routine
Privilege name for
GRANT:ALL PRIVILEGESPrivilege name for
REVOKE:ALL PRIVILEGESPrivilege shown by
SHOW GRANTS:ALL PRIVILEGES
EXAMPLES
GRANT
To grant the ALL PRIVILEGES privilege at global scope, replace the user specification ('USERNAME'@'HOSTNAME') in the following query to align to your requirements:
GRANT ALL PRIVILEGES
ON *.*
TO 'USERNAME'@'HOSTNAME';
To grant the ALL PRIVILEGES privilege at database scope, replace the user specification ('USERNAME'@'HOSTNAME') and database name (DATABASE_NAME) in the following query to align to your requirements:
GRANT ALL PRIVILEGES
ON DATABASE_NAME.*
TO 'USERNAME'@'HOSTNAME';
To grant the ALL PRIVILEGES privilege at table scope, replace the user specification ('USERNAME'@'HOSTNAME'), database name (DATABASE_NAME), and table name (TABLE_NAME) in the following query to align to your requirements:
GRANT ALL PRIVILEGES
ON DATABASE_NAME.TABLE_NAME
TO 'USERNAME'@'HOSTNAME';
To grant the ALL PRIVILEGES privilege at routine scope, replace the user specification ('USERNAME'@'HOSTNAME'), database name (DATABASE_NAME), and routine name (ROUTINE_NAME) in the following query to align to your requirements:
GRANT ALL PRIVILEGES
ON DATABASE_NAME.ROUTINE_NAME
TO 'USERNAME'@'HOSTNAME';
REVOKE
To revoke the ALL PRIVILEGES privilege at global scope, replace the user specification ('USERNAME'@'HOSTNAME') in the following query to align to your requirements:
REVOKE ALL PRIVILEGES
ON *.*
FROM 'USERNAME'@'HOSTNAME';
To revoke the ALL PRIVILEGES privilege at database scope, replace the user specification ('USERNAME'@'HOSTNAME') and database name (DATABASE_NAME) in the following query to align to your requirements:
REVOKE ALL PRIVILEGES
ON DATABASE_NAME.*
FROM 'USERNAME'@'HOSTNAME';
To revoke the ALL PRIVILEGES privilege at table scope, replace the user specification ('USERNAME'@'HOSTNAME'), database name (DATABASE_NAME), and table name (TABLE_NAME) in the following query to align to your requirements:
REVOKE ALL PRIVILEGES
ON DATABASE_NAME.TABLE_NAME
FROM 'USERNAME'@'HOSTNAME';
To revoke the ALL PRIVILEGES privilege at routine scope, replace the user specification ('USERNAME'@'HOSTNAME'), database name (DATABASE_NAME), and routine name (ROUTINE_NAME) in the following query to align to your requirements:
REVOKE ALL PRIVILEGES
ON DATABASE_NAME.ROUTINE_NAME
FROM 'USERNAME'@'HOSTNAME';
SHOW Output
A user's privileges can be displayed using the SHOW GRANTS statement.
If the ALL PRIVILEGES privilege is present, it will be shown as ALL PRIVILEGES in the output. For example:
SHOW GRANTS FOR 'app_user'@'192.0.2.%';
+---------------------------------------------------------------------+
| Grants for app_user@192.0.2.% |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'192.0.2.%' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'app_user'@'192.0.2.%' |
| GRANT ALL PRIVILEGES ON `db1`.`customers` TO 'app_user'@'192.0.2.%' |
+---------------------------------------------------------------------+
Privilege Failure
The ALL PRIVILEGES privilege results in no failures.