How to create this encryption system that allows multiple users / objects

Question

I am trying to figure out how to create the best solution for my project. I made this simple picture in Photoshop to try to illustrate the problem and how I want it (if possible).

Illustrative image

I will also try to explain this based on the figure. Firstly, we have a couple of objects on the left, these objects are all encrypted using our own encryption key (EKey in the picture), and then they are stored in the database. On the other hand, we have different users placed in roles (one user can have many roles), and roles are associated with different objects. Thus, one person has access to the objects that the role provides. So, for example, Role A can have access to objects A and B. Role B has access only to object C, and Role C has access to all objects. Nothing strange about that, right? Different roles have different objects that they can access.

Now to the problem.

Each user must log in with his username / password, and then he gets access to the objects that provide his roles. All objects are encrypted, so she needs to somehow get the decryption key. I do not want to store the encryption key as a text string on the server. If possible, it should be decrypted using the user's password (along with the role) or the like. Thus, you must be a user on the server to decrypt the object and work with it.

I was thinking about creating a public / private key encryption system, but I am a little fixated on how to provide decryption keys for objects to various users. Since I need to be able to move users to and from roles, add new users, add new roles and create / delete objects.

, , .

, , .

:

-Encrypted objects.

- .

- .

- .

Answer 1

SQL Server. (A, B, C, D) ( , ). , A, B C. () . , ( ). , , / . , , .

- :

: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

? . . , , . , .

Answer 2

, , ?

, , . , . , , , ( ). , , , . . , , .

. total, ( ). , , , , ( , "" - OS, DB ?). , SSH SSL. , .