程序猿数据库学习指南

日期:2019-12-08编辑作者:数据库

一、数据库的基本认识

链接:https://www.cnblogs.com/baishuchao/articles/9157750.html

一、用户授权

mysql> grant all on *.* to 'test'@'%' identified by '123456';            
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all privileges on *.* to 'test'@'%' identified by '123456' with grant option;  
Query OK, 0 rows affected, 1 warning (0.01 sec)
  • all/all privileges:表示将所有权限授予给用户。也可指定具体的权限,如:select、creat、drop等。至于all与all privileges有什么区别,目前楼主还没有发现,待补
  • on:表示这些权限对哪些数据库和表生效,格式:数据库名.表名,这里写“*”表示所有数据库,所有表。如果要指定将权限应用到test库的user表中,可以这么写:test.user,如果要指定将权限应用到test库的所有表中,可以这么写:test.*
  • to:将权限授予哪个用户。格式:”用户名”@”登录IP或域名”。%表示没有限制,在任何主机都可以登录。比如:”tang”@”192.168.0.%”,表示tang这个用户只能在192.168.0IP段登录
  • identified by:指定用户的登录密码
  • with grant option:表示允许用户将自己的权限授权给其它用户(grant权限)。
    这里不做过多解释,具体详见:
    http://blog.csdn.net/dongdong9223/article/details/47445625
    https://www.cnblogs.com/aguncn/p/4313724.html

可以使用grant给用户添加权限,权限会自动叠加,不会覆盖之前授予的权限,比如你先给用户添加一个select权限,后来又给用户添加了一个insert权限,那么该用户就同时拥有了select和insert权限。

用户详情的权限列表请参考MySQL官网说明:http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

二、MySQL数据库介绍

链接:https://www.cnblogs.com/baishuchao/articles/9157764.html

二、刷新权限

对用户做了权限变更之后,一定记得重新加载一下权限,将权限信息从内存中写入数据库。尤其是你对那些权限表user、db、host等做了update或者delete更新的时候。以前遇到过使用grant后权限没有更新的情况,只要对权限做了更改就使用FLUSH PRIVILEGES命令来刷新权限。

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

三、MySQL数据库安装

链接:https://www.cnblogs.com/baishuchao/articles/9174371.html

三、查看用户权限

查看当前用户的权限:

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看某个用户的权限:

mysql> select host,user from user;
+--------------+---------------+
| host         | user          |
+--------------+---------------+
| localhost    | tang          |
+--------------+---------------+

mysql> show grants for wps;
+----------------------------------------------------+
| Grants for wps@%                                   |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'wps'@'%'                    |
| GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wps'@'%' |
+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for tang;
ERROR 1141 (42000): There is no such grant defined for user 'tang' on host '%'
mysql> show grants for 'tang'@'localhost';
+----------------------------------------------------------+
| Grants for tang@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tang'@'localhost'                 |
| GRANT ALL PRIVILEGES ON `discuz`.* TO 'tang'@'localhost' |
+----------------------------------------------------------+
2 rows in set (0.01 sec)

四、SQL之库操作

链接:https://www.cnblogs.com/baishuchao/articles/9174455.html

四、回收权限

删除test这个用户的create权限,该用户将不能创建数据库和表。

mysql> revoke create on *.* from 'test'@'localhost';              
Query OK, 0 rows affected (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> create database test02;
ERROR 1044 (42000): Access denied for user 'test'@'localhost' to database 'test02'

五、SQL之表操作

链接:https://www.cnblogs.com/baishuchao/articles/9174701.html

五、删除用户

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | test          |
+-----------+---------------+
2 rows in set (0.00 sec)

#删除用户
mysql> drop user 'test'@'localhost';
Query OK, 0 rows affected (0.05 sec)

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
+-----------+---------------+
1 rows in set (0.00 sec)

本文由www.129028.com金沙发布于数据库,转载请注明出处:程序猿数据库学习指南

关键词:

centos MySQL 5.5 源代码 cmake 安装

1.2 mysql安装方式介绍 1.rpm,yum:安装方便,安装速度快,无法定制(有依赖关系,安装依赖包) 2.二进制:不需要安装...

详细>>

mysql中列属性

非空约束 create table 表名( id int not null ); mysql列属性包括:NULL 、default、comment、primary key、unique key 唯一约束 create ta...

详细>>

MySQL 5.7基于GTID的主从复制www.129028.com金沙

  1 1.Check the necessary parameter is surely spedified. 2 (root@localhost mysql3306.sock)[(none)]04:04:00show variables like 'server_id'; 3 +--------...

详细>>

利用mysqlbinlog_flashback闪回丢失数据

        today,i'll have a test with the opensource tool mysqlbinlog_flashback which is released by 58daojia,here'sthe github address: https://githu...

详细>>