WITH EXCEPT_ALL
AS
(
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)) AS rownum,
country, region, city
FROM HR.Employees
EXCEPT
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)),
country, region, city
FROM Sales.Customers
)
SELECT country, region, city
FROM EXCEPT_ALL;
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)) AS rownum,
country, region, city
FROM HR.Employees
INTERSECT
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)),
country, region, city
FROM Sales.Customers;
WITH INTERSECT_ALL
AS
(
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)) AS rownum,
country, region, city
FROM HR.Employees
INTERSECT
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)),
country, region, city
FROM Sales.Customers
)
SELECT country, region, city
FROM INTERSECT_ALL;
Безопасность данных – не место для халтуры
Атака вида SQL-injection продолжает оставаться одной из самых серьезных угроз для SQL Server. База данных может быть восприимчива к таким атакам, если она или приложение (в основном это веб-приложения) содержат код, который хакер может использовать для ввода дополнительной команды SQL в одном из полей ввода приложения. Затем приложение подставляет модифицированный код в базу данных, в результате хакер может получить доступ к конфиденциальной информации, изменению данных или объектов базы данных, и запуску административных операций в базе данных или, в некоторых случаях, по отношению к операционной системе.
Для защиты своих баз и приложений разработчики должны использовать лучшие практики, позволяющие смягчить угрозу SQL-injection. Для разработчиков SQL Server и разработчиков приложений, основной задачей является как можно большая изоляция команд SQL от введенных пользователем данных.
Атака SQL-injection происходит, когда код приложения или базы данных динамически генерирует запросы, объединяя команду с введенными данными пользователя. Пользователь вводит информацию через интерфейс приложения, которая она становится частью SQL команды и выполняется в базе данных. Давайте посмотрим на примере, рисунок 1 показывает простое приложение с одним пользовательским полем ввода.
Рисунок 1. Ввод идентификатора пользователя в пользовательский интерфейс
Приложение возвращает список фильмов, которые пользователь взял напрокат. Пользователь вводит идентификатор учетной записи и нажимает Enter. Приложения Приложение или базы данных, объединяет логин с предопределенной командой SELECT так, чтобы логин становится частью условия WHERE. Если пользователь предоставляет правильный логин, список фильмов, возвращается в интерфейсе и все счастливы.
Теперь предположим, что хакер хочет проникнуть в систему для получения данных или нанести ущерб. Например, хакер может попытаться удалить все данные из таблицы аренды (предполагается, что хакер может получить имя таблицы через отдельный хак). Вместо предоставления простого идентификатора пользователя, хакер мог бы также добавить команду TRUNCATE TABLE, как показано на рисунке 2.
Рисунок 2. Ввод изгоев код в пользовательском интерфейсе
Обратите внимание, что значение, введенное в пользовательский интерфейс, теперь включает в себя точку с запятой, а затем команду TRUNCATE TABLE. Поскольку SQL Server поддерживает точку с запятой в качестве разделителя команд и поддерживает несколько команд в блоке, хакер может легко отправить эти команды в базу данных. В результате, таблица аренды будет удалена.
Это, конечно, является очень простым сценарием, но он иллюстрирует логику нападения SQL инъекции. Фокус в том, чтобы убедиться, что этого не может произойти, именно поэтому данное руководство может помочь в том, что нельзя делать.
В идеальном мире, команда разработчиков имеет полный доступ к базе данных для доступа приложения к данным. Например, команда может настаивать на том, что все запросы, сгенерированные приложением будут параметризованы или приложение будет иметь доступ к данным только через хранимые процедуры, а не выпускать собственные SQL команды.
Но реальность разработки приложений и обслуживания может сделать такие заявления трудно реализуемыми, особенно при работе с унаследованными системами, в смешанных средах, и при противоположных мнениях. Тем не менее, разработчики баз данных должны оставаться бдительными, чтобы защититься от атак SQL-injection. По этой причине, для этого был придуман набор правил, которые следует учитывать при создании компонентов базы данных для поддержки различных типов приложений. Не все из этих правил являются применимыми к каждой ситуации, и некоторые из них лишь частично применимы, но к каждому из них необходимо присмотреться. Таким образом, вы можете применить то, что наилучшим образом соответствует вашим потребностям в текущей ситуации.
Когда это возможно, нужно разрешить приложениям взаимодействовать с базой данных только через хранимые процедуры. Таким образом, учетным записям базы данных, используемым приложением, требуются только разрешения, необходимые для выполнения хранимой процедуры, без необходимости разрешения на доступ к базовым таблицам. Даже если код приложения подвержен SQL-injection, нападение не удастся, так как приложению не хватает разрешений, необходимых для доступа или манипулирования этими таблицами. Кроме того, хранимые процедуры проверяет тип входных параметров, что может помочь смягчить атаку, если хакер пытается передать значение, которое нарушает тип.
Несмотря на эти преимущества, хранимых процедур самих по себе не достаточно, чтобы защититься от SQL-injection. Они должны быть частью общей стратегии защиты от подобных атак. Тем не менее, некоторые хранимые процедуры являются более безопасными в использовании, чем другие. Например, статические хранимые процедуры не имеют параметров и, следовательно, не могут использоваться для инъекции вредоносного кода. Хранимых Хранимые процедуры, которые содержат только параметризованные команды SQL, также устойчивы к SQL инъекции, поскольку данные команд хранятся отдельно от самой команды. Другими словами, необходимо избегать динамического SQL в хранимых процедурах, когда это возможно.
Динамический SQL может значительно увеличить риск атаки SQL-injection, когда команда языка объединяется с пользовательскими данными. В некоторых случаях, однако, не возможно избежать динамического SQL. Например, вы можете определить хранимую процедуру, которая создает логин в базе данных, где логин передается в качестве параметра. Проблема в том, что заявление CREATE LOGIN не принимает значение переменной для имени, таким образом вы вынуждены построить вашу команду динамически и при этом пройти проверку синтаксиса ядром базы данных.
Один из способов помочь смягчить риски, связанные с динамическим SQL – корректно экранировать пользовательский ввод. Экранированию пользовательских значений помогают специальные безвредные символы, которые могут быть переданы на вход, (такие как скобки или одинарные кавычки). При использовании с другими элементами языка, эти символы, при использовании с другими элементами языка могут представлять угрозу для базы данных, когда объединяются в статической части SQL запроса. Чтобы избежать этих символов, используйте функции QUOTENAME или REPLACE по мере необходимости для обработки идентификаторов и строковых значений. При использовании какой-либо функции, убедитесь, что правильно рассчитали длину буфера для использования экранирующих символов, иначе вы подставите себя для атаки через усечение, (один из типов атаки SQL-injection, который использует усечение для инъекции вредоносного кода).
Другим способом предотвращения SQL-injection является использование системной хранимой процедуры sp_executesql, вместо оператора EXECUTE, для выполнения динамических запросов. С помощью хранимой процедуры sp_executesql, у вас есть способ использовать параметризацию динамического запроса, так что с данные остаются отдельно от команд языка. Но убедитесь, что правильно используете хранимую процедуру, не используйте ее в качестве простой замены EXECUTE. Вы должны включить параметры в определение команды при его создании и когда вы вызываете хранимую процедуру sp_executesql.
Даже если вы можете ограничить приложения доступом к хранимым процедурам и избежать использования динамического SQL, вы все равно должны убедиться, что доступ к базе данных ограничивается в полной мере. Одним из ваших лучших средств защиты является следование стратегии наименьших привилегий. Каждой учетной записи базы данных должны быть назначены минимальные права, необходимые для доступа к данным. Именно поэтому ограничение доступа к хранимым процедурам может быть настолько эффективным. В идеале, вы предоставляете право выполнить процедуры и запрещаете доступ к чему-либо еще. Если вы позволяете приложению выполнить SQL запросы напрямую, приложение должно использовать учетные записи с наименьшими привилегиями и, с четким указанием того, что учетная запись может читать и/или изменять в базе данных. Ни при каких обстоятельствах вы не должны назначить учетной записи привилегии администратора (например, sysadmin) в приложении.
Даже внутри хранимых процедур, вы должны следовать стратегии минимального доступа. Например, если вы используете оператор EXECUTE AS, для запуска SQL запросов в процедуре, используйте учетную запись с минимальными привилегиями. Если высокопривилегированная операция должна быть выполнена, создайте хранимые процедуры для выполнения этой операции и подпишите хранимые процедуры с помощью сертификата. Цель состоит в том, чтобы гарантировать, что даже если злоумышленник обнаружит дыру в безопасности приложения, он мало что мог сделать. Приложение, имеющее доступ к БД, всегда должно быть ограничено низким уровнем привилегий учетной записи, имеющей минимальные права для выполнения запросов, выполняемых в базе данных.
Независимо от того, насколько тщательны вы были в защите от атак SQL-injection, вы все равно должны запускать код базы данных через необходимые проверки, чтобы убедиться – это безопасно. Начните с проверок кода, которые специально проверяют код его на восприимчивость к SQL-injection, таких как неправильное использование динамического SQL. Отсюда вы можете перейти к фазе проверки качества. В дополнение к любому нормальному тестированию и проверки процессов, которые будут выполняться в базе данных, вместе с тем поддерживаемый код должен быть подвергнут попытке SQL-injection, чтобы попробовать найти уязвимости. Процесс тестирования должен включать все уровни SQL-injection, в том числе попытку усечения данных. Наконец, когда база данных используется, лог-файлы и другие устройства слежения должны быть использованы для мониторинга баз данных для любых признаков SQL-injection.
Принципы, предложенные выше, только затрагивают основные темы, но они должны направить вас в правильном направлении. Учитывая распространение мобильных приложений и растущую потребность синхронизировать данные между несколькими устройствами, необходимо проявлять бдительность больше, чем когда-либо. Даже одна уязвимость поставит вашу базу в зону риска, а безопасность данных – не место для халтуры. Разработчики баз данных и разработчики приложений должны работать вместе, чтобы избежать таких уязвимостей. Атаки SQL-injection могут быть предотвращены, но только если вы предпримете необходимые шаги для защиты вашей системы.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
IF (OBJECT_ID('tmp_isolation_level') IS NOT NULL) DROP TABLE [dbo].[tmp_isolation_level]GOCREATE TABLE [dbo].[tmp_isolation_level] ( [rowid] int IDENTITY (1,1), [textvalue] varchar (100))GOINSERT INTO [dbo].[tmp_isolation_level] ([textvalue])SELECT '111111111111'UNION ALLSELECT '222222222222'UNION ALLSELECT '333333333333'UNION ALLSELECT '444444444444'UNION ALLSELECT '555555555555'UNION ALLSELECT '666666666666'GO
| Поток 1 | Поток 2 | Комментарий |
|---|---|---|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
|
Устанавливаем уровень изоляции. В данном примере нам не важно какой уровень изоляции будет на 2-м потоке, поэтому я использую значение по умолчанию или "READ COMMITTED" |
BEGIN TRANSACTION |
BEGIN TRANSACTION
|
Открываем транзакции |
SELECT
*
FROM[tmp_isolation_level]
|
- | Читаем таблицу в первом потоке (тут все хорошо) |
| - |
SELECT *
FROM [tmp_isolation_level]
|
Читаем таблицу во втором потоке (тут все хорошо) |
| - |
update [tmp_isolation_level]
|
Пытаемся внести изменения в данные, во втором потоке. В этом месте 2-й запрос блокируется 1-м и ожидает завершения транзакиции 1-го потока |
COMMIT TRANSACTION
|
- | Завершение транзакции 1-го потока разблокирует ресурс (таблицу). Записываются изменения 1-го потока |
| - |
update [tmp_isolation_level]
|
Выполняется разблокированный UPDATE |
| - |
COMMIT TRANSACTION
|
Завершение транзакции 2-го потока, записываются изменения 2-го потока |
| Поток 1 | Поток 2 | Комментарий |
|---|---|---|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
|
Устанавливаем уровень изоляции |
BEGIN TRANSACTION
|
BEGIN TRANSACTION
|
Открываем транзакции |
SELECT *
FROM [tmp_isolation_level]
|
- | Читаем таблицу в первом потоке (тут все хорошо) |
| - |
SELECT *
FROM [tmp_isolation_level]
|
Читаем таблицу во втором потоке (тут все хорошо) |
UPDATE [tmp_isolation_level]
|
- |
Пытаемся внести изменения в данные, в первом потоке, но данные являются заблокированными в этом месте 1-й запрос блокируется 2-м и ожидает завершения транзакиции 2-го потока |
| - |
UPDATE [tmp_isolation_level]
|
Хорошо, пробуем внести изменения данных во 2-м потоке. НО, для 2-го потока данные тоже заблокированы.... |
Сообщение 1205, уровень 13, состояние 45, строка 2
|
- |
В одном из потоков вылетает ошибка о взаимоблокировке. Т.е. 1-й и 2-й потоки заблокировали друг друга. Ядро базы обнаруживает этот факт, прерывает один из потоков, и завершает второй. Прерваный поток завершается с откатом изменений (ROLLBACK TRANSACTION) |
| - |
COMMIT TRANSACTION
|
Прерывание 1-го потока разблокирует 2-й, а он в свою очередь завершается без ошибок. |
| - |
Выполнение команд успешно завершено.
|
- |
Пришлось не так давно выяснять, почему база в которой и данных то почти нет, съела 250Гб...
Выяснили с помощью вот этого скрипта.
SELECT
[o].[id]
, OBJECT_NAME([o].[id], DB_ID()) AS [table_name]
, [i1].[rowcnt] AS [rows]
, (ISNULL(SUM([i1].[reserved]), 0) + ISNULL(SUM([i2].[reserved]), 0)) * [ps].[pagesizeKB] AS [reservedKB]
, (ISNULL(SUM([i1].[dpages]), 0) + ISNULL(SUM([i2].[used]), 0)) * [ps].[pagesizeKB] AS [dataKB]
, ((ISNULL(SUM([i1].[used]), 0) + ISNULL(SUM([i2].[used]), 0))
- (ISNULL(SUM([i1].[dpages]), 0) + ISNULL(SUM([i2].[used]), 0))) * [ps].[pagesizeKB] AS [index_sizeKB]
, ((ISNULL(SUM([i1].[reserved]), 0) + ISNULL(SUM([i2].[reserved]), 0))
- (ISNULL(SUM([i1].[used]), 0) + ISNULL(SUM([i2].[used]), 0))) * [ps].[pagesizeKB] AS [unusedKB]
FROM [sys].[sysobjects] [o]
LEFT OUTER JOIN [sys].[sysindexes] [i1] ON [i1].[id] = [o].[id] AND [i1].[indid] < 2
LEFT OUTER JOIN [sys].[sysindexes] [i2] ON [i2].[id] = [o].[id] AND [i2].[indid] = 255
CROSS JOIN (SELECT ([low] / 1024) [pagesizeKB] FROM [master].[dbo].[spt_values] WHERE [number] = 1 AND [type] = 'E' ) AS [ps]
WHERE OBJECTPROPERTY([o].[id], N'IsUserTable') = 1 --same as: [o].xtype = 'IsView'
OR (OBJECTPROPERTY([o].[id], N'IsView') = 1 AND OBJECTPROPERTY([o].[id], N'IsIndexed') = 1)
GROUP BY [ps].[pagesizeKB], [o].[id], [i1].[rowcnt]
ORDER BY 3 DESC
Содержит одну строку для каждой базы данных в экземпляре Microsoft SQL Server.
Если участник, вызывающий sys.databases, не является владельцем базы данных и база данных не master или tempdb, минимально требуемыми разрешениями для просмотра соответствующей строки являются разрешения уровня сервера ALTER ANY DATABASE или VIEW ANY DATABASE, или разрешение CREATE DATABASE в базе данных master. Определить базу данных, к которой подключен участник, можно в таблице sys.databases.Если база данных не находится в режиме ONLINE или параметр AUTO_CLOSE установлен в ON, значения некоторых столбцов могут быть равны NULL. Если база данных находится в режиме OFFLINE, соответствующая строка не будет видна пользователям с недостаточными правами доступа. Чтобы увидеть соответствующую строку в случае, когда база данных находится в режиме OFFLINE, пользователю необходимо иметь, по крайней мере, разрешение уровня сервера ALTER ANY DATABASE или разрешение CREATE DATABASE в базе данных master.
Имя столбца |
Тип данных |
Описание |
||||
name |
Имя базы данных, уникальное внутри экземпляра SQL Server. |
|||||
database_id |
Идентификатор базы данных, уникальный внутри экземпляра SQL Server. |
|||||
source_database_id |
Не NULL = идентификатор базы данных-источника данного моментального снимка базы данных.
NULL = моментальный снимок не базы данных. |
|||||
|
owner_sid |
varbinary(85) |
SID (идентификатор безопасности) внешнего владельца базы данных, зарегистрированного на сервере. | ||||
create_date |
Дата создания или переименования базы данных. Для базы данныхtempdb это значение изменяется каждый раз при перезапуске сервера. | |||||
compatibility_level |
Целое число, которое соответствует версии SQL Server, для которой поведение совместимо:
90
100
110
NULL — база данных не находится в режиме «в сети», либо параметр AUTO_CLOSE установлен в ON, и база данных закрыта. |
|||||
collation_name |
Параметры сортировки для базы данных. Действует как параметры сортировки по умолчанию для базы данных.
NULL — база данных не находится в режиме «в сети», либо параметр AUTO_CLOSE установлен в ON, и база данных закрыта. |
|||||
user_access |
Установка доступа пользователя:
0 = указано MULTI_USER.
1 = указано SINGLE_USER;
2 = указан RESTRICTED_USER. |
|||||
user_access_desc |
nvarchar(60) |
Описание установки доступа пользователя:
MULTI_USER
SINGLE_USER
RESTRICTED_USER |
||||
is_read_only |
1 = база данных находится в режиме READ_ONLY
0 = база данных находится в режиме READ_WRITE |
|||||
is_auto_close_on |
1 = параметр AUTO_CLOSE находится в состоянии ON
0 = параметр AUTO_CLOSE находится в состоянии OFF |
|||||
is_auto_shrink_on |
1 = параметр AUTO_SHRINK находится в состоянии ON
0 = параметр AUTO_SHRINK находится в состоянии OFF |
|||||
состояние |
Состояние базы данных
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
|
|||||
state_desc |
nvarchar(60) |
Описание состояния базы данных:
ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE
Дополнительные сведения см. в разделе Состояния базы данных. |
||||
is_in_standby |
База данных доступна только для чтения для журнала восстановления. |
|||||
is_cleanly_shutdown |
1 = база данных закрыта верно; восстановление при запуске не требуется
0 = база данных закрыта неверно; требуется восстановление при запуске |
|||||
is_supplemental_logging_enabled |
1 = SUPPLEMENTAL_LOGGING в состоянии ON
0 = SUPPLEMENTAL_LOGGING в состоянии OFF |
|||||
snapshot_isolation_state |
Состояние транзакций изоляции моментальных снимков, задаваемое при помощи параметра ALLOW_SNAPSHOT_ISOLATION.
0 = изоляция моментальных снимков в состоянии OFF (по умолчанию). Изоляция моментальных снимков запрещена.
1 = изоляция моментальных снимков в состоянии ON. Изоляция моментальных снимков разрешена.
2 = изоляция моментальных снимков в состоянии перехода в состояние OFF. Для всех транзакций записываются изменения. Нельзя запустить новые транзакции, использующие изоляцию моментальных снимков. База данных находится в состоянии перехода в состояние OFF до тех пор, пока все транзакции, активные при выполнении инструкции ALTER DATABASE, не будут завершены.
3 = изоляция моментальных снимков в состоянии перехода в состояние ON. Для новых транзакций записываются изменения. Транзакции не могут использовать изоляцию моментальных снимков до тех пор, пока состояние изоляции моментальных снимков не перейдет в 1 (ON). База данных находится в состоянии перехода в состояние ON до тех пор, пока все транзакции, активные при выполнении инструкции ALTER DATABASE, не будут завершены. |
|||||
snapshot_isolation_state_desc |
nvarchar(60) |
Описание состояния транзакций изоляции моментальных снимков, задаваемое при помощи параметра ALLOW_SNAPSHOT_ISOLATION:
OFF
ON
IN_TRANSITION_TO_ON
IN_TRANSITION_TO_OFF |
||||
is_read_committed_snapshot_on |
1 = параметр READ_COMMITTED_SNAPSHOT установлен в значение ON. Операции чтения с уровнем изоляции read-committed основаны на просмотре моментальных снимков и не запрашивают блокировок.
0 = параметр READ_COMMITTED_SNAPSHOT установлен в значение OFF (по умолчанию). Операции чтения с уровнем изоляции read-committed используют разделяемые блокировки. |
|||||
recovery_model |
Выбранная модель восстановления:
1 = FULL
2 = BULK_LOGGED
3 = SIMPLE |
|||||
recovery_model_desc |
nvarchar(60) |
Описание выбранной модели восстановления:
FULL
BULK_LOGGED
SIMPLE |
||||
page_verify_option |
Значение параметра PAGE_VERIFY:
0 = NONE
1 = TORN_PAGE_DETECTION
2 = CHECKSUM | |||||
page_verify_option_desc |
nvarchar(60) |
Описание значений параметра PAGE_VERIFY:
NONE
TORN_PAGE_DETECTION
CHECKSUM |
||||
|
is_auto_create_stats_on
|
1 = AUTO_CREATE_STATISTICS
в
состоянии
ON
0 = AUTO_CREATE_STATISTICS
в
состоянии
OFF
|
|||||
|
is_auto_update_stats_on
|
1 = AUTO_UPDATE_STATISTICS
в
состоянии
ON
0 = AUTO_UPDATE_STATISTICS
в
состоянии
OFF
|
|||||
|
is_auto_update_stats_async_on
|
1 = AUTO_UPDATE_STATISTICS_ASYNC
в
состоянии
ON
0 = AUTO_CREATE_STATISTICS_ASYNC
в
состоянии
OFF
|
|||||
|
is_ansi_null_default_on
|
1 = ANSI_NULL_DEFAULT в состоянии ON
0 = ANSI_NULL_DEFAULT в состоянии OFF
|
|||||
|
is_ansi_nulls_on
|
1 = ANSI_NULLS в состоянии ON
0 = ANSI_NULLS в состоянии OFF
|
|||||
|
is_ansi_padding_on
|
1 = ANSI_PADDING в состоянии ON
0 = ANSI_PADDING в состоянии OFF
|
|||||
|
is_ansi_warnings_on
|
1 = ANSI_WARNINGS
в
состоянии
ON
0 = ANSI_WARNINGS
в
состоянии
OFF
|
|||||
|
is_arithabort_on
|
1 = ARITHABORT в состоянии ON
0 = ARITHABORT в состоянии OFF
|
|||||
|
is_concat_null_yields_null_on
|
1 = CONCAT_NULL_YIELDS_NULL
в
состоянии
ON
0 = CONCAT_NULL_YIELDS_NULL
в
состоянии
OFF
|
|||||
|
is_numeric_roundabort_on
|
1 = NUMERIC_ROUNDABORT
в
состоянии
ON
0 = NUMERIC_ROUNDABORT
в
состоянии
OFF
|
|||||
|
is_quoted_identifier_on
|
1 = QUOTED_IDENTIFIER
в
состоянии
ON
0 = QUOTED_IDENTIFIER
в
состоянии
OFF
|
|||||
|
is_recursive_triggers_on
|
1 = RECURSIVE_TRIGGERS
в
состоянии
ON
0 = RECURSIVE_TRIGGERS
в
состоянии
OFF
|
|||||
|
is_cursor_close_on_commit_on
|
1 = CURSOR_CLOSE_ON_COMMIT
в
состоянии
ON
0 = CURSOR_CLOSE_ON_COMMIT
в
состоянии
OFF
|
|||||
|
is_local_cursor_default
|
1 = CURSOR_DEFAULT соответствует локальному курсору
0 = CURSOR_DEFAULT соответствует глобальному курсору
|
|||||
|
is_fulltext_enabled
|
1 = полнотекстовый режим включен для данной базы данных
0 = полнотекстовый режим отключен для данной базы данных
|
|||||
|
is_trustworthy_on
|
1 = база данных помечена как надежная
0 = база данных не помечена как надежная
|
|||||
|
is_db_chaining_on
|
1 = межбазовые цепочки владения в состоянии ON
0 = межбазовые цепочки владения в состоянии OFF
|
|||||
|
is_parameterization_forced
|
1 = параметризация в состоянии FORCED
0 = параметризация в состоянии SIMPLE
|
|||||
|
is_master_key_encrypted_by_server
|
1 = база данных имеет главный ключ шифрования
0 = база данных не имеет главного ключа шифрования
|
|||||
|
is_published
|
1 = база данных является базой данных публикации в топологии репликации транзакций или моментальных снимков
0 = не является базой данных публикации
|
|||||
|
is_subscribed
|
Данный столбец не используется. Он всегда возвращает 0, независимо от состояния подписчика базы данных.
|
|||||
|
is_merge_published
|
1 = база данных является базой данных публикации в топологии репликации слиянием
0 = база данных не является базой данных публикации в топологии репликации слиянием
|
|||||
|
is_distributor
|
1 = база данных является базой данных распространителя в топологии репликации
0 = база данных не является базой данных распространителя в топологии репликации
|
|||||
|
is_sync_with_backup
|
1 = база данных помечена для синхронизации с резервной копией при помощи репликации
0 = база данных не помечена для синхронизации с резервной копией при помощи репликации
|
|||||
|
service_broker_guid
|
Идентификатор компонента Service Broker для данной базы данных. Используется как целевой экземпляр
broker_instance
в таблице маршрутизации.
|
|||||
|
is_broker_enabled
|
1 = брокер в этой базе данных в данный момент отправляет и принимает сообщения.
0 = все отправленные сообщения останутся в очереди передачи, а полученные сообщения не будут помещены в очередь в этой базе данных.
По умолчанию в восстановленных или прикрепленных базах данных брокер отключен.Исключением является зеркальное отображение базы данных, при котором брокер включается после отработки отказа.
|
|||||
|
log_reuse_wait
|
Действие или объект, ожидаемые в настоящий момент журналом транзакций:
0 = ничего;
1 = контрольная точка;
2 = резервная копия журнала;
3 = активное резервное копирование или восстановление;
4 = активная транзакция;
5 = зеркальное отображение базы данных;
6 = репликация;
7 = создание моментального снимка базы данных;
8 = просмотр журнала;
9 = вторичная реплика Группы доступности AlwaysOn применяет записи журнала транзакций этой базы данных к соответствующей базе данных-получателю;
10 = только для внутреннего использования;
11 = только для внутреннего использования;
12 = только для внутреннего использования;
13 = самая старая страница;
14 = другое (переходное).
|
|||||
|
log_reuse_wait_desc
|
nvarchar
(60)
|
Описание действия или объекта, ожидаемого в настоящий момент журналом транзакций:
NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
AVAILABILITY_REPLICA
Только для внутреннего использования
Только для внутреннего использования
Только для внутреннего использования
OLDEST_PAGE
OTHER_TRANSIENT
|
||||
|
is_date_correlation_on
|
1 = DATE_CORRELATION_OPTIMIZATION
в
состоянии
ON
0 = DATE_CORRELATION_OPTIMIZATION
в
состоянии
OFF
|
|||||
|
is_cdc_enabled
|
1 = в базе данных включена система отслеживания измененных данных. Дополнительные сведения см. в разделе
sys.sp_cdc_enable_db (Transact-SQL)
.
|
|||||
|
is_encrypted
|
Указывает, зашифрована ли база данных (отражает последнее состояние, установленное с помощью предложения ALTER DATABASE SET ENCRYPTION). Может использоваться одно из следующих значений:
1 = зашифрована;
0 = не зашифрована.
Дополнительные сведения о шифровании баз данных см. в разделе
Прозрачное шифрование данных (TDE)
.
Если база данных находится в процессе расшифровки, столбец
is_encrypted
отображает значение 0.Состояние процесса шифрования базы данных можно просмотреть с помощью динамического административного представления
sys.dm_database_encryption_keys
.
|
|||||
|
is_honor_broker_priority_on
|
Указывает, учитываются ли в базе данных приоритеты диалогов (отражает последнее состояние, установленное предложением ALTER DATABASE SET HONOR_BROKER_PRIORITY). Может использоваться одно из следующих значений:
1 = HONOR_BROKER_PRIORITY имеет значение ON;
0 = HONOR_BROKER_PRIORITY
имеет
значение
OFF.
|
|||||
|
replica_id
|
Уникальный идентификатор локальной реплики доступности Группы доступности AlwaysOn группы доступности, если таковая имеется, частью которой является база данных.
NULL = база данных не является частью реплики доступности в группе доступности.
|
|||||
|
group_database_id
|
Уникальный идентификатор базы данных в группе доступности, в которой участвует база данных, если такая группа имеется. Значение
group_database_id
для этой базы данных совпадает в первичной реплике и в каждой вторичной реплике, для которой база данных присоединена к группе доступности.
NULL = база данных не является частью реплики доступности в любой группе доступности.
|
|||||
|
default_language_lcid
|
Указывает идентификатор локали (lcid) языка по умолчанию автономной базы данных.
Примечание.
Такие функции, как
Настройка параметра конфигурации сервера «язык по умолчанию»
хранимой процедуры
sp_configure
. Это значение равно
null
для неавтономной базы данных.
|
|||||
|
default_language_name
|
nvarchar
(128)
|
Указывает язык по умолчанию автономной базы данных.
Это значение равно
null
для неавтономной базы данных.
|
||||
|
default_fulltext_language_lcid
|
Указывает идентификатор локали (lcid) языка полнотекстового поиска по умолчанию автономной базы данных.
Примечание.
Такие функции, как
Настройка параметра конфигурации сервера «язык полнотекстового поиска по умолчанию»
по умолчанию хранимой процедуры
sp_configure
. Это значение равно
null
для неавтономной базы данных.
|
|||||
|
default_fulltext_language_name
|
nvarchar
(128)
|
Указывает язык полнотекстового поиска по умолчанию автономной базы данных.
Это значение равно
null
для неавтономной базы данных.
|
||||
|
is_nested_triggers_on
|
Указывает, разрешены ли вложенные триггеры в автономной базе данных.
0 = вложенные триггеры не разрешены
1 = вложенные триггеры разрешены
Примечание.
Такие функции, как
Настройка конфигурации сервера nested triggers
хранимой процедуры
sp_configure
. Это значение равно
null
для неавтономной базы данных. Дополнительные сведения см. в разделе
sys.configurations (Transact-SQL)
.
|
|||||
|
is_transform_noise_words_on
|
Указывает должны ли преобразовываться пропускаемые слова в автономной базе данных.
0 = пропускаемые слова не должны преобразовываться.
1 = пропускаемые слова должны преобразовываться.
Примечание.
Такие функции, как
Параметр конфигурации сервера «transform noise words»
хранимой процедуры
sp_configure
. Это значение равно
null
для неавтономной базы данных.Дополнительные сведения см. в разделе
sys.configurations (Transact-SQL)
.
|
|||||
|
two_digit_year_cutoff
|
Указывает числовое значение в диапазоне от 1753 до 9999, представляющее пороговый год для интерпретации года, представленного двумя цифрами, в виде года, представленного четырьмя цифрами.
Примечание.
Такие функции, как
Настройка параметра конфигурации сервера two digit year cutoff
хранимой процедуры
sp_configure
. Это значение равно
null
для неавтономной базы данных.Дополнительные сведения см. в разделе
sys.configurations (Transact-SQL)
.
|
|||||
|
containment
|
tinyint
not null
|
Указывает состояние включения базы данных.
0 = включение базы данных отключено
1 = база данных находится в состоянии частичного включения
2 = база данных находится в состоянии полного включения
|
||||
|
containment_desc
|
nvarchar
(60) not null
|
Указывает состояние включения базы данных.
NONE = прежняя версия базы данных (нулевое включение)
PARTIAL = частично автономная база данных
FULL = полностью автономная база данных
|
||||
|
target_recovery_time_in_seconds
|
Предполагаемое время восстановления базы данных в секундах. Допускает значение NULL.
|
ALTER VIEW [sys].[databases] AS
SELECT d.name, d.id AS database_id, r.indepid AS source_database_id,d.sid AS owner_sid, d.modified AS create_date,
convert(tinyint, (d.category/256) & 0xff) AS compatibility_level,
convert(sysname, CollationPropertyFromID(p.cid, 'name')) AS collation_name,
p.user_access, ua.name AS user_access_desc,
sysconv(bit, d.status & 0x400) AS is_read_only, -- DBR_RDONLY
sysconv(bit, d.status & 1) AS is_auto_close_on, -- DBR_CLOSE_ON_EXIT
sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on, -- DBR_AUTOSHRINK
p.state, st.name AS state_desc,
sysconv(bit, d.status & 0x200000) AS is_in_standby, -- DBR_STANDBY
sysconv(bit, d.status & 0x40000000) AS is_cleanly_shutdown, -- DBR_CLEANLY_SHUTDOWN
sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on, -- DBR_READCOMMITTED_SNAPSHOT
p.recovery_model, ro.name AS recovery_model_desc,
p.page_verify_option, pv.name AS page_verify_option_desc,
sysconv(bit, d.restat & 0x1000000) AS is_auto_create_stats_on, -- DBR_AUTOCRTSTATS
sysconv(bit, d.restat & 0x40000000) AS is_auto_update_stats_on, -- DBR_AUTOUPDSTATS
sysconv(bit, d.restat & 0x80000000) AS is_auto_update_stats_async_on, -- DBR_AUTOUPDSTATSASYNC
sysconv(bit, d.restat & 0x4000) AS is_ansi_null_default_on, -- DBR_ANSINULLDFLT
sysconv(bit, d.restat & 0x4000000) AS is_ansi_nulls_on, -- DBR_ANSINULLS
sysconv(bit, d.restat & 0x2000) AS is_ansi_padding_on, -- DBR_ANSIPADDING
sysconv(bit, d.restat & 0x10000000) AS is_ansi_warnings_on, -- DBR_ANSIWARNINGS
sysconv(bit, d.restat & 0x1000) AS is_arithabort_on, -- DBR_ARITHABORT
sysconv(bit, d.restat & 0x10000) AS is_concat_null_yields_null_on, -- DBR_CATNULL
sysconv(bit, d.restat & 0x800) AS is_numeric_roundabort_on, -- DBR_NUMEABORT
sysconv(bit, d.restat & 0x800000) AS is_quoted_identifier_on, -- DBR_QUOTEDIDENT
sysconv(bit, d.restat & 0x20000) AS is_recursive_triggers_on, -- DBR_RECURTRIG
sysconv(bit, d.restat & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
sysconv(bit, d.restat & 0x100000) AS is_local_cursor_default, -- DBR_DEFLOCALCURS
sysconv(bit, d.restat & 0x20000000) AS is_fulltext_enabled, -- DBR_FTENABLED
sysconv(bit, d.restat & 0x200) AS is_trustworthy_on, -- DBR_TRUSTWORTHY
sysconv(bit, d.restat & 0x400) AS is_db_chaining_on, -- DBR_DBCHAINING
sysconv(bit, d.restat & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM
sysconv(bit, d.restat & 64) AS is_master_key_encrypted_by_server, -- DBR_MASTKEY
sysconv(bit, d.category & 1) AS is_published,
sysconv(bit, d.category & 2) AS is_subscribed,
sysconv(bit, d.category & 4) AS is_merge_published,
sysconv(bit, d.category & 16) AS is_distributor,
sysconv(bit, d.category & 32) AS is_sync_with_backup,
d.svcbrkrguid AS service_broker_guid,
sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled,
p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
sysconv(bit, d.restat & 0x00000004) AS is_date_correlation_on -- DBR_DATECORRELATIONOPT
FROM master.sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, d.id ) p
LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
WHERE d.id < 0x7fff
AND has_access('DB', d.id ) = 1
GO
Системные базовые таблицы — это основные таблицы, в которых хранятся метаданные определенной базы данных. База данных master рассматривается особо, так как она содержит некоторые дополнительные таблицы, которых нет в других базах данных. Эти таблицы содержат устойчивые метаданные, областью которых является весь сервер.