MariaDB 用户及权限管理 -- cnDBA.cn

1 创建用户

1.1 直接创建

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql>  create user dave identified by 'dave';
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql
Database changed
mysql> select host,user,password from user where user='dave';
+------+------+-------------------------------------------+
| host | user | password                                  |
+------+------+-------------------------------------------+
| %    | dave | *C1D2517835013B47DB24695088326012F0EED75B |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)

这种方法创建的用户，可以从任何机器上访问mysql。

也可以在创建时指定能否访问的IP地址：http://www.cndba.cn/dave/article/2634

mysql> create user dave2@localhost identified by 'dave2';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,password from user where user='dave2';
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | dave2 | *758B40BF82B4807BB1818466A60336E31FEC59F8 |
+-----------+-------+-------------------------------------------+
1 row in set (0.00 sec)

这种创建方法没有任何权限, 需要单独的授权。

mysql> select *  from user where user='dave' /G;
*************************** 1. row ***************************
                  Host: %
                  User: dave
              Password: *C1D2517835013B47DB24695088326012F0EED75B
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: 
 authentication_string: 
      password_expired: N
               is_role: N
          default_role: 
    max_statement_time: 0.000000
1 row in set (0.00 sec)

1.2 授权时创建

在给用户授权时，如果用户不存在，会自动创建用户：
—所有权限：http://www.cndba.cn/dave/article/2634

mysql> grant all privileges on cndba.* to dave3@localhost identified by "cndba";
Query OK, 0 rows affected (0.00 sec)

—部分权限：http://www.cndba.cn/dave/article/2634

mysql> grant select,insert,update,delete on cndba.* to dave4@"%" identified by "cndba";
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,password from user where user in ('dave3', 'dave4');
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | dave3 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
| %         | dave4 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
+-----------+-------+-------------------------------------------+
2 rows in set (0.00 sec)

[root@Dave ~]# mysql -udave3 -pcndba
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 17
Server version: 5.5.5-10.2.12-MariaDB-log MariaDB Server

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql>

2 修改用户权限

2.1 查看当前的权限

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION       |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for dave;
+-----------------------------------------------------------------------------------------------------+
| Grants for dave@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dave'@'%' IDENTIFIED BY PASSWORD '*C1D2517835013B47DB24695088326012F0EED75B' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use information_schema
Database changed
mysql> select * from schema_privileges;
+---------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE             | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+---------------------+---------------+--------------+-------------------------+--------------+
| 'dave3'@'localhost' | def           | cndba        | SELECT                  | NO           |
| 'dave3'@'localhost' | def           | cndba        | INSERT                  | NO           |
| 'dave3'@'localhost' | def           | cndba        | UPDATE                  | NO           |
| 'dave3'@'localhost' | def           | cndba        | DELETE                  | NO           |
| 'dave3'@'localhost' | def           | cndba        | CREATE                  | NO           |
| 'dave3'@'localhost' | def           | cndba        | DROP                    | NO           |
| 'dave3'@'localhost' | def           | cndba        | REFERENCES              | NO           |
| 'dave3'@'localhost' | def           | cndba        | INDEX                   | NO           |
| 'dave3'@'localhost' | def           | cndba        | ALTER                   | NO           |
| 'dave3'@'localhost' | def           | cndba        | CREATE TEMPORARY TABLES | NO           |
| 'dave3'@'localhost' | def           | cndba        | LOCK TABLES             | NO           |
| 'dave3'@'localhost' | def           | cndba        | EXECUTE                 | NO           |
| 'dave3'@'localhost' | def           | cndba        | CREATE VIEW             | NO           |
| 'dave3'@'localhost' | def           | cndba        | SHOW VIEW               | NO           |
| 'dave3'@'localhost' | def           | cndba        | CREATE ROUTINE          | NO           |
| 'dave3'@'localhost' | def           | cndba        | ALTER ROUTINE           | NO           |
| 'dave3'@'localhost' | def           | cndba        | EVENT                   | NO           |
| 'dave3'@'localhost' | def           | cndba        | TRIGGER                 | NO           |
| 'dave4'@'%'         | def           | cndba        | SELECT                  | NO           |
| 'dave4'@'%'         | def           | cndba        | INSERT                  | NO           |
| 'dave4'@'%'         | def           | cndba        | UPDATE                  | NO           |
| 'dave4'@'%'         | def           | cndba        | DELETE                  | NO           |
+---------------------+---------------+--------------+-------------------------+--------------+
22 rows in set (0.00 sec)

2.2 授权

之前我们创建的用户dave4 是没有权限的，我们对这个用户授权：

mysql> select * from user where user like 'dave4' /G;
*************************** 1. row ***************************
                  Host: %
                  User: dave4
              Password: *220602FC23D5425FC4EDB4C570738DA574FB78C1
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
……

2.3 赋所有权限：

mysql>  select host,user,password from user where user like 'dave%';
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| %         | dave  | *C1D2517835013B47DB24695088326012F0EED75B |
| localhost | dave2 | *758B40BF82B4807BB1818466A60336E31FEC59F8 |
| localhost | dave3 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
| %         | dave4 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
+-----------+-------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> grant all on *.* to dave4@'%';
Query OK, 0 rows affected (0.00 sec)

这里是*.* 是代表所有的数据库和所有的表。

注意这里必须是dave4@'%'的格式，只有这种格式才能保证用户的唯一性。

mysql> use mysql
Database changed
mysql> select * from user where user like 'dave4' /G;
*************************** 1. row ***************************
                  Host: %
                  User: dave4
              Password: *220602FC23D5425FC4EDB4C570738DA574FB78C1
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
……

2.4 赋部分权限

mysql>  grant select,update on cndba.* to dave3@localhost ;
Query OK, 0 rows affected (0.00 sec)

2.5 删除权限

mysql> revoke all on *.* from dave4@'%';
Query OK, 0 rows affected (0.00 sec)

这里是所有权限，也可以回收部分权限。

mysql> select * from user where user like 'dave4' /G;
*************************** 1. row ***************************
                  Host: %
                  User: dave4
              Password: *220602FC23D5425FC4EDB4C570738DA574FB78C1
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
……

3 修改用户密码

--当前用户密码：
mysql> use mysql
Database changed
mysql> select host,user,password from user ;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  |                                           |
| %         | dave  | *C1D2517835013B47DB24695088326012F0EED75B |
| 127.0.0.1 | root  |                                           |
| ::1       | root  |                                           |
| localhost | dave2 | *758B40BF82B4807BB1818466A60336E31FEC59F8 |
| localhost | dave3 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
| %         | dave4 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
+-----------+-------+-------------------------------------------+
7 rows in set (0.00 sec)

3.1 通过授权修改

mysql> grant all privileges on cndba.* to dave3@"%" identified by "cndba2";
Query OK, 0 rows affected (0.00 sec)

3.2 通过update 修改

mysql> update user set password=PASSWORD("cndba") where user='dave3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

4 删除用户

mysql> select host,user,password from user where user like 'dave%';
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| %         | dave  | *C1D2517835013B47DB24695088326012F0EED75B |
| localhost | dave2 | *758B40BF82B4807BB1818466A60336E31FEC59F8 |
| localhost | dave3 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
| %         | dave4 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
| %         | dave3 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
+-----------+-------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> drop user dave3;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,password from user where user like 'dave%';
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| %         | dave  | *C1D2517835013B47DB24695088326012F0EED75B |
| localhost | dave2 | *758B40BF82B4807BB1818466A60336E31FEC59F8 |
| localhost | dave3 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
| %         | dave4 | *220602FC23D5425FC4EDB4C570738DA574FB78C1 |
+-----------+-------+-------------------------------------------+
4 rows in set (0.00 sec)

如果一个用户有多个host，那么可以通过如下方式来标识唯一性：
mysql> drop user dave3@”localhost”;

5 刷新用户权限

Mysql 的用户权限修改之后必须刷新才能生效。

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

签到成功

CNDBA社区

MariaDB 用户及权限管理

1 创建用户

1.1 直接创建

1.2 授权时创建

2 修改用户权限

2.1 查看当前的权限

2.2 授权

2.3 赋所有权限：

2.4 赋部分权限

2.5 删除权限

3 修改用户密码

3.1 通过授权修改

3.2 通过update 修改

4 删除用户

5 刷新用户权限

dave

QQ交流群

注册联系QQ

签到成功

CNDBA社区

MariaDB 用户及权限 管理

1 创建用户

1.1 直接创建

1.2 授权时创建

2 修改用户权限

2.1 查看当前的权限

2.2 授权

2.3 赋所有权限：

2.4 赋部分权限

2.5 删除权限

3 修改用户密码

3.1 通过授权修改

3.2 通过update 修改

4 删除用户

5 刷新用户权限

dave

QQ交流群

注册联系QQ

MariaDB 用户及权限管理