mysql权限相关操作

默认帐号存储位置

位于mysql数据库中
位于user表

创建mysql帐号

需要登陆mysql
使用超级管理员登陆
只有超级管理员才有权限添加帐号

C:\Users\Administrator>mysql -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.67-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>    

创建一个帐号，用户名为ue，密码为123456

mysql> create user `ue`@`localhost` identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql>                                                                                                                             

使用新号登陆

C:\Users\Administrator>mysql -uue -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.67-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  

查看所有的数据库 新号

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> 

使用root管理员查看所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bfdb               |
| dbdao              |
| dbhui              |
| dbhuifu            |
| huifu3             |
| huifu4             |
| myschool           |
| mysql              |
| petshop            |
| tbone              |
| test               |
| testdb             |
+--------------------+
13 rows in set (0.01 sec)

mysql>   

小结
使用上述命令创建的帐号，是没有权限的
如果想要查操作数据库，需要给新号赋于权限才行

权限授与

给ue帐号添加权限，查询权限与插入权限
目标是宠物商店中的宠物主表
使用超级管理员root帐号来执行命令

mysql> grant insert,select on petshop.petowner to `ue`@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql>    

使用ue帐号再次查询数据库
关注被授权后的影响
结果，现在可以看到多个数据库了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

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

mysql>  

使用宠物商店数据库
查看有哪些表
发现：只能看得到有操作权限的表

mysql> use petshop;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_petshop |
+-------------------+
| petowner          |
+-------------------+
1 row in set (0.01 sec)

mysql> 

尝试查询宠物主人的数据

mysql> select * from petowner;
+----+-----------+----------+-------+
| id | name      | password | money |
+----+-----------+----------+-------+
|  1 | 小龙女    | long123  |   100 |
|  2 | 李莫仇    | mo123    |   160 |
|  3 | 郭芙      | fu123    |   300 |
|  4 | 阿土伯    | 123      |   800 |
+----+-----------+----------+-------+
4 rows in set (0.01 sec)

mysql>    

尝试着删除数据
由于当前用户没有删除权限
命令会被拒绝

mysql> delete from petowner where id = 3;
ERROR 1142 (42000): DELETE command denied to user 'ue'@'localhost' for table 'petowner'
mysql>    

查看指定用户的权限

查看ue用户的的权限

mysql> show grants for `ue`@`localhost`;
+-----------------------------------------------------------------------------------------------------------+
| Grants for ue@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ue'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT ON `petshop`.`petowner` TO 'ue'@'localhost'                                          |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>   

权限相关的操作

CREATE和DROP权限

INSERT、DELETE、SELECT和UPDATE权限

ALTER权限

密码相关操作

修改root密码

mysqladmin –u username –p password "newpassword"

超级管理员修改他人的密码

mysql> set password for `ua`@`localhost`=password("123");
Query OK, 0 rows affected (0.00 sec)

mysql>

登陆用户修改自己的密码
例子适用于超级管理员与普通用户
例子：设置自己的密码为 abc

mysql> set password = password("abc");
Query OK, 0 rows affected (0.00 sec)

删除用户

例子：删除用户名ua
查看全部可登陆用户

mysql> drop user `ua`@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,password from user;
+--------------+-------------------------------------------+
| user         | password                                  |
+--------------+-------------------------------------------+
| root         | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 |
| zs@localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root         | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 |
| ls           | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 |
| jinyu        | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 |
| zhangfei     | *AB1448C04823DEBA88736B75BFC0145C06AE89DD |
| teacher      | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| yingmu       | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| ub           | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| uc           | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ue           | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------------+-------------------------------------------+
11 rows in set (0.00 sec)

mysql>