This repository has been archived by the owner on Sep 10, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
LlavesDesencriptar.sql
132 lines (95 loc) · 4.18 KB
/
LlavesDesencriptar.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
Create Table Passwords (
passcifrado varbinary(max) NOT NULL
)
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'passdemasterkey'
CREATE CERTIFICATE AccCert1
WITH SUBJECT = 'AccCert1'
GO
/* drop certificate AccCert1 */
CREATE SYMMETRIC KEY AccKey1
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE AccCert1
GO
CREATE ASYMMETRIC KEY ClaveAsym
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'juanjoguti'
OPEN SYMMETRIC KEY AccKey1 DECRYPTION BY CERTIFICATE AccCert1
INSERT INTO Dbo.Passwords(passcifrado) VALUES (encryptByKey(Key_GUID('AccKey1'),'juanjoguti'))
CLOSE ALL SYMMETRIC KEYS
REVERT
---------------------------------------------------------------------------
/* Aqui podemos vereficar la existencia de las llaves simetricas */
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
---------------------------------------------------------------------------
Create Table UsuariosImportantes(
idUsuario int IDENTITY(1,1) NOT NULL,
Nombre varbinary(MAX) NOT NULL,
Apellido1 varbinary(MAX) NOT NULL,
Apellido2 varbinary(MAX) NOT NULL,
FechaNacimiento datetime NOT NULL,
Pass varbinary(200) NOT NULL,
Email varbinary(MAX) NOT NULL,
FechaIngreso datetime NOT NULL,
Enabled bit NOT NULL,
idTipoEntidad int NOT NULL,
idIdioma int NOT NULL
);
/*
DECLARE @AsymID INT;
SET @AsymID = ASYMKEY_ID('ClaveAsym');
INSERT INTO Usuarios(Nombre, Apellido1, Apellido2, FechaDeNacimiento, Password, Email, FechaIngreso, Enabled, idTipoEntidad, idIdioma)
VALUES (CONVERT(nvarchar(50),ENCRYPTBYASYMKEY(@AsymID,'Fran')),CONVERT(nvarchar(50),ENCRYPTBYASYMKEY(@AsymID,'Murillo')),CONVERT(nvarchar(50),ENCRYPTBYASYMKEY(@AsymID,'Jimenez')),
'1993-10-25',HashBytes('SHA1','micontraseña'),CONVERT(nvarchar(50),ENCRYPTBYASYMKEY(@AsymID,'[email protected]')),
'2014-10-25',1,1,1)
Select * from Usuarios
DECLARE @AsymID INT;
SET @AsymID = ASYMKEY_ID('ClaveAsym');
SELECT CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,Convert(varbinary(max),Nombre),N'juanjoguti')), CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,Convert(varbinary(max),Apellido1),N'juanjoguti')),CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,Convert(varbinary(max),Apellido2),N'juanjoguti'))
FROM Usuarios
WHERE idUsuario = 46
go
*/
DECLARE @AsymID INT;
SET @AsymID = ASYMKEY_ID('ClaveAsym');
INSERT INTO UsuariosImportantes(Nombre, Apellido1, Apellido2, FechaNacimiento, Pass, Email, FechaIngreso, Enabled, idTipoEntidad, idIdioma)
VALUES (ENCRYPTBYASYMKEY(@AsymID,'Fran'),ENCRYPTBYASYMKEY(@AsymID,'Murillo'),ENCRYPTBYASYMKEY(@AsymID,'Jimenez'),
'1993-10-25',HashBytes('SHA1','micontraseña'),ENCRYPTBYASYMKEY(@AsymID,'[email protected]'),
'2014-10-25',1,1,1)
Select * from UsuariosImportantes
DECLARE @AsymID INT;
SET @AsymID = ASYMKEY_ID('ClaveAsym');
SELECT CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,Nombre,N'juanjoguti')), CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,Apellido1,N'juanjoguti')),CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,Apellido2,N'juanjoguti'))
FROM UsuariosImportantes
WHERE idUsuario = 1
go
CREATE PROCEDURE infoUsuarioImportante (@correo nvarchar(150),@passwordllave nvarchar(50))
AS
BEGIN
DECLARE @passwordautentica nvarchar(100);
OPEN SYMMETRIC KEY AccKey1 DECRYPTION BY CERTIFICATE AccCert1
Select @passwordautentica = (Select CONVERT(VARCHAR,decryptByKey(Passwords.passcifrado)) from Passwords)
CLOSE ALL SYMMETRIC KEYS
REVERT
SET @passwordautentica = CONVERT(nvarchar(100),@passwordautentica)
IF @passwordautentica = @passwordllave
BEGIN
DECLARE @AsymID INT;
SET @AsymID = ASYMKEY_ID('ClaveAsym');
SELECT UsuariosImportantes.idUsuario,
CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,UsuariosImportantes.Nombre,N'juanjoguti')) as Nombre ,
CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,UsuariosImportantes.Apellido1,N'juanjoguti')) as Apellido,
Apellido2,FechaNacimiento,CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,UsuariosImportantes.Email,N'juanjoguti')) as Email
FROM UsuariosImportantes Where @correo = CONVERT(VarChar(50),DECRYPTBYASYMKEY(@AsymID,UsuariosImportantes.Email,N'juanjoguti'))
END;
ELSE
BEGIN
PRINT 'La clave ingresada esta incorrecta'
END;
END;
GO
drop procedure infoUsuarioImportante
exec dbo.infoUsuarioImportante '[email protected]','juanjoguti';
go