关于Sql Sever中登录用户，用户，角色的理解

本文区分三个概念：

登录用户即访问数据库管理系统的账户，即使登陆上去，也只能作为 guest 去访问数据库的部分权限

而用户是实际去操作数据库的，根据权限不同他所操作的权限也不同

        角色其实就代表着权限，解释一下为什么要搞一个用户，一个权限去操作数据库，因为在现实生活当中，随着工作的调动，对数据库的权限也随着变动，这时只需要分配角色给用户即可，很方便，就好像一个职业一样。

=========================================================================实验代码

1.创建用户
①为采购、销售、客户管理等三个部门的经理创建用户标识，具有创建用户或者角色的权利。
create login David with password='david123',default_database=TPC
create user David for login David with default_schema=dbo
grant create role to David;
 
create login Tom with password='tom123',default_database=TPC
create user Tom for login Tom with default_schema=dbo
grant create role to Tom;
 
create login Kathy with password='kathy123',default_database=TPC
create user Kathy for login Kathy with default_schema=dbo
grant create role to Kathy;
②为采购、销售、客户管理等三个部门的职员创建用户标识和用户口令。
create login Jeffery with password='jeffery123',default_database=TPC
create user Jeffery for login Jeffery with default_schema=dbo;
 
create login Jane with password='jane123',default_database=TPC
create user Jane for login Jane with default_schema=dbo;
 
create login Mike with password='mike123',default_database=TPC
create user Mike for login Mike with default_schema=dbo;
 
2.创建角色并分配权限
①为各个部门分别创建一个查询角色，并分配查询权限。
CREATE ROLE PurchaseRole;--创建PurchaseRole角色
GRANT SELECT ON supplier TO PurchaseRole WITH GRANT OPTION;
GRANT SELECT ON part TO PurchaseRole WITH GRANT OPTION;
GRANT SELECT ON partsupp TO PurchaseRole WITH GRANT OPTION;
 
CREATE ROLE SaleRole;--创建SaleRole角色
GRANT SELECT ON orders TO SaleRole WITH GRANT OPTION;
GRANT SELECT ON lineitem TO SaleRole WITH GRANT OPTION;
 
CREATE ROLE CustomerRole;--创建CustomerRole角色
GRANT SELECT ON customer TO CustomerRole WITH GRANT OPTION;
GRANT SELECT ON nation TO CustomerRole WITH GRANT OPTION;
GRANT SELECT ON region TO CustomerRole WITH GRANT OPTION;
 
②为各个部门分别创建一个职员角色，对本部门的信息具有查看、插入权限。
CREATE ROLE PurchaseEmployeeRole;--创建PurchaseEmployeeRole角色
GRANT SELECT,INSERT ON supplier TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON part TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON partsupp TO PurchaseEmployeeRole;
 
CREATE ROLE SaleEmployeeRole;--创建SaleEmployeeRole角色
GRANT SELECT,INSERT ON orders TO SaleEmployeeRole;
GRANT SELECT,INSERT ON lineitem TO SaleEmployeeRole;
 
CREATE ROLE CustomerEmployeeRole;--创建CustomerEmployeeRole角色
GRANT SELECT,INSERT ON customer TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON nation TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON region TO CustomerEmployeeRole;
③为各部门创建一个经理角色，相应角色对本部门的信息具有完全控制权限，对其他部门信息具有查询权，经理有权给本部门职员分配权限。
CREATE ROLE PurchaseManagerRole;--创建PurchaseManagerRole角色
GRANT ALL ON supplier TO PurchaseManagerRole;
GRANT ALL ON part TO PurchaseManagerRole;
GRANT ALL ON partsupp TO PurchaseManagerRole;
exec sp_addrolemember 'SaleRole','PurchaseManagerRole';
exec sp_addrolemember 'CustomerRole','PurchaseManagerRole';
 
CREATE ROLE SaleManagerRole;--创建SaleManagerRole角色
GRANT ALL ON orders TO SaleManagerRole;
GRANT ALL ON lineitem TO SaleManagerRole;
exec sp_addrolemember 'PurchaseRole','SaleManagerRole';
exec sp_addrolemember 'CustomerRole','SaleManagerRole';
 
CREATE ROLE CustomerManagerRole;--创建CustomerManagerRole角色
GRANT ALL ON customer TO CustomerManagerRole;
GRANT ALL ON nation TO CustomerManagerRole;
GRANT ALL ON region TO CustomerManagerRole;
exec sp_addrolemember 'PurchaseRole','CustomerManagerRole';
exec sp_addrolemember 'SaleRole','CustomerManagerRole';
3.给用户分配权限
--①给各部门经理分配权限
exec sp_addrolemember 'PurchaseManagerRole','David';
exec sp_addrolemember 'SaleManagerRole','Tom';
exec sp_addrolemember 'CustomerManagerRole','Kathy';
②给各部门职员分配权限
exec sp_addrolemember 'PurchaseEmployeeRole','Jeffery' ;
exec sp_addrolemember 'SaleEmployeeRole','Jane' ;
exec sp_addrolemember 'CustomerEmployeeRole','Mike' ;
4.回收角色或用户权限
①回收客户经理角色的销售信息查看权限
exec sp_droprolemember 'SaleRole','CustomerManagerRole' ;
②回收MIKE的客户部门职员权限
exec sp_droprolemember 'CustomerEmployeeRole','Mike' ;
5.验证权限分配的正确性
①以David用户名登录数据库，验证采购部门经理的权限
SELECT *
FROM part;
SELECT *
FROM orders;
②回收MIKE的客户部门职员权限
SELECT *
FROM customer;
SELECT *
FROM part;