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

Откуда: Чебаркуль
Сообщений: 4182
Задача: в пользовательской базе создаю процедуру, которая при вызове создает в tempdb таблицу
Проблема: при вызове из одной базы - все срабатывает, таблица удаляется и создается,
при вызове из другой получаю

CREATE TABLE permission denied in database 'tempdb'.



Выполняет юзер 'sa'.
Owner на обеих базах и у темпдб тоже 'sa'

Что проверить?

Скрипт:

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_usp_test]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = 
	N'CREATE        PROCEDURE [dbo].[_usp_test]
	AS return 0'
END
GO

ALTER        PROCEDURE [dbo].[_usp_test]
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON
declare @stDrop nvarchar(1024)
declare @stCreate nvarchar(1024)
declare @sqluse nvarchar(1024)=N'use tempdb;'
select @stDrop=N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[test_'+db_name()+N']'') AND type in (N''U''))
BEGIN
	DROP TABLE [dbo].[test_'+db_name()+N']
END'


select @stCreate=N'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[test_'+db_name()+N']'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[test_'+db_name()+N']
(
      id BIGINT NOT NULL identity(1,1),
      name nvarchar(256)
);
ALTER TABLE [dbo].[test_'+db_name()+N'] ADD CONSTRAINT [PK_test_'+db_name()+N'_id] PRIMARY KEY NONCLUSTERED ([id]);
END;
GRANT SELECT,UPDATE,INSERT,DELETE on [dbo].[test_'+db_name()+N'] TO PUBLIC;'

BEGIN TRY
	--print @sqluse+@stDrop
	--print @sqluse+@stCreate

	EXEC(@sqluse+@stDrop);
	EXEC(@sqluse+@stCreate);
END TRY
BEGIN CATCH
	print error_message()
END CATCH

return 0
END
GO
GRANT EXEC ON [dbo].[_usp_test] TO PUBLIC;
GO

exec  [dbo].[_usp_test]
GO
1 июл 16, 14:01    [19359499]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
из процедуры сделайте
select system_user, user

что-то там наверное guest, а не dbo.
1 июл 16, 14:05    [19359543]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
Да, еще такое дело.
Не бывает юзера "sa", есть такой логин.
Но в процедуре-то execute as owner.
Owner dbo, но только в этой базе.
Либо надо ее делать TRUSTWORTY, тогда и снаружи базы останется логин sa, либо вообще подход менять.
Наверное одна из баз с TRUSTWORTY on, вторая с off
1 июл 16, 14:15    [19359626]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
из процедуры сделайте
select system_user, user

что-то там наверное guest, а не dbo.


Оба sa dbo по запросу.

Дальше интересно
сделал так
ALTER        PROCEDURE [dbo].[_usp_test]
--WITH EXECUTE AS OWNER


Вернул как было

ALTER        PROCEDURE [dbo].[_usp_test]
WITH EXECUTE AS OWNER


на обеих базах сработало без ошибок(!)

При этом больше ничего не менял в процедуре и т.д.

Да, select @@VERSION

автор
Microsoft SQL Server 2016 (RTM-GDR) (KB3164398) - 13.0.1708.0 (X64) Jun 2 2016 07:18:56 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 Standard 6.2 <X64> (Build 9200: )

1 июл 16, 14:15    [19359627]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
Да, еще такое дело.
Не бывает юзера "sa", есть такой логин.
Но в процедуре-то execute as owner.
Owner dbo, но только в этой базе.
Либо надо ее делать TRUSTWORTY, тогда и снаружи базы останется логин sa, либо вообще подход менять.
Наверное одна из баз с TRUSTWORTY on, вторая с off


яволь!
вторая не TRUSTWORTHY
спасибо!
1 июл 16, 14:17    [19359638]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
А печать юзера и систем_юзера сделай из динамики, сразу после use tempdb, тогда станет ясно, под кем ты в темпдб.
Извини, Ролг, я сегодня чего-то ники плохо читаю. Приветствую!
1 июл 16, 14:20    [19359670]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
А печать юзера и систем_юзера сделай из динамики, сразу после use tempdb, тогда станет ясно, под кем ты в темпдб.
Извини, Ролг, я сегодня чего-то ники плохо читаю. Приветствую!


все ок ;-)

я думаю,что если создавать таблицы в темпдб, то надо убрать exec as owner из процедуры.
1 июл 16, 14:25    [19359706]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
Когда убрал execute as owner, остался сисадмином.
Когда же оно есть, о логинах можно забыть, ты сам закрываешь песочницу, становишься только юзером.
Когда база TRUSTWORTY с овнером sa, у него есть authenticate server, т.е.песочница вновь открыта, логин уже снова логин, не юзер.

А в случае без execute as owner выполни не под админом и снова получишь ту же ошибку
1 июл 16, 14:25    [19359707]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
Когда убрал execute as owner, остался сисадмином.
Когда же оно есть, о логинах можно забыть, ты сам закрываешь песочницу, становишься только юзером.
Когда база TRUSTWORTY с овнером sa, у него есть authenticate server, т.е.песочница вновь открыта, логин уже снова логин, не юзер.

А в случае без execute as owner выполни не под админом и снова получишь ту же ошибку


да, ты прав, после юзе темпдб во второй базе

sa	guest


Я этим execute as owner пытался решить проблему, когда сервер рестартовал и любой первый же юзер вызвавший процедуру проверит и восстановит таблицу в темпдб
1 июл 16, 14:33    [19359752]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
Но получается так: если можно базу сделать trustworthy - я сделаю, и проблем не будет с созданием таблицы после рестарта сервера.
На сервере, где можно запустить процедуру на старте - тоже.
А вот с серверами типа Amazon RDS проблема. Там ни того, ни другого нет.
1 июл 16, 14:38    [19359792]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
такие дела решаются через сертификат, живущий в обеих базах.
в первой, куда нужен доступ, делают юзера из этого сертификата и ему дают нужные права,
во вторую базу перетаскивают сертификат,
им подписывают процедуру.
теперь у выполняющего процедуру есть права того юзера в той первой базе.

но темпдб пересоздается при рестарте, поэтому решение морочное.

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

если не надо, чтоб доступ у всех просто так, а надо через процедуру,
то решение с сертификатом.
но база должна быть постоянная, а не пересоздаваемая каждый раз без сертификата
1 июл 16, 14:54    [19359920]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

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

но темпдб пересоздается при рестарте, поэтому решение морочное.

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

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


Собственно, процедуру я сделал так, что она создает в любой указанной базе, в простейшем случае - в той же, где и процедура, в основной базе.
Но у темпдб есть некоторые преимущества - самоочистка и Simple Recovery model, что я так понимаю должно усорять транзакции в ней, по сравнению с полной моделью

С сертификатами надо посмотреть, как работать в ограниченных средах типа Amazon RDS, Azure
1 июл 16, 15:16    [19360113]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
Ролг Хупин
Собственно, процедуру я сделал так, что она создает в любой указанной базе, в простейшем случае - в той же, где и процедура, в основной базе.

ну так если нет на базе TRUSTWORTHY, да еще и с овнером с админскими правами,
нет и прав на создание в любой базе (вообще выхода за пределы базы нет).
1 июл 16, 15:28    [19360178]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
Ролг Хупин
Собственно, процедуру я сделал так, что она создает в любой указанной базе, в простейшем случае - в той же, где и процедура, в основной базе.

ну так если нет на базе TRUSTWORTHY, да еще и с овнером с админскими правами,
нет и прав на создание в любой базе (вообще выхода за пределы базы нет).


Потому я и решил на темпдб остановиться, в ней по идее должно быть проще с правами.

Предполагается, что TRUSTWORTHY должно быть ы нормальных условиях, но вот Amazon RDS не позволяет.

Так что в общем вопрос остается таким: как мне сделать так, чтобы вызов процедуры под любым юзером мог создать таблицу в темпдб.
1 июл 16, 15:52    [19360336]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
Ролг Хупин
Потому я и решил на темпдб остановиться, в ней по идее должно быть проще с правами.

нет!
в ней проще с доступом, потому что открыт гость.
а прав на создание таблиц без решеток в ней нет ни у кого,
потому что в этом смысле это такая же база как и все.
в нее все вхожи, но без никаких прав.
решетки это исключение
1 июл 16, 15:57    [19360353]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
Ролг Хупин
Потому я и решил на темпдб остановиться, в ней по идее должно быть проще с правами.

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


да.... я это я протупил.

Как заставить жить таблицу ## до рестарта сервера?
1 июл 16, 16:05    [19360393]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
Ролг Хупин
Так что в общем вопрос остается таким: как мне сделать так, чтобы вызов процедуры под любым юзером мог создать таблицу в темпдб.

выдать гостю соответствующие права.
но это пустить козла в огород.
либо идти по пути сертификатов, тогда будет работать ровно до рестарта.
автоматизировать создание сертификата в темпдб,
создание юзера из него, перетаскивание в базу, подпись процедуры -- это гимор.
даже если сложить все это в стартаповую процедуру, однажды это хозяйство навернется
1 июл 16, 16:06    [19360394]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
Ролг Хупин
Так что в общем вопрос остается таким: как мне сделать так, чтобы вызов процедуры под любым юзером мог создать таблицу в темпдб.

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


В Amazon RDS нет стартаповых процедур, в Азуре похоже тоже.

Я думаю можно наверное использовать ## и если исчезла - любой юзер может на лету пересоздать по ходу работы и продолжать работать.
1 июл 16, 16:13    [19360433]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
Ролг Хупин
Как заставить жить таблицу ## до рестарта сервера?

держать ее в вечно висящей процедуре в цикле вида while 1 = 1?
процедура стартует 1 раз автоматически при рестарте и создает таблицу,
а потом к ней как-то обращается в цикле, с каким-то waitfor delay.
но цикл вечный, живет до рестарта
1 июл 16, 16:18    [19360454]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
чего-то меня занесло.
обратиться к таблице надо 1 раз, вне цикла.
а потом пускай висит пустой цикл,
это же все равно сессия, в которой обратились к ##.
1 июл 16, 16:29    [19360504]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
чего-то меня занесло.
обратиться к таблице надо 1 раз, вне цикла.
а потом пускай висит пустой цикл,
это же все равно сессия, в которой обратились к ##.


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


автор
After creation, global temporary tables become visible to any user and any connection. They can be manually dropped with DROP TABLE command. Global temporary tables are automatically dropped when the session that create the table completes and there is no active references to that table. If the creator session ends, but there is an active references to that table from other sessions, SQL Server waits until the last T-SQL statement of the reference session completes and then table is dropped. Also during the execution of reference session statements, the table becomes unavailable for new connections.
1 июл 16, 16:33    [19360517]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
у меня это и так создающая процедура.
и пускай она висит вечно
1 июл 16, 16:41    [19360553]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
у меня это и так создающая процедура.
и пускай она висит вечно


Хорошо, как стартонуть такую процедуру, при условии, что у сервера нет возможности пускать стратап процедуру?
Вариант: у меня есть некие 2-3 процедуры, которые клиентское приложение в любом случае пускает на старте
Но как вызвать эту вечнозеленую процедуру, чтобы она осталась висеть?

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

А еще как?
1 июл 16, 17:16    [19360740]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
o-o
Guest
Ролг Хупин
Вариант: у меня есть некие 2-3 процедуры, которые клиентское приложение в любом случае пускает на старте
Но как вызвать эту вечнозеленую процедуру, чтобы она осталась висеть?

запускает приложение, как те самые 2-3 процедуры.
а в процедуре создание таблицы и сразу потом while 1 = 1
1 июл 16, 17:28    [19360790]     Ответить | Цитировать Сообщить модератору
 Re: Почему нет прав при выполнении процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4182
o-o
Ролг Хупин
Вариант: у меня есть некие 2-3 процедуры, которые клиентское приложение в любом случае пускает на старте
Но как вызвать эту вечнозеленую процедуру, чтобы она осталась висеть?

запускает приложение, как те самые 2-3 процедуры.
а в процедуре создание таблицы и сразу потом while 1 = 1


а так :
я могу всунуть вызов в те 2-3 процедуры, но не в код приложения, при этом надо иметь возможность вызова fire-and-forget.
?
1 июл 16, 17:49    [19360896]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить