3.创制用户,3.创办用户

1.由此mysql数据库的user表查看用户相关新闻

MySQL创立数据库与成立用户以及授权,mysql数据库

1.经过mysql数据库的user表查看用户相关信息

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user,password from user ;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *87F2746835A04895BB77E12AA5054A767******* |
| qxyw      | root |                                           |
| 127.0.0.1 | root |                                           |
| localhost |      |                                           |
| qxyw      |      |                                           |
+-----------+------+-------------------------------------------+
5 rows in set (0.00 sec)

2.创办数据库

mysql> create database [databasename] default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

3.开立用户

mysql> create user 'dba'@'%' identified by '*******';
Query OK, 0 rows affected (0.00 sec)

user表中host列的值的意思 %              匹配所有主机 localhost  
 localhost不会被分析成IP地址,直接通过UNIXsocket连接 127.0.0.1    
 会通过TCP/IP协议连接,并且只可以在本机访问; ::1              
  ::1就是十分辅助ipv6的,表示同ipv4的127.0.0.1

4.对dba用户给予针对指定数据库实行增删改查的权限

mysql> grant select,insert,update,delete,create on [databasename].* to dba;
Query OK, 0 rows affected (0.00 sec)

留意:修改完权限将来 一定要刷新服务,或者重启服务,刷新服务用:FLUSH
PRIVILEGES

5.得以经过show
grants命令查看权限,若想要在原本的基本功上加码权限则继续执行grant

mysql> grant drop on [databasename].* to dba;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for dba;
+----------------------------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `[databasename]`.* TO 'dba'@'%'              |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

6.经过revoke命令可以移除用户的连带权限

mysql> revoke drop on [databasename].* from dba;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for dba;
+----------------------------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `[databasename]`.* TO 'dba'@'%'                    |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

http://www.bkjia.com/Mysql/1215804.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1215804.htmlTechArticleMySQL创建数据库与创建用户以及授权,mysql数据库
1.通过mysql数据库的user表查看用户相关新闻 mysql use mysqlReading table
information for completion of…

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *87F2746835A04895BB77E12AA5054A767******* |
| qxyw      | root |                                           |
| 127.0.0.1 | root |                                           |
| localhost |      |                                           |
| qxyw      |      |                                           |
+-----------+------+-------------------------------------------+
5 rows in set (0.00 sec)

2.创立数据库

mysql> create database [databasename] default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

3.成立用户

mysql> create user 'dba'@'%' identified by '*******';
Query OK, 0 rows affected (0.00 sec)

user表中host列的值的意思

%               匹配所有主机

localhost    localhost不会被分析成IP地址,直接通过UNIXsocket连接

127.0.0.1   会通过TCP/IP协议连接,并且不得不在本机访问;

::1              ::1就是同盟帮助ipv6的,表示同ipv4的127.0.0.1

4.对dba用户给予针对指定数据库举办增删改查的权杖

mysql> grant select,insert,update,delete,create on [databasename].* to dba;
Query OK, 0 rows affected (0.00 sec)

专注:修改完权限以后 一定要刷新服务,或者重启服务,刷新服务用:FLUSH
PRIVILEGES

5.足以透过show
grants命令查看权限,若想要在原先的基础上加码权限则继续执行grant

mysql> grant drop on [databasename].* to dba;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for dba;
+----------------------------------------------------------------------------------------------------+
| Grants for dba@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `[databasename]`.* TO 'dba'@'%'              |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

6.透过revoke命令能够移除用户的连锁权限

mysql> revoke drop on [databasename].* from dba;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for dba;
+----------------------------------------------------------------------------------------------------+
| Grants for dba@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `[databasename]`.* TO 'dba'@'%'                    |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

相关文章