Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is possible by using the SQL Server cryptographic infrastructure. You encrypt each object (A, B, C, D) with its own symmetric key (data is always encrypted with a symmetric key, never with an asymmetric one). Each role has an asymmetric key, or a certificate, so there are asymmetric keys A, B and C. The role asymmetric key(s) are encrypted with the role's password. Each symmetric key is encrypted by the asymmetric keys of the roles that have acces to it (symmetric keys can be encrypted multiple times). When a user enters the system, it opens the symmetric key(s) of its role, using the role specific certificate/assymetric key. This places the symmetric keys in the current user's keychain, giving access to the objects encrypted with these keys.</p> <p>Here is a code demo:</p> <pre><code>:setvar server . :setvar dbname cryptdemo :connect $(server) use master; if db_id('$(dbname)') is not null drop database [$(dbname)]; create database [$(dbname)]; go :connect $(server) use [$(dbname)]; go create certificate RoleA encryption by password = '123!#Password' with subject = 'RoleA' create certificate RoleB encryption by password = '213!#Password' with subject = 'RoleB' create certificate RoleC encryption by password = '312!#Password' with subject = 'RoleC' go :connect $(server) use [$(dbname)]; go -- Role A has access to Object A and Object B create symmetric key ObjectA WITH ALGORITHM = AES_256 encryption by certificate RoleA; create symmetric key ObjectB WITH ALGORITHM = AES_256 encryption by certificate RoleA; go :connect $(server) use [$(dbname)]; go -- Role B has access to Object C create symmetric key ObjectC WITH ALGORITHM = AES_256 encryption by certificate Roleb; go :connect $(server) use [$(dbname)]; go -- Role C has access to Objects A, B and C open symmetric key ObjectA decryption by certificate RoleA with password = '123!#Password' alter symmetric key ObjectA add encryption by certificate RoleC; open symmetric key ObjectB decryption by certificate RoleA with password = '123!#Password' alter symmetric key ObjectB add encryption by certificate RoleC; open symmetric key ObjectC decryption by certificate RoleB with password = '213!#Password' alter symmetric key ObjectC add encryption by certificate RoleC; go :connect $(server) use [$(dbname)]; go create table Objects ( id int not null identity(1,1) primary key, data varbinary(max)); go :connect $(server) use [$(dbname)]; go -- Role A inserts an Object A and an Object B: open symmetric key ObjectA decryption by certificate RoleA with password = '123!#Password' open symmetric key ObjectB decryption by certificate RoleA with password = '123!#Password' insert into Objects (data) values (encryptbykey(Key_GUID('ObjectA'), 'Object A inserted by Role A')); insert into Objects (data) values (encryptbykey(Key_GUID('ObjectB'), 'Object B inserted by Role A')); go :connect $(server) use [$(dbname)]; go -- Role B inserts an Object C open symmetric key ObjectC decryption by certificate RoleB with password = '213!#Password' insert into Objects (data) values (encryptbykey(Key_GUID('ObjectC'), 'Object C inserted by Role B')); go :connect $(server) use [$(dbname)]; go -- Role C inserts objects A, B, C open symmetric key ObjectA decryption by certificate RoleC with password = '312!#Password' open symmetric key ObjectB decryption by certificate RoleC with password = '312!#Password' open symmetric key ObjectC decryption by certificate RoleC with password = '312!#Password' insert into Objects (data) values (encryptbykey(Key_GUID('ObjectA'), 'Object A inserted by Role C')); insert into Objects (data) values (encryptbykey(Key_GUID('ObjectB'), 'Object B inserted by Role C')); insert into Objects (data) values (encryptbykey(Key_GUID('ObjectC'), 'Object C inserted by Role C')); go :connect $(server) use [$(dbname)]; go -- Role A can see Objects A and B: open symmetric key ObjectA decryption by certificate RoleA with password = '123!#Password' open symmetric key ObjectB decryption by certificate RoleA with password = '123!#Password' select id, data, cast(decryptbykey(data) as varchar(max)) as decrypted from Objects ; go :connect $(server) use [$(dbname)]; go -- Role B can see Object C open symmetric key ObjectC decryption by certificate RoleB with password = '213!#Password' select id, data, cast(decryptbykey(data) as varchar(max)) as decrypted from Objects ; go :connect $(server) use [$(dbname)]; go -- Role C can see Objects A, B and C open symmetric key ObjectA decryption by certificate RoleC with password = '312!#Password' open symmetric key ObjectB decryption by certificate RoleC with password = '312!#Password' open symmetric key ObjectC decryption by certificate RoleC with password = '312!#Password' select id, data, cast(decryptbykey(data) as varchar(max)) as decrypted from Objects ; go </code></pre> <p>Is this a smart think to do though? No. Encryption is <strong>never</strong> the answer to address access rights problems. You obviously don't understand key management and provisioning, and you'll make a useless pseudo-secure mess. Sorry to rain on your parade, but is necessary.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. This table or related slice is empty.
    2. VO
      singulars
      1. This table or related slice is empty.
    3. VO
      singulars
      1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload