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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: --- title: SQLServer · 最佳实践 · RDS for SQL Server 2012 权限限制的提升与改善 author: 石沫 --- # 背景 SQL Server 作为一种强大的关系型数据库,能够提供所有场景的应用。在绝大多数云计算公司中,都提供了SQL Server作为服务的云数据库服务,譬如阿里云。但既然是服务,那么服务就需要可管理,可控制

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

1.2 更改LOGIN

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

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

2

1.3 删除LOGIN

--drop login
DROP LOGIN Test11 

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

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:mmmgggg,其他任何路径都不会创建成功。 这个只是为了规范正确的路径而已,没有其他任何功能限制。

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

如下图:
4

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

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
9天前
|
SQL 安全 算法
【SQL server】玩转SQL server数据库:第四章 数据库安全性
【SQL server】玩转SQL server数据库:第四章 数据库安全性
75 12
|
4天前
|
SQL 存储 关系型数据库
RDS for MySQL的SQL分类与数据类型
小明需在MySQL中管理商品信息,使用SQL完成业务操作。SQL分为DQL(查询)、DML(增删改)、DDL(定义)、DCL(权限控制)和TCL(事务)五大类。DDL用于创建、修改和删除数据库结构,DML处理数据,DCL控制权限,TCL管理事务,DQL则用于查询数据。MySQL有多种数据类型,如数值型(整数、小数)、日期型和字符串型等,选择合适的数据类型是高效开发的关键。
15 0
|
7天前
|
SQL API 数据处理
实时计算 Flink版产品使用合集之sqlserver增量快照,用户需要什么权限
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
24 4
|
1天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
18 2
|
1天前
|
SQL JSON atlas
实时计算 Flink版产品使用合集之SQL Server CDC是否支持抽取SQL Server视图
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
9天前
|
SQL 安全 网络安全
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
102 0
|
9天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
74 10
|
9天前
|
SQL 存储 算法
【SQL server】玩转SQL server数据库:第一章 绪论
【SQL server】玩转SQL server数据库:第一章 绪论
44 5
|
9天前
|
SQL 数据库 数据库管理
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
72 11
|
9天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
122 6