1:登录到mysql数据库,查看func的表结构
C:\Users\Administrator>mysql -h localhost -u root -p mysql -e "desc func"
Enter password: ***
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint(1) | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
等价于:
C:\Users\Administrator>mysql -uroot -p mysql -e "desc func"
Enter password: ***
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint(1) | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
其他数据库也是一样:
C:\Users\Administrator>mysql -uroot -p basetest -e"select *from test"
Enter password: ***
+------+---------+
| a | name |
+------+---------+
| 10 | beijing |
| 25 | beijing |
| NULL | beijing |
+------+---------+
创建用户:
2:创建用户时指定非明文密码的流程:
mysql> select password('123');
+-------------------------------------------+
| password('123') |
+-------------------------------------------+
| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> create user 'test_1'@'localhost'identified by password '*23AE809DDACAF96AF0FD78ED04B6A265E05A
A257';
Query OK, 0 rows affected (0.01 sec)
这样使用create创建出来的用户是没有任何操作权限的,需要使用grant授予权限给test_1 ,参考如下
grant all privileges on*.* to 'test_1'@'localhost'
3:使用grant 创建用户
mysql> grant all privileges on*.* to 'test'@'localhost' identified by'123'
-> ;
Query OK, 0 rows affected (0.00 sec)
其实这样创建出来的用户是不在有grant权限了:参考如下信息:
File_priv: Y
Grant_priv: N
4:上面都是针对所有的库操作的,下面进行针对某个库:
mysql> grant all on basetest.* to 'test'@'localhost' identified by '123'
-> ;
Query OK, 0 rows affected (0.00 sec)
这样一来登录之后只能查看到的信息
C:\Users\Administrator>mysql -utest -p
Enter password: ***
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| basetest |
+--------------------+
2 rows in set (0.00 sec)
5:创建没有密码的账户,即是不用密码也可登录服务器:
mysql> create user 'test'@'localhost'
-> ;
Query OK, 0 rows affected (0.00 sec)
C:\Users\Administrator>mysql -utest
Welcome to the MySQL monitor. Commands end with ; or \g.
此时的操作:
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
6:貌似在mysql5.5版本上无法使用grant创建没有密码的用户,使用create倒是可以,所以只能曲线救国了,
先使用create创建没有密码的用户,再用grant授予相应的权限
7:不推荐使用如下方式修改账户密码,我曾经改过,导致无法再次进入mysql服务器
C:\Users\Administrator>mysqladmin -uroot -p password '123'
Enter password: ***
Warning: single quotes were not trimmed from the password by your command
line client, as you might have expected.
8:收回权限: revoke all on*.* from test@localhost;
9:查看某个用户权限信息:
mysql> select * from user where User='test_1' \G
10:将权限精确到basetest数据库的test表的sname字段上
mysql> grant select(name) on basetest.test to test_column@localhost identified by '123'
C:\Users\Administrator>mysql -h localhost -u root -p mysql -e "desc func"
Enter password: ***
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint(1) | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
等价于:
C:\Users\Administrator>mysql -uroot -p mysql -e "desc func"
Enter password: ***
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint(1) | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
其他数据库也是一样:
C:\Users\Administrator>mysql -uroot -p basetest -e"select *from test"
Enter password: ***
+------+---------+
| a | name |
+------+---------+
| 10 | beijing |
| 25 | beijing |
| NULL | beijing |
+------+---------+
创建用户:
2:创建用户时指定非明文密码的流程:
mysql> select password('123');
+-------------------------------------------+
| password('123') |
+-------------------------------------------+
| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> create user 'test_1'@'localhost'identified by password '*23AE809DDACAF96AF0FD78ED04B6A265E05A
A257';
Query OK, 0 rows affected (0.01 sec)
这样使用create创建出来的用户是没有任何操作权限的,需要使用grant授予权限给test_1 ,参考如下
grant all privileges on*.* to 'test_1'@'localhost'
3:使用grant 创建用户
mysql> grant all privileges on*.* to 'test'@'localhost' identified by'123'
-> ;
Query OK, 0 rows affected (0.00 sec)
其实这样创建出来的用户是不在有grant权限了:参考如下信息:
File_priv: Y
Grant_priv: N
4:上面都是针对所有的库操作的,下面进行针对某个库:
mysql> grant all on basetest.* to 'test'@'localhost' identified by '123'
-> ;
Query OK, 0 rows affected (0.00 sec)
这样一来登录之后只能查看到的信息
C:\Users\Administrator>mysql -utest -p
Enter password: ***
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| basetest |
+--------------------+
2 rows in set (0.00 sec)
5:创建没有密码的账户,即是不用密码也可登录服务器:
mysql> create user 'test'@'localhost'
-> ;
Query OK, 0 rows affected (0.00 sec)
C:\Users\Administrator>mysql -utest
Welcome to the MySQL monitor. Commands end with ; or \g.
此时的操作:
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
6:貌似在mysql5.5版本上无法使用grant创建没有密码的用户,使用create倒是可以,所以只能曲线救国了,
先使用create创建没有密码的用户,再用grant授予相应的权限
7:不推荐使用如下方式修改账户密码,我曾经改过,导致无法再次进入mysql服务器
C:\Users\Administrator>mysqladmin -uroot -p password '123'
Enter password: ***
Warning: single quotes were not trimmed from the password by your command
line client, as you might have expected.
8:收回权限: revoke all on*.* from test@localhost;
9:查看某个用户权限信息:
mysql> select * from user where User='test_1' \G
10:将权限精确到basetest数据库的test表的sname字段上
mysql> grant select(name) on basetest.test to test_column@localhost identified by '123'