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

Откуда: Владивосток
Сообщений: 93
Коллеги, добрый день, над логином не ржать, это не правда))
Пишу клиента на шарпе к SQL 2005, коннектюсь к базе под обрезанным юзером, который обладает только ролью public, и может запускать только процедуры созданный под схемой 'dbo' и ничего более.

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

procedure [dbo].[test_count_dt_insert]
@par_test varchar(100) output
with execute as 'dbo'
as
declare @ip varchar(100), @mac varchar(100)
set @ip = (
SELECT dmec.client_net_address
  FROM sys.sysprocesses sp 
  JOIN 
    sys.dm_exec_connections dmec --причем не работает только с этой таблицей, с sysprocesses берет нормально, тотже мак-адресс
    ON sp.spid = dmec.session_id
  WHERE sp.spid = @@SPID
)
set @mac = (select net_address from sys.sysprocesses WHERE spid = @@SPID)--этот запрос работает нормально
insert into test_count_dt values (@ip, @mac, getdate())
29 окт 12, 15:20    [13391618]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
Glory
Member

Откуда:
Сообщений: 104760
Открываем хелп по sys.dm_exec_connections и читаем раздел Permissions
29 окт 12, 15:25    [13391667]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
master_sql,

http://msdn.microsoft.com/en-us/library/ms179881.aspx
sys.sysprocesses (Transact-SQL)

Remarks

If a user has VIEW SERVER STATE permission on the server, the user will see all executing sessions in the instance of SQL Server; otherwise, the user will see only the current session.


http://msdn.microsoft.com/en-us/library/ms181509.aspx
sys.dm_exec_connections (Transact-SQL)

Permissions

Requires VIEW SERVER STATE permission on the server.


Итак, вопрос - ваш dbo имеет права VIEW SERVER STATE?
29 окт 12, 15:26    [13391686]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
master_sql
Member

Откуда: Владивосток
Сообщений: 93
Ребят,
спасибо за оперативные ответы, очевидно что не имеет.
Причем я полазил по ссылкам и загрантить VIEW SERVER STATE не получается, поскольку сервер говорить что либо логина нет, либо прав нет

use master
GRANT VIEW SERVER STATE TO [dbo]

Msg 15151, Level 16, State 1, Line 2
Cannot find the login 'dbo', because it does not exist or you do not have permission.


грант для логина под которым я цепляюсь к базе результата не дает.
30 окт 12, 00:18    [13394047]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
master_sql
Ребят,
спасибо за оперативные ответы, очевидно что не имеет.
Причем я полазил по ссылкам и загрантить VIEW SERVER STATE не получается, поскольку сервер говорить что либо логина нет, либо прав нет

use master
GRANT VIEW SERVER STATE TO [dbo]

Msg 15151, Level 16, State 1, Line 2
Cannot find the login 'dbo', because it does not exist or you do not have permission.


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

CREATE LOGIN test WITH PASSWORD = 'test', CHECK_POLICY = OFF
GRANT VIEW SERVER STATE TO test

EXECUTE AS LOGIN = 'test'

SELECT dmec.client_net_address
  FROM sys.sysprocesses sp 
  JOIN 
    sys.dm_exec_connections dmec
    ON sp.spid = dmec.session_id
30 окт 12, 00:41    [13394083]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
master_sql
Member

Откуда: Владивосток
Сообщений: 93
Mind,

Спасиб, загрантил VIEW SERVER STATE на юзера подключения к бд и убрал вообще строку with execute as 'dbo' и заработало.
не совсем понятно конечно почему он мне при этом дает вставлять данные в test_count_dt при простом паблике юзера коннекта на базу. Так кто через манагмент цепляясь к базе я эту операцию делать не могу. (insert into test_count_dt values ('', '', getdate())). Но тут явно пробелы просто в моем образовании, наверное потому что на процедуре для public даю права execute под схемой dbo.

procedure [dbo].[test_count_dt_insert]
@par_test varchar(100) output
--with execute as 'dbo'
as
declare @ip varchar(100), @mac varchar(100)
set @ip = (
SELECT dmec.client_net_address
  FROM sys.sysprocesses sp 
  JOIN 
    sys.dm_exec_connections dmec --причем не работает только с этой таблицей, с sysprocesses берет нормально, тотже мак-адресс
    ON sp.spid = dmec.session_id
  WHERE sp.spid = @@SPID
)
set @mac = (select net_address from sys.sysprocesses WHERE spid = @@SPID)--этот запрос работает нормально
insert into test_count_dt values (@ip, @mac, getdate())


Коллеги, всем большое Благодарю, Ваши советы помогли мне правильные слова набирать в google)
30 окт 12, 03:32    [13394229]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
Glory
Member

Откуда:
Сообщений: 104760
master_sql
не совсем понятно конечно почему он мне при этом дает вставлять данные в test_count_dt при простом паблике юзера коннекта на базу.

Потому что есть права на процедуру, а у процедуры и таблицы один владелец - dbo
Это называется ownership chain
30 окт 12, 10:15    [13394892]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
master_sql
Ребят,
спасибо за оперативные ответы, очевидно что не имеет.
Причем я полазил по ссылкам и загрантить VIEW SERVER STATE не получается, поскольку сервер говорить что либо логина нет, либо прав нет

use master
GRANT VIEW SERVER STATE TO [dbo]

Msg 15151, Level 16, State 1, Line 2
Cannot find the login 'dbo', because it does not exist or you do not have permission.


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

Вроде решили вопрос, но все же отвечу:
Логина и нет, потому что dbo - это пользователь базы данных (User), а VIEW SERVER STATE - это разрешение сервера. Его можно выдать только имени входа (login).

Для процедуры в базе данных, насколько я понимаю, нельзя задать выполнение от определенного логина (WITH EXECUTE as 'login_name'), поэтому единственный вариант - выдать VIEW SERVER STATE логину, который выполняет процедуру.
30 окт 12, 12:16    [13395671]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
master_sql
Member

Откуда: Владивосток
Сообщений: 93
Glory,
спасибо Сенсей!

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

procedure [dbo].[test_count_dt_insert]
@par_test varchar(100) output
as
insert into test_count_dt
SELECT dmec.client_net_address, sp.net_address, getdate()
  FROM sys.sysprocesses sp 
  JOIN sys.dm_exec_connections dmec ON sp.spid = dmec.session_id
  WHERE sp.spid = @@SPID


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

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

Спасибо.
30 окт 12, 12:33    [13395811]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
Glory
Member

Откуда:
Сообщений: 104760
master_sql
да у меня обрезанный логин и может совершать с базой только регламентированные хранимками действия,

Логин с базой ничего не делает
В базе работает пользователь, в которого явно или неявно мапится логин

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

Мда. Сначала дать права, а потом с ними бороться ?
Ну поставьте внутри процедуры задержку.
30 окт 12, 12:44    [13395894]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
master_sql
Member

Откуда: Владивосток
Сообщений: 93
Glory
Логин с базой ничего не делает
В базе работает пользователь, в которого явно или неявно мапится логин

я право ещё раз извиняюсь за свою безграмотность в плане даже теоретических основ, это все от недостатка чтения матчасти

Glory
Мда. Сначала дать права, а потом с ними бороться ?
Ну поставьте внутри процедуры задержку.


Просто предполагается что база MS SQL будет на сервере с белым IP (внешним), поскольку она будет видна с глобала, я и опасаюсь за слабые места.
Я вот подумал, наверное просто нужно воткнуть проверку разности getdate()'а текущего момента для данного ip и mac до последнего его посещения, и если эта разность допустим явно меньше секунды, то такого товарища "отсекать" от пользования базой, ну или как минимум не давать ему "досить" таблицу.
А задержка процедуры будет, будет давать задержку программы-клиента, так как она ждет выходного параметра от процедуры.
30 окт 12, 13:10    [13396152]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
master_sql,
Можно "завалить" сервер даже не имея прав на вашу хранимку, SELECT 1 всегда выполнится ...
30 окт 12, 20:17    [13399514]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
master_sql
Member

Откуда: Владивосток
Сообщений: 93
Mind
master_sql,
Можно "завалить" сервер даже не имея прав на вашу хранимку, SELECT 1 всегда выполнится ...


т.е. я так понимаю лечить эту тему только фаерволом, чтобы слал в игнорлист хосты которые слишком много обращаются к серваку в ограниченный промежуток времени.
31 окт 12, 03:05    [13400287]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
Glory
Member

Откуда:
Сообщений: 104760
master_sql
т.е. я так понимаю лечить эту тему только фаерволом, чтобы слал в игнорлист хосты которые слишком много обращаются к серваку в ограниченный промежуток времени.

Одиночный коннект с одиночным запросом может привести сервер в ступор
Вы боретесь с ветрянными мельницами.
Мониторить нужно состояние сервера.
31 окт 12, 10:16    [13400701]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
master_sql
Member

Откуда: Владивосток
Сообщений: 93
Glory
Одиночный коннект с одиночным запросом может привести сервер в ступор
Вы боретесь с ветрянными мельницами.
Мониторить нужно состояние сервера.


Надеюсь ещё не сильно бешу своими тупыми вопросами, но по кол-ву сообщений и манере разговора, видно что Вы человек опытный, хотел спросить, какие снимать параметры в основном для мониторинга, в контексте работы sql-сервера. Я так понимаю основные параметры работы железа, загрузка ЦП, ОЗУ, а применительно к сервисам SQL, какие и как снимать основные характеристики?

P.S. я просто писал пинговалку для своей работы, которая тестит канал интернета предприятия предоставляющие провайдерские услуги и в случае проблем шлет алармовые смс, тут наверное что-либо похожее можно сделать (ну естественно только не время пинга снимать, а параметры сервера)
31 окт 12, 12:39    [13401663]     Ответить | Цитировать Сообщить модератору
 Re: Дать обрезанной роли доступ к sys  [new]
Glory
Member

Откуда:
Сообщений: 104760
BOL - Performance Monitoring and Tuning How-to Topics
31 окт 12, 12:51    [13401742]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить