Права на объекты в других базах и серверные объекты

17 апреля 2018, 20:46

/*
		Задача.
		
		Пусть есть две базы db1 и db2:

		master:
			test_login (user login)

		db1:
			test_user (database user for test_login)

			dbo.test_proc1 (stored procedure)
			dbo.test_proc2 (stored procedure)

		db2:
			dbo.t (table)
			dbo.p (stored procedure)

		Нужно от некоторого пользователя test_user:
			1) из процедуры db1.dbo.test_proc1:
				- вызвать процедуру db2.dbo.p
				- сделать явную вставку в db2.dbo.t

			2) из процедуры db1.dbo.test_proc2:
				- опросить какую-нибудь системную DMV


		с минимальными правами пользователя test_user:
			- execute на test_proc1
			- execute на test_proc2

*/

/*
	Создаем базы, сертификаты и пользоваетелей.
*/
create database db1;
go
create database db2;
go


use db1;
go

create certificate __c
	encryption by password = 'P@ssw0rd12435rrfr'
	with
		subject = '...',
		expiry_date = '20991231';
go

create user __u from certificate __c;
go

backup certificate __c
to 
	file = 'c:\temp\__c.bak'
	with private key 
	( 
		file = 'c:\temp\__c_priv.bak' ,
		encryption by password = 'P@ssw0rd12435rrfr',
		decryption by password = 'P@ssw0rd12435rrfr'
	) 
go


use master;
go

create certificate __c
from
	file = 'c:\temp\__c.bak'
	with private key 
	(
		file = 'c:\temp\__c_priv.bak', 
		decryption by password = 'P@ssw0rd12435rrfr',
		encryption by password = 'P@ssw0rd12435rrfr'
	);
go

-- Этому логину будут раздаваться права на объекты сервера,
-- которые будут использоваться в пользовательских хранимках,
-- подписанных сертификатом __c.
create login __l from certificate __c;
go


use db2;
go

create certificate __c
from
	file = 'c:\temp\__c.bak'
	with private key 
	(
		file = 'c:\temp\__c_priv.bak', 
		decryption by password = 'P@ssw0rd12435rrfr',
		encryption by password = 'P@ssw0rd12435rrfr'
	);
go

-- Этому пользователю будут раздаваться права на объекты базы db2,
-- которые будут использоваться во внешних хранимках,
-- подписанных сертификатом __c.
create user __u from certificate __c;
go


/*
	Создаем объекты в базе db1
*/
use db1;
go

create procedure test_proc1
as
begin
	set nocount on;

	insert into db2.dbo.t(id, name)
	values(1, 'Строка вставлена из db1.dbo.test_proc1 в db2.dbo.p');

	exec db2.dbo.p;
end
go

create procedure test_proc2
as
begin
	set nocount on;

	select *
	from
		sys.dm_exec_sessions;
end
go

add signature to test_proc1 by certificate __c with password = 'P@ssw0rd12435rrfr';
go
add signature to test_proc2 by certificate __c with password = 'P@ssw0rd12435rrfr';
go

-- Этот grant нужен только для test_proc2
use master
go
grant view server state to __l;
go


/*
	Создаем объекты в базе db2
*/
use db2 
go

create table t(id int, name nvarchar(100));
go

create procedure p
as
begin
	set nocount on;

	select id, name from t
		union all
	select 2, 'Процедура db2.dbo.p вызвана из db1.dbo.test_proc1';
end
go

add counter signature to p by certificate __c with password = 'P@ssw0rd12435rrfr';
go

-- этот грант нужент ТОЛЬКО для разрешения ЯВНОЙ вставки в базу
-- из любой процедуры, подписанной сертификатом __c (в примере db1.dbo.test_proc1)
grant insert on t to __u;

-- если требуется только вызов процедуры p, достаточно только
-- далее отработает owner chaining
grant execute on p to __u;
go

/*
	
	Для наглядности происходящего явно выключаем:
		DB_CHAINING
		TRUSTWORTHY

*/
use master;
go
alter database db1 set db_chaining off;
go
alter database db2 set db_chaining off;
go
alter database db1 set trustworthy off;
go
alter database db2 set trustworthy off;
go

/*
	Некий пользовательский логин для тестирования.
*/
create login test_login with password = 'P@ssw0rd2asdvas';
go

use db1;
go
create user test_user for login test_login;
go
grant execute on test_proc1 to test_user;
go
grant execute on test_proc2 to test_user;
go


/*

	Тестируем

*/
use db1;
go

execute as login = 'test_login'

select suser_sname(), user_name(), *
from 
	sys.fn_my_permissions(NULL, 'SERVER');

select suser_sname(), user_name(), *
from 
	sys.fn_my_permissions(NULL, 'DATABASE');

select suser_sname(), user_name(), *
from 
	sys.fn_my_permissions('dbo.test_proc1', 'OBJECT');

select suser_sname(), user_name(), *
from 
	sys.fn_my_permissions('dbo.test_proc2', 'OBJECT');

exec test_proc1;

exec test_proc2;

revert;

FAQ

Права на объекты в других базах и серверные объекты