"SELECT * ..." вне закона

Posted by on in Blogs
Когда мы говорим о БД, как правило всегда уделяем огромное внимание различного рода ограничениям на данные и их структуры. Тем не менее, даже при наличии подробной и строгой модели данных, мы не можем предусмотреть всего, что захочется сделать из самых лучших побуждений прикладному разработчику. Например, использовать запрос на выборку данных вида "Select * from ..." в своем программном коде. Считается хорошим тоном не использовать такую конструкцию в тексте программы или двоичном коде. Запрашивая только необходимые колонки, можно сэкономить объемы передаваемых данных,  ускорить оптимизацию запроса, улучшить использование памяти на сервере, обеспечить нужный уровень разделения доступа и т.п. Правда, многие утверждают, что с современными СУБД и продвинутыми возможностями их оптимизаторов, выигрыш производительности стремится к нулю. Зато гибкость при изменении структур БД на этапе поддержки и эксплуатации может сохранить много времени и нервов тем же разработчикам.  Маленькая хитрость заключается в том, что можно ограничить возможности наших друзей-разработчиков средствами администратора БД.

Идея очень проста: допустим, надо ограничить возможность применять запрос "SELECT * FROM dbo.tblNoSelectStar" пользователю "test_user".  Заведем в этой табличке скрытую колонку, и дадим права на просмотр этой колонки всем, кроме указанного пользователя. Это для примера, разумеется, на практике мы будем оперировать группами и ролями.
USE master
go
CREATE DATABASE DenySelectStar
ON PRIMARY(
NAME='DenySelectStar',
FILENAME='c:\MSSQL\DATA\DenySelectStar.mdf',
SIZE=2304KB,
MAXSIZE=UNLIMITED,
FILEGROWTH=1024KB )
LOG ON (
NAME='DenySelectStar_log',
FILENAME='c:\MSSQL\DATA\DenySelectStar_log.LDF',
SIZE=576KB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10% )
COLLATE Cyrillic_General_CI_AS
go

CREATE LOGIN test_user WITH PASSWORD = 'test_user_psw';
go

ALTER LOGIN test_user ENABLE
go
GRANT CONNECT SQL TO test_user
go

USE DenySelectStar
go
CREATE USER test_user FOR LOGIN test_user WITH DEFAULT_SCHEMA = DenySelectStar
go
EXEC sp_addrolemember N'db_datareader', N'test_user'
go

Мы создали тестовую базу данных, логин и пользователя этой базы. Я привел текст соответствующего скрипта, который получил с помощью своего Embarcadero DBArtisan. Это средство администрирования БД MS SQL Server, вместе с другими очень полезными инструментами управления вашими базами данных можно получить в составе нашего нового продукта: Embarcadero DB PowerStudio для MS SQL Server. Далее создаем таблицу и заполняем ее тестовыми записями.
USE DenySelectStar
go
CREATE TABLE dbo.tblNoSelectStar (
    IdentityKey int     IDENTITY,
    fldOne       int     NULL,
    DummyColumn char(1) COLLATE Cyrillic_General_CI_AS NULL,
    CONSTRAINT PK__tblNoSel__default
    PRIMARY KEY CLUSTERED (IdentityKey)
)
go

Я не стал вставлять картинки редактора таблиц, как я создавал эту таблицу, а привел скрипт из окошка Preview. Добавлять записи можно в интерактивном редакторе данных, как на этой картинке


Отлично! Теперь зададим права  доступа:



Мы готовы! Войдем под пользователем test_user и попытаемся выполнить сакральный запрос:



Результат - отрицательный!



Следует заметить одну важную деталь: вы теперь не сможете делать запросы типа
SELECT count(*) FROM ...

вместо этого придется  использовать поле первичного ключа:
select count(t.IdentityKey) from dbo.tblNoSelectStar t

По мотивам http://www.sqlservercentral.com.



About
Gold User, Rank: 11, Points: 295
SC at Embarcadero. DB Tools expert Delphi practitioner

Comments

Check out more tips and tricks in this development video: