1、创建与删除用户:
创建用户语法:CREATE USER user_name [IDENTIFIED BY ‘user_password’];
MariaDB [(none)]> create user user1 identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select Host,User,Password from mysql.user where User='user1';
+------+-------+-------------------------------------------+
| Host | User | Password |
+------+-------+-------------------------------------------+
| % | user1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+-------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
用户重命名:
语法:RENAME USER old_username TO new_username;
MariaDB [(none)]> rename user user1 to aaa;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select Host,User,Password from mysql.user where User='aaa';
+------+------+-------------------------------------------+
| Host | User | Password |
+------+------+-------------------------------------------+
| % | aaa | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)
删除用户语法:DROP USER user_name;
MariaDB [(none)]> drop user user1;
Query OK, 0 rows affected (0.00 sec)
2、GRANT和REVOKE
GRANT和REVOKE命令用来管理访问权限,也可以用来创建和删除用户。GRANT和REROKE命令对于谁可以操作服务器及其内容的各个方面提供了多程度控制,从谁可以关闭服务器,到谁可以修改特定表字段的信息都可以控制。
具体使用:
MariaDB [(none)]> grant select,insert on aaa.* to 'user2'@'192.168.126.%' identified by 'redhat';
Query OK, 0 rows affected (0.00 sec) //授权user2用户select和insert权限,并指定可登录的网段;
MariaDB [(none)]> flush privileges; //刷新授权表;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for 'user2'@'192.168.126.%'; //查看user2用户的权限信息;
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.126.% |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.126.%' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT, INSERT ON `aaa`.* TO 'user2'@'192.168.126.%' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> revoke insert on aaa.* from 'user2'@'192.168.126.%'; //收回user2用户的insert权限;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for 'user2'@'192.168.126.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.126.% |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.126.%' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT ON `aaa`.* TO 'user2'@'192.168.126.%' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
注意:当用户使用GRANT和REVOKE命令更改用户权限后,退出MySQL系统,用户使用新账户名登录MySQL的时候,可能会因为没有刷新授权表而导致登录错误,这是因为在用户设置账号完毕后,只有重新加载授权表才能使之前设置的授权表生效。使用FLUSH PRIVILEGES命令可以重载授权表。另外需要注意的是,只有root这样拥有全部权限的用户才可以执行此命令。当用户重载授权表后,退出MySQL后,使用新创建的用户名即可正常登录MySQL。
3、授予权限的层级
1)全局层级:
全局权限使用于一个给定服务器中的所有数据库,存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销数据库权限。
2)数据库层级
数据库权限适用于一个给定数据库中的所有目标,存储在mysql.db和mysql.host表中。GRANT ALL ON dn_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
3)表层级
表权限适用于一个给定表中的所有列,存储在mysql.tables_priv表中。GRANT ALL ON db_name.tb1_name和REVOKE ALL ON db_name.tb1_name只授予和撤销表权限。
4)列层级
列权限适用于一个给定表中的单一列,这些权限存储在mysql.columns_priv表中。当使用REVOKE时,必须指定与被授权列相同的列。
5)子程序层级
CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且,除了CREATE ROUTINE外,这些权限可以被授予为子程序层级,并存储在mysql.procs_priv表中。
4、账户密码管理
1)可以用mysqladmin命令更改密码
[root@www ~]# mysqladmin -uroot password "redhat"
[root@www ~]# mysql -uroot -predhat -e "select Host,User,Password from mysql.user where User='root';"
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| www | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
+-----------+------+-------------------------------------------+
2)通过set password命令设置用户密码
MariaDB [(none)]> set password=password('123456');
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select Host,User,Password from mysql.user where User='root';
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| www | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
+-----------+------+-------------------------------------------+
4 rows in set (0.01 sec)
3)更改已有账户密码,应用UPDATE语句来设置password列值
MariaDB [(none)]> update mysql.user set Password=password('redhat') where User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [(none)]> select Host,User,Password from mysql.user where User='root';
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| www | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)