Shulou(Shulou.com)06/01 Report--

Root > mysql-p

Account management operation of Mariadb

CREATE USER: create a new account and set a password for it (optional):

CREATE USER account [IDENTIFIED BY 'password']

This statement only creates an account without any permission assignment to the account, which is done by the GRANT statement.

Example:

MariaDB [(none)] > CREATE USER angel IDENTIFIED BY 'angel'

DROP USER: delete an account and all permissions associated with it:

DROP USER account

This statement does not delete any databases and related data objects that the specified user can access.

Example:

MariaDB [(none)] > DROP USER angel

RENAME USER: change the name of a given account.

RENAME USER from_account TO to_account

Example:

MariaDB [(none)] > RENAME USER 'angel.t1' @ '127.0.1' TO 'angel.t1 @'%'

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

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

MariaDB [(none)] > CREATE USER 'angel' @ 'localhost' IDENTIFIED BY' angel'

MariaDB [(none)] > CREATE USER 'angel' @'1 0.1.1.1 'IDENTIFIED BY' angel'

The first statement creates a user named angel and restricts it to connect only from the local machine; the second sentence creates a user named angel and restricts it to connect to the service only from the client of ip 10.1.1.1. Although the user names are the same, in fact, they are not the same account, and their corresponding permissions may not be the same.

Of course, in many cases, there is no strict requirement that users can only connect to the server from one host. At this point, you can use wildcards:

MariaDB [(none)] > CREATE USER 'angel' @ '10.1.1.1.%' IDENTIFIED BY 'angel'

MariaDB [(none)] > CREATE USER 'angel' @'%. Angel web.cn' IDENTIFIED BY 'angel'

MariaDB [(none)] > CREATE USER 'angel' @'% 'IDENTIFIED BY' angel'

The wildcards% and-of LIKE in SQL can be used here. If you use "%" or "-" itself, you need to escape it with "\".

The first statement above creates a user named angel and allows all hosts under the 10.1.1.1.* ip segment to connect to the server; the second statement creates a user named angel and allows it to connect to the server from any host in the angel web.cn domain; and the third statement creates a user named angel and allows it to connect to the server from any host. (the third way is the most convenient, but the least secure for the system. )

MariaDB authorizes the account

Grant statement is required for account authorization. Here is the syntax of the GRANT statement:

GRANT privileges (columns) ON what To account [IDENTIFIED BY 'password'] [REQUIRE encryption requirements] [WITH grant or resource management options]

In the GRANT statement, the GRANT statement changes its permissions if the user exists, creates it if no GRANT statement exists, and assigns the given permissions to it.

Here are some of the most commonly used syntax elements for GRANT statements.

Privileges, the permissions of the authorized account.

Columns, the data column that the permission will act on. If you need to enumerate multiple data columns, separate them with commas.

What, the level of permission.

Account, authorized account. The format of account is' user_name'@'host_name'.

Password, password for the account. Similar to IDENTIED BY in CREATE USER.

Determine the authority of the account

There are many kinds of permissions that can be granted to users. The following three tables are a summary of permissions.

Database management authority

Actions allowed by permissions name permissions

CREATE USER uses advanced account management statements

FILE reads and writes files on the MariaDB server host

GRANT OPTION grants account permissions to other accounts

PROCESS to view information about running threads

RELOAD reloads permission data or updates logs and caches

REPLICATION CLIENT queries the running location of the master / slave server

REPLICATION SLAVE runs as a replicated slave server

SHOW DATBASES uses the SHOW DATABASES statement to view all database names

SHUTDOWN shuts down the server

SUPER uses the KILL command to terminate threads and perform other superuser operations

Database object operation permissions

ALTER changes the definition of a data table or index

ALTER ROUTINE changes or deletes a stored function or stored procedure

CREATE creates a database or data table

CRATE ROUTINE creates a stored function or stored procedure

CREATE TEMPORARY TABLE creates temporary tables with the TEMPORARY keyword

CREATE VIEW creates a view

DELETE deletes existing rows in the database

DROP deletes a database, data table, or other object

EVENT creates, deletes, or modifies various events for the time scheduler

EXECUTE executes stored functions or stored procedures

INDEX creates or deletes an index

INSERT inserts a new data row into the data table

LOCK TABLE explicitly locks the data table with the LOCK TABLE statement

REFERENCE not used (reserved words)

SELECT retrieves rows of data in a data table

SHOW VIEW view the definition of the view

TRGGER creates or deletes triggers

UPDATE modifies data rows

Other permissions

ALL [PRIVILEGES] all operation permissions (except GRANT)

A special "No permission" permission for USAGE

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

Grant user permissions

In order to grant permissions to other users, the user who first issued the authorization must have that permission and must have GRANT OPTION privileges.

MariaDB allows authorization at many levels, such as database system global, database, data table, data column, and so on. The permission level is controlled by the ON clause.

Execute the following statement:

MariaDB [(none)] > GRANT ALL ON test.* TO 'angel'@'localhost' WITH GRANT OPTION

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

MariaDB [(none)] > SHOW GRANTS FOR 'yangfan'@'localhost'

+-+

| | Grants for angel @ localhost |

+-+

| | GRANT USAGE ON *. * TO 'angel'@'localhost' IDENTIFIED BY PASSWORD' * F6131BD316F9043F5FE0E5B95F5713D6F86A157F' |

| | GRANT ALL PRIVILEGES ON `test`.* TO 'angel'@'localhost' WITH GRANT OPTION |

+-+

Execute the following statement:

MariaDB [(none)] > GRANT SELECT,INSERt,UPDATE ON test.* TO 'angel'@'127.0.0.1'

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

MariaDB [(none)] > SHOW GRANTS FOR 'yangfan'@'127.0.0.1'

+-+

| | Grants for angel@127.0.0.1 |

+-+

| | GRANT USAGE ON *. * TO 'yangfan'@'127.0.0.1' IDENTIFIED BY PASSWORD' * F6131BD316F9043F5FE0E5B95F5713D6F86A157F' |

| | GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'angel'@'127.0.0.1' |

+-+

Revoke user rights

The REVOKE statement is used to revoke user authorization. The following is the syntax of the REVOKE statement:

REVOKE preivileges [columns] ON what FROM account

Previously, we granted full permissions to the account 'yangfan'@'localhost',. Now we delete the permissions on the account:

MariaDB [(none)] > REVOKE ALL ON test.* FROM 'yangfan'@'localhost'

In this way, the permissions of the account 'yangfan'@'localhost' to the database test are completely deleted.

MariaDB [(none)] > SHOW GRANTS FOR 'yangfan'@'localhost'

+-+

| | Grants for angel@localhost |

+-+

| | GRANT USAGE ON *. * TO 'angel'@'localhost' IDENTIFIED BY PASSWORD' * F6131BD316F9043F5FE0E5B95F5713D6F86A157F' |

| | GRANT USAGE ON `test`.* TO 'angel'@'localhost' WITH GRANT OPTION |

+-+

Note: USAGE is the "no permission" permission mentioned above.

We then delete the INSERT and UPDATE permissions of the account 'yangfan'@'127.0.0.1':

MariaDB [(none)] > REVOKE INSERT, UPDATE ON test.* FROM 'yangfan'@'127.0.0.1'

View the results:

MariaDB [(none)] > SHOW GRANTS FOR 'yangfan'@'127.0.0.1'

+-+

| | Grants for angel@127.0.0.1 |

+-+

| | GRANT USAGE ON *. * TO 'angel'@'127.0.0.1' IDENTIFIED BY PASSWORD' * F6131BD316F9043F5FE0E5B95F5713D6F86A157F' |

| | GRANT SELECT ON `test`.* TO 'angel'@'127.0.0.1' |

+-+

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.