Manage MariaDB user Accounts

What users can connect to the MARIADB server, where they can connect, what they can do after connecting to the server, and so on, is what this article will involve.

The user's permission information is stored in several permission tables in the MySQL library. The account management of MariaDB is mainly done by the following SQL statements.

• CREATE user,drop user and RENAME user. Three statements are used to create, delete, and rename MariaDB accounts, respectively.
• GRANT. Assign permissions to the specified MariaDB account.
• REVOKE. Revoke the permissions of the specified MariaDB account.
• SET PASSWORD. Sets the password for a given mariadb account.
• SHOW GRANT. Displays all permissions currently owned by the specified MARIADB account.

MARIADB Account Management operations

• Create USER: Creates a new account and sets a password for it (optional action):
  CREATE USER account [identified by ' Password '];
  This statement simply creates the account, does not assign any permission to the account, and the permission assignment is done by the GRANT statement.
  Example:

  MariaDB [(None)]> CREATE USER Yangfan identified by ' Yangfan ';

• drop USER: Delete an account and all permissions associated with it:
  drop USER account;
  
  example:

   

    MariaDB [(none)]> DROP USER Yangfan;  

• RENAME USER: Change the name of the given account.
  RENAME USER From_account to To_account
  Example:

  MariaDB [(None)]> RENAME USER ' Yangfan.lalala ' @ ' 127.0.0.1 ' to ' yangfan.lalala ' @ '% ';

Accounts in account management statements such as CREATE user consist of a username and a hostname in the format ' user_name ' @ ' host_name '.

The host part refers to which hosts the customer connects to the server. To restrict a user from connecting to the MariaDB service from a specified host:

MariaDB [(None)]> CREATE USER ' yangfan ' @ ' localhost ' identified by ' Yangfan ';

MariaDB [(None)]> CREATE USER ' yangfan ' @ ' 169.254.195.91 ' identified by ' Yangfan ';

The first statement creates a user named Yangfan and restricts it from being connected to the computer only; In the second sentence, create a user named Yangfan and restrict it to connect to the service only from the IP 169.254.195.91 client. Although the user name is the same, in fact, they are not the same account, their corresponding permissions may vary.

Of course, in many cases it is not necessary to require users to connect to servers from only one host. You can use the wildcard character at this time:

MariaDB [(None)]> CREATE USER ' yangfan ' @ ' 169.254.195.% ' identified by ' Yangfan ';

MariaDB [(None)]> CREATE USER ' yangfan ' @ '%.yangfanweb.cn ' identified by ' Yangfan ';

MariaDB [(None)]> CREATE USER ' yangfan ' @ '% ' identified by ' Yangfan ';

The wildcard characters like in SQL are% and--and can be used here. If you use "%" or "-" itself, you need to use "\" to escape it.

The first statement above creates a user named Yangfan and allows it to connect to the server on all hosts under the 169.254.195.* IP segment, and the second statement creates a user named Yangfan and allows it to be enabled from the yangfanweb.cn Any host in the domain connects to the server; The third statement creates a user named Yangfan and allows it to connect to the server from any host. (The third way is most convenient, but the least secure for the system.) ）

MariaDB to account authorization

The GRANT statement is required for account authorization, and the following is the syntax of the GRANT statement:

GRANT Privileges (columns) on "to" [identified by ' password '][require encryption requirements] [with Grant or R esource management options];

In the GRANT statement, if the user exists, the GRANT statement changes its permissions, creates it if the grant statement does not exist, and assigns the given permission to it.

The following are some of the most common syntax elements of the GRANT statement.

• Privileges, permissions for authorized accounts.
• Columns, the data column to which the permission will function. If you need to enumerate multiple columns of data, separate them with commas.
• What, the level of permissions.
• Account, authorized accounts. The account format is ' user_name ' @ ' host_name '.
• Password, the password for the account. Similar to identied by in CREATE USER.

determine permissions for an account

There are many kinds of permissions that can be granted to a user. The following 3 tables are summaries of permissions.

Database Administration Permissions
Permission Name	Actions allowed by permissions
CREATE USER	Using Advanced Account Management statements
FILE	Read and write files on the MariaDB server host
GRANT OPTION	Grant account privileges to other accounts
PROCESS	To view information about a thread that is running
RELOAD	Reload permission data or update logs and caches
REPLICATION CLIENT	Querying where the primary/slave server is running
REPLICATION SLAVE	Running from the server with replication
SHOW datbases	View all database names with the show DATABASES statement
SHUTDOWN	Shutting down the server
SUPER	Terminating a thread with the KILL command and other Superuser actions

Database object Operation permissions
Alter	Change the definition of a data table or index
ALTER ROUTINE	Change or delete a stored function or stored procedure
CREATE	Create a database or data table
CRATE ROUTINE	Create a stored function or stored procedure
CREATE Temporary TABLE	Create a temporary table with the TEMPORARY keyword
CREATE VIEW	Create a View
DELETE	Delete existing data rows in the database
DROP	Delete a database, data table, or other object
EVENT	Create, delete, or modify various events for the time scheduler
EXECUTE	Executing a stored function or stored procedure
INDEX	Create or delete an index
INSERT	Insert a new row of data into a data table
LOCK TABLE	To explicitly lock a data table with a lock table statement
REFERENCE	Not used (reserved word)
SELECT	Retrieving data rows in the data table
SHOW VIEW	To view the definition of a view
Trgger	Create or delete a trigger
UPDATE	modifying data rows

Additional permissions
all [privileges]	All operation permissions (but not GRANT)
USAGE	A special "No Permissions" permission

Database management permissions control the operation of the server, so it is rarely authorized with ordinary users. Database object manipulation permissions control access to server data.

Grant user Permissions

To grant permissions to other users, the user who first issued the authorization must have that permission and must have the grant OPTION permission.

MariaDB allows authorization at various levels, such as global database systems, databases, data tables, data columns, and so on. The permission level is controlled by the ON clause.

Execute the following statement:

MariaDB [(None)]> grant all on test.* to ' yangfan ' @ ' localhost ' with GRANT OPTION;

The above statement authorizes the account ' yangfan ' @ ' localhost ' for all permissions on the database test and all objects inside it.

MariaDB [(None)]> SHOW GRANTS for ' yangfan ' @ ' localhost '; +------------------------------------------------------- ---------------------------------------------------------+| Grants for [email protected]                                                                                   |+------------------------------------------------------------------------------ ----------------------------------+| GRANT USAGE on *. yangfan ' @ ' localhost ' identified by PASSWORD ' *f6131bd316f9043f5fe0e5b95f5713d6f86a157f ' | | GRANT all privileges the ' test '. * to ' yangfan ' @ ' localhost ' with GRANT OPTION                                    |+------------------------------------- ---------------------------------------------------------------------------+

Execute the following statement:

MariaDB [(None)]> GRANT select,insert,update on test.* to ' Yangfan ' @ ' 127.0.0.1 ';

The above statement changes the permissions of the account ' Yangfan ' @ ' 127.0.0.1 ' to only the SELECT, INSERT, and UPDATE operations on the database test and all objects inside it.

MariaDB [(None)]> SHOW GRANTS for ' yangfan ' @ ' 127.0.0.1 '; +------------------------------------------------------- ---------------------------------------------------------+| Grants for [email protected]                                                                                   |+------------------------------------------------------------------------------ ----------------------------------+| GRANT USAGE on *. Yangfan ' @ ' 127.0.0.1 ' identified by PASSWORD ' *f6131bd316f9043f5fe0e5b95f5713d6f86a157f ' | | GRANT SELECT, INSERT, UPDATE on ' test '. * to ' yangfan ' @ ' 127.0.0.1 '                                              |+--------------------------------------------- -------------------------------------------------------------------+

Revoke user Rights

Revoke user authorization with the REVOKE statement, the following is the syntax for the REVOKE statement:

REVOKE preivileges [columns] on how from account;

Before we grant all permissions to the account ' yangfan ' @ ' localhost ', we now have permission to delete the account:

MariaDB [(None)]> REVOKE all on test.* from ' yangfan ' @ ' localhost ';

This allows the account ' yangfan ' @ ' localhost ' permission to the database test to be completely deleted.

MariaDB [(None)]> SHOW GRANTS for ' yangfan ' @ ' localhost '; +------------------------------------------------------- ---------------------------------------------------------+| Grants for [email protected]                                                                                   |+------------------------------------------------------------------------------ ----------------------------------+| GRANT USAGE on *. yangfan ' @ ' localhost ' identified by PASSWORD ' *f6131bd316f9043f5fe0e5b95f5713d6f86a157f ' | | GRANT USAGE on ' test '. * to ' yangfan ' @ ' localhost ' with Grant OPTION                                             |+--------------------------------------------- -------------------------------------------------------------------+

Note: USAGE is the "No permissions" permission mentioned above.

We will then remove the INSERT and UPDATE permissions for the account ' Yangfan ' @ ' 127.0.0.1 ':

MariaDB [(None)]> REVOKE INSERT, UPDATE on test.* from ' Yangfan ' @ ' 127.0.0.1 ';

View results:

MariaDB [(None)]> SHOW GRANTS for ' yangfan ' @ ' 127.0.0.1 '; +------------------------------------------------------- ---------------------------------------------------------+| Grants for [email protected]                                                                                   |+------------------------------------------------------------------------------ ----------------------------------+| GRANT USAGE on *. Yangfan ' @ ' 127.0.0.1 ' identified by PASSWORD ' *f6131bd316f9043f5fe0e5b95f5713d6f86a157f ' | | GRANT SELECT on ' test '. * to ' yangfan ' @ ' 127.0.0.1 '                                                              |+----------------------------------------------------------- -----------------------------------------------------+

Manage MariaDB user Accounts