SQL高级-账户管理

账户管理

• MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种：
  • 务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
  • 数据库级别账号：对特定数据库执行增删改查的所有操作
  • 数据表级别账号：对特定表执行增删改查等所有操作
  • 字段级别的权限：对某些表的特定字段进行操作
  • 存储程序级别的账号：对存储程序进行增删改查的操作
• 账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

创建用户

语法格式：

CREATE USER ‘username’ @ ‘host’ [IDENTIFIED] BY ‘passwd’
说明：
username：用户名
host：指定可以登录的主机，其中localhost表示本机，%表示所有主机
passwd：密码

代码示例

CREATE USER user1 @ '%' IDENTIFIED BY '123456';

修改用户

修改用户信息可以使用ALTER USER 语句，比如修改当前用户的密码
代码示例

ALTER USER USER() IDENTIFIED BY '12345678';

删除用户

语法格式：

DROP USER [IF EXISTS] user [, user] …

代码示例

DROP USER user1;

查看用户

• 所有用户及权限信息存储在mysql数据库的user表中，查看user表结构
  代码示例

  DESC USER;
  

• 查看所有用户
  代码示例

  select host,user,authentication_string from user;
  

  • 字段说明：
    • Host表示允许访问的主机
    • User表示用户名
    • authentication_string表示密码，为加密后的值

权限授予

• 创建账户、授权

  • 需要使用实例级账户登录后操作，以root为例

  • 常用权限主要包括：create、alter、drop、insert、update、delete、select

  • 如果分配所有权限，可以使用all privileges

  • 语法格式

    grant 权限列表 on 数据库 to ‘用户名’@‘访问主机’ identified by ‘密码’;

    注意： 使用GRANT进行授权时，如果该用户不存在，可以跟上GRANT语句后面跟上IDENTIFIED BY直接创建该用户

  • 查看用户权限

    show grants for 用户名@‘访问主机’;

• 代码示例
  创建一个zhangsan的账号，密码为123456，只能通过本地访问, 并且只能对mytest数据库中的所有表进行读操作

  • 第一步，登录root账户

    mysql -uroot -p
    Enter password: ****
    

  • 第二步，创建账号并授权

    grant select on mytest.* to 'zhangsan'@'localhost' identified by '123456';
    

  • 第三部，退出root

    quit
    

  • 第四部，登录zhangsan账户

    mysql -uzhangsan -p
    Enter password: ******
    

权限列表

权限	权限说明及作用的级别
ALTER[PRIVILEGES]	除了GRANT OPTION和PROXY之外，以指定的访问级别授予所有特权
ALTER	修改权限，作用于全局，数据库，数据表
ALTER_ROUTINE	修改存储过程，作用于全局，数据库，存储过程
CREATE	创建权限，作用于全局，数据库，数据表
CREATE_ROUTINE	创建存储过程的权限，作用于全局，数据库
CREATE_TABLESPACE	表空间和日志文件组的创建、更改、删除，全局权限
CREATE_TEMPORARY_TABLES	创建临时表的权限，作用于数据库，数据表
CREATE_USER	创建、删除，重命用和移除用户权限的权限，全局权限
	CREATE_VIEW
DELETE	删除数据权限，作用于全局，数据库，数据表
DROP	删除数据库、数据表、视图的权限，作用于全局，数据库，数据表
EVENT	使用事件的权限，作用于全局，数据库
EXECUTE	执行存储过程的权限，作用于全局，数据库，存储过程
FILE	读取或写入文件的权限，全局权限
GRANT_OPTION	允许授权或取消授权的权限，作用于全局，数据库，数据表，存储过程，代理
INDEX	使用索引的权限，作用于全局，数据库，数据表
INSERT	写入权限，作用于全局，数据库，数据表，数据列
LOCK_TABLES	在执行SELECT时可以启动LOCK_TABLES的权限，全局或数据库级别的权限
PROCESS	使用SHOW PROCESSLIST查询全部存储过程的权限，全局权限
PROXY	启用用户代理，作用级别从用户到用户
REFERENCES	创建外健权限，作用于全局，数据库，数据表，数据列
RELOAD	启动FLUSH操作，全局权限
REPLICATION CLIENT	使用户能够询问主服务器或从服务器在哪里，全局权限
REPLICATION SLAVE	启用复制从属服务器以从主服务器读取二进制日志事件,全局权限
SELECT	查询权限，作用于全局，数据库，数据表，数据列
SHOW_DATABASES	查询全部数据库，全局权限
SHOW_VIEWS	启用使用SHOW CREATE VIEW,作用于全局，数据库，数据表
SHUTDOWN	关闭数据库服务器权限，全局权限
SUPER	启用其他管理操作的使用，例如CHANGE MASTER TO，KILL，PURGE BINARY LOGS，SET GLOBAL和mysqladmin debug命令。 全局权限
TRIGGER	启用触发器的权限，作用于全局，数据库，数据表
UPDATE	更新权限，作用于全局，数据库，数据表，数据列
USAGE	无特权