December 21, 2016 at 9:35 am
Hello,
I'm trying to allow access to a table in one database (A) from a stored procedure in another database (B), without adding the stored procedure user to the first database (A).
I have:
1. added a certificate to database A
2. created a user for this certificate in database A
3. granted select access to the table in database A for this user
4. copied the certificate to database B
5. signed the stored procedure in database B with this certificate
6. granted execute permissions for the stored procedure to
as described in http://www.sommarskog.se/grantperm.html#Certificates
But when I execute the stored procedure I get this message:
The server principal "testuser" is not able to access the database "Testing" under the current security context.
I have checked in sys.crypt_properties to make sure that the stored procedure is signed OK - it is.
In my stored procedure I interrogate sys.user_token to see if there is one with type "USER MAPPED TO CERTIFICATE" - there isn't, although the article above doesn't show this technique for the cross-database example, so I'm not sure if it's meant to.
I have tested my code in a single database with the cross-database stuff commented out and it works fine.
Here's my code:
USE master
go
-- Create a test login.
if not exists (select *
from sys.sql_logins
where name = 'testuser'
)
CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
go
use RCBTesting
go
if object_id('TestGetDataSigned') is not null
drop procedure TestGetDataSigned
go
if USER_ID('TestingCrossDatabaseCertUser') is not null
drop user TestingCrossDatabaseCertUser
go
if CERT_ID('TestingCrossDatabaseCert') is not null
drop certificate TestingCrossDatabaseCert
go
CREATE CERTIFICATE TestingCrossDatabaseCert
ENCRYPTION BY PASSWORD = 'Testing testing 123 hello'
WITH SUBJECT = 'cross-database test',
START_DATE = '20161201', EXPIRY_DATE = '20500101'
go
-- Get the certificate bytes into a temp table so we can use it in
-- the target database.
if object_id('tempdb..#keys') is not null
drop table #keys
go
CREATE TABLE #keys (pubkey varbinary (MAX) NOT NULL,
privkey varbinary(MAX) NOT NULL)
INSERT #keys (pubkey, privkey)
SELECT certencoded(cert_id('TestingCrossDatabaseCert')),
certprivatekey(cert_id('TestingCrossDatabaseCert'),
'Banana???',
'Testing testing 123 hello')
go
-- Create the certificate user. Note that we do not grant access to
-- testuser.
CREATE USER TestingCrossDatabaseCertUser FROM CERTIFICATE TestingCrossDatabaseCert
go
--Create test table
if object_id('test') is not null
drop table test
go
create table test (a int not null)
insert test values (1)
go
--Grant permissions for user to table
grant select on test to TestingCrossDatabaseCertUser
go
use JeremySandbox
go
--user already exists
if user_id('testuser') is not null
drop user testuser
go
CREATE USER testuser
go
--create signed test proc
if object_id('TestGetDataSigned') is not null
drop procedure TestGetDataSigned
go
create procedure TestGetDataSigned
as
SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token
--select *
--from dbo.test
EXEC ('SELECT * from RCBTesting.dbo.test')
go
grant execute on TestGetDataSigned to testuser --[SOM\hrgsrvacc01]
go
--create unsigned test proc
if object_id('TestGetDataUnsigned') is not null
drop procedure TestGetDataUnsigned
go
create procedure TestGetDataUnsigned
as
SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token
--select *
--from dbo.test
EXEC ('SELECT * from RCBTesting.dbo.test')
go
grant execute on TestGetDataUnsigned to testuser -- [SOM\hrgsrvacc01]
go
if CERT_ID('TestingCrossDatabaseCert') is not null
drop certificate TestingCrossDatabaseCert
go
-- Import the certificate we created in the first test database into the second.
DECLARE @sql nvarchar(MAX)
SELECT @sql =
'CREATE CERTIFICATE TestingCrossDatabaseCert
FROM BINARY = ' + convert(nvarchar(MAX), pubkey, 1) + '
WITH PRIVATE KEY (BINARY = ' + convert(nvarchar(MAX), privkey, 1) + ',
DECRYPTION BY PASSWORD = ''Banana???'',
ENCRYPTION BY PASSWORD = ''Testing testing 123 hello - can be different??'')'
FROM #keys
PRINT @sql
EXEC (@sql)
DROP TABLE #keys
go
ADD SIGNATURE TO TestGetDataSigned BY CERTIFICATE TestingCrossDatabaseCert
WITH PASSWORD = 'Testing testing 123 hello - can be different??'
go
SELECT Module = object_name(cp.major_id),
[Cert/Key] = coalesce(c.name, a.name),
cp.crypt_type_desc
FROM sys.crypt_properties cp
LEFT JOIN sys.certificates c ON c.thumbprint = cp.thumbprint
LEFT JOIN sys.asymmetric_keys a ON a.thumbprint = cp.thumbprint
go
execute as user = 'testuser' --'SOM\hrgsrvacc01'
go
select *
from RCBTesting.dbo.test
go
exec dbo.TestGetDataSigned
go
exec dbo.TestGetDataUnsigned
go
revert
go
USE master
go
DROP LOGIN testuser
go
And this is the text of the output it gives:
(1 row(s) affected)
(1 row(s) affected)
CREATE CERTIFICATE TestingCrossDatabaseCert
FROM BINARY = 0x308201C13082012AA003020102021023DDA0C10C7F06894D0DB6A11DEE4A3F300D06092A864886F70D0101050500301E311C301A0603550403131363726F73732D646174616261736520746573743020170D3136313230313030303030305A180F32303530303130313030303030305A301E311C301A0603550403131363726F73732D6461746162617365207465737430819F300D06092A864886F70D010101050003818D0030818902818100E29E08A44A6EE9114482CEBA7865A45C035FD66C77BDED8169D03E73A766D6800916102E7FFA340C75C092FCD25E168A9F2EB557A2F8CE195E6597C5EAFA44A24A9456E3D0767515FD72FDED122AF5879AFA80AD728695CB254AFAF48180EDBD4BE77D548B51138B23CBD0EAA40DA083ABD9DE588724DB2DB9A36F331D13C0030203010001300D06092A864886F70D010105050003818100AC54540D23D98A420C83FF8ECBCD659B990142846FF72C4240D3FD6A4B5E60F651551861836814F538A0B0391710B4CC5794D04B40B7E5F97E72A0E45DAAEB93BFB67BDA10280BDE9CF539BE09D8DE790022E386B55002AF817B015ACC12EECD3E48D187E0A6E19288437A70664776DC58365C81F593437D38093564C3015AA2
WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000A23EAFAA30F8D95A61E83D98632DD1EA0702000000A40000BB233FBF3EA257D8101130FCA5EACAA1212E3DA31B9E89DA6A1783F5B47C9D234E90FCD5B3E24FDCF00AD2ADE5A593547162C6BC016E03D7D868A284737AA56B096667219500FDA82B1942CFB897132255CA7F953CF3ABAE767E506F1681D50ABFC8EC6ED5E114E59B3CFB24EF8D98B2B7C1DE64329B5F5101F64850A0DF3B00453D960F1E09A682D2CD7601433F3D774600A979F82A241E7F2A5B069295A8EF5855777D93AEEBD08538D01F46506781AE24A6304F59A85C3CF4584B16E8B36ACCCFBEC813F566F487AF579A37840B4D7F7574914AE5AA39336C92434AB19A6C2186D6E40FB678AC31B48E0B4233119B4B0B1DE75F7E8EE13539084AD79E11C88756D4946DEE89DE0EAC0A63EEE5C2F539770BA37973A72869F1DF8DE4404DD7E52A7707A2C532EF46B76837333EED7C7D2F9F47B81565C51703E9B1CD4D190EF19F6A11F8A8F509FDD0256A402C41000B444E5F4CC6DA4F4C11FDFA1C6CAA3E78D2F29BA8B4D3407CCF6A082FB6AA764C966F066D282C24E8C0A66C2572CFC310D10D5334C377D8C360146013B336881B7517BA781C4CFA277AD28037A29C08E75723164614FA6FD802FC07155FDE6A12C0674CF0A8F60AFA8090D2E98D3E817F36373EB9F871021E669A01C26149314B6494E2B963D039593A6882D76693DC18C8F6EC8A48788BE9C35A92752181CF0BA2C54E4789ECC32111AF92749C010AF0E8FD51223F5F738679DFE00AA6C42500272988E0D4534273D696B1BE8AFA32A594F60DB983109C3E5C0C9EB17F356242DB144952204B7C9E074A7903236DB2F416CCE64D724BBE334C1934,
DECRYPTION BY PASSWORD = 'Banana???',
ENCRYPTION BY PASSWORD = 'Testing testing 123 hello - can be different??')
(1 row(s) affected)
Msg 916, Level 14, State 1, Line 2
The server principal "testuser" is not able to access the database "RCBTesting" under the current security context.
(2 row(s) affected)
Msg 916, Level 14, State 1, Line 1
The server principal "testuser" is not able to access the database "RCBTesting" under the current security context.
(2 row(s) affected)
Msg 916, Level 14, State 1, Line 1
The server principal "testuser" is not able to access the database "RCBTesting" under the current security context.
Any assistance would be gratefully received.
Thanks,
Rachel.
December 21, 2016 at 9:45 am
If you're using an SP that is cross database, permissions are not inherited.
This means that the login must have a user on the target database, and have permission to select from that table (in this scenario).
From what I could see of your SQL, testuser only exists on JeremySandbox, they do not exist on RCBTesting. As a result, when test User executes your sp's, TestGetDataSigned & TestGetDataUnsigned, they can't access the table your sp's are trying to get data from as they are located in the RCBTesting Database.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 21, 2016 at 9:53 am
Thom A (12/21/2016)
If you're using an SP that is cross database, permissions are not inherited.This means that the login must have a user on the target database, and have permission to select from that table (in this scenario).
From what I could see of your SQL, testuser only exists on JeremySandbox, they do not exist on RCBTesting. As a result, when test User executes your sp's, TestGetDataSigned & TestGetDataUnsigned, they can't access the table your sp's are trying to get data from as they are located in the RCBTesting Database.
Thanks Thom,
But this is exactly what I am trying to avoid by signing the stored procedure with the certificate - the user can't have access to the other database (RCBTesting).
And the comments in Erland's example code say this:
-- Create the certificate user. Note that we do not grant access to
-- testuser.
Rachel.
December 21, 2016 at 10:17 am
You could, instead, have the SP itself do an EXECUTE AS. That user, instead, has access to use the certificates on the other database, and, also to access the data.
You would, however, need to give the user (in this case testuser), impersonate permissions instead.
This is very simplistic, and there may be a better solution, but...
USE TestDB;
GO
--Create the TestUser LOGIN and USER
CREATE LOGIN TestUser WITH PASSWORD = 'Test123';
GO
CREATE USER TestUser FOR LOGIN TestUser;
GO
--Create the CertUser LOGIN and USER
USE AdventureWorks2012;
GO
CREATE LOGIN CertUser WITH PASSWORD = 'Cert123', DEFAULT_DATABASE = AdventureWorks2012;
GO
ALTER LOGIN [CertUser] DISABLE; --So that no one can log in as the CERTIFICATE user
GO
CREATE USER CertUser FOR LOGIN CertUser;
GO
GRANT SELECT ON Person.[Address] TO CertUser;
GO
USE master;
GO
GRANT IMPERSONATE ON LOGIN::[CertUser] TO [TestUser] -- Need Impersonation rights! :)
GO
USE TestDB;
GO
CREATE USER CertUser FOR LOGIN CertUser; --Need to create the user here as well I'm afraid, but they won't have any permissions.
GO
--Create the proc that will fail
CREATE PROC WithoutExecAs_sp AS
SELECT *
FROM AdventureWorks2012.Person.[Address];
GO
--Create the proc that will work
CREATE PROC WithExecAs_sp AS
EXECUTE AS LOGIN = 'CertUser';
SELECT *
FROM AdventureWorks2012.Person.[Address];
REVERT;
GO
--Give Exec rights
GRANT EXECUTE ON WithoutExecAs_sp TO TestUser;
GRANT EXECUTE ON WithExecAs_sp TO TestUser;
GO
--Start the test
EXECUTE AS LOGIN = 'TestUser';
--This one will fail
EXEC WithoutExecAs_sp;
GO
--This one will work
EXEC WithExecAs_sp;
GO
REVERT;
GO
--Clean up!
USE TestDB;
GO
DROP USER TestUser;
GO
DROP LOGIN TestUser;
GO
DROP PROC WithoutExecAs_sp;
DROP PROC WithExecAs_sp;
USE AdventureWorks2012;
GO
DROP USER CertUser;
GO
DROP LOGIN CertUser;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 21, 2016 at 10:30 am
Aaaargh! :crying:
It was because I was doing execute as user instead of execute as login!
All that remains now is to see if I can set up a SQL Server Agent credential and proxy to use it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply