As в sql: Оператор SQL AS: синтаксис, примеры

SQL — Синтаксис Alias (псевдоним)

Вы можете переименовать таблицу или столбец временно, давая другое имя, известное как alias (псевдоним). Использование таблицы псевдонимов для переименования таблицы в определенном заявлении SQL. Переименование является временное изменение и фактическое имя таблицы не изменяется в базе данных. В столбцах  псевдонимы используются для переименования столбцов таблицы для целей конкретного запроса SQL.

Синтаксис

Основной синтаксис псевдонима таблиц выглядит следующим образом.

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

 

Основной синтаксис псевдонима столбца состоит в следующем.

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

Примеры

Рассмотрим следующие две таблицы.

Таблица 1 — Таблица CUSTOMERS выглядит следующим образом:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Maxim    |  35 | Moscow    | 21000. 00 |
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 34000.00 |
|  5 | Ruslan   |  34 | Omsk      | 45000.00 |
|  6 | Dima     |  32 | SP        |          |
|  7 | Roma     |  34 | SP        |          |
+----+----------+-----+-----------+----------+

 

Таблица 2 — Таблица ORDERS выглядит следующим образом:

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2017-01-08 00:00:00 |           3 |  45500 |
| 100 | 2017-01-08 00:00:00 |           3 |  18000 |
| 101 | 2017-01-18 00:00:00 |           2 |  21500 |
| 103 | 2017-03-15 00:00:00 |           4 |  11000 |
+-----+---------------------+-------------+--------+

 

Теперь, следующий блок кода покажет использование псевдонима таблицы.

SQL> SELECT C.ID, C.NAME, C.AGE, O.AMOUNT 
   FROM CUSTOMERS AS C, ORDERS AS O
   WHERE  C.ID = O.CUSTOMER_ID;

 

Это произведет следующий результат.

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | Oleg     |  33 |  45500 |
|  3 | Oleg     |  33 |  18000 |
|  2 | AndreyEx |  38 |  21500 |
|  4 | Masha    |  35 |  11000 |
+----+----------+-----+--------+

 

Теперь используем псевдоним столбца.

SQL> SELECT  ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME
   FROM CUSTOMERS
   WHERE SALARY IS NOT NULL;

 

Это произведет следующий результат.

+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
|           1 | Maxim         |
|           2 | AndreyEx      |
|           3 | Oleg          |
|           4 | Masha         |
|           5 | Ruslan        |
|           6 | Dima          |
|           7 | Roma          |
+-------------+---------------+

 

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Агрегатные функции в sql и переименование столбцов

На уроке будет рассмотрена тема sql переименование столбца (полей) при помощи служебного слова AS; также рассмотрена тема агрегатные функции в sql. Будут разобраны конкретные примеры запросов

Содержание:

  • Переименование полей AS
  • Агрегатные функции в SQL
    • Предложение GROUP BY в SQL
    • Оператор Having SQL

Переименование полей AS

Имена столбцов в запросах можно переименовывать. Это придает результатам более читабельный вид.

В языке SQL переименование полей связано с использованием ключевого слова AS, которое и используется для переименования имен полей в результирующих наборах

Синтаксис:

SELECT <имя поля> AS <псевдоним> FROM …

Рассмотрим пример переименования в SQL:

Пример БД «Институт»: Вывести фамилии учителей и их зарплаты, для тех преподавателей, у которых зарплата ниже 15000, переименовать поле zarplata на «низкая_зарплата»

✍ Решение: 

1
2
3

SELECT name, zarplata AS низкая_зарплата
  FROM teachers
  WHERE zarplata<15000;

Результат:

SQL As 2_1. БД Компьютерный магазин. Вывести данные о компьютерах. Переименовать столбец Скорость в «Мб», а столбец HD в «Гб»

Переименование столбцов в SQL часто необходимо при вычислении значений, связанных с несколькими полями таблицы. Рассмотрим пример:

Пример БД «Институт»: Из таблицы teachers вывести поле name и вычислить сумму зарплаты и премии, назвав поле «зарплата_премия»

✍ Решение: 

1
2

SELECT name, (zarplata+premia) AS zarplata_premia 
  FROM teachers;

Результат:

SQL As 2_1. БД Институт. Вывести фамилии учителей и разницу между их зарплатой и премией. Назвать вывод «зарплата_минус_премия»

Задание 2_1.БД «Компьютерные курсы». Из таблицы Личные данные вывести значения полей Код студента, Word, Excel, Access и вычислить среднее значение по полям Word, Excel, Access, назвав поле «Среднее»

SQL As 2_2. БД Компьютерный магазин. Вывести объем оперативной памяти в Килобайтах (из Мб получить Кб). Выводить и исходное и получившееся значение

Задание 2_2.БД «Компьютерные курсы». Вывести номера телефонов и оценки по Word студентов, успеваемость по курсу Word которых ниже 4 баллов, переименовать поле Word на Низкая успеваемость

Агрегатные функции в SQL

Для получения итоговых значений и вычисления выражений используются агрегатные функции в sql:

ФункцияОписание
COUNT(*)Возвращает количество строк таблицы.
COUNT(имя поля)Возвращает количество значений в указанном столбце.
SUM(имя поля)Возвращает сумму значений в указанном столбце.
AVG(имя поля)Возвращает среднее значение в указанном столбце.
MIN(имя поля)Возвращает минимальное значение в указанном столбце.
MAX(имя поля)Возвращает максимальное значение в указанном столбце.

Все агрегатные функции возвращают единственное значение.

Функции COUNT, MIN и MAX применимы к любым типам данных.

Функции SUM и AVG используются только для числовых полей.
Между функциями COUNT(*) и COUNT() есть разница: вторая при подсчете не учитывает NULL-значения.

Важно: при работе с агрегатными функциями в SQL используется служебное слово AS

Пример БД «Институт»: Получить значение самой большой зарплаты среди учителей, вывести итог как «макс_зп»

✍ Решение: 

SELECT MAX(zarplata) AS макс_зп
  FROM teachers;

Результаты:

Рассмотрим более сложный пример использования агрегатных функций в sql.

Пример: БД Компьютерный магазин. Найти имеющееся в наличии количество компьютеров, выпущенных производителем Америка

✍ Решение: 

1
2
3
4
5
6
7
8

SELECT COUNT( * ) 
FROM  `pc` 
WHERE  `Номер` 
IN (
  SELECT  `Номер` 
  FROM product
  WHERE Производитель =  "Америка"
)

SQL As 2_3. БД Компьютерный магазин. Вывести общее количество продуктов, странами-производителями которых является Россия

Agr func 2_2. БД Институт. Вывести минимальную и максимальную зарплату учителей

Agr func 2_3.БД Институт. Выберите название курса, уроки по которому не проводились и не запланированы проводиться. Дополните код:

1
2
3
4
5
6

SELECT  `title` 
FROM  `courses` 
WHERE  `title` NOT 
IN (
   ...
)

Agr func 2_4. БД Институт. Измените предыдущее задание: Посчитайте количество тех курсов, уроки по которым не проводились и не запланированы проводиться. Выводите результат с именем «нет_уроков»

Задание 2_3. БД «Компьютерные курсы». Вывести год рождения самого младшего студента, назвать поле «Младший»

Задание 2_4. БД «Компьютерные курсы». Посчитать количество всех студентов группы 101. Назвать поле «Группа101»

Предложение GROUP BY в SQL

Оператор group by в sql обычно используется совместно с агрегатными функциями.

Агрегатные функции выполняются над всеми результирующими строками запроса. Если запрос содержит оператор GROUP BY, каждый набор строк, заданных в предложении GROUP BY, составляет группу, и агрегатные функции выполняются для каждой группы отдельно.

Рассмотрим пример с таблицей lessons:

Пример:

  1. Выдавать количество проведенных уроков учителем Иванов из таблицы lessons (порядковый номер Иванова 1 (tid)).
    2. SELECT COUNT(tid) AS Иванов 
FROM lessons
WHERE tid=1

    Результат:

  2. Выдавать количество проведенных уроков учителем Иванов по разным курсам из таблицы lessons 
    3. SELECT course, COUNT( tid ) AS Иванов
FROM lessons
WHERE tid =1
GROUP BY course

    Результат:

Важно: Таким образом, в результате использования GROUP BY все выходные строки запроса разделяются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах (т.е. агрегатные функции выполняются для каждой группы отдельно).

При этом стоит учесть, что при группировке по полю, содержащему NULL-значения, все такие записи попадут в одну группу.

SQL group by 2_5. БД Компьютерный магазин. Для различных типов принтеров определить их среднюю стоимость и количество (т. е. отдельно по лазерным, струйным и матричным). Использовать агрегатные функции AVG(поле) и COUNT(поле). Результат должен выглядеть так:

SQL group by 2_5. БД Институт. Посчитать количество уроков, проведенных одним и тем же учителем. Результат должен выглядеть так:

Задание 2_5.БД «Компьютерные курсы». Вывести количество человек в

каждой группе и количество человек на

каждом курсе из таблицы Список. Назвать вычисляемые поля «кол_во_в_гр» и «кол_во_на_курс»

Оператор Having SQL

Предложение HAVING в SQL необходимо для проверки значений, которые получены с помощью агрегатной функции после группировки (после использования GROUP BY). Такая проверка не может содержаться в предложении WHERE.

Пример: БД Компьютерный магазин. Посчитать среднюю цену компьютеров с одинаковой скоростью процессора. Выполнить подсчет только для тех групп, средняя цена которых меньше 30000.

✍ Решение: 

SELECT AVG(`Цена`) ,`Скорость`
FROM  `pc` 
GROUP BY `Скорость` 
HAVING AVG(`Цена`) <30000

Результат:

Важно: В операторе Having нельзя использовать псевдоним (например, сред_цена), используемый для именования значений агрегатной функции.

Having SQL 2_6. БД Компьютерный магазин. Для различных типов принтеров определить их среднюю стоимость (т.е. отдельно по лазерным, струйным и матричным). Вести подсчет только если средняя стоимость

Having SQL 2_6. БД Институт
Посчитать количество уроков, проведенных одним и тем же учителем. Выдавать значение только для тех учителей, у которых уроков больше двух.

Задание 2_6. БД «Компьютерные курсы». Получить количество учеников каждой группы при условии, что курс не меньше 3-го

Каково назначение ключевого слова SQL «AS»?

спросил

Изменено
4 года, 6 месяцев назад

Просмотрено
205 тысяч раз

Вы можете задать псевдонимы таблиц в SQL, введя идентификатор сразу после имени таблицы.

 ВЫБРАТЬ * ИЗ таблицы t1;

Вы даже можете использовать ключевое слово AS для обозначения псевдонима.

 SELECT * FROM table AS t1;

Какая разница между ними, если есть?

Я вижу, что старые администраторы баз данных склонны писать операторы без AS , но в большинстве новых руководств он используется.

Обновление: Я знаю, для чего нужны псевдонимы таблиц и столбцов. Мне любопытно, в чем причина наличия отдельного ключевого слова для установки псевдонимов, хотя оно работает и без него.

2

Нет никакой разницы между обоими операторами выше. AS — это просто более явный способ указать псевдоним

.

5

Все, кто ответил до меня, правы. Вы используете его как псевдоним ярлыка для таблицы, когда у вас есть длинные запросы или запросы с соединениями. Вот пара примеров.

Пример 1 

 SELECT P.ProductName,
       П. ПродуктГрупп,
       P.ProductRetailPrice
ИЗ ПРОДУКЦИИ AS P

Пример 2 

 SELECT P.ProductName,
       P.ProductRetailPrice,
       O.Количество
ИЗ ПРОДУКЦИИ AS P
LEFT OUTER JOIN Orders AS O ON O.ProductID = P.ProductID
ГДЕ O.OrderID = 123456

Пример 3
Использование ключевого слова AS является хорошей практикой и очень рекомендуется, но можно выполнить тот же запрос и без него (что я и делаю часто).

 ВЫБЕРИТЕ P.ProductName,
       P.ProductRetailPrice,
       O.Количество
ИЗ ПРОДУКЦИИ Р
LEFT OUTER JOIN Заказы O ON O.ProductID = P.ProductID
ГДЕ O.OrderID = 123456

Как видите, в последнем примере я пропустил ключевое слово AS. И его можно использовать как псевдоним.

Пример 4 

 ВЫБЕРИТЕ P.ProductName AS "Product",
       P.ProductRetailPrice AS "Розничная цена",
       O.Количество AS "Заказанное количество"
ИЗ ПРОДУКЦИИ Р
LEFT OUTER JOIN Заказы O ON O.ProductID = P.ProductID
ГДЕ O.OrderID = 123456

Результат примера 4 

 Продукт Розничная цена Заказанное количество
Голубо-малиновая жевательная резинка $10 шт. /$50, 2 упаковки
Коробка Twizzler по 5 долларов США/25 долларов США, 10 коробок

Если вы не уверены, какой синтаксис выбрать, особенно когда кажется, что выбор невелик, обратитесь к книге по эвристикам. Насколько мне известно, единственной книгой по эвристикам для SQL является «Стиль программирования SQL Джо Селко»:

.

Корреляционное имя чаще
называется псевдонимом, но я буду формальным.
В SQL-92 они могут иметь необязательный
Оператор AS , и его следует использовать
чтобы было понятно, что что-то
получает новое имя. [стр.16]

Таким образом, если вашей команде не нравится соглашение, вы можете обвинить Celko — я знаю, что люблю 😉

ОБНОВЛЕНИЕ 1: IIRC в течение длительного времени Oracle не поддерживал ключевое слово AS (предыдущее корреляционное имя), что может объяснить, почему некоторые старожилы не используют его обычно.

ОБНОВЛЕНИЕ 2: термин «имя корреляции», хотя и используется в стандарте SQL, неуместен. В основе лежит концепция «переменной диапазона».

ОБНОВЛЕНИЕ 3: Я только что перечитал то, что написал Celko, и он ошибся: таблица не переименовывается! Я сейчас думаю:

Корреляционное имя чаще называют псевдонимом, но я буду формальным. В стандартном SQL они могут иметь необязательное ключевое слово AS , но его не следует использовать, поскольку может создаться впечатление, что что-то переименовывается, когда это не так. Фактически, его следует опустить, чтобы подчеркнуть, что это переменная диапазона.

0

Ключевое слово AS предназначено для присвоения имени ALIAS таблице базы данных или столбцу таблицы. В вашем примере оба утверждения верны, но есть обстоятельства, при которых требуется предложение AS (хотя Сам оператор AS является необязательным), например.

 ВЫБЕРИТЕ оклад * 2 КАК "Двойной оклад" ОТ сотрудника;

В этом случае таблица Сотрудник имеет столбец оклада , и мы просто хотим удвоить оклад с новым именем Двойной оклад .

Извините, если мое объяснение неэффективно.

Обновление на основе вашего комментария, вы правы, мое предыдущее заявление было недействительным. Единственная причина, о которой я могу думать, это то, что Предложение AS уже давно существует в мире SQL, поэтому в настоящее время оно включено в RDMS для обратной совместимости.

1

Использование будет более очевидным, если вы не используете ‘SELECT *’ (это плохая привычка, от которой вам следует избавиться):

 SELECT t1.colA, t2.colB, t3.colC FROM вдоль имени таблицы AS t1, другое длинное имя таблицы AS t2, еще одно длинное имя таблицы AS t3 WHERE t1.colD = t2.colE...

1

На заре SQL он был выбран как решение проблемы дублирования имен столбцов (см. примечание ниже).

Чтобы заимствовать запрос из другого ответа:

 SELECT P.ProductName,
       P.ProductRetailPrice,
       O.Количество
  ИЗ ПРОДУКЦИИ AS P
       INNER JOIN Orders AS O ON O. ProductID = P.ProductID
 ГДЕ O.OrderID = 123456

Столбец ProductID (и, возможно, другие) является общим для обеих таблиц, и, поскольку синтаксис условия соединения требует ссылки на обе таблицы, «точечная квалификация» обеспечивает устранение неоднозначности.

Конечно, лучшим решением было бы вообще никогда не допускать дублирования имен столбцов! К счастью, если вы используете новый синтаксис NATURAL JOIN , необходимость в переменных диапазона P и O отпадает:

 SELECT ProductName, ProductRetailPrice, Quantity
  ИЗ ПРОДУКЦИИ NATURAL JOIN Заказы
 ГДЕ OrderID = 123456

Но почему ключевое слово AS является необязательным? Мое воспоминание из личной беседы с членом комитета по стандартизации SQL (то ли Джо Селко, то ли Хью Дарвеном) заключалось в том, что их воспоминание заключалось в том, что во время определения стандарта продукт одного поставщика (Microsoft?) требовал его включения, а продукт другого поставщика (Oracle?) требовал его исключения, поэтому выбранный компромисс заключался в том, чтобы сделать его необязательным. У меня нет цитаты за это, вы либо верите мне, либо нет!

На заре реляционной модели перекрестное произведение (или тета-соединение, или равное соединение) отношений, заголовки которых не пересекаются, порождало отношение с двумя атрибутами с одинаковыми именами; Решение Кодда этой проблемы в его реляционном исчислении заключалось в использовании точечной квалификации, которая позже была эмулирована в SQL (позднее стало понятно, что так называемое естественное соединение было примитивным без потерь, т. е. естественное соединение может заменить все тета-соединения и даже перекрестное произведение).0005

Источник: Business System 12, примечания к слайдам презентации, представленной на семинаре по внедрению TTM, Университет Нортумбрии, 2–3 июня 2011 г. Хью Дарвеном

3

AS в данном случае является необязательным ключевым словом, определенным в ANSI SQL 92 для определения <<имя корреляции> , обычно известного как псевдоним для таблицы.

 <ссылка на таблицу> ::=
            <имя таблицы> [ [ AS ] <имя корреляции>
                [ <левая скобка> <список производных столбцов> <правая скобка> ] ]
          | <производная таблица> [ AS ] <имя корреляции>
                [ <левая скобка> <список производных столбцов> <правая скобка> ]
          | <присоединенная таблица>
     <производная таблица> ::= <табличный подзапрос>
     <список производных столбцов> ::= <список имен столбцов>
     <список имен столбцов> ::=
          <имя столбца> [ { <запятая> <имя столбца> }... ]
     Синтаксические правила
     1) <имя корреляции>, непосредственно содержащееся в <ссылке на таблицу>.
        ence> TR выставляется TR. <имя таблицы> немедленно содержит
        в <ссылке на таблицу> TR выставляется TR тогда и только тогда, когда TR
        не указывает <имя корреляции>.

Рекомендуется НЕ использовать ключевое слово AS для псевдонимов таблиц, поскольку оно не поддерживается рядом широко используемых баз данных.

4

Это формальный способ указать корреляционное имя для объекта, чтобы вы могли легко обратиться к нему в другой части запроса.

Если вы создадите запрос с помощью редактора запросов в SQL Server 2012, например, вы получите следующее:

 SELECT e.EmployeeID, s.CompanyName, o.ShipName
ОТ сотрудников КАК ВНУТРЕННЕЕ СОЕДИНЕНИЕ
                         Заказы КАК o ON e.EmployeeID = o.EmployeeID ВНУТРЕННЕЕ СОЕДИНЕНИЕ
                         Грузоотправители AS s ON o.ShipVia = s.ShipperID
ГДЕ (s.CompanyName = 'Федеральная доставка')

Однако удаление AS не имеет значения, как показано ниже:

 SELECT e.EmployeeID, s.CompanyName, o.ShipName
ОТ сотрудников e ВНУТРЕННЕЕ СОЕДИНЕНИЕ
                         Заказы o ON e.EmployeeID = o.EmployeeID ВНУТРЕННЕЕ СОЕДИНЕНИЕ
                         Грузоотправители s ON o.ShipVia = s.ShipperID
ГДЕ (s.CompanyName = 'Федеральная доставка')

В этом случае использование AS излишне, но во многих других местах оно необходимо.

1

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

Введение в безопасность SQL Server.

Часть 1

Безопасность часто считается самой важной обязанностью администратора базы данных. SQL Server имеет множество мощных функций для обеспечения безопасности и защиты данных, но для их правильной реализации требуются планирование и усилия. В этой статье, первой из серии, Роберт Шелдон рассматривает множество компонентов, доступных для обеспечения безопасности и защиты баз данных SQL Server.

Серии на данный момент:
  1. Введение в безопасность SQL Server — часть 1
  2. Введение в безопасность SQL Server — часть 2
  3. Введение в безопасность SQL Server — часть 3
  4. Введение в безопасность SQL Server — часть 4
  5. Введение в безопасность SQL Server — часть 5
  6. Введение в безопасность SQL Server — часть 6
  7.  

Безопасность может быть одной из самых сложных проблем, с которыми приходится сталкиваться при управлении экземпляром SQL Server, но она также и одна из самых важных, особенно когда на кону стоят конфиденциальные и личные данные. На самом деле, для многих организаций безопасность является приоритетом номер один, что неудивительно, учитывая, что поставлено на карту.

К счастью, SQL Server включает множество инструментов для защиты данных от кражи, уничтожения и других видов злонамеренного поведения. В этой статье, первой в серии статей о безопасности SQL Server, я познакомлю вас со многими из этих инструментов с целью предоставить вам обзор вариантов, доступных для защиты ваших данных. В последующих статьях я углублюсь в различные технологии, предоставив более подробный обзор того, как они работают и что требуется для защиты экземпляра SQL Server.

Аутентификация и авторизация SQL Server

Защита данных начинается с возможности аутентификации пользователей и авторизации их доступа к определенным данным. С этой целью SQL Server включает механизм аутентификации для проверки личности пользователей, пытающихся подключиться к экземпляру SQL Server, а также механизм авторизации, определяющий, к каким ресурсам данных могут получить доступ авторизованные пользователи и какие действия они могут выполнять.

Аутентификация и авторизация в SQL Server выполняются с помощью комбинации участников безопасности, защищаемых объектов и разрешений. Однако прежде чем я перейду к ним, важно отметить, что SQL Server поддерживает два режима аутентификации: Аутентификация Windows, , иногда называемая интегрированной безопасностью, и SQL Server и аутентификация Windows, иногда называемая смешанным режимом .

Проверка подлинности Windows интегрирована с учетными записями пользователей и групп Windows, что позволяет использовать локальную или доменную учетную запись Windows для входа в SQL Server. Когда пользователь Windows подключается к экземпляру SQL Server, механизм базы данных проверяет учетные данные для входа в систему по ключевому токену Windows, устраняя необходимость в отдельных учетных данных SQL Server. Microsoft рекомендует по возможности использовать проверку подлинности Windows.

Однако в некоторых случаях может потребоваться проверка подлинности SQL Server. Например, пользователи могут подключаться из доменов, не являющихся доверенными, или сервер, на котором размещен SQL Server, не является частью домена, и в этом случае вы можете использовать механизмы входа, встроенные в SQL Server, без привязки к учетным записям Windows. В этом сценарии пользователь указывает имя пользователя и пароль для подключения к экземпляру SQL Server, полностью минуя проверку подлинности Windows

Вы можете указать режим проверки подлинности при настройке экземпляра SQL Server или изменить его после реализации через свойства сервера, как показано на рисунке 1.9.0005

Рис. 1. Выбор режима проверки подлинности для экземпляра SQL Server 2017

В основе механизмов проверки подлинности и авторизации лежат участники, защищаемые объекты и разрешения, которые необходимо настроить, чтобы предоставить пользователям доступ к нужным им данным и предотвратить доступ неавторизованных пользователей к данным, которые им не нужны.

Вы можете просматривать и работать с субъектами, защищаемыми объектами и разрешениями через SQL Server Management Studio (SSMS), используя либо встроенные средства графического интерфейса пользователя, либо доступные операторы T-SQL. На рис. 2 показан обозреватель объектов в SSMS с расширенным 9Папка 0027 Security для базы данных WideWorldImporters , а ниже — расширенная папка Security для экземпляра SQL Server.

Рис. 2. Работа с функциями безопасности в Object Explorer

Субъекты — это отдельные лица, группы или процессы, которым предоставляется доступ к экземпляру SQL Server либо на уровне сервера, либо на уровне базы данных. Субъекты уровня сервера включают учетные записи и роли сервера, которые перечислены в учетных записях 9.0028 и Роли сервера подпапок в папке Безопасность :

  • Логин — это отдельная учетная запись пользователя для входа в экземпляр SQL Server. Логином может быть локальная или доменная учетная запись Windows или учетная запись SQL Server. Вы можете назначить разрешения на уровне сервера для имени входа, например предоставить пользователю возможность создавать базы данных или имена для входа.
  • Роль сервера — это группа пользователей, которые имеют общий набор разрешений на уровне сервера. SQL Server поддерживает фиксированные роли сервера и определяемые пользователем роли сервера. Вы можете назначить логины для фиксированной роли сервера, но вы не можете изменить ее разрешения. Вы можете сделать и то, и другое с определяемой пользователем ролью сервера.

Участники уровня базы данных включают пользователей и роли базы данных, которые перечислены в подпапках Пользователи и Роли в папке базы данных Безопасность :

  • Пользователь базы данных — это отдельная учетная запись пользователя для входа в определенную базу данных. Пользователь базы данных обычно сопоставляется с соответствующим логином сервера, чтобы обеспечить доступ к экземпляру SQL Server, а также к самим данным. Однако вы можете создавать пользователей базы данных, не зависящих от входа в систему, что может быть полезно для разработки и тестирования приложений, управляемых данными, а также для реализации автономных баз данных.
  • Роль базы данных — это группа пользователей, имеющих общий набор разрешений на уровне базы данных. Как и в случае с серверными ролями, SQL Server поддерживает как фиксированные, так и определяемые пользователем роли базы данных.

Каждому участнику безопасности можно предоставить права, позволяющие этому участнику получать доступ или изменять набор защищаемых объектов. Защищаемые объекты — это объекты, составляющие базу данных и серверную среду. Они могут включать что угодно, от функций до пользователей базы данных и конечных точек. SQL Server распределяет объекты иерархически на уровне сервера, базы данных и схемы:

  • Защищаемые объекты на уровне сервера включают базы данных, а также такие объекты, как имена входа, роли сервера и группы доступности.
  • Защищаемые объекты на уровне базы данных включают схемы, а также такие объекты, как пользователи базы данных, роли базы данных и полнотекстовые каталоги.
  • Защищаемые объекты на уровне схемы включают такие объекты, как таблицы, представления, функции и хранимые процедуры.

Разрешения определяют уровень доступа, разрешенный принципалам для конкретных защищаемых объектов. Вы можете предоставлять или запрещать доступ к защищаемым объектам на уровне сервера, базы данных или схемы. Разрешения, которые вы предоставляете на более высоком уровне иерархии, также могут применяться к дочерним объектам, если вы специально не переопределяете разрешения на более низком уровне.

Например, если предоставить разрешение SELECT субъекту user1 в схеме Sales в базе данных WideWorldImporters , пользователь сможет запрашивать все табличные данные в этой схеме. Однако если вы затем откажете в разрешении SELECT для таблицы Sales.Customers , пользователь не сможет запрашивать эту таблицу, но сможет получить доступ к другим таблицам в этой схеме.

Вы можете использовать SSMS или T-SQL для просмотра разрешений, которые были явно предоставлены пользователю в отношении защищаемого объекта. Например, на рис. 3 показаны разрешения, предоставленные user2 принципал в таблице Sales. Customers . В этом случае пользователю были предоставлены разрешения DELETE , INSERT , SELECT и UPDATE без явного отказа в разрешениях.

Рис. 3. Просмотр разрешений пользователя, предоставленных объекту схемы

Настройка разрешений для нескольких принципов на нескольких защищаемых объектах может быть сложным и иногда разочаровывающим процессом. Если вы не сделаете это правильно, вы можете в конечном итоге отказать в разрешениях пользователям, которые должны иметь доступ к определенным данным, или, что еще хуже, предоставить доступ пользователям, которые не должны. Безопаснее всего следовать принципу наименьших привилегий, работая на самом детальном уровне, применимом в данной ситуации.

Дополнительные функции управления доступом

SQL Server также предоставляет несколько других функций для управления доступом к данным. Например, вы можете реализовать безопасность на уровне строк для конкретной таблицы, создав политику безопасности , которая вызывает один или несколько предикатов . Политика безопасности определяет структуру, необходимую для применения к таблице безопасности на уровне строк. Предикат — это функция табличного значения, обеспечивающая логику, необходимую для определения того, к каким строкам применяется политика безопасности.

Политика безопасности поддерживает два типа предикатов: фильтрация и блокировка. Предикат фильтра отфильтровывает строки, доступные для операций чтения, а предикат блока блокирует операции записи, нарушающие предикат. Вы можете включать предикаты фильтрации и блокировки в политику безопасности, а также вызывать одни и те же или разные функции предикатов внутри этой политики.

Например, база данных WideWorldImporters включает FilterCustomersBySalesTerritoryRole , которая определяет как предикат фильтра, так и предикат блокировки. На рис. 4 показана политика безопасности в том виде, в каком она указана в обозревателе объектов, вместе с определением политики. В этом случае и предикат фильтра, и предикат блока вызывают функцию DefineCustomerAccess , которая определяет, к каким строкам может получить доступ текущий пользователь.

Рисунок 4. Просмотр определения политики безопасности в SSMS

Еще одна функция безопасности SQL Server — это 9Роль приложения 0027, аналогична роли базы данных, за исключением того, что она используется специально для назначения разрешений приложению. Однако, в отличие от ролей базы данных, роли приложений не содержат членов. Кроме того, они вызываются только тогда, когда приложение подключается к SQL Server и вызывает системную хранимую процедуру sp_setapprole , передавая имя роли приложения и пароль. SQL Server применяет разрешения, предоставленные роли приложения, на время соединения.

Другой механизм доступа, предоставляемый SQL Server, — это учетные данные , — объект уровня сервера (запись), который содержит информацию для проверки подлинности, такую ​​как имя пользователя и пароль. Учетные данные позволяют пользователю SQL Server подключаться к ресурсу за пределами среды SQL Server. Например, вы можете использовать учетные данные для запуска внешней сборки или для доступа к ресурсам домена, если вы вошли в систему с использованием проверки подлинности SQL Server.

Шифрование данных SQL Server

Также важными для безопасности SQL Server являются возможности шифрования, встроенные в ядро ​​базы данных. Шифрование предоставляет способ кодирования или запутывания данных, чтобы только авторизованные пользователи могли просматривать данные в незашифрованном состоянии. Для всех остальных зашифрованные данные выглядят тарабарщиной.

Шифрование не является механизмом контроля доступа, т. е. оно не предотвращает доступ неавторизованных пользователей к данным. Однако шифрование может ограничить раскрытие конфиденциальных данных, если неавторизованным пользователям удастся обойти защиту контроля доступа SQL Server. Например, если киберпреступники получат зашифрованную информацию о кредитной карте из базы данных, они не смогут разобраться в этих данных, пока не найдут способ их расшифровки.

SQL Server поддерживает несколько подходов к шифрованию для различных типов данных и рабочих нагрузок. Например, вы можете шифровать данные на уровне столбца, используя встроенную иерархию шифрования SQL Server и инфраструктуру управления ключами. В этой модели каждый уровень шифрует нижележащий уровень, используя многоуровневую архитектуру, состоящую из сертификата открытого ключа и нескольких симметричных ключей. Таким образом, данные столбца всегда защищены до тех пор, пока они не будут специально расшифрованы.

Другим инструментом, доступным для SQL Server, является прозрачное шифрование данных (TDE), которое шифрует и расшифровывает как данные, так и файлы журналов в режиме реального времени, работая на уровне страниц, чтобы обеспечить защиту данных в состоянии покоя. Механизм базы данных шифрует страницы перед их записью на диск и расшифровывает их при чтении страниц в память. В отличие от шифрования на уровне столбцов, приложению не нужно выполнять определенные действия для расшифровки данных. Весь процесс происходит за кадром.

SQL Server также поддерживает функцию Always Encrypted, которая позволяет клиентскому приложению выполнять фактические операции шифрования без раскрытия ключей шифрования ядру базы данных. Однако для реализации Always Encrypted необходимо сначала сгенерировать ключи шифрования столбцов, необходимые для поддержки Always Encrypted. Для этого можно использовать Мастер Always Encrypted в SSMS, как показано на рисунке 5.

Рисунок 5. Запуск мастера Always Encrypted

После шифрования столбцов данные готовы для клиентского доступа. Однако для того, чтобы клиентское приложение могло подключаться к зашифрованным данным, оно должно включать драйвер, поддерживающий Always Encrypted и способный выполнять операции шифрования и дешифрования.

Еще одна полезная функция безопасности SQL Server — динамическое маскирование данных (DDM) — инструмент для маскирования всех или части значений данных. Хотя DDM на самом деле не шифрует данные, он ограничивает объем открытых данных для неавторизованных пользователей. Например, вы можете использовать DDM для маскировки всех, кроме последних четырех цифр, номера кредитной карты или национального идентификатора, такого как номер социального страхования.

Инструменты SQL Server

SQL Server включает в себя ряд других инструментов для защиты данных и ограничения рисков. Например, вы можете использовать диспетчер конфигурации SQL Server для настройки параметров запуска или подключения или использовать хранимую процедуру sp_configure для настройки глобальных параметров SQL Server. SQL Server также предоставляет аспекты конфигурации контактной зоны для включения или отключения функций на уровне экземпляра, как показано на рисунке 6.

Рисунок 6. Доступ к аспектам конфигурации контактной зоны для экземпляра SQL Server 2017

Вы можете использовать эти инструменты, чтобы гарантировать, что только те функции, которые необходимы для поддержки ваших пользователей и приложений, включены в любой момент времени, помогая уменьшить открытую поверхность и, следовательно, уровень риска.

SQL Server также предоставляет инструменты для выявления потенциальных проблем с базой данных. Например, SQL Server предоставляет свойство TRUSTWORTHY в качестве одного из свойств базы данных. Свойство показывает, может ли текущий экземпляр SQL Server доверять базе данных и ее содержимому.

Кроме того, SSMS предоставляет функцию обнаружения и классификации данных для классификации, маркировки и составления отчетов о потенциально конфиденциальных данных в базе данных, а также средство оценки уязвимостей SQL (SVA) для обнаружения, отслеживания и устранения потенциальных уязвимостей базы данных. На рисунке 7 показаны результаты выполнения оценки SVA для базы данных WideWorldImporters в SQL Server 2017.

Рисунок 7. Просмотр отчета об оценке уязвимостей SQL

Одним из наиболее ценных инструментов безопасности SQL Server является аудит SQL Server, который предоставляет структуру для отслеживания и регистрации событий, происходящих в ядре базы данных. С помощью аудита SQL Server вы можете отслеживать события на уровне сервера, на уровне базы данных или на том и другом.

Аудит SQL Server состоит из трех основных типов компонентов. Первым является объект аудита, который обеспечивает структуру для проведения процесса аудита. Объект аудита определяет цель для проверяемых событий. Целью могут быть файлы журналов, журнал приложений или журнал безопасности. Объект аудита также включает параметры конфигурации, такие как количество и размер файлов журнала.

В дополнение к объекту аудита аудит обычно включает спецификацию аудита сервера, спецификацию аудита базы данных для каждой применимой базы данных или их комбинацию. Спецификации определяют, какие события следует проверять на уровне сервера или на уровне базы данных. Например, на рис. 8 показана спецификация аудита базы данных, которая проверяет события UPDATE и DELETE в таблице Sales.Customers .

Рисунок 8. Просмотр проверенных действий, определенных в спецификации аудита базы данных

В этом случае оба события относятся к пользователю базы данных user1 . Если пользователь попытается обновить или удалить данные в таблице Customers , аудит SQL Server зарегистрирует это событие в целевом репозитории.

Наряду со всеми этими инструментами SQL Server также предоставляет ряд представлений каталога и динамических представлений управления для доступа к данным, связанным с безопасностью. Например, вы можете получить сведения о разрешениях, предоставленных и запрещенных конкретному пользователю базы данных.

Защита экземпляра SQL Server

Помимо принятия мер в рамках SQL Server для защиты данных, администраторы баз данных также должны быть уверены в реализации средств защиты, связанных с экземпляром SQL Server в целом, таких как отключение неиспользуемых компонентов SQL Server, своевременное применение исправлений и пакетов обновления для системы безопасности, а также обеспечение полной защиты и безопасности файлов базы данных и резервных копий в любое время.

Но стратегия защиты не должна ограничиваться только SQL Server. Операционная система хоста также должна поддерживаться в актуальном состоянии и должным образом исправляться, при этом не меньшее внимание должно уделяться уменьшению площади поверхности.

Кроме того, администраторы баз данных и ИТ-администраторы должны обеспечить физическую защиту хост-сервера и наличие сетевых средств защиты, таких как брандмауэры и средства обнаружения вторжений. Экземпляр SQL Server должен быть защищен как физически, так и логически, чтобы обеспечить максимальную безопасность.

Команды разработчиков также должны обеспечить надлежащую проверку приложений, подключающихся к экземпляру SQL Server, на наличие проблем с безопасностью. Приложение, управляемое данными, подвержено ряду атак, включая внедрение строки подключения, повышение привилегий и внедрение SQL. Команды должны учитывать безопасность данных с самого начала, при первой разработке приложения, а не после его реализации.

Защита SQL Server

Безопасность SQL Server — это обширная тема, и то, что я здесь рассмотрел, лишь поверхностно касается ее. Вы должны рассматривать эту статью только как отправную точку, предназначенную для того, чтобы предупредить вас о многих соображениях безопасности, которые необходимо принять во внимание, и о различных инструментах SQL Server, доступных для защиты данных.