数据控制语句:用于控制不同的数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限权限和安全级别,主要包括关键字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 |