Change Password: SET PASSWORD FOR 'sharqa'@'localhost' = PASSWORD('linux@'); Grant Access: GRANT ALL PRIVILEGES ON *.* to 'alexander'@'localhost' WITH GRANT OPTION; GRANT
Syntax GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [ user_options ...] user_specification: username [authentication_option] | PUBLIC authentication_option: IDENTIFIED BY 'password' | IDENTIFIED BY PASSWORD 'password_hash' | IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule ...] authentication_rule: authentication_plugin | authentication_plugin {USING|AS} 'authentication_string' | authentication_plugin {USING|AS} PASSWORD('password') GRANT PROXY ON username TO user_specification [, user_specification ...] [WITH GRANT OPTION] GRANT rolename TO grantee [, grantee ...] [WITH ADMIN OPTION] grantee: rolename username [authentication_option] user_options: [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] [WITH with_option [with_option] ...] object_type: TABLE | FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY priv_level: * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name with_option: GRANT OPTION | resource_option resource_option: MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count | MAX_STATEMENT_TIME time tls_option: SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'
Description The GRANT statement allows you to grant privileges or roles to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. Use the REVOKE statement to revoke privileges granted with the GRANT statement. Use the SHOW GRANTS statement to determine what privileges an account has.
Account Names For GRANT statements, account names are specified as the username argument in the same way as they are for CREATE USER statements. See account names from the CREATE USER page for details on how account names are specified.
Implicit Account Creation The GRANT statement also allows you to implicitly create accounts in some cases. If the account does not yet exist, then GRANT can implicitly create it. To implicitly create an account with GRANT, a user is required to have the same privileges that would be required to explicitly create the account with the CREATE USER statement. If the NO_AUTO_CREATE_USER SQL_MODE is set, then accounts can only be created if authentication information is specified, or with a CREATE USER statement. If no authentication information is provided, GRANT will produce an error when the specified account does not exist, for example: show variables like '%sql_mode%' ; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED BY ''; ERROR 1133 (28000): Can't find any matching row in the user table GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED VIA PAM using 'mariadb' require ssl ; Query OK, 0 rows affected (0.00 sec) select host, user from mysql.user where user='user123' ; +------+----------+ | host | user | +------+----------+ | % | user123 | +------+----------+
Privilege Levels Privileges can be set globally, for an entire database, for a table or routine, or for individual columns in a table. Certain privileges can only be set at certain levels. Global privileges do not take effect immediately and are only applied to connections created after the GRANT statement was executed.
| Privilege | Description |
|---|---|
| CREATE | Create a database using the CREATE DATABASE statement, when the privilege is granted for a database. You can grant the CREATE privilege on databases that do not yet exist. This also grants the CREATE privilege on all tables in the database. |
| CREATE ROUTINE | Create Stored Programs using the CREATE PROCEDURE and CREATE FUNCTION statements. |
| CREATE TEMPORARY TABLES | Create temporary tables with the CREATE TEMPORARY TABLE statement. This privilege enable writing and dropping those temporary tables |
| DROP | Drop a database using the DROP DATABASE statement, when the privilege is granted for a database. This also grants the DROP privilege on all tables in the database. |
| EVENT | Create, drop and alter EVENTs. |
| GRANT OPTION | Grant database privileges. You can only grant privileges that you have. |
| LOCK TABLES | Acquire explicit locks using the LOCK TABLES statement; you also need to have the SELECT privilege on a table, in order to lock it. |
| SHOW CREATE ROUTINE | Permit viewing the SHOW CREATE definition statement of a routine, for example SHOW CREATE FUNCTION, even if not the routine owner. From MariaDB 11.3.0. |
| Privilege | Description |
|---|---|
| ALTER | Change the structure of an existing table using the ALTER TABLE statement. |
| CREATE | Create a table using the CREATE TABLE statement. You can grant the CREATE privilege on tables that do not yet exist. |
| CREATE VIEW | Create a view using the CREATE_VIEW statement. |
| DELETE | Remove rows from a table using the DELETE statement. |
| DELETE HISTORY | Remove historical rows from a table using the DELETE HISTORY statement. Displays as DELETE VERSIONING ROWS when running SHOW GRANTS until MariaDB 10.3.15 and until MariaDB 10.4.5 (MDEV-17655), or when running SHOW PRIVILEGES until MariaDB 10.5.2, MariaDB 10.4.13 and MariaDB 10.3.23 (MDEV -20382). From MariaDB 10.3.4. From MariaDB 10.3.5, if a user has the SUPER privilege but not this privilege, running mariadb-upgrade will grant this privilege as well. |
| DROP | Drop a table using the DROP TABLE statement or a view using the DROP VIEW statement. Also required to execute the TRUNCATE TABLE statement. |
| GRANT OPTION | Grant table privileges. You can only grant privileges that you have. |
| INDEX | Create an index on a table using the CREATE INDEX statement. Without the INDEX privilege, you can still create indexes when creating a table using the CREATE TABLE statement if the you have the CREATE privilege, and you can create indexes using the ALTER TABLE statement if you have the ALTER privilege. |
| INSERT | Add rows to a table using the INSERT statement. The INSERT privilege can also be set on individual columns; see Column Privileges below for details. |
| REFERENCES | Unused. |
| SELECT | Read data from a table using the SELECT statement. The SELECT privilege can also be set on individual columns; see Column Privileges below for details. |
| SHOW VIEW | Show the CREATE VIEW statement to create a view using the SHOW CREATE VIEW statement. |
| TRIGGER | Required to run the CREATE TRIGGER, DROP TRIGGER, and SHOW CREATE TRIGGER statements. When another user activates a trigger (running INSERT, UPDATE, or DELETE statements on the associated table), for the trigger to execute, the user that defined the trigger should have the TRIGGER privilege for the table. The user running the INSERT, UPDATE, or DELETE statements on the table is not required to have the TRIGGER privilege. |
| UPDATE | Update existing rows in a table using the UPDATE statement. UPDATE statements usually include a WHERE clause to update only certain rows. You must have SELECT privileges on the table or the appropriate columns for the WHERE clause. The UPDATE privilege can also be set on individual columns; see Column Privileges below for details. |
| Privilege | Description |
|---|---|
| INSERT (column_list) | Add rows specifying values in columns using the INSERT statement. If you only have column-level INSERT privileges, you must specify the columns you are setting in the INSERT statement. All other columns will be set to their default values, or NULL. |
| REFERENCES (column_list) | Unused. |
| SELECT (column_list) | Read values in columns using the SELECT statement. You cannot access or query any columns for which you do not have SELECT privileges, including in WHERE, ON, GROUP BY, and ORDER BY clauses. |
| UPDATE (column_list) | Update values in columns of existing rows using the UPDATE statement. UPDATE statements usually include a WHERE clause to update only certain rows. You must have SELECT privileges on the table or the appropriate columns for the WHERE clause. |
| Privilege | Description |
|---|---|
| ALTER ROUTINE | "gChange the characteristics of a stored function using the ALTER FUNCTION statement. |
| EXECUTE | "gUse a stored function. You need SELECT privileges for any tables or columns accessed by the function. |
| GRANT OPTION | "gGrant function privileges. You can only grant privileges that |
| Privilege | Description |
|---|---|
| ALTER ROUTINE | Change the characteristics of a stored procedure using the ALTER PROCEDURE statement. |
| EXECUTE | Execute a stored procedure using the CALL statement. The privilege to call a procedure may allow you to perform actions you wouldn't otherwise be able to do, such as insert rows into a table. |
| GRANT OPTION | Grant procedure privileges. You can only grant privileges that you have. |
| Privilege | Description |
|---|---|
| PROXY | Permits one user to be a proxy for another. |
| Limit Type | Decription |
|---|---|
| MAX_QUERIES_PER_HOUR | Number of statements that the account can issue per hour (including updates) |
| MAX_UPDATES_PER_HOUR | Number of updates (not queries) that the account can issue per hour |
| MAX_CONNECTIONS_PER_HOUR | Number of connections that the account can start per hour |
| MAX_USER_CONNECTIONS | Number of simultaneous connections that can be accepted from the same account; if it is 0, max_connections will be used instead; if max_connections is 0, there is no limit for this account's simultaneous connections. |
| MAX_STATEMENT_TIME | Timeout, in seconds, for statements executed by the user. See also Aborting Statements that Exceed a Certain Time to Execute. |
| Option | Description |
|---|---|
| REQUIRE NONE | TLS is not required for this account, but can still be used. |
| REQUIRE SSL | The account must use TLS, but no valid X509 certificate is required. This option cannot be combined with other TLS options. |
| REQUIRE X509 | The account must use TLS and must have a valid X509 certificate. This option implies REQUIRE SSL. This option cannot be combined with other TLS options. |
| REQUIRE ISSUER 'issuer' | The account must use TLS and must have a valid X509 certificate. Also, the Certificate Authority must be the one specified via the string issuer. This option implies REQUIRE X509. This option can be combined with the SUBJECT, and CIPHER options in any order. |
| REQUIRE SUBJECT 'subject' | The account must use TLS and must have a valid X509 certificate. Also, the certificate's Subject must be the one specified via the string subject. This option implies REQUIRE X509. This option can be combined with the ISSUER, and CIPHER options in any order. |
| REQUIRE CIPHER 'cipher' | The account must use TLS, but no valid X509 certificate is required. Also, the encryption used for the connection must use a specific cipher method specified in the string cipher. This option implies REQUIRE SSL. This option can be combined with the ISSUER, and SUBJECT options in any order. |