Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 ограничение на определенные строки в двух таблицах  [new]
loviji
Member

Откуда: баку
Сообщений: 10
Добрый день, в администрировании бд я новичок.
Хотелось бы что бы один пользователь в определенном бд мог делать все, кроме редактирование определенных строк в бд.
Я для этого пользователя привязал роли db)datareader, db_datawriter, db_ddladmin.
и создал view с where конструкцией.
теперь как мне дальше продолжать?
11 янв 13, 13:50    [13753744]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
db_ddladmin - это чтобы он мог ее сразу удалить?
11 янв 13, 13:51    [13753755]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
loviji
Member

Откуда: баку
Сообщений: 10
Гавриленко Сергей Алексеевич,
это я думал что бы легче организовать доступ к stored proc and funcs. Но как я понял это не правильный подход.
11 янв 13, 13:54    [13753780]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
kain111
Member

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

Если только как то извращаться и создать тригер, в котором бы была проверка на логин пользователя и в зависимости от этого в случае изменения строк попадающих под условие выдавало бы ошибку или просто ничего не делало. Но даже в этом случае придется помучиться чтобы ограничить доступ к этому тригеру.
На сколько я помню права задаются разрешениями. и чтобы что то одно запретить, придется выдать разрешения на все остальные объекты и операции.Могу ошибаться. пусть коллеги поправят.
14 янв 13, 11:43    [13765393]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
Гость333
Member

Откуда:
Сообщений: 3683
kain111
можно запретить изменять данные во всей таблице. но чтобы гранулировать права до уровня строк., вроде такое не возможно.

Можно создать представление и в нём отрегулировать права доступа. Гугление по фразе "sql server row level security" второй ссылкой выдаёт статью How to Setup Row Level Security for SQL Server (посмотрел по диагонали, вроде весьма толково реализовано).
Четвёртая ссылка ведёт на документацию Microsoft, но там очень кратко описано: Granting Row-Level Permissions in SQL Server
14 янв 13, 12:04    [13765550]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
kain111
Member

Откуда:
Сообщений: 227
автор
Можно создать представление и в нём отрегулировать права доступа. Гугление по фразе "sql server row level security" второй ссылкой выдаёт статью How to Setup Row Level Security for SQL Server


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

Получается ТС надо:
1)запретить чтение и любое изменение таблицы источник.
2) создать представление
3) запретить изменение представления и дать право на select из представления
Как будет работать пересечение прав?
И получается что если у пользователя будет право создавать объекты в БД, то он сможет создать свое представление без условия или табличную функцию?
14 янв 13, 13:12    [13766164]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
kain111
Member

Откуда:
Сообщений: 227
автор
как в случае запрета на чтения данных из таблицы будет действие разрешения на чтение из представления разные ли это получаются объекты?

я проверил. Действительно , даже логично. новый объект, а права на уровне объектов.
автор
И получается что если у пользователя будет право создавать объекты в БД, то он сможет создать свое представление без условия или табличную функцию?
еще актуально.
14 янв 13, 13:32    [13766394]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
Гость333
Member

Откуда:
Сообщений: 3683
kain111
и как в случае запрета на чтения данных из таблицы будет действие разрешения на чтение из представления разные ли это получаются объекты?

Объекты разные, да.

Пример: логинится админ, создаёт юзера Васю и таблицу с представлением:
use tempdb;
go
create login vasya with password = 'petya', check_policy = off;
create user vasya from login vasya;
go
create table dbo.test_table (id int, name varchar(100));
insert dbo.test_table values(1, 'Иванов');
insert dbo.test_table values(2, 'Петров');
insert dbo.test_table values(100, 'Gates');
go
create view dbo.test_view as
select * from dbo.test_table where id < 100;
go
deny select, update on dbo.test_table to vasya;
grant select, update on dbo.test_view to vasya;
go


Дальше логинится Вася.
-- Здесь Вася в пролёте, т.к. The SELECT permission was denied on the object 'test_table', database 'tempdb', schema 'dbo'.
select * from tempdb.dbo.test_table;

-- Тут Вася видит разрешённые ему данные (2 row(s) affected)
select * from tempdb.dbo.test_view;

-- Здесь ничего не апдейтится (0 row(s) affected)
update tempdb.dbo.test_view
set name = 'Ellison'
where id = 100;

-- А здесь "Иванов" меняется на "Сидоров" (1 row(s) affected)
update tempdb.dbo.test_view
set name = 'Сидоров'
where id = 1;


kain111
И получается что если у пользователя будет право создавать объекты в БД, то он сможет создать свое представление без условия или табличную функцию?

— У обычного пользователя не должно быть права на создание объектов в БД;
— Если некий пользователь принадлежит к роли db_ddladmin, то создать представление он может, а вот сделать выборку из этого представления — уже не может без дополнительных прав.
14 янв 13, 13:37    [13766440]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
kain111
Member

Откуда:
Сообщений: 227
Гость333,

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

дополнительные права покрываются же павами на чтение из этой же конкретной бд т.е. ролью db_datareader?
и получается что для задачи ТС с указанными ролями datareader, db_datawriter, db_ddladmin способ создания представления sql server row level security не подходит. Необходимо либо заменить роль datareader на какую то свою с перечислением всех объектов и поддерживать ее, что накладно? либо убрать роль db_ddladmin.
15 янв 13, 10:11    [13770564]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
Гость333
Member

Откуда:
Сообщений: 3683
kain111
для задачи ТС с указанными ролями datareader, db_datawriter, db_ddladmin способ создания представления sql server row level security не подходит

От такого пользователя, вроде бы, никак не защититься.

Вообще интересно, что в главе BOL Database-Level Roles роль db_datareader описана как
автор
Members of the db_datareader fixed database role can read all data from all user tables.

А по факту db_datareader может читать данные не только из таблиц, но и из представлений и из табличных функций.

kain111
убрать роль db_ddladmin

И это правильно :) Просто ТС плохо представляет себе, что значит "всё" в его "что бы один пользователь в определенном бд мог делать все, кроме ... " :)
15 янв 13, 11:13    [13770857]     Ответить | Цитировать Сообщить модератору
 Re: ограничение на определенные строки в двух таблицах  [new]
kain111
Member

Откуда:
Сообщений: 227
автор
От такого пользователя, вроде бы, никак не защититься.

а если рассмотреть тригер на таблице в котором бы проверялся логин пользователя и на основании этого было бы ограничение, ну и конечно отобрать права на его отклчение.
поможет ли в таком случае execute as ? точнее какие права должен иметь пользователь чтобы выполнять запросы от прав другого пользователя?
BOL говорит следующее
автор
Permissions

To specify EXECUTE AS on a login, the caller must have IMPERSONATE permissions on the specified login name. To specify EXECUTE AS on a database user, the caller must have IMPERSONATE permissions on the specified user name. When EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.
в указанных ТС ролях этого права нет ?
можно ли как то произвести изменения мимо включенного тригера ? или еще какие варианты есть.
15 янв 13, 13:47    [13771984]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить