数据库基础：sql语句之数据控制语句（DCL）

数据控制语句：用于控制不同的数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限权限和安全级别，主要包括关键字grant、revoke等。其主要是DBA用来管理系统中的对象权限时使用，一般开发人员很少使用。

一、创建用户

1.创建用户
create user 'username'@'lhost' identified by 'password'

-- username:你将创建的用户的用户名
-- host:指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%
-- identified by :确认关键字，后接密码
-- password :用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器

/*
举个例子：
create user 'lzh'@'localhost' identified by '123456';
create user 'lzh'@'%' identified by '123456';
create user 'lzh'@'%' identified by '';  等效于： create user 'lzh'@'%';
*/

二、授权

2.授权
grant privileges on dbname.tablename to 'username'@'host' identified by 'password' [with grant option]

-- privileges:用户的操作权限,如SELECT , INSERT , UPDATE 等（具体详见下表）,如果要授予所的权限,则使用all
-- dbname:数据库名，tablename:表名，如果是所有表的话，则dbname.*
-- with grant option:命令中不带这个，则，该用户username不能将权限授予其他人，反之，则可以

/* 
举个例子：
grant selelct on test.* to 'lzh'@'localhost' identified by '123456';
grant all on  *.* to 'lzh'@'%' with grant option;
*/

三、设置与更改密码

3.设置及更改密码
set password for 'username'@'host' = password('new_password')

-- 若是当前登录用户，可简写： set password = password ('new_password')

四、撤销用户权限

4.撤销用户权限
 revoke privilege on dbname.tablename from 'username'@'host'

-- privilege:同授权部分

五、删除用户

5.删除用户
drop user 'username'@'host'

附：

sql权限表：

Privilege	Grant Table Column	Context
all [privileges]	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