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

Откуда: Москва
Сообщений: 191
mssql 2005
Добрый день, не посоветуете как и можно ли написать пользовательскую функцию которая бы делала запрос в другую бд под определенной (известной) учетной записью?

Проблема такая - есть две разные бд (две программы, одна CRM компании другая 1С компании) на одном mssql сервере, в них работают разные юзеры и первая (CRM) должна читать данные второй (1С).

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

в общем запутался... -
можно ли реализовать второй вариант? и как это сделать - если есть пример - было бы супер
3 апр 14, 19:57    [15829517]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Есть такая штука - Cross Database ownership Chaining.
3 апр 14, 20:25    [15829596]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
ef1
Member

Откуда: Москва
Сообщений: 191
Спасибо! уже читаю))
3 апр 14, 20:41    [15829664]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
SERG1257
Member

Откуда:
Сообщений: 2873
Копните в сторону сертификатов
https://www.sql.ru/blogs/decolores/950
3 апр 14, 20:45    [15829674]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
o-o
Guest
пробую для эксперимента подписать сертификатом функцию (inline table-valued function).
а мне выдается ошибка:
Msg 15560, Level 16, State 1, Line 1
Cannot add or drop a signature on 'dbo.fn_test' because only modules can be signed.


БОЛ на ADD SIGNATURE выдает:

ADD [ COUNTER ] SIGNATURE TO module_class::module_name
BY <crypto_list> [ ,...n ]

module_name
Is the name of a stored procedure, function, assembly, or trigger to be signed or countersigned.


тогда в чем может быть проблема?
или inline table-valued function не считается за module?
но ведь мое fn_test есть даже в sys.sql_modules

в интернете и документации нахожу только примеры подписи процедур.
кто-нибудь пробовал функции подписывать или может объяснить причину ошибки?
я лично склоняюсь к тому, что они за модуль считают нечто выполняемое,
а inline table-valued function проходит как нечто вроде вьюхи, т.е. неисполняемая сущность
4 апр 14, 19:08    [15834863]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
o-o
но ведь мое fn_test есть даже в sys.sql_modules
facepalm.jpg
И самое главное не потому что VIEW тоже там есть.
o-o
я лично склоняюсь к тому, что они за модуль считают нечто выполняемое,
а inline table-valued function проходит как нечто вроде вьюхи, т.е. неисполняемая сущность
Не выражаемо Эпично.

Да вообще всецело эталоннейший пост. Спасибо o-o.

Ссори за OffTop. Не удержался.
5 апр 14, 01:29    [15836063]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
o-o
Guest
Mnior,

ужас, как смешно, ну давайте вместе ржать, хором
причем, когда ТС хочет ФУНКЦИЮ, смотрящую в соседнюю базу,
и ему говорят, копай в сторону сертификатов -- все ок,
а как кого-то пробирают сомнения, можно ли ф-цию сертификатом подписать,
и они оправдываются, так ух как весело!
хотите угадаю? вы и вовсе нифигошеньки сертификатом не пробовали подписывать :P

+
Mnior, а я про вас мультик знаю.

граждане, простите оффтоп, но могу тайну раскрыть.
Mnior -- ето Sterculius, the Roman god of feces
если кто не в теме, в ютубе наберите Beavis and ButtHead: Peace, Love and Understanding
вот который в конце мульта кричит
"You have desecrated my temple! You offend me! Prepare to feel the brunt of my wrath!" -- вот он и есть Mnior.
ну и действо, к-ое за этим следует, вот это и есть коронная деятельность г-на Mnior -а на форуме.
applause

хороших выходных, Sterculius
5 апр 14, 02:22    [15836136]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
o-o
ужас, как смешно, ну давайте вместе ржать, хором
"Ржать"? Не думаю что "FacePalm" и "эпично" стоят прям так рядом.
o-o
причем, когда ТС хочет ФУНКЦИЮ, смотрящую в соседнюю базу,
и ему говорят, копай в сторону сертификатов -- все ок
Это в какой логике вы решили что одобряют это решение?
Хотя вы этими высказываниями продолжаете дополнять эпичную картину. Шедеврально.
o-o
а как кого-то пробирают сомнения, можно ли ф-цию сертификатом подписать,
и они оправдываются, так ух как весело!
Не весело - эпично.

А сколько бывают типов UDF?! Вы знаете.
- Все числа чётные!
- Почему?
- "2" чётное?
- Да
- Ну вот.
- Но "1" не чётное же!
а) - Ну и что?!
б) - Вы противоречите доказанной мной теореме.

o-o
хотите угадаю? вы и вовсе нифигошеньки сертификатом не пробовали подписывать :P
Не угадали.
Но это дополняет эпичность картины - ибо это совершенно неважно.
+ o-o
o-o
Mnior, а я про вас мультик знаю.
Опять не угадали.

Не надо боятся того и тех, когда не понимаете что происходит. Я понимаю, защитная реакция, швыряться какашками, но это бессмысленно.
Вы такой легко ранимый, но обычно никто никому ничего плохого не желает, совсем совсем. Расслабьтесь.
Моя реакция, была моя рекция, выражать было её эгоистично, но ... не удержался, ибо вызвало бурю ассоциаций.
Частично похоже на вот такое. :)
5 апр 14, 23:09    [15838229]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
o-o
Guest
Mnior,

+

эпичный
1. соотн. с сущ. эпос III, связанный с ним
2. Свойственный эпосу [эпос III] как ряду связанных между собою исторических событий, отличающихся значительностью, величием; эпический III 2..
3. Величественно-спокойный.
4. Преисполненный значительности, героизма.

а мой случай -- какой?

Mnior, давайте в фэйсбуке дружить?
(думаю, как минимум 2 человека могут нам обменять наши мэйлы: мы оба общались письменно с locky, правильно?
ну и, думаю, тов. Моисеенко с sql-ex.ru к Вам каким-то боком. на Linkedin-е я есть в его Connection-ах, а Вы?)

опять ведь кому-то тему зафлудили...
хотя можно дело поправить: ТС, Вам дать пример, как сертификат между базами перетащить?
в блоге уважаемого DeColo®es-a этого нет, там просто master используется, т.к. немного другой случай:
нужно право уровня сервера дать, а не выйти из одной базы в другую.

спасибо за музон, но тоже не угадали, ооочень плохо отнушусь к попсне,
с советами уже ближе к истине, спсб :)

раз мы такие взаимно вежливые сегодня, ок, тоже сообщу: не кидаюсь я какашками,
ассоциация Mnior - Sterculius, простите, давняя и стойкая, хотя я понимаю, что Вам постоянно храм оскверняют
но оч. часто Ваша реакция -- та самая; (и чего плохого-то? или трудно быть богом?)
что вижу, то и говорю, уж тоже не знаю, почему именно вчера пришло в голову публично этим поделиться :)

6 апр 14, 00:30    [15838457]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
о-о
ТС, Вам дать пример, как сертификат между базами перетащить?
Имхо это не нужно. Думаю хватает просто объяснить что это сделано совершенно для другой задачи и совершенно не эффективно по сравнению с предложенным вариантом Сергея Алексеевича выше.
А так вы просто запутываете разговор, себя и других.

Топик для оффтопа: 15838664
о-о, там весь ответ.
6 апр 14, 02:53    [15838665]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
o-o
Guest
никого я не запутываю
ef1
1. получается что можно сформировать запрос с прямым указанием обращения к таблицам второй бд - но при этом нужно этого пользователя добавить во вторую бд - что не интересно
2. а можно во второй бд сделать одного универсального пользователя на чтение и читать через него - но тогда нужно в первой бд написать или хп или ufd - где указывать логи и пароль этого юзера и от него выполнять запрос во вторую бд


Cross Database ownership Chaining -- это как раз для 1-ого случая.
т.е. да, пользователя придется добавлять во вторую базу.

сертификат -- это для 2-ого случая.
без добавления пользователя во 2-ую базу.
только не надо "указывать логи и пароль этого юзера и от него выполнять запрос во вторую бд".
делается так:
во второй бд создаете сертификат.
из сертификата -- пользователя во второй базе ("сделать одного универсального пользователя на чтение")
ему выдаете необходимые права на таблицы.
сертификат надо перенести в первую базу: делается через бэкап сертификата на диск
с последующим созданием его из файла.
все. теперь процедуру или СКАЛЯРНУЮ функцию подписываете этим самым сертификатом.
пользователь первой базы, не имея никакого доступа ко второй базе, сможет запускать процедуру
или использовать скалярную ф-цию.
7 апр 14, 00:35    [15840920]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
o-o
Cross Database ownership Chaining -- это как раз для 1-ого случая.
т.е. да, пользователя придется добавлять во вторую базу.
Как раз наоборот.
Если владельцы объектов (владелец процедуры в одной базе и владелец таблицы в другой) совпадают, то никакой дальнейшей проверки не будет.

+ Олицетворение в модулях никто не отменял

Сертификаты тут и не нужны. Они нужны если надо предоставить аутентичный доступ, который теряется при смене объектов.
Или, к примеру, если Ownership Chaining недопустим по каким либо причинам (секьюрити).
7 апр 14, 15:19    [15844169]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
o-o
Guest
Mnior
o-o
Cross Database ownership Chaining -- это как раз для 1-ого случая.
т.е. да, пользователя придется добавлять во вторую базу.
Как раз наоборот.
Если владельцы объектов (владелец процедуры в одной базе и владелец таблицы в другой) совпадают, то никакой дальнейшей проверки не будет.


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

create database db1;
go
create database db2;
go

create login crossdb_test with password = '*****';
go

alter database db2
set db_chaining on;

alter database db1
set db_chaining on;
go

use db1;
go

create user crossdb_test from login crossdb_test;
exec sp_addrolemember 'db_owner', crossdb_test;
go

create proc dbo.usp_test as 
select * from db2.dbo.test_table;
go

use db2;
create table dbo.test_table (id int);
insert into dbo.test_table (id) values (1);
go

use db1;
go

execute as login = 'crossdb_test';
exec dbo.usp_test;
---------------------------
Msg 916, Level 14, State 1, Procedure usp_test, Line 3
The server principal "crossdb_test" is not able to access the database "db2" under the current security context.
7 апр 14, 17:46    [15845325]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
o-o
Guest
Mnior
Сертификаты тут и не нужны


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


вот именно для того и нужны (один из случаев, разумеется).
чтобы дать доступ к объектам другой базы пользователю,
НЕ ОТМАПЛЕННОМУ В ТУ БАЗУ.

доступ к объектам дается заведенному во второй базе пользователю.
созданному командой create user from certificate.
сертификат перетаскивается в первую базу и им подписывается модуль.
не отмапленный во вторую базу юзер первой базы, имеющий execute на процедуру,
получает доступ к объектам второй базы.

у меня есть работающий пример, или нафига мне тут разоряться.
ВЫ же сказали, что тоже подписывали, и что пример не надо.
7 апр 14, 17:56    [15845375]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Не надо таких категоричных заявлений. Все решается очень просто:
use master
go
if exists ( select * from sys.databases a where a.name = 'db1' )
    exec('drop database db1')
go
if exists ( select * from sys.databases a where a.name = 'db2' )
    exec('drop database db2')
go
create database db1;
go
create database db2;
go
if not exists (select * from master.sys.syslogins a where a.name = 'crossdb_test')
    create login crossdb_test with password = '92738354-ADDC-40C3-AD64-6A875134AEC1';
go

alter database db2
set db_chaining on;

alter database db1
set db_chaining on;
go
use db2
grant connect to guest
use db1;
go

create user crossdb_test from login crossdb_test;
exec sp_addrolemember 'db_owner', crossdb_test;
go

create proc dbo.usp_test as 
select * from db2.dbo.test_table;
go

use db2;
create table dbo.test_table (id int);
insert into dbo.test_table (id) values (1);
go

use db1;
go

execute as login = 'crossdb_test';
exec dbo.usp_test;
go
revert


Сообщение было отредактировано: 7 апр 14, 17:59
7 апр 14, 17:59    [15845396]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
o-o
Guest
да, извиняюсь за категоричность.
если честно, я в курсе.
только еще вот рекомендацию в скобочках дает уважаемый мной(и, думаю, не только мной) товарищ
(Erland Sommarskog)
поэтому я здесь всю цитату приведу

Cross-Database Access

If a stored procedure sp1 in database A accesses a table tbl2 in database B, ownership chaining can apply as well, if the procedure owner also owns tbl2. In the trivial case, the two databases have the same owners and all involved objects are owned by dbo. The user running sp1 must also be a user in database B. (Unless you have enabled access for the guest user in database B, something I don't recommend.)
7 апр 14, 18:05    [15845429]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Ссылка по теме: http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/
7 апр 14, 18:06    [15845441]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
o-o
Cross-Database Access

If a stored procedure sp1 in database A accesses a table tbl2 in database B, ownership chaining can apply as well, if the procedure owner also owns tbl2. In the trivial case, the two databases have the same owners and all involved objects are owned by dbo. The user running sp1 must also be a user in database B. (Unless you have enabled access for the guest user in database B, something I don't recommend.)
Если гесту не давать права, то ничего страшного не произойдет.

Или есть информация об эксплуатации включенного но бесправного геста?

Сообщение было отредактировано: 7 апр 14, 18:08
7 апр 14, 18:07    [15845448]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
o-o
Guest
Гавриленко Сергей Алексеевич,
спасибо за инфо.
+

я только эксперименты провожу на личном сервере,
опыта и прав админства на рабочем у меня нет,
могу только верить на слово тем, кого уважаю
7 апр 14, 18:15    [15845490]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Есть немало статей про improving performance с этим ownership chaining.
И главное что это нормальное повседневное решение, когда сервера не шаряться нескольким участникам бизнеса, всё в однородной среде одной компании.
А сертификаты больше к параноидальности, где есть разнонаправленные интересы, где могут быть злоупотребления.
Эти два механизма не равноправны.

+
o-o
могу только верить на слово тем, кого уважаю
:)
Эта тонкая грань между больше доверять и верить на слово
8 апр 14, 03:26    [15846967]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
o-o
Guest
+

это называется, пока на своей шкуре не прочувствовал,
прислушайся к советам более опытных. они не на пустом месте советуют

я и не спорю с "нормальным повседневным решением",
может, у человека проблемой лишь навеяно, а интерес академический.
про первый вариант он сказал "неинтересно".
разве рабочие проблемы решаются с критеpиями типа интересно/не очень?
для меня это означает: расскажите, можно ли организовать доступ в базу без добавления туда пользователя.
мне не жалко, я рассказываю.
и мне стало интересно именно про функцию -- т.к. это именно что как будто на сами данные выдать,
по логике не должно было прокатить, а в хэлпе указано. было конец рабочего дня и вопрос был задан в спешке.
но впредь сто раз проверю, прежде чем постить, чтобы поумерить цеплючесть некоторых
эпичность настроения пусть кто другой вам повышает :P
8 апр 14, 08:20    [15847149]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
o-o: 15848971
8 апр 14, 13:12    [15848979]     Ответить | Цитировать Сообщить модератору
 Re: UFD с обращением к чужой БД под определенным логином  [new]
ef1
Member

Откуда: Москва
Сообщений: 191
Спасибо - всем участникам обсуждения!!!!
все сделал по совету Гавриленко Сергея Алексеевича
все работает в рамках единого информационного пространства компании

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

еще раз всем спасибо!
11 апр 14, 16:14    [15869150]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить