mysql> insert into users values ('lisi',PASSWORD('123123')); ##方法二
Query OK, 1 row affected (0.00 sec)
2、查询数据记录
mysql> select * from benet.users; ##查询表中所有列
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| zhangsan | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> select user_name,user_passwd from benet.users where user_name='zhangsan'; ##按条件查询
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| zhangsan | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
3、修改数据记录
mysql> update benet.users set user_passwd=PASSWORD('') WHERE user_name='lisi'; ##修改users表中zhangsan的密码为空
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from benet.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | |
| zhangsan | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
修改root用户密码
mysql> update mysql.user set password=PASSWORD('123456') WHERE user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> FLUSH PRIVILEGES; ##刷新用户授权,使其立即生效。
Query OK, 0 rows affected (0.00 sec)
mysql> EXIT
Bye
[root@localhost ~]# mysql -u root -p
Enter password: ##输入新密码“123456”
4、删除数据记录
mysql> delete from benet.users where user_name='lisi'; ##删除表中user_name列为‘zhangsan’的用户
Query OK, 1 row affected (0.00 sec)
mysql> select * from benet.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| zhangsan | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
数据库中默认添加了来宾用户和本地用户(user,password均为空),出去安全考虑,应该产出这些来宾用户,
mysql> select user,host,password from mysql.user where user=''; ##显示user列为空的用户数据
+------+-----------------------+----------+
| user | host | password |
+------+-----------------------+----------+
| | localhost | |
| | localhost.localdomain | |
+------+-----------------------+----------+
2 rows in set (0.00 sec)
mysql> delete from mysql.user where user=''; ##删除mysql.user表中user列为空的字段
Query OK, 2 rows affected (0.00 sec)
四、数据库用户授权
1、授予权限,使用“GRANT”子句,当制动用户不存在时,grant语句会创建新用户,否则,grant语句会修改用户信息。
格式:GRANT 权限列表 ON 库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY '密码' ]
权限列表:用于列出授权使用的各种数据库操作,以逗号进行分割,如“select,insert,update”。使用“all”表示所有授权,可执行任何操作。
库名.表名:用于指定授权操作的库和表的名称,其中可以使用通配符“*”。例如,使用“benet.*”。表示授权操作的对象为“benet”数据库中的所有表。
用户名@来源地址:用于指定的用户名称和允许访问的客户机地址,即谁能连接,从哪里连接。来源地址可以使域名,IP地址,还可以使用“%”通配符,
表示某个区域或网段内的所有地址,如“%。benet.com” “192.168.1.%”等等。
IDENTIFIED BY:用于设置用连接数据库时所使用的密码字符串,在新建用户时,若省略“IDENTIFIED BY”部分,则用户的密码将为空。
mysql> GRANT select on benet.* to 'xiaoming'@'localhost' IDENTIFIED BY '123123'; ##使用grant语句创建xiaoming用户
Query OK, 0 rows affected (0.01 sec) 并授予查询权限
2、查看权限
mysql> show grants for 'xiaoming'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for xiaoming@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoming'@'localhost' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |
| GRANT SELECT ON `benet`.* TO 'xiaoming'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3、撤销权限;REVOKE语句:用于撤销指定用户对数据库的权限,车修好后用户仍然可以连接数据库,单将被禁止执行对应数据库的操作。
格式:REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址
mysql> revoke all on benet.* from 'xiaoming'@'localhost'; ##撤销“xiaoming”对“benet.*”的所有权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'xiaoming'@'localhost'; ##确认已撤销对benet数据库的权限
+-----------------------------------------------------------------------------------------------------------------+
| Grants for xiaoming@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoming'@'localhost' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)``