SQLServer · 最佳实践 · RDS for SQL Server 2012 权限限制的提升与改善

title: SQLServer · 最佳实践 · RDS for SQL Server 2012 权限限制的提升与改善

author: 石沫

背景

SQL Server 作为一种强大的关系型数据库，能够提供所有场景的应用。在绝大多数云计算公司中，都提供了SQL Server作为服务的云数据库服务，譬如阿里云。但既然是服务，那么服务就需要可管理，可控制，因此，在云计算初期，都对云数据库服务进行了严格的权限控制，好处就是可控可管理，但给用户会带来一些限制，某些限制实际上是可以再细粒度管控。因此，今天我们就要介绍一下阿里云数据库SQL Server 2012在权限限制方面的提升与改善。

用户最关注的权限和使用

根据我们对用户的理解和日常用户的反馈，最常见的需求如下：

1. 需要创建、修改和删除登录和用户的权限

2. 需要创建、修改和删除数据库的权限

3. 数据库上的所有权限

4. 需要结束连接的权限

5. 需要方便读取数据库日志的权限

6. 需要链接服务器权限

7. 需要SQL Server Profiler的跟踪功能

8. 需要Database Engine Tuning Advisor来调校性能

一、 RDS SQL Server 2012权限新增功能使用介绍

我们在SQL Server 2012的版本中，用户的关注是我们改进和提升的方向，因此，经过论证与设计，会将这些权限还给用户，让用户有更多的自由选择和方便调试性能、管理自己的数据。那么如何来使用这些功能，我这里会将每个需求都演示一下如何使用（针对阿里云数据库SQL Server 2012）：

1. 创建、修改和删除登录和用户的权限

首先，阿里云数据库SQL Server 2012会提供一个根帐户，这个根帐户也可以与你新建帐户的权限一致，但强烈建议你新建一个帐户,根帐户可以修改密码，也可以DISABLE掉，甚至删除掉，你还可以重置根账号。

1.1 创建LOGIN

--check current login user
SELECT SUSER_NAME(),ORIGINAL_LOGIN()

--create login
CREATE LOGIN Test11
WITH PASSWORD=N'4C9ED138-C8F5-4185-9E7A-8325465CA9B7'

创建登录过程中，会将服务器级、数据库级别等权限授予用户。你会在message(消息)看到下列信息：

1.2 更改LOGIN

--create login
ALTER LOGIN Test11
WITH PASSWORD=N'123',
CHECK_POLICY=OFF

你不能更改你创建的任何之外的LOGIN，否则会出现一下错误：

1.3 删除LOGIN

--drop login
DROP LOGIN Test11

同样你不能删除你创建的任何之外的LOGIN，否则会出现一下错误：

1.4 创建USER

你只能在自己新建的用户数据库创建用户，在系统数据库上是无法做操作的, 所以为了演示创建用户，需要先创建一个数据库testdb(稍后介绍具体信息)：
使用login test11登录到实例（假如你的密码是123，因为刚才新建的用户已经删除，请重新创建一下）
请注意，如果是当前用户创建数据库，那么就将当前用户设置该数据库的一个用户，并且角色自动分配为db_owner, 你也可为数据库创建其他用户和角色：

USE TestDB
GO
--create user
CREATE USER [Test] FOR LOGIN [Test]
--add database role
ALTER ROLE [db_owner] ADD MEMBER [Test]
GO
-- query user name and role name
SELECT
    dpp.name,dpm.name
FROM sys.database_principals dpp
INNER JOIN sys.database_role_members drm
    ON dpp.principal_id=drm.member_principal_id
INNER JOIN sys.database_principals dpm
    ON dpm.principal_id=drm.role_principal_id
WHERE dpp.name='test'

1.5 更新USER

你可以更改USER,与SQL SERVER原始的操作方式一样，比如更改用户映射的登录，如下：

USE TestDB
GO
ALTER USER test WITH LOGIN=test

1.6 删除USER

你还可以更改USER，与SQL SERVER原始的操作方式一样，如下：

USE TestDB
GO
DROP USER test

二、创建、修改和删除数据库

2.1 创建数据库

正如上面所说，如果是当前用户创建数据库，那么就将当前用户设置该数据库的一个用户，并且角色自动分配为db_owner。如下：

CREATE DATABASE TestDb

你会收到下列消息：

Change database owner to sa sucessfully.
Create user Test11 and grant db_owner role in database TestDb.

表示当前创建的登录用户是Test11,并且已经分配db_owner角色
注意：
在创建数据库是，你不能随意指定文件路径，你可以不指定任何路径，例如上面的SQL语句，但你也可以指定正确的路径。如果你指定错误的路径，会收到错误提示：

USE [master]
GO

CREATE DATABASE [TestDb_error_path]
 ON  PRIMARY
( NAME = N'TestDb_error_path', FILENAME = N'E:\KKKK\DDD\\DATA\TestDb_error_path.mdf' )
 LOG ON
( NAME = N'TestDb_error_path_Log', FILENAME = N'E:\\KKKK\DDD\\DATA\TestDb_error_path_log.ldf')

你会收到下列信息:

Msg 50000, Level 16, State 1, Procedure *, Line 57
The file path [
E:\\KKKK\DDD\\DATA\TestDb_error_path.mdf
E:\\KKKK\DDD\\DATA\TestDb_error_path_log.ldf ] is invalid,please specify correct path folder [ E:\mmm\gggg\ ].
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

显然，这个正确的路径只有E:\mmm\gggg\，其他任何路径都不会创建成功。这个只是为了规范正确的路径而已，没有其他任何功能限制。

2.2 更改数据库

你可以更改大部分的数据库属性，但有几个地方你需要注意，有些特性是不可以随便更改的,。

2.2.1 不能移动到错误的文件路径

ALTER DATABASE [TestDb]
MODIFY FILE
( NAME = N'TestDb', FILENAME = N'E:\KKKK\DDD\DATA\TestDb.mdf' )

你会得到下列信息：

Msg 50000, Level 16, State 1, Procedure , Line 152
The file path [
E:\KKKK\DDD\DATA\TestDb.mdf ] is invalid,please specify correct path folder [ E:\mmm\gggg\ ].
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

2.3.2 不能将数据库的恢复模式设置为FULL之外的其他模式

ALTER DATABASE [TestDb]
SET RECOVERY SIMPLE

你会得到下列信息：

Msg 50000, Level 16, State 1, Procedure , Line 46
Login User [Test11] can't change database [TestDb] recovery model.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

2.3.3 将数据库设置为OFFLINE后，不能直接ONLINE

USE [master]
GO

--set offline
--ALTER DATABASE [TestDb]
--SET OFFLINE
--WITH ROLLBACK AFTER 0

ALTER DATABASE [TestDb]
SET ONLINE

你会得到下列错误信息：

Msg 5011, Level 14, State 9, Line 1
User does not have permission to alter database 'TestDb', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

当前设置为OFFLINE时，也不必惊慌，你可以使用sp_rds_set_db_online存储过程，让数据库ONLINE:

EXEC sp_rds_set_db_online 'TestDb'

2.2 删除数据库

删除数据库没有任何特别之处，但是如果你的数据库没有进行过任何备份，也会提示：

DROP DATABASE [TestDb]

        -------------------------------------------------------------------------------------------------
        Kindly reminder:
            your database [TestDb] does not exist any backup set.
        -------------------------------------------------------------------------------------------------
Login User [Test11] has dropped database [TestDb] .

三、数据库上的所有权限

数据库我们首先设置为db_owner,但是回收了备份等必要的管控机制，现在的数据库级别权限非常的自由，但也是有两面性，权限更多了，那么自己操作的时候就需要更严谨和更仔细，避免导致数据库的使用问题。

四、结束连接的权限

结束连接的权限，我们更通俗地将是KILL权限，RDS SQL Server 2012已经授予这个权限，但是用户只能KILL自己的的连接，用户不要KILL其他连接，比如备份的连接。

KILL (SPID)

五、需要方便读取数据库日志的权限

以前你需要在控制台读取数据库错误日志，现在你可以直接使用存储过程sp_rds_read_error_logs读取错误日志，使用方法与sp_readerrorlog是一样的：

-- eg.1
EXEC sp_rds_read_error_logs
-- eg.2
EXEC sp_rds_read_error_logs 0,1 ,'error'

六、需要链接服务器权限 (暂时不支持)

链接服务器现在还存在两个问题，首先，不能用UI界面去创建链接服务器，UI界面创建链接服务器需要sysadmin权限，可以使用一系列的存储过程创建。第二，由于RDS的设计，不能直接通过DNS和对应的IP创建，具体创建方式，暂时不能提供。但有解决方案。但我们提供了一个简单的一键创建方式：

DECLARE
        @linked_server_name sysname = N'my_link_server',
        @data_source sysname = N'*',   --style: 10.1.10.1,1433
        @user_name sysname = N'**' ,
        @password nvarchar(128) = N'**',
        @link_server_options xml
        = N'
            <rds_linked_server>
                <config option="data access">true</config>
                <config option="rpc">true</config>
                <config option="rpc out">true</config>
            </rds_linked_server>
        '
        EXEC sp_rds_add_linked_server
            @linked_server_name,
            @data_source,
            @user_name,
            @password,
            @link_server_options

如下图：

message(消息):
The linked server 'my_link_server' has set option 'data access' to 'true'.
The linked server 'my_link_server' has set option 'rpc' to 'true'.
The linked server 'my_link_server' has set option 'rpc out' to 'true'.
create link server 'my_link_server' successfully.

七、SQL Server Profiler的跟踪功能

RDS SQL Server 2012的 Profiler功能没有变化，但是不能生成文件到服务器上，因为你不能登录到RDS的实例服务器上。你可以放在表里。在使用SQL Server Profiler功能时，请注尽可能使用多的过滤条件和使用少量的列。

八、Database Engine Tuning Advisor来调校性能

这个功能的使用方法没有办法，不过，绝大多数时，用户不会使用这个功能。只是提供一种可选的方式。

总结

RDS SQL Server在权限方面做了很多改变，能够满足绝大多数用户的使用，性能调优和管理方面的需求。但是权限开放了，也需要用户谨慎使用，比如数据库层面，可以有更多设置了，设置不同的值会对数据库的性能产生不同影响。

时间： 2024-04-26 15:35:28