SQL语法之DCL

本文主要介绍MySQL语法之DCL(data control language)操作。

1.MySQL数据控制语言

用来定义数据库的访问权限和安全级别，及创建用户。包括GRANT、REVOKE等。

2.GRANT语句

# 创建用户和授权
mysql> create user 'jerry'@'localhost' identified by '123456';
mysql> grant select,insert on db1.emp to 'jerry'@'localhost';

# 直接授权
mysql> grant select,insert on db1.emp to 'jerry'@'localhost' identified by '123456' with grant option;
# with grant option表示允许用户将自己的权限授权给其它用户
mysql> flush privileges;

有哪些权限可以用于授权？

Privilege	Column	Context
ALL	Synonym for “all privileges”	Server administration
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for “no privileges”	Server administration

3.REVOKE语句

# 创建用户和授权
mysql> revoke select on db1.emp from 'jerry'@'localhost';
mysql> flush privileges;

4.查看权限

# 查看权限
mysql> show grants for 'jerry'@'localhost';

5.删除用户

mysql> drop user 'jerry'@'localhost';

6.创建用户授权

mysql> select host,user,password from mysql.user;			//mysql5.7之前
mysql> select host,user,authentication_string from mysql.user;		//mysql5.7之后
mysql> create user test identified by '123456';
mysql> grant all privileges on *.* to 'test'@'%'identified by '123456' with grant option; 
mysql> flush privileges;
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
mysql> ALTER USER USER() IDENTIFIED BY '12345678';
mysql> SET PASSWORD = PASSWORD('new password');
mysql> delete from mysql.user where User='test' and Host='localhost';

mysql> CREATE USER 'jerry'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE INTERVAL 180 DAY;
mysql> alter user 'tom'@'localhost' password expire interval 30 day;
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
mysql> set password for 'tom'@'localhost' =  '12345678';
mysql> ALTER USER 'tom'@'localhost' IDENTIFIED BY '654321';
mysql> update mysql.user set authentication_string=password('123456') where user='tom';
mysql> flush privileges;