数据库技术-安全管理（二）

前言

前面一篇文章介绍了数据库技术-安全管理（一），下面学习SQL Server的安全控制和Oracle的安全管理的内容，在NCRE数据库技术里常以选择题、填空题进行考察。文章篇幅较长，请耐心看完。

目录

SQL Server 的安全控制（重要）

• 身份验证模式
• 登录账户
• 数据库用户
• 权限管理
• 角色

Oracle的安全管理 （了解）

• 用户与资源管理
• 权限管理

习题

SQL Server 的安全控制

身份验证模式

SQL Server身份验证模式主要包括两种模式：Windows身份验证模式和混合身份验证模式。

「Windows身份验证模式」
适用对象:用Windows操作系统的用户。
1.当使用Windows身份验证模式时，用户必须首先登录到Windows操作系统中，然后再登录到SQL Server。
2.用户登录到SQL Server时，只需选择Windows身份验证模式，而无须再提供登录名和密码，系统会从用户登录到Windows操作系统时提供的用户名和密码中查找当前用户的登录信息，以判断其是否是SQL Server的合法用户。

「混合身份验证模式」
定义:混合身份验证模式是指SQL Server允许Windows授权用户和SQL授权用户登录到SQL Server数据库服务器。
适用对象:非Windows操作系统的用户。
「如果希望允许非Windows操作系统的用户也能登录到SQL Server数据库服务器上，则应该选择混合身份验证模式。」
如果在混合身份验证模式下选择使用SQL授权用户登录SQL Server数据库服务器，则用户必须提供登录名和密码两部分内容，因为SQL Server必须要用这两部分内容来验证用户的合法身份。

❝

注意：

• 1.对于SQL Server来说，一般推荐使用Windows身份验证模式，这种安全模式能够与Windows操作系统的安全系统集成在一起，以提供更多的安全功能。
• 2.SQL Server身份验证的登录信息(用户名和密码)都保存在SQL Server实例上，而Windows身份验证的登录信息是由Windows和SQL Server实例共同保存的。

❞

登录账户

SQL Server 2008的安全权限是「基于标识用户身份的登录标识符」(Login ID ,登录ID)的,登录ID就是控制访问SQL Server数据库服务器的用户账户。如果未指定有效的登录ID ,则用户不能连接到SQL Server数据库服务器。
在SQLServer 2008中,有两类登录账户:由SQL Server自身负责身份验证的登录账户和登录到SQL Server的Windows网络账户 ,可以是组账户或用户账户。
在安装完SQL Server 2008之后,系统本身会自动地创建一些登录账户,称为「内置系统账户」,用户也可以根据自己的需要创建自己的登录账户。

「建立登录账户」
创建新的登录账户的T-SQL语句是CREATE LOGIN ,其简化语法格式为:

CREATE LOGIN login_name { WITH <option_list1>  FROM <sources>)
<sources> :: =
 WINDOWS [WITH<windows_options>[,...n] ]
<option_list1> :: =
 PASSWORD = 'password' [,<option_list2>[,...n]]
<option_list2> :: =
 SID = sid
 | DEFAULT_DATABASE = database
 | DEFAULT_LANGUAGE = language
<windows_options> :: =
 | DEFAULT_DATABASE = database
 | DEFAULT_LANGUAGE = language

参数含义与使用注意

login_name :指定创建的登录名。有四种类型的登录名: SQL Server身份验证的登录名、 Windows身份验证的登录名、证书映射的登录名和非对称密钥映射的登录名。
WINDOWS :指定将登录名映射到Windows用户名。    
PASSWORD='password' :仅适用于SQL Server身份验证的登录名。指定新建登录名的密码。    
SID = sid:仅适用于SQL Server身份验证的登录名。指定新登录名的GUID(全球唯一标识符)。如果未选择此选项,则SQL Server将自动指派GUID。    
DEFAULT_DATABASE = database:指定新建登录名的默认数据库。如果未包括此选项,则默认数据库将设置为master。    
DEFAULT_LANGUAGE = language:指定新建登录名的默认语言。如果未包括此选项,则默认语言将设置为服务器的当前默认语言。    

❝

注意：如果从Wmdows域账户映射login_name ,则login_name必须用方括号[]括起来。
PASSWORD='password' :仅适用于SQL Server身份验证的登录名。指定新建登录名的密码。
SID=sid:仅适用于SQL Server身份验证的登录名。

❞

建立登录账户示例
1.创建SQL Server身份验证的登录账户。登录名为User1 ,密码1a2a3a4a5a6a。

CREATE LOGIN User1 WITH PASSWORD='1a2a3a4a5a6a'  # 注意不要忘记WITH 

2.创建Windows身份验证的登录账户。从Windows域账户创建(TEST\Win_User2]登录账户。

CREATE LOGIN [TEST\Win_User2] FROM WINDOWS  # 注意不要忘记FROM 

3.创建SQL Server身份验证的登录账户。登录名为User3,密码123456.要求该登录账户首次连接服务器时必须更改密码。

CREATE LOGIN User3 WITH PASSWORD='123456'
MUST_CHANGE

「修改登录账户属性」
对于已经建立好的SQL Server登录账户，可以对登录账户的密码、默认数据库等属性进行修改，其简化语法格式为:

ALTER LOGIN login_name
         {
 <status_option>
 WITH <set_option>[,...n]
         }
<status_option> :: =
 ENABLE | DISABLE
<set_option> :: =
 PASSWORD ='password'
 [ 
 OLD_PASSWORD ='oldpassword'
 ]
 | DEFAULT_DATABASE = database
 | DEFAULT_LANGUAGE = language
 | NAME = login_name

参数含义与使用注意

login_name :指定正在更改的登录名。    
ENABLE | DISABLE :启用或禁用此登录名。    
PASSWORD='password' :仅适用于SQL Server身份验证的登录账户。指定正在更改的登录名的密码。    
OLD_PASSWORD='oldpassword' :仅适用于SQL Server身份验证的登录账户。要指定新密码的登录名的当前密码。    
DEFAULT_DATABASE = database :指定将指派给该登录名的默认数据库。   
DEFAULT_LANGUAGE = language :指定将指派给该登录名的默认语言。    
NAME = login_name :重命名该登录名的新名称。如果是Windows身份验证的用户,则与新名称对应的Windows的用户名必须与SQL Server中的登录名相关联。

修改登录账户属性示例
1.启用已禁用的登录账户。启用User1登录账户。

ALTER LOGIN User1 ENABLE

2.更改登录账户的密码。将User1登录账户的密码改为YJS001。

ALTER LOGIN User1 WITH PASSWORD= 'YJS001'

3.更改登录账户名。将User3登录账户名改为NewUser。

ALTER LOGIN User3 WITH NAME =NewUser

「删除登录账户」
在删除登录账户时，应该先将该登录账户在各个数据库中映射的数据库用户删除掉(如果有的话) , 然后再删除登录账户。否则会产生没有对应的登录账户的孤立的数据库用户。删除登录账户的T-SQL语句为DROP LOGIN，其语法格式为:

DROP LOGIN login_name  # 其中login_name为要删除的登录账户的名字。

例如删除User2登录账户。

DROP LOGIN  User2

❝

注意:不能删除正在使用的登录账户,也不能删除拥有任何数据库对象、服务器级别对象的登录账户。

❞

数据库用户

数据库用户一般都来自于服务器上已有的登录账户,让登录账户成为数据库用户的操作称为"映射" 。管理数据库用户的过程实际上就是建立登录账户与数据库用户之间的映射关系的过程。
「默认情况下，新建立的数据库只有一个用户: dbo ,它是数据库的拥有者。」

「建立数据库用户」
建立数据库用户的T-SQL语句是CREATE USER，其简化的语法格式如下:

CREATE USER user_name[ { FOR | FROM} # 指定在此数据库中用于识别该用户的名称。
{ LOGIN login name} ]  # 指定要映射为数据库用户的SQL Server登录名。

❝

注意:
1.如果省略FOR LOGIN,则新的数据库用户将被映射到同名的SQL Server登录名。
2.不能使用CREATE USER语句创建guest用户,因为每个数据库中均已存在guest用户。默认guest用户是禁用状态 ,可通过授予guest用户CONNECT权限来启用该用户。

❞

建立数据库用户示例
首先创建名为SQL_yjs且具有密码的SQL Server身份验证的服务器登录名，然后在test数据库中创建与此登录名对应的数据库用户yjs

CREATE LOGIN SQL_yjs
 WITH PASSWORD='123456'
GO
 USE test
GO
 CREATE USER yjs FOR LOGIN SQL_yjs
GO

「Guest用户」
Guest是SQL Server中一个特殊的数据库用户,在实际应用中经常作为数据库的匿名访问者使用。
当没有映射到数据库用户的登录账户试图访问数据库时，SQL Server将尝试用guest用户来连接。可以通过为guest用户授予CONNECT权限来启用用户数据库中的guest用户。

# 启用guest用户(使guest用户具有连接权限)的语句如下:
GRANT CONNECT TO guest;
# 禁用guest用户(收回guest用户的连接权限)的语句如下:
REVOKE CONNECT TO guest;

❝

注意:在每个数据库中还有一个特殊用户: dbo ,该用户的全称是database owner。dbo是创建该数据库的登录账户映射到该数据库中的用户，其所创建的对象都是dbo.对象名。

❞

「删除数据库用户」
从当前数据库中删除一个用户 ，实际就是解除登录账户和数据库用户之间的映射关系。但并不影响登录账户的存在。
「删除数据库用户之后，其对应的登录账户仍然存在。」
删除数据库语法格式为:

DROP USER user_name

❝

注意:不能从数据库中删除拥有对象的用户。必须先删除或转移安全对象的所有者,然后再删除拥有这些对象的数据库用户。

❞

权限管理

「当登录账户成为数据库中的合法用户后，除具有一些系统视图的查询权限，对数据库中的用户数据和对象并不具有任何操作权限。」

「对象级别的权限」

1.授权语句GRANT
T-SQL授权语句的简化语法格式为:

GRANT <permission> [,...n]
ON
[OBJECT ::] [schema_name].object_name[(column [,...n])]
TO <database_principal>[,...n]
[ WITH GRANT OPTION ]
<permission> :: =
ALL [PRIMLEGES] | permission[(column[,..n])]

参数含义与使用注意
Permission :指定可以授予的对象权限。
ALL :指授予适用于指定对象的所有ANSI-92权限。对于不同的对象, ALL的含义有所不同。

PRIVILEGES :包含此参数是为了符合ANSI 92标准。
ON [OBJECT::] [schema name].object_name :指定被授予权限的对象。如果指定了schema_name，则OBJECT短语是可选的。
「如果使用了OBJECT短语,则需要作用域限定符( : : )。如果未指定schema_name ,则使用默认架构。如果指定了schema_name，则需要架构作用域限定符(.)。」
TO<database_ principal> : 指定要向其授子权限的主体,可以星数据库用户名,也可以是用户定义的角色名。
「WITH GRANT OPTION :指示该主体还可以向其他主体授予所指定的权限。」
Column:指定表、视图或表值函数中将被授予权限的列名。只能在SELECT、REFERENCES及UPDATE操作上对列进行授权。
授权语句示例
1.授予用户YJS对Address表具有SELECT权限。

GRANT SELECT ON Address TO YJS

2.授予用户YJS对HumanResources.Employeelnfo存储过程具有EXECUTE权限。

GRANT EXECUTE ON OBJECT :: HumanResources.Employeelnfo TO YJS

2.拒绝权限DENY
拒绝用户具有某权限使用的是DENY语句,在T-SQL中其简化语法格式如下:

DENY<permission>[,...n] ON
[OBJECT ::][ schema_name ].object_name [(column[...n])]
TO <database_principal> [,...n]
[CASCADE]
[AS <database_principal>]

其中，CASCADE表示要拒绝的权限也会被对此主体授予该权限的其他主体拒绝。其他各参数含义同GRANT语句。

拒绝权限示例
1.拒绝用户QWE对Person.Address表具有SELECT权限。

DENY SELECT ON OBJECT :: Person.Address TO QWE

2.拒绝用户ASD对HumanResources.Employeelnfo存储过程具有EXECUTE权限。

DENY EXECUTE ON HumanResources.Employeelnfo To ASD

3.收权语句REVOKE

收权也就是将已授给用户的权限收回来,即不允许用户再具有该权限。语法格式如下:

REVOKE [ALL [ PRMILEGES ]] |<permission>[,...n]
ON
 [OBJECT :: ] [ schema_name ].object_name[(column [,...n])]
 {FROM | TO} < database_principal >[,...n]
[CASCADE][ AS <role_name> ]

参数含义与使用注意

CASCADE:对应GRANT语句中的WITH GRANT OPTION ,表示要撤销的权限也会从此主体授予或拒绝该权限的其他主体中撇销。
AS <role_name>:指定要基于哪个角色发出这个命令。
其他参数含义同GRANT语句。

收权语句示例
撤销用户QWE对Person.Address表的SELECT权限。

REVOKE SELECT ON OBJECT :: Person.Address FROM QWE

「语句级别的权限」
SQL Sever允许对执行不同的语句进行权限控制,这些语句主要包括以下图中7种语句:

1.授权语句GRANT
语法格式如下:

GRANT {ALL | <statement_pemission>[,...n] | 
To <database_principal> [,...n]

授权语句示例
1.授予用户QWE具有创建表的权限。

GRANT CREATE TABLE TO QWE

2.授予用户userI和user2都具有创建表和视图的权限。

GRANT CREATE TABLE，CREATE VIEW TO userl, user2

2.拒绝权限DENY
语法格式如下:

DENY { ALL | <statement_permission> [,...n]}
TO <database_principal> [,...n]

拒绝权限示例
拒绝用户user具有创建视图的权限。

DENY CREATE VIEW TO user

3.收权语句REVOKE
语法格式如下:

REVOKE { ALL | <statement_permission> [,...n]}
{FROM | TO} <database_principal>[,...n]

收权语句示例
收回用户QWE创建表的权限。

REVOKE CREATE TABLE FROM QWE

角色

在数据库中,为便于对用户及权限进行管理,将一组具有相同权限的用户组织在一起，这一组具有相同权限的用户就称为角色(Role)。

使用角色的好处:

系统管理员只需对权限的种类进行划分,然后将不同的权限授予不同的角色，而不必关心有哪些具体的用户。而且当角色中的成员发生变化时,比如添加成员或删除成员,系统理员都无须做任何关于权限的操作。

在SQL Server 2008中，角色分为预定义的系统角色和用户定义角色两种。又根据角色作用范围的不同,系统角色又分为服务器级角色(称为固定服务器角色)和数据库级角色(称为固定数据库角色)。用户定义的角色均是数据库级角色。角色的分类如下图所示:

角色

系统预定义服务器角色public :每个SQL Server登录名都是public服务器角色中的成员。
如果未向某个服务器主体授予或拒绝对某人安全对象的特定权限,则该用户将具有授予public角色的权限。
「1.为固定服务器角色添加成员」
在固定服务器角色中添加成员使用的是sp_addsvrolemember系统存储过程。语法格式如下:

sp_addsrvrolemember [ @loginame = ] 'login'，
[@rolename=] 'role'

参数含义与使用注意
[@loginame=] 'login' :要添加到固定服务器角色中的登录名。login可以是SQL Server身份验证的登录名,也可是Windows身份验证的登录名。如果该Windows登录名没有SQL Server的访问权限,则该操作将自动授予该登录名对SQL Server的访问权限。
[@rolename=]'role' :要添加到的固定服务器角色的名称，默认值为NULL 

该存储过程的返回值为: 0(成功)或1(失败)。

为固定服务器角色添加成员示例 将Windows身份验证的TEST\Win_User|登录名添加到sysadmin角色中。

EXEC sp.addsrvrolemember 'TEST\Win_User' , 'sysadmin'

2.删除固定服务器角色成员从固定服务器角色中删除成员使用的是sp_dropsrvrolemember系统存储过程。语法格式如下:

sp_dropsrvrolemember [ @loginame = ] 'login'，
[@rolename= ] 'role'

参数含义与使用注意

[ @loginame= ] 'login' :要从固定服务器角色中删除的登录名。
[ @rolename= ] 'role' :固定服务器角色名称。

该存储过程的返回值为: 0(成功)或1(失败)。

删除固定服务器角色成员示例
从dbcreator角色中删除User2.

EXEC sp_dropsrvrolemember 'User2' ，'dbcreator'

「固定数据库角色」
固定数据库角色是定义在数据库级别上的,而且存在于每个数据库中。下表列出了固定数据库角色及其具有的权限。

系统预定义的数据库角色public :每个数据库用户都是public数据库角色中的成员
如果未向某个用户授予或拒绝对安全对象的特定权哏,则该用户将具有public角色的权限。

1.为固定数据库角色添加成员
数据库角色成员的来源是数据库中的用户。从固定数据库角色中添加成员使用的是sp_addrolemember系统存储过程。语法格式如下:

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

参数含义与使用注意

[ @rolename= ] 'role' :当前数据库中的数据库角色名。
[ @membername= ] 'security_account' :要添加到角色中的数据库用户名。security_account可以是数据库用户、数据库角色、Windows登录名或Windows组。如果新成员是没有相应数据库用户的Windows登录名 ,则将为其创建一个对应的数据库用户。

该存储过程的返回值为: 0(成功)或1(失败)。

为固定数据库角色添加成员示例
将User2添加到当前数据库的db_datawriter角色中。

EXEC sp_addrolemember 'db_datawriter'，'User2'

2.删除固定数据库角色成员
从固定数据库角色中删除成员使用的是sp_droprolemember系统存储过程。
语法格式如下:

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

各参数含义同sp_addrolemember 。该存储过程的返回值为: 0(成功)或1(失败)。删除固定数据库角色成员示例 在当前数据库中,删除db_datawriter角色中的USer2成员。

EXEC sp_droprolemember 'db_datawriter' ,'User2'

「用户定义的角色」
用户定义的角色属于数据库一级的角色。用户定义的角色主要是为简化用户在使用数据库时的权限管理。
创建用户定义的角色
创建用户自定义角色的T-SQL语句是CREATE ROLE。其语法格式为:

CREATE ROLE role_name [ AUTHORIZATION owner_name ]

参数含义与使用注意

role_name :待创建角色的名称。
AUTHORIZATION owner_name :将拥有新角色的数据库用户或角色。如果未指定用户,则执行CREATE ROLE的用户将拥有该角色。

创建用户定义的角色示例
在当前数据库中创建用户自定义角色MathDept ,其拥有者为Software角色。

CREATE ROLE MathDept AUTHORIZATION Software;

为用户定义的角色授权
为用户定义的角色授权使用的T-SQL语句与实现对数据库用户授权完全一样。
添加和删除用户定义的角色中的成员。
与为固定数据库角色添加和删除成员使用语句一样。
删除用户定义的角色

其语法格式如下:

DROP ROLE role_name

删除用户定义的角色示例
在当前数据库中删除用户定义的角色InfoDept

DROP ROLE InfoDept

❝

注意:不能从数据库中删除拥有安全对象的角色。若要删除拥有安全对象的数据库角色,必须首先转移这些安全对象的所有权,或从数据库中删除这些安全对象;不能从数据库中删除拥有成员的角色。若要删除有成员的角色,必须首先删除角色中的成员。

❞

Oracle的安全管理

Oracle不仅支持集中式应用,也支持跨平台、分布式数据库应用,即用户数据物理上可以被分布存储在网络的多个节点(场地)中,而逻辑上是一个整体。

Oracle分布式数据库系统没有全局数据库管理系统,没有全局数据字典,没有全局死锁检测及全局管理调度程序,其结点之间的互操作是由Oracle的通信模块和数据库服务器共同完成的,即网络中的每个数据库服务器都是全局事务的协调者。

用户与资源管理

Oracle数据库中的用户按其操作权限的大小可分为DBA用户和普通用户。DBA用户是在数据库系统安装时由DBMS自动创建生成的,它们是sys和Isystem用户。拥有 DBA用户全部的系统特权(权限)。普通用户是由DBA用户或具有相应特权的用户创建,并授予系统特权的用户。

「建立用户」
建立一个Oracle数据库用户user1。

CREATE USER user1 IDENTIFIED BY u66771
DEFAULT TABLESPACE student
QUOTA 5M ON student

命令中的user1是新建的用户名,密码是u66771 ;
DEFAULT子句指定新用户创建的数据库对象,如表、视图等将被存储在student表空间中;
QUOTA子句限制user1使用student表空间中的空间限额为5MB.

「管理用户和资源」
具有DBA特权的用户可以改变一个用户对系统资源的使用限额 ,并对用户密码进行管理。将user1用户对student表空间的空间使用限额增至60MB.

ALTER USER user1
QUOTA 60M ON student

DBA用户可以用下面的命令修改任何用户的密码,普通用户也可以用此命令修改自己的密码,如将自己的密码改为wl2345 ,用命令:

ALTER USER user1 IDENTIFIED BY w12345
-w12345为新的密码

「删除用户」
从数据库中删除userl用户及其所拥有的全部数据库对象。

DROP USER userl CASCADE

命令中的CASCADE选项指定在删除用户的同时,删除其所拥有的全部数据库对象,否则仅删除userl用户名。

权限管理

「系统特权」
当用户使用合法的用户名注册到数据库之后, Oracle DBMS还会进一步检查一个用户是否具有对相应数据库资源的操作权。Oracle提供了以下三种默认的角色(一组特权)。

授予系统特权和角色
授予用户user1具有建立表和视图的权限。

GRANT create table, create view TO user1 WITH ADMIN OPTION

回收系统特权和角色
回收角色test的系统特权create procedure.

REVOKE create procedure FROM test

「对象特权」对象特权用于维护表级、行级与列级数据的安全性。下表列出了 数据库对象-----表、 视图、 序列、快照、存储过程与函数各对象上允许的操作权限(V表示操作允许)

授予对象权限将dep表上的全部操作权授予user1用户

GRANT all ON dep TO user1

回收对象特权表的拥有者根据需要可以随时回收曾经授予的对象特权。如回收用户user1在dep表上的全部权限语句

REVOKE all ON dep FROM user1

习题

1.SQL Server 的身份验证模式有_____和_____两种。2.在SQL Server2008中，系统提供的具有管理员权限的角色是_______。

参考资料及声明

• 全国计算机等级考试三级教程.数据库技术 2019.12.
• 三级数据库技术：全国计算机等级考试上机考试题库 2017.10.
• 部分图片源自网络，使用图片是为了传递更多信息，普及相关知识，若有来源标注错误或侵犯了您的合法权益，请及时与我联系，我将及时更正、删除或依法处理。