-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdb_lab_03.sql
153 lines (117 loc) · 4.12 KB
/
db_lab_03.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
use master
go
-- Create database
create database db_bank
on primary (name='db_bank', filename='C:\mssql\data\db_bank.mdf', size=50Mb, maxsize=150Mb, filegrowth=25Mb)
log on (name='db_bank_log', filename='C:\mssql\data\db_bank_log.ldf', size=30Mb, maxsize=100Mb, filegrowth=25Mb);
go
-- Retrieve stored symmetric keys
select * from sys.symmetric_keys
go
-- Create symmetric key (master database)
create master key encryption by password = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
go
-- Create a certificate
create certificate secure_credit_cards with subject = 'custom credit card number';
go
-- Create symmetric key (current database)
create symmetric key lscck_04
with algorithm = aes_256
encryption by certificate secure_credit_cards;
use db_bank
CREATE TABLE rol (
idrol int NOT NULL,
rol varchar(20) DEFAULT NULL
);
go
alter table rol add constraint pk_rol primary key(idrol);
go
CREATE TABLE usuario (
idusuario int NOT NULL,
nombre varchar(50) DEFAULT NULL,
correo varchar(100) DEFAULT NULL,
usuario varchar(15) DEFAULT NULL,
clave varchar(100) DEFAULT NULL,
rol int DEFAULT NULL,
estatus int DEFAULT 1,
);
go
alter table usuario add constraint pk_usuario primary key(idusuario);
go
alter table usuario add constraint fk_usuario_rol foreign key(rol) references rol(idrol);
go
-- Create person table
create table customer(
cedcustomer varchar(10) not null,
nombre varchar(30) not null,
correo varchar(30) not null
);
go
alter table customer add constraint pk_customer primary key(cedcustomer);
go
-- Create example table
create table card_list(
customer varchar(10) not null,
creditCard varchar(16) not null,
encryptedCC varbinary(250) null
);
go
alter table card_list add constraint pk_card_list primary key(creditCard);
go
alter table card_list add constraint fk_card_list_customer foreign key(customer) references customer(cedcustomer);
go
-- EncryptByKey(par_1, par_2, par_3, par_4)
-- par_1: key_GUID to be used to encrypt
-- par_2: value to be stored
-- par_3: add authenticator, only if value = 1
-- par_4: authenticator value
-- Open the symmetric key with which to encrypt the data.
open symmetric key lscck_04 decryption by certificate secure_credit_cards;
go
-- Insert data
INSERT INTO rol (idrol, rol) VALUES
(1, 'administrador'),
(2, 'supervisor'),
(3, 'caja');
go
INSERT INTO usuario (idusuario, nombre, correo, usuario, clave, rol, estatus) VALUES
(1, 'Carlos', '[email protected]', 'administrador', EncryptByKey(Key_GUID('lscck_04'),'1234',1, HashBytes('MD5',convert(varbinary,500))), 1, 1),
(2, 'Martin', '[email protected]', 'supervisor', EncryptByKey(Key_GUID('lscck_04'),'abcd',1, HashBytes('MD5',convert(varbinary,500))), 2, 1),
(3, 'Karol', '[email protected]', 'caja', EncryptByKey(Key_GUID('lscck_04'),'wxyz',1, HashBytes('MD5',convert(varbinary,500))), 3, 1);
go
insert into usuario
values('605960578', 'Juanito', '[email protected]');
go
insert into usuario
values('608960578', 'Marian', '[email protected]');
go
insert into usuario
values('605900698', 'Lucas', '[email protected]');
go
insert into card_list
values('605960578', '6041710012564010', EncryptByKey(Key_GUID('lscck_04'),'605960578',0));
go
insert into card_list
values('608960578', '6042210012564010', EncryptByKey(Key_GUID('lscck_04'),'608960578',0));
go
insert into card_list
values('605960298','6041810012569010',EncryptByKey(Key_GUID('lscck_04'),'605960298',1, HashBytes('SHA1',convert(varbinary,500))));
go
insert into card_list
values('605900698','6041810012569020',EncryptByKey(Key_GUID('lscck_04'),'605900698',1, HashBytes('SHA1',convert(varbinary,500))));
go
-- Close the key
close symmetric key lscck_04;
-- Retrieve data
select * from usuario;
select * from usuario u
inner join card_list c
on c.cusID = u.cedusuario
select c.cusID, u.nombre, c.creditCard, c.encryptedCC,
DecryptByKey(c.encryptedCC) decryptedCC
from usuario u inner join card_list c
on c.cusID = u.cedusuario;
select c.customer, u.nombre, c.creditCard, c.encryptedCC,
CONVERT(varchar, DecryptByKey(c.encryptedCC,1,HashBytes('SHA1',convert(varbinary,500)))) decryptedCC
from usuario u inner join card_list c
on c.cusID = u.cedusuario