LOCK TABLES 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 ability to execute LOCK TABLES.
This also requires the
SELECTprivilege.
Note that the tables are not actually locked by Xpand, as this privilege is primarily for compatibility.
DETAILS
Scope: Global, Database
Privilege name for
GRANT:LOCK TABLESPrivilege name for
REVOKE:LOCK TABLESPrivilege shown by
SHOW GRANTS:LOCK TABLES
MariaDB Xpand's LOCK TABLES privilege allows certain SQL statements to be executed:
However, although Xpand accepts LOCK TABLES and UNLOCK TABLES statements without error, Xpand does not actually lock or unlock the table.
EXAMPLES
GRANT
The following examples demonstrate grant of a single privilege. A single GRANT statement can grant multiple privileges at the same scope by providing a comma-separated list of the privileges.
To grant the LOCK TABLES privilege at global scope, replace the user specification ('USERNAME'@'HOSTNAME') in the following query to align to your requirements:
GRANT LOCK TABLES
ON *.*
TO 'USERNAME'@'HOSTNAME';
To grant the LOCK TABLES 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 LOCK TABLES
ON DATABASE_NAME.*
TO 'USERNAME'@'HOSTNAME';
REVOKE
The following examples demonstrate revoke of a single previously-granted privilege. A single REVOKE statement can revoke multiple privileges at the same scope by providing a comma-separated list of the privileges.
To revoke the LOCK TABLES privilege at global scope, replace the user specification ('USERNAME'@'HOSTNAME') in the following query to align to your requirements:
REVOKE LOCK TABLES
ON *.*
FROM 'USERNAME'@'HOSTNAME';
To revoke the LOCK TABLES 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 LOCK TABLES
ON DATABASE_NAME.*
FROM 'USERNAME'@'HOSTNAME';
SHOW Output
A user's privileges can be displayed using the SHOW GRANTS statement.
If the LOCK TABLES privilege is present, it will be shown as LOCK TABLES in the output. For example:
SHOW GRANTS FOR 'app_user'@'192.0.2.%';
+-----------------------------------------------------------+
| Grants for app_user@192.0.2.% |
+-----------------------------------------------------------+
| GRANT LOCK TABLES ON `app_db`.* TO 'app_user'@'192.0.2.%' |
+-----------------------------------------------------------+
Privilege Failure
The LOCK TABLES privilege results in no failures since it makes no changes in operation.