登录、用户、角色、权限

admin管理员组

文章数量:1534545

2024年1月25日发(作者：)

1. 管理和维护登录名技术

1.1 SQL Server的身份验证模式

用户想操作SQL Server中某一数据库中的数据，必须满足以下3个条件：

首先，登录SQL Server服务器时必须通过身份验证；其次，必须是该数据库的用户或者是某一数据库角色的成员；最后，必须有执行该操作的权限。由此可看SQL Server数据库的安全性检查是通过登录名、用户、权限来完成的。

1.1.1 Windows 身份验证模式

当用户通过Windows用户帐户进行连接时，SQL Server通过回叫Windows 以获得信息，重新验证帐户名和密码，并在ins系统视图中查找该帐户，确定该帐户是否有权限登录。在这种方式下，用户不必提供登录名和密码让SQL server验证。

1.1.2混合验证模式

混合验证模式使用户能够通过Windows身份验证或SQL Server身份验证与SQL Server实例连接。在SQL Server验证模式下，SQL Server在ins系统视图中检测输入的登录名和密码。如果在ins视图中存在该登录名，并且密码也是匹配的，那么该登录名可以登录到SQL Server；否则，登录失败。在这种方式下，用户必须提供登录名和密码，让SQL server验证。

1.1.3设置验证模式

可以使用SQL Server Management Studio来设置或改变验证模式。当使用SQL Server

Management Studio时，应遵循以下步骤：

(1)打开SQL Server Management Studio，在“对象资源管理器”中，右击需要修改验证模式的服务器，再单击快捷菜单中的“属性”选项，出现服务器属性对话框，在服务器属性对话框中单击“安全性” 选择页。

(2)如果想仅使用Windows身份验证，选择“Windows身份验证模式”；如果想使用混合认证模式，选择“SQL Server和Windows身份验证模式”。

(3)在“登录审核”中设置是否对用户登录SQL Server 2005服务器的情况进行审核，即是否将登录成功和失败的信息写入SQL Server错误日志中。

1.2 登录账户管理

1.2.1系统安装时创建的登录账户

SQL Server 2005安装好之后，系统会自动产生一些系统内置登录账户。

本地管理员组（BUILTAdministrators）:默认属于sysadmin角色中的成员，因此具有管理员权限。

系统管理员 (sa):默认情况下，它指派给固定服务器角色 sysadmin，并不能进行更改。

1.2.2创建登录账户

1.2.2.1使用SQL Server Management Studio添加Windows登录账户

注意：授权一个Windows用户或组访问SQL Server，必须以这个用户登录到Windows后才能验证这个用户能否联接到SQL Server。授权用户或组访问SQL Server时，此Windows用户和组必须事先存在。

1.2.2.2．使用SQL Server Management Studio添加SQL Server登录账户

1.2.2.3．使用CREATE LOGIN语句创建登录账户。

CREATE LOGIN login_name { WITH | FROM }

例11.1 创建一个使Windows用户HBSIZangSan得以连接到SQL Server 的登录账户。

CREATE LOGIN [HBSIZhangSan] FROM WINDOWS

例11.2 创建一个SQL Server 登录，登录名为lisi并指定密码abcd。

CREATE LOGIN lisi WITH PASSWORD = 'abcd '

1.2.3 修改登录账户

(1)使用SQL Server Management Studio修改登录账户的属性

在SQL Server Management Studio的“对象资源管理器”中，展开服务器下的“安全性”节点，展开“登录名”，右击需要修改的登录名，在快捷菜单中选择“属性”，弹出“登录属性”对话框。在此对话框中进行相应的属性修改，如默认数据库、默认语言、密码等，还可以对账户的状态属性进行修改。

(2)使用ALTER LOGIN语句修改登录账户属性

ALTER LOGIN login_name { | WITH [ ,... ]}

例11.3 将lisi登录账户名称更改为lisi_new。

ALTER LOGIN lisi WITH NAME=lisi_new

例11.4 将lisi_new 登录账户的密码修改为“abcdef”。

ALTER LOGIN lisi_new WITH PASSWORD=’abcdef’

1.2.4 删除登录账户

(1)使用SQL Server Management Studio删除登录账户

(2)使用DROP LOGIN 语句删除登录账户

DROP LOGIN login_name

例11.5 删除登录账户lisi_new。

DROP LOGIN lisi_new

2．数据库用户管理

2.1 默认数据库用户

2.1.1．数据库所有者 (DataBase Owner ,dbo)

dbo 是数据库的所有者，拥有数据库中的所有对象，每个数据库都有dbo, sysadmin服务器角色的成员自动映射成dbo, 无法删除 dbo 用户，且此用户始终出现在每个数据库中。通常，登录名sa映射为库中的用户dbo。另外，由固定服务器角色 sysadmin 的任何

成员创建的任何对象都自动属于dbo。

2.1.2．Guest用户

Guest 用户帐户允许没有用户帐户的登录名访问数据库。当登录名没有被映射到一个用户名上时，如果在数据库中存在Guest用户，登录名将自动映射成Guest，并获得相应的数据库访问权限。Guest用户可以和其他用户一样设置权限，不能删除Guest用户，但可在除master和tempdb之外的任何数据库中禁用Guest用户。

2.1.3．Information_schema和sys用户

每个数据库中都包含Information_scheme和sys用户，它们出现在目录视图中。使用它们获取有关数据库的元数据信息。

2.2创建数据库用户

2.2.1．使用SQL Server Management Studio添加数据库用户

2.2.2．使用CREATE USER语句添加数据库用户

CREATE USER user_name

{ FOR | FROM } LOGIN login_name

例11.7在AdventureWorks数据库中创建数据库用户zhangsan，其登录名为zhangsan 。

Use AdventureWorks

Go

CREATE USER zhangsan FROM LOGIN zhangsan

3. 固定服务器角色的特点和管理

3.1角色管理

角色是为了易于管理而按相似的工作属性对用户进行分组的一种方式。在SQL Server中，组是通过角色来实现的。在SQL Server中，角色分为服务器角色和数据库角色两种。服务器角色是服务器级别的一个对象，只能包含登录名。数据库角色是数据库级别的一个对象，只能包含数据库用户名。

3.1.1 固定服务器角色

固定服务器角色：Sysadmin、Serveradmin、Setupadmin、Securityadmin、Processadmin、Dbcreator、Diskadmin、bulkadmin。

3.1.1.1使用SQL Server Management Studio将登录账户添加到固定服务器角色

3.1.1.2使用存储过程sp_addsrvrolemember办法用来添加登录账户，使其成为服务器角色的成员

sp_addsrvrolemember 'login','role'

其中：login：添加到固定服务器角色中的登录名。 role：要添加登录名的固定服务器角色的名称。

3.1.2 固定数据库角色

固定数据库角色：db_owner、db_accessadmin 、db_datareader、db_datawriter、db_ddladmin、db_securityadmin、db_backupoperator、db_denydatareader、db_denydatawriter、Public。

（1）使用SQL Server Management Studio将用户添加到固定数据库角色

（2）使用存储过程sp_addrolemember来添加用户，使其成为数据库角色的成员

其语法格式为： sp_addrolemember 'role' , 'security_account'

其中：role：当前数据库中的数据库角色的名称。 security_account：是添加到该角色的用户。

3.1.3 自定义数据库角色

当一组用户需要在 SQL Server 中执行一组指定的活动时，为了方便管理，可以创建用户自定义的数据库角色。

（1）使用SQL Server Management Studio创建用户自定义数据库角色。

（2）使用CREATE ROLE语句创建数据库角色

CREATE ROLE role_name [ AUTHORIZATION owner_name ]

其中：role_name：将创建的数据库角色名称。

AUTHORIZATION owner_name ：将拥有新角色的数据库用户或角色。

（3）应用程序角色

当要求对数据库的某些操作不允许用户用任何工具来进行操作，而只能用特定的应用程序来处理时就可以建立应用程序角色。应用程序角色不包含成员；默认情况下，应用程序角色是非活动的，需要用密码激活。在激活应用程序角色以后，当前用户原来的所有权限会自动消失，而获得了该应用程序角色的权限。可以通过图形界面创建应用程序角色，也可以通过CREATE APPLICATION ROLE 语句来创建。

3.2. 权限管理

权限类型:权限用来控制用户如何访问数据库对象。一个用户可以直接分配到权限，以可以作为一个角色的成员来间接的得到权限。一个用户可以同时属于具有不同权限的多个角色。

权限分为：对象权限、语句权限、暗示性权限。

对象权限:是指用户访问和操作数据库中表、视图、存储过程等对象的权限。

有五个不同的权限：查询（SELECT）、插入（INSERT）、更新（UPDATE）、删除（DELETE）、执行（EXECUTE）。前四个权限用于表和视图，执行只用于存储过程。

3.2.1．语句权限

语句权限是指用户创建数据库或在数据库中创建或修改对象、执行数据库或事务日志备份的权限。语句权限有：BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、DEFAULT、CREATE

FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE、CREATE VIEW。

3.2.2．暗示性权限

暗示性权限是指系统预定义角色的成员或数据库对象所有者所拥有的权限。例如，sysadmin 固定服务器角色成员自动继承在 SQL Server 安装中进行操作或查看的全部权限。 数据库对象所有者还有暗示性权限，可以对所拥有的对象执行一切活动。

3.2.3．权限管理操作

一个用户或角色的权限可以有三种存在的形式：授予（granted）、拒绝（denied）、废除（revoked）。授予是赋予用户某权限；拒绝是禁止用户的某权限；废除是撤销以前授予或拒绝的权限。

3.2.3．1．使用SQL Server Management Studio管理权限

3.2.3．2．用Transact_SQL语句管理权限

（1）语句权限

GRANT/DENY/REVOKE { ALL | statement [ ,„n ] } TO security_account [ ,„n ]

其中：ALL：表示授予/拒绝/废除所有可用的权限。

Statement：是被授予/拒绝/废除的语句，可以包括11.5中介绍的所有的语句权限。

security_accoun：是权限将应用的用户。

例如，给用户user1创建表的权限。

USE sales

GO

GRANT CREATE TABLE TO user1

Go

（2）对象权限

GRANT/DENY/REVOKE { ALL[ PRIVILEGES ] }

| permission [ ( column [ ,...n ] ) ] [ ,...n ]

[ ON securable ] TO principal [ ,...n ]

其中：Permission：权限的名称。

column：指定表中将授予/拒绝/废除其权限的列的名称。需要使用括号“()”。

Securable：指定将授予/拒绝/废除其权限的安全对象。

Principal：主体的名称，可为其授予安全对象权限的主体随安全对象而异。

例如，授予用户user1在Seller表上的SELECT、UPDATE和INSERT权限。

USE sales

GO

GRANT SELECT,UPDATE,INSERT

ON Seller TO user1

Go

本文标签： 用户权限数据库角色登录