数据库编程4_（用户，角色）

1.登录账户管理

（1）创建登录账户（sql server）

[ execute ] sp_addlogin [ @loginame = ] 'login'

        [ , [ @ 'passwd' = ] 'password' ] [ , [@ defdb = ] 'database' ]

•  @loginame = 'login' : login指定登录名称
• @passwd= ‘password' : password指定登录密码，若不指定则默认为null
• @defdb = 'database' : database指定登录后用户访问的数据库，若不指定则默认的master数据库
• 在sp_addlogin中，除了登录名称之外，其余选项均为可选项。执行sp_addlogin时，必须具有相应的权限。只有数据库内置固定角色sysadmin和securityadmin的成员才能执行该系统存储过程。

例

创建登录账户login3，密码为’p123456',默认的数据库为scoreDB

sp_addlogin login3，'p123456'，'scoreDB'

(2)修改登录账户属性

修改登录账号的命令由；修改登录密码，修改默认的数据库和删除账号

sp_password [ [@old = ] 'old_password' ,] [ @new= ] 'new_password' [, [ @loginame= ] 'login' ]

例

将login3的密码修改为p654321

sp_password 'p123456' ,'p654321', 'login3'

修改默认的数据库语法：

sp_defaultdb [ @loginame= ] 'login'，[ @defdb= ] 'database‘

例

将login3 访问的数据库修改为BookDB

sp_defaultdb 'login3'，'BookDB'

删除登录账号语法：

sp_droplogin [ @loginame= ] 'login'

例

删除登录账号login3

sp_droplogin 'login3'

2.用户管理

（1）添加用户

sp_adduser [ @loginame = ] 'login' [,  [@name_in_db] 'user' ]

其中loginame为登录账号的名称，user指定数据库用户的名称

例

将登录账号login1添加到当前数据库orderDB中，且用户名为u1

sp_adduser login1，u1

（2）删除用户

sp_dropuser [ @ name_in_db = ] 'user'

例

从当前数据库中删除用户u1

sp_dropuser u1

3权限的授予回收

grant和revoke有2种权限：目标权限和命令权限

（1）命令权限的授予与回收

命令级的权限主要指DDL操作权限。命令权限的授予语句grant和回收语句revoke的语法分别为：

grant { all | <command_list> } to { pubilc | <username_list> }

revoke { all | <command_list> } from { pubilc | <username_list> }

• <command_list>可以是create database，create default，create function，create procedure，create rule，create table，create view，create index，backup database，backup log，等操作。一次可以授予多种权限，授予多种权限时，权限之间用逗号分隔。若用户具有创建对象的create权限，则自动拥有其创建对象的修改权限alter和删除权限drop。对于基本表，自动具有所创建表上创建，删除和修改触发器的权限。修改alter和删除drop权限不额外授权
• all：表示上述所有权限
• public：表示所有用户
• <username_list>：指定的用户名列表。如果将某组权限同时授予多个用户，则用户名之间用逗号隔开

例

将创建表和视图的权限授予用户u1和u2

grant create table,create view to u1,u2

从用户u2收回创建视图的权限

revoke create view from u2

（2）目标权限的授予和回收

目标权限主要指对对象的DML操作权限。对象权限的授予语句grant和回收语句revoke的语法分别为

grant { all | <command_list> } on <objectName> [ (< columnName-list >) ]

        to {public | <username_list > } [ with grant option ]

revoke { all | <command_list> } on <objectName> [ (< columnName-list >) ]

        from {public | <username_list > } [ cascade | restrict ]

• <command_list>可以是update,select,insert,delete,execute和all。execute针对存储过程授予执行权限，其他针对基本表和视图授权，all表示所有权限。
• cascade:级联回收（从一个用户/角色那里收回权限可能导致其他用户/角色也失去该权限）
• restrict:默认值，若转赋了权限，则不能回收
• with grant option :将指定对象上的目标权限授予其他安全账户的能力，但不允许循环授予（不允许将得到的权限授予其祖先）

 例

将存储过程proSearchBySno的执行权限授予用户u1，u2，u3

grant excute on proSearchBySno to u1,u2,u3

将对班级表Class的查询，插入权限授予用户u1，且用户u1可以转授其所获得的权限给其他用户

grant select,insert on Class to u1 with grant option

将对学生表的性别，出生日期的查询和修改权限授予u3，u4，u5

grant select ,update on Student(sex,birthday) to u3,u4,u5

当对列授予权限时，命令项（<command_list>）可以包括select或update或二者结合，而在select种若使用select*，则必须对表所有的列赋予select权限

4.数据库角色

        数据库角色是指被命名的一组与数据库操作相关的权限。角色是权限的集合，可以为一组具有相同权限的用户创建一个角色，角色简化了授权操作

（1）创建数据库角色

sp_addrole [ @ rolename = ] 'role'

只有数据库内置固定角色sysadmin,db_securityadmin及db_owner的成员才能执行该系统的存储过程、

例

创建角色r1

sp_addrole 'r1'

(2)删除数据库角色

sp_droprole [ @rolename= ] 'role'

例

删除数据库角色r2

sp_droprole 'r2'

（3）增加数据库角色成员

sp_addrolemember [ @ rolename = ] 'role', [ @membername= ] 'security_account'

• [ @rolename = ] 'role' : 当前数据库中的数据库角色的名称
• [ @membername = ] 'security_account':security_account 可以是数据库用户，数据库角色，windows登录或windows组

只有内置固定角色sysadmin 及 db_owner 的成员才能执行该系统的存储过程

例

将用户u2添加到数据库角色r1中

sp_addrolemember 'r1','u2'

(4)删除数据库角色

sp_droprolemember [ @ rolename = ] 'role', [ @membername= ] 'security_account'

例

将数据库角色r1中删除用户u2

sp_droprolemember 'r1','u2'

(5)给角色授权

grant {all | <command_list>} on <objectName> to <role_list>

(6)角色权限的回收

revoke { all | <command_list> } on <objectName> from <role_list>

(7)将角色授予其他的角色或用户

grant <role-list> to {<role_list> | <user_list>} [with admin option]

sql-server不适用，一般sql-server使用sp_addrolemember来增加数据库角色成员

（8）从角色或用户中回收角色

revoke <role> from { <role_list> | <user_list> }

sql-server不适用，一般sql-server使用sp_droprolemember来删除数据库角色成员

例

创建一个角色'Role1'

sp_addrole 'Role1'

使用grant语句，让角色拥有Student表中的select，update，insert权限

grant select,update,insert on Student to Role1

将角色赋予给用户u1,u2,u3让他们具有角色Role1中的所有权限

sp_addrolemember 'Role1','u1’

sp_addrolemember 'Role1','u2’

sp_addrolemember 'Role1','u3’

通过角色Role1可以一次性的收回用户u1的3个权限

sp_droprolemember 'Role1','u1'

收回角色Role1对表Student的查询权限

revoke select on Student from Role1