Recent Posts

Archives

Categories

Meta

Adatelérés két adatbázis között

Két db között a legegyszerűbb adatelérés, ha mindkét db-ben létrehozzuk ugyanazt a user-t megfelelő jogosultságokkal. Ekkor semmi dolgunk.

Amennyiben viszont két különböző db között kell megteremteni az adatelérést, akkor ugyan szintén használható a fenti módszer, de ennél szigorúbb eljárást is követhetünk.

Tegyük fel van 2 adatbázisunk (db1, db2) és a db1-ben lévő táblát akarjuk elérni a db2-ből.

-- Create test two databases
CREATE DATABASE db1
go
-- Move to first test database.
USE db1
go
-- A test table.
CREATE TABLE testtbl (a int NOT NULL,
                      b int NOT NULL,
                      c int NOT NULL)
go
-- Insert some test data.
INSERT testtbl (a, b, c) VALUES (12, 23, 34)
go

Ez eddig az elérni kívánt objektum (testtbl) előkészítése.

Most akkor mi kell ahhoz, hogy a testtbl elérhető legyen?

Kell egy tanusítvány (crossdbcert) és egy user (certuser, amihez login nem kell) a tanusítványra. A user-nek SELECT joga van a táblához:

-- Create certificate in db1
CREATE CERTIFICATE crossdbcert
   ENCRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds'
   WITH SUBJECT = 'Cross-db test',
   START_DATE = '20020101', EXPIRY_DATE = '20200101'
go
-- Create the certificate user. Note that we do not grant access to
-- testuser.
CREATE USER certuser FROM CERTIFICATE crossdbcert
go
GRANT SELECT ON testtbl TO certuser
go

Akkor most nézzük a db2-t. Itt kell egy user (testuser), akinek execute joga van egy aláírt tárolton. Ez a tárolt adja ki a select-et a db1 testtbl táblájára:

CREATE DATABASE db2
go
CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
go
-- Switch to the second database.
USE db2
go
-- Welcome the test user to this database.
CREATE USER testuser
go
-- Signed test procedure.
CREATE PROCEDURE signed_sp AS
    SELECT a, b, c FROM db1..testtbl
go
GRANT EXECUTE ON signed_sp TO testuser
go

Jelenleg még a testuser a tárolt hivásra jogosultság hibával elszáll. Ennek feloldása a következőképpen történik:

Előszöris a tanusítványt (crossdbcert) létrehozzuk a db2-ben is (ez BACKUP – RESTORE paranccsal is mehet, de 2012-től tudjuk temp táblán keresztül is létrehozni):

USE db1
go
-- Get the certificate bytes into a temp table so we can use it in 
-- the target database.
CREATE TABLE #keys (pubkey varbinary (MAX) NOT NULL,
                    privkey varbinary(MAX) NOT NULL)
INSERT #keys (pubkey, privkey)
   SELECT certencoded(cert_id('crossdbcert')),
          certprivatekey(cert_id('crossdbcert'), 
                   'She said She said',
                         'Lucy in the Skies with Diamonds')
go
use db2
go
-- Import the certificate we created in the first test database into the second.
DECLARE @sql nvarchar(MAX)
SELECT @sql = 
   'CREATE CERTIFICATE crossdbcert 
    FROM BINARY = ' + convert(nvarchar(MAX), pubkey, 1) + '
    WITH PRIVATE KEY (BINARY = ' + convert(nvarchar(MAX), privkey, 1) + ',
                      DECRYPTION BY PASSWORD = ''She said She said'',
                      ENCRYPTION BY PASSWORD = ''Helter Skelter'')'
FROM #keys

PRINT @sql
EXEC (@sql)
DROP TABLE #keys
go

A fenti tanusítvány másolatot 2012 előtt így lehet másolni:

use db1
go
BACKUP CERTIFICATE crossdbcert TO FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'She said She said',
                  DECRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds')
go
use db2
go
CREATE CERTIFICATE crossdbcert FROM FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk'
, DECRYPTION BY PASSWORD = 'She said She said', ENCRYPTION BY PASSWORD = 'Helter Skelter')
go

 

Végül a tároltat a tanusítvánnyal aláírjuk:

-- Sign the test procedures.
ADD SIGNATURE TO signed_sp BY CERTIFICATE crossdbcert
    WITH PASSWORD = 'Helter Skelter'
go

Ettől kezdve minden db2 user, aki jogot kap a signed_sp-re, képes lekérdezni a testtbl táblát.

További cikkek ezeken a linkeken:

https://www.mssqltips.com/sqlservertip/2549/options-for-cross-database-access-within-sql-server/

https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/

http://bytes.com/topic/sql-server/answers/80722-inter-database-stored-procedures-permissions

http://www.sommarskog.se/grantperm.html#certcrossdb