443 Chapter6.Designing Database Security

Designing Database Security
Lesson 1:  Designing Instance-level Security
1. Configuring Service Account
(1) In SQL Server 2005, when selecting the service account for SQL server instance, you should use an account with minimal permissions to restrict the access to system on your network. Because service accounts are low-privileged accounts, additional configuration is required to ensure that the SQL Server instance can function. To run a SQL Server instance, the account need permissions to several resources, such as the following
 Read/Write permissions to the SQL Server registry keys
 Run as Service tights
 Read/Write permissions to the directories containing database files
(2) Group membership
(3) Service Account Permissions
DBA do not need to change the default permissions assigned to default SQL service group
DBA should understand the security context of each command you will execute. (For example, backup)
DBA should grant any additional permission needed for processes to execute to the Windows group that contains the service account.
2. Instance-level principal
(1) Principal are entities that are granted permissions to a securable. At an instance level, principals are implemented as logins, which can be of five types:
 Standard SQL Server login
 Windows login
 Windows group
 Certificate
 Asymmetric key
 
You can map a SQL Server login to a Windows user account or Windows group.
Create a Windows user account, specify the user to run IIS, and then you can use this account to connect to SQL Server.
(2) sample
3. Authentication mode
(1) Windows authentication
(2) SQL Server and Windows authentication (Mixed Mode)
4. Configuring the attack surface
(1) SQL Server installs and configures under three principles:
 Secure by design
 Secure by default
 Secure by deployment
(2) SQL Server Configuration Manager. It enables you to stop, pause, and start SQL Server services. The most function of the SSCM is to change service accounts and service account passwords.
 

(3) SQL Server Surface Area Configuration. It enables you to turn features and behaviors on or off.

MSDE, new called SQL Server Express Edition, is installed by many applications to e used as a local data store. Each of these installations is another instance of SQL Server.

The default connection for different SQL Server Edition
Edition Default setting
Express, Evaluation, Developer Local connection
Workgroup, Standard, Enterprise Both local and remote connection


 Some description for surface configuration features
Feature Description
Ad Hoc Remote Queries If you have any queries that use OPENROWSET or OPENDATASOURCE command
CLR If you intend to use triggers, functions, stored procedures, custom aggregates, or CLR Data types written in .NET language, you must enable the CLR capability.
Database Mail 
Xp_cmdshell You commonly use xp_cmdshell in administrative procedures to execute ad hoc SQL commands as well as operating system commands.

 

 

5. Server role
name principal_id sid type type_desc
bulkadmin 10 0x0A R SERVER_ROLE
dbcreator 9 0x09 R SERVER_ROLE
diskadmin 8 0x08 R SERVER_ROLE
processadmin 7 0x07 R SERVER_ROLE
public 2 0x02 R SERVER_ROLE
securityadmin 4 0x04 R SERVER_ROLE
serveradmin 5 0x05 R SERVER_ROLE
setupadmin 6 0x06 R SERVER_ROLE
sysadmin 3 0x03 R SERVER_ROLE

6. SQL Server Agent proxy accounts
Job steps created within SQL Server Agent can access external subsystems such as SSIS or operating system commands. You can create proxy accounts for these jobs to provide the appropriate permissions. This provides more granular control than granting permissions to the SQL Server Agent service account because every job step would then have elevated permissions
7. Designing Security for .NET Assemblies
(1) .NET assemblies use Code Access Security (CAS) to restrict the allowable set of operations that can be performed. SQL Server also uses CAS to lock down the managed code and ensure the integrity of the database server and operating system.
(2) When creating the assembly, the sysadmin specifies the level of CAS that is applied to the assembly. The access permissions available for assemblies are SAFE, EXTERNAL_ACCESS, and UNSAFE.
type Description
SAFE It is the default permission set and does not allow access to resources external to the SQL Server instance or any machine resources.
EXTERNAL_ACCESS It enables an assembly to access resource external to the SQL Server instance such as files, shares, and network resources.
UNSAFE It enables to perform any operation and access any resource.
8. Practice: Managing Accounts

Lesson 2:  Designing Database Security
1. Database Users and Roles
To grant access to the database, you add the login as a user in the database by executing CREATE USER command. When adding a user to a database, the user is normally mapped to a login, certificate, or asymmetric key. You can add each user in a database to one or more database roles.
Role Description
db_accessadmin Adds or removes user in the database
db_backupoperator Backs up the database
db_datareader Issues select operations against all tables within the database.
db_datawriter Issues insert, update, and delete operations against all tables within the database
db_ddladmin Execute DDK commands
db_denydatareader Denies select operations against all tables within the database
db_denydatawriter Denies insert, update, and delete operations against all tables
db_owner Owner of the database with full control over all objects
db_securityadmin Manages role membership and permissions
public Default group that every user belongs to
2. Designing Schemas
(1) Schemas are new features in SQL Server 2005 that provide a means to group objects within a database together.
 
(2) A schema is a securable object that enables you to group multiple objects into a schema and then grant permissions to a principal to the scheme.
(3) The most powerful capability of schemas is to manage permissions. Users with the authority to read related to employees can be granted permissions in two ways.
 You can grant select permission to each object within the HumanResources schema
 Or you can issue a single grant statement on the HumanResource schema.
(4) Schema provides more powerful security assignments.
3. Defining Encryption
(1) Encryption is accomplished by using a flexible, multilayered series that start at the instance level and goes through data within the database.
(2) Service Master Key. The root of the encryption hierarchy is the service master key, which is automatically generated the first time a credential needs to be encrypted. Service master keys are derived from the Windows credentials of the SQL Server service account and encrypted using either the local machine key or the Windows Data Protection API.
(3) Database Maser key.
 The next layer in the encryption hierarchy is the database master key, which must be explicitly generated using the following command. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
 Each database has a different master key.
 The database master key is used to protect any certificates, symmetric keys, or asymmetric keys that are stored within the database.
 The database master key is encrypted by using Triple Data Encryption Standards (DES) and the user-supplied password.
 When you make a request to decrypt data, the service master key is used to decrypt the database master key; decrypt a certificate, symmetric key, or asymmetric key; decrypt the data.
 TSQL Command: OPEN | BACKUP | RESOTER | CLOSE MASTER KEY
(4)  Asymmetric Keys. Asymmetric keys use a public and private key system.
(5) Certificates. A public key certificate is digital signed instrument that binds the public key to an identity. It can be a person, organization, or machine that controls the corresponding private key. A certificate is normally issued by a certificate authority that certifies the identity holding the certificate.
(6) Symmetric keys. Symmetric keys use a single key for both encryption and decryption.
4. Designing DDL Triggers
(1) The main purpose of DDL triggers is to audit as well as regulate actions performed on a database. This enables DDL operation to be restricted even if a user might normally have the permission to execute the DDL command.
5. Practice: Encryption
6. Practice
(1) Creating a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
GO

SELECT * FROM sys.symmetric_keys

(2) Encrypting Data with a Passphrase
DECLARE @plaintext NVARCHAR(50)
DECLARE @key NVARCHAR(50)
DECLARE @decrypted nvarchar(50)
DECLARE @encrypted varbinary(52)

SET @plaintext = 'SQL Server 2005'
SET @key = 'MyKey'
SET @encrypted = EncryptByPassPhrase(@key, @plaintext)
SET @decrypted = DecryptByPassPhrase(@key, @encrypted)

SELECT @encrypted Encrypted, @decrypted Decrypted

(3) Creating s self-signed Certificate
DECLARE @encrypted varbinary(500)
DECLARE @decrypted varchar(50)
SET @encrypted = EncryptByCert(Cert_id('MyCert'), 'SQL Server 2005')
SET @decrypted = DecryptByCert(Cert_id('MyCert'), @encrypted)

SELECT @encrypted Encrypted, @decrypted Decrypted

(4) Creating an Asymmetric key
CREATE ASYMMETRIC KEY MyAsymmetricKey WITH ALGORITHM = RSA_2048
GO

SELECT * FROM sys.asymmetric_keys
GO

DECLARE @encrypted varbinary(500)
DECLARE @decrypted varchar(50)

SET @encrypted = EncryptByAsymKey(AsymKey_id('MyAsymmetricKey'), 'SQL Server 2005')
SET @decrypted = DecryptByAsymKey(AsymKey_id('MyAsymmetricKey'), @encrypted)

SELECT @encrypted Encrypted, @decrypted Decrypted

(5) Create a DDL Trigger
CREATE TRIGGER tddl_table_preventdropalter
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
 PRINT 'You are attempting to drop or alter tables in production!'
 ROLLBACK ;

DROP TABLE XmlTransfer


Lesson 3:  Securing Database Object
1. Permission Scope
(1) Permissions are granted on a securable that can be a database, a schema, or an object. This creates a hierarchical structure of permissions within a database.
(2) The first layer of security that you will want to plan within a database is a schema. Objects are then created within each schema. After objects are created in schemas, permission are granted on the schemas to provide security access to an application.
2. Execution Context
(1) An execution can be specified for code. Specifying an execution context enables code to be run under a specific security context.
(2) Module execution context
Context Description
CALLER Default behavior. The permissions of the user are evaluated based on the permissions granted by the schema owner.
<user name> Execute the code under another user’s credentials
SELF Executes under the security credentials of the user specifying the execution context
OWNER Executes under the security credentials of the owner of the schema that the object belongs to.
3. Encrypting Columns
4. Practice: Granting Permission
(1) Grant permission to a database.
(2) Grant permission to a schema.
(3) Encrypt and decrypt column
create table staff
(
staff_no nvarchar(5) not null,
first_name nvarchar(30) not null,
last_name nvarchar(10) not null,
salary varbinary(256)
)
go

insert into staff
(staff_no, first_name, last_name, salary)
values
('10000','Bill', 'Zhang',
ENCRYPTBYPASSPHRASE('P@$$w0rd', convert(nvarchar(20),'8000.00'),
1, convert(varbinary, '10000')))

GO

select
staff_no, first_name, last_name,
CONVERT(nvarchar(20), DecryptByPassphrase('P@$$w0rd', salary, 1, CONVERT(varbinary, '10000')))
    AS salary
 from staff

 

转载于:https://www.cnblogs.com/yang_sy/archive/2009/04/16/1437391.html

边缘计算【智能+安全检测】系列教程--使用OpenCV+GStreamer实现真正的硬解码,完全消除马赛克 通过现有博客的GStreamer的解码方式解码,大多情况应该存在上图马赛克的问题,请勿紧张,其实上述url并没有调用硬件解码,后面有调用硬件解码的ULR,我们来在接下来的介绍中释获。通过RTSP协议连接海康威视摄像头将图片送入AI推理,发现OpenCV的VideoCapture使用的是软解码大量消耗了cpu的资源,并无法做到实时数据处理存在5秒钟的延时。只能把解码这部分送到硬件解码器去处理分摊掉cpu的压力, 阅读详情

相关推荐

RAG优化实战:微调Embedding模型提升检索精度全流程

在构建基于大语言模型(LLM)的问答系统时,检索增强生成(RAG)架构是关键技术路线。其核心原理在于,通过Embedding模型将文本转换为高维向量,并在向量空间中进行语义相似度计算,从而实现精准检索。这项技术的核心价值在于,它能有效利用专属知识库,为大模型提供准确的上下文信息,从而减少“幻觉”并提升答案的专业性与时效性。然而,通用Embedding模型在面对医疗、法律、金融等垂直领域的专业术语和行话时,往往存在语义理解偏差,导致检索不准,成为RAG系统的关键瓶颈。为解决此问题,微调(Fine-tuning

weixin_34088583的博客 503

443 Chapter4.Designing Database Server Security Policies

Designing Database Server Security PoliciesLesson 1: Enforcing SQL Server Security through Group Policy1.What is Group Policy(1)Group policy is a framework that administrators use to control t...

aoding8407的博客 204

VScode高效开发STM32:头文件路径配置全解析

本文详细解析了在VScode中高效开发STM32项目的核心步骤——头文件路径配置。针对常见的“找不到头文件”问题,文章深入剖析了IntelliSense的工作原理,并提供了手把手创建和解读c_cpp_properties.json配置文件的完整指南,包括includePath、defines、compilerPath等关键项的设置,帮助开发者彻底解决红色波浪线错误,实现精准的代码提示与跳转。

weixin_28835789的博客 342

177.Agent-LangGraph-MCP的构成说明

MCP架构通过MCP主机、客户端与服务器的协同,实现工具、资源与提示词的统一管理。MCP服务器提供Tools、Resources、Prompts能力,MCP客户端则负责发现、调用与读取,使Agent可按需获取上下文。其核心在于将工具从Agent中抽离,封装为可复用的服务,提升灵活性与可维护性,推动AI应用高效协作。

计算机王的博客 315

SCAN和JTAG分别是什么?它们之间有什么联系与区别?

SCAN(扫描)技术是集成电路测试领域的一种方法,主要用于边界扫描测试(Boundary Scan)。它是在集成电路设计中引入的一种测试结构,允许测试人员通过在芯片的输入/输出引脚上设置和读取特殊寄存器(边界扫描寄存器,BSR)的内容,来间接测试电路板上的互联和芯片内部功能块。这种方式可以在不接触其他内部节点的情况下测试整个电路板的连接性,极大地提高了测试覆盖率和可诊断性。JTAG(Joint Test Action Group,联合测试行动小组)则是开发并推广边界扫描测试标准的一个组织。

初心不忘产学研的博客 2545

【天线与电波传播】电波传播部分 第7章 电波传播的基础知识3

菲涅尔区与菲涅尔带的基本知识

Chen59_2328的博客 1602

Docker Compose:搭建本地多服务开发环境

从一个 Java API、PostgreSQL 和 Redis 开始,配置服务名、端口、环境变量、健康检查、数据卷和依赖关系。理解 Compose 文件如何被解析、项目如何隔离,以及哪些数据不能依赖“重建容器”保留,能够把个人开发环境整理成团队可复用的启动入口。

m0_62221377的博客 478

Redash离线环境部署2020.1(centos+windows)

背景 Redash是一款开源的BI系统,核心功能是添加数据源,然后进行数据的可视化展示。一般会拿出来同Supervisor和Metabase进行选型比较,这里对三者的选型不做细致分析。而是给出在Redash开发环境部署踩过的坑,很多坑是自己琢磨出来的,网上几乎找不到更新的资料。适用于没怎么用过linux,也不怎么熟悉python的,用的是最新(master)的redash的代码(上一个发布版本...

Jone_wan的专栏 2139

AI全栈实战 | 3.9-01 爬虫入门:requests 加 BeautifulSoup 三分钟跑通,但 90% 的人栽在这 4 个“礼貌“问题上

⚠️:本文内容仅用于。请遵守目标站点的服务条款与相关法律法规,不得对目标服务造成负担,不得采集、传播个人隐私数据。技术是中性的,边界在你手里。爬虫的入门技术(requests 发请求 + BeautifulSoup 解析)三分钟就能学会。真正区分"会用爬虫"和"会做爬虫的人"的,是这篇我们跑通"请求→解析→存储"的最小闭环,然后重点把"合规与礼貌"这根弦给你绷上。

源码获取 | 运行部署 | 定制二开修改,如果回复不及时请耐心等待,请见谅。 445

MySQL 分库分表及其平滑扩容方案

MyCAT不仅仅可以用作读写分离,以及分表分库、容灾管理,而且可以用于多租户应用开发、云平台基础设施,让你的架构具备很强的适应性和灵活性。分表对于一张多行(记录)多列(字段)的二维数据表,又分两种情形(1)垂直分表竖向切分,不同分表存储不同的字段,可以把不常用或者大容量、或者不同业务的字段拆分出去;扩容前每个节点的数据,有一半要迁移至一个新增节点中,对应关系比较简单。此时,四个节点的数据都已完整,只是有冗余(多存了和自己配对的节点的那部分数据),择机清除即可(过后随时进行,不影响业务)。...

m0_71777195的博客 1930

【各种轮子2】基于Icepak的PCB散热仿真比较

随着电子产品的集成度越来越高,PCB板的尺寸越来越小,板级芯片散热的问题越来越成为电子工程师的一个重要挑战。对于板级芯片散热,主要依靠工程师对PCB自身的设计进行优化,同时要兼顾系统的尺寸和成本。本文分别从芯片角度和PCB角度进行建模,芯片模型选用QFN封装,PCB模型采用走线导入模型,探讨了芯片结构,PCB铜厚,PCB叠层厚度对芯片散热的影响。

3671

spring boot 4集成SQLLite,及其SQLLite的建库

spring boot 4集成SQLLite,及其SQLLite的建库

weixin_41463944的博客 239

LeRobot 入门教程(十七)NVIDIA IsaacLab Arena 集成

LeRobotEnvHub现已集成IsaacLabArena实现GPU加速仿真,支持GR1/G1/伽利略人形机器人实体和多种操作任务。安装需满足CUDA GPU、Ubuntu系统和IsaacSim 5.1.0要求,通过conda环境配置依赖项。系统提供SmolVLA和PI0.5等预训练策略评估功能,支持视频录制和并行环境配置。环境配置选项涵盖机器人实体、任务类型、观测维度等参数,并包含针对CUDA内存、视频录制等常见问题的解决方案。此外,LightWheel AI还通过LW-BenchHub引入了包含268

2506_90492529的博客 1250

【基于 WebSocket 的多用户网页五子棋】—— 数据管理模块

但行好事,莫问前程

qq_42996461的博客 260

STM32F407中文手册

STM32f407中文手册 本参考手册面向应用开发人员,提供有关使用 STM32F405xx/07xx、STM32F415xx/17xx、 STM32F42xxx 和 STM32F43xxx 微控制器存储器与外设的完整信息。STM32F405xx/07xx、STM32F415xx/17xx、STM32F42xxx 和 STM32F43xxx 构成一个微 控制器系列,各产品具有不同的存储器大小、封装和外设。有关订购信息以及器件的机械与电气特性,请参见数据手册。有关 ARM Cortex™-M4F 内核的信息,请参见《Cortex™-M4F 技术参考手册》。

大中型超市收银系统(可直接运行)

光步前台收银系统系统功能:商品销售、商品退货、挂单、收银员权限、会员销售、单品取消、整单取消、商品查询、脱机销售、脱机数据上传,基本资料下载,开钱箱、小票打印、收款结算,支持多币种付款方式(现金、储值卡、礼券、微信等),支持多种促销。运行环境:.net 4.0登陆信息用户名:1001 密码:123456内置商品信息,商品编码可输入00001000 - 00010000,测试效果相关功能键F5 结算 F9挂单 F11商品查询 F8退货 Insert单品取消 Del整单取消 F12开钱箱F2 会员 Ctrl+Z退出系统 Home 锁屏 相关源码待公开

OpenClaw 深度调查:把 AI 助手做成你自己的控制平面

对 openclaw/openclaw 的完整技术尽调:Gateway 架构、Agent 运行时、工具与沙箱、30 个消息通道、181 个插件、安全信任边界、工程代价与采用建议。17 章 / 10 张图,全部数字标注口径,数据截至 2026-10-31。

厚积薄发 363

visio安装包

Microsoft Office Visio 是一款专业的制图软件。利用经过更新的形状、协作工具和数据链接图表,创建专业级图表以简化复杂的信息。

IDEA 简介:从零认识 IntelliJ IDEA

IDEA 全称,是由 JetBrains 公司开发的一款主流 Java 集成开发环境(IDE)。它集代码编辑、编译、调试、测试、版本控制、构建管理等功能于一体,被大量 Java 开发者视为日常开发的首选工具。所谓 IDE(Integrated Development Environment,集成开发环境),就是把写代码所需的各类工具整合到同一个界面中。相比只用文本编辑器加命令行编译,IDE 能显著减少重复操作,让开发者把精力集中在业务逻辑上。

ztI10的博客 187

efm32 入门.pdf

本PDF共计22页,介绍的是操作驱动API,下载与否自己斟酌。

TransactionInactiveError:await 之后事务已经不活跃了

TransactionInactiveError 出现于 IndexedDB 事务已结束(非 active 状态)时调用 put、get 等操作。常见于 await 后或 setTimeout 中执行请求,因任务已完成,事务已提交或进入 finished 状态。请求必须在创建事务的同一任务中挂起,否则无效。解决方法:在 onsuccess 回调内同步发起后续操作,或提前将所有请求挂入事务。避免在异步上下文中使用事务操作,确保请求在事务活跃期内完成。

qq_40822000的博客 160

Docker部署linkding:HTTPS与数据备份

部署私人书签库,除了让页面能够打开,还需要考虑几个实际问题:重建容器后数据是否保留、登录和保存是否正常、升级前怎样备份,以及出现 502 或 CSRF 错误时从哪里检查。本文使用 Docker Compose 部署 linkding,采用默认 SQLite 数据库,通过 Caddy 提供 HTTPS,并完成账户创建、数据持久化验证和备份。如果重建后账户或书签消失,应先检查是否换了项目目录、改了挂载路径,或者启动了另一套实例,不要急着重新初始化。遇到权限问题,应根据日志确认具体目录和运行用户,再调整权限。

tedcloud123的博客 194

云计算课程小组PPT汇报任务书

36人分为6组,每组6人;形式:生活案例调研 + 理论解读;汇报时长7‑9min;PPT建议10‑14页。

Michelle8023的博客 229

企业官网源码与数据资产交接:四项权利、五组自动检查

企业官网源码交接不仅要看压缩包,还要核域名、云资源、构建脚本、数据库恢复、权限与密钥。

sweetofmine1的博客 187

软件项目交付时,源代码之外还应该交付哪些技术资料?

软件项目交付时,除可运行程序外,还应核对源代码、数据库结构、接口文档、部署运维、测试验收和账号资产。本文从实际交付环节拆解完整资料清单,并说明合同如何约定交付范围、知识产权与维护边界。

chengduyoushu的博客 309

Java 项目实战:交友项目后台系统——网关统一鉴权与 B 端管理后台

本次改造通过引入SpringCloud Gateway实现网关统一鉴权与路由,新建mf-admin管理端复用C端域服务(Dubbo调用),实现管理员登录与用户管理四件查询。核心套路:网关白名单+双体系JWT(同工具不同payload)、管理端零直连业务库、分页形态区分(MySQL自动count/Mongo手动count)、浏览器端独立聚合。踩坑12条,附自检清单与后续改造方向,可复用于多角色后台系统。

Wang的专栏 73

大表 ALTER TABLE 怎么做?NineData OnlineDDL 配置与执行实践

介绍NineData OnlineDDL大表变更实践,涵盖执行策略、切表控制、拷贝批次与可视化进度追踪。

dbasenewbie的博客 296

Axum 初步入门学习笔记

)自定义错误实现后,可以直接作为Result的ErrAxum 是一个构建在 Tokio、Tower、Hyper 之上的 Rust Web 框架,核心能力是:Router 路由、Extractor 提取器、Tower 中间件、IntoResponse 响应机制、State 共享状态。那张分层架构图是“地图”不是“说明书”:它帮你建立全局观,但 Service、Repository、数据库、Redis、MQ 都是你自己在 Handler 内部调用的业务/基础设施层,不是 Axum 自带的。

weixin_43219667的博客 206

MySQL报Too many open files?句柄耗尽的排查与参数配置

文章讲清 max_connections 与 open_files_limit 两个额度、句柄被谁占走、分区表为什么最容易踩,给出从 error log 到 /proc/pid/limits 的定位顺序与句柄预算算法。

「一个在数据库赛道上边学边分享的学妹」——聚焦国产数据库/信创方向、分享学习笔记、踩坑经验和行业观察。 338
上一篇: 443 Chapter8. Failover clustering -- not completed
下一篇: 443 Chapter7.Planning for High Availability in the Enterprise
aoding8407
博客等级 码龄11年 2粉丝 0原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值