64 lines
2.1 KiB
MySQL
64 lines
2.1 KiB
MySQL
|
-- V002__create_refresh_token.sql
|
||
|
|
-- Creates dbo.RefreshToken table for opaque token rotation with chain revocation
|
||
|
|
-- Run on: SIGCM2 (prod) and SIGCM2_Test (integration tests)
|
||
|
|
|
||
|
|
SET QUOTED_IDENTIFIER ON;
|
||
|
|
SET ANSI_NULLS ON;
|
||
|
|
GO
|
||
|
|
|
||
|
|
IF OBJECT_ID(N'dbo.RefreshToken', N'U') IS NOT NULL
|
||
|
|
BEGIN
|
||
|
|
PRINT 'Table dbo.RefreshToken already exists — skipping.';
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
GO
|
||
|
|
|
||
|
|
CREATE TABLE dbo.RefreshToken
|
||
|
|
(
|
||
|
|
Id INT IDENTITY(1,1) NOT NULL,
|
||
|
|
UsuarioId INT NOT NULL,
|
||
|
|
TokenHash NVARCHAR(88) NOT NULL, -- SHA-256 base64url = 43 chars sin padding; margen a 88
|
||
|
|
FamilyId UNIQUEIDENTIFIER NOT NULL, -- una familia = una sesion de login
|
||
|
|
IssuedAt DATETIME2(3) NOT NULL,
|
||
|
|
ExpiresAt DATETIME2(3) NOT NULL, -- absolute: heredado en cada rotacion
|
||
|
|
RevokedAt DATETIME2(3) NULL,
|
||
|
|
ReplacedById INT NULL,
|
||
|
|
CreatedByIp VARCHAR(45) NOT NULL, -- IPv4/IPv6 textual
|
||
|
|
UserAgent NVARCHAR(512) NULL,
|
||
|
|
|
||
|
|
CONSTRAINT PK_RefreshToken PRIMARY KEY CLUSTERED (Id),
|
||
|
|
CONSTRAINT FK_RefreshToken_Usuario
|
||
|
|
FOREIGN KEY (UsuarioId) REFERENCES dbo.Usuario(Id),
|
||
|
|
CONSTRAINT FK_RefreshToken_ReplacedBy
|
||
|
|
FOREIGN KEY (ReplacedById) REFERENCES dbo.RefreshToken(Id),
|
||
|
|
CONSTRAINT UQ_RefreshToken_TokenHash UNIQUE (TokenHash)
|
||
|
|
);
|
||
|
|
GO
|
||
|
|
|
||
|
|
-- Lookup por familia para chain revocation
|
||
|
|
CREATE INDEX IX_RefreshToken_UsuarioId_FamilyId
|
||
|
|
ON dbo.RefreshToken (UsuarioId, FamilyId);
|
||
|
|
GO
|
||
|
|
|
||
|
|
-- Indice filtrado para revocaciones masivas de activos
|
||
|
|
CREATE INDEX IX_RefreshToken_Active
|
||
|
|
ON dbo.RefreshToken (UsuarioId, FamilyId)
|
||
|
|
WHERE RevokedAt IS NULL;
|
||
|
|
GO
|
||
|
|
|
||
|
|
-- Housekeeping futuro
|
||
|
|
CREATE INDEX IX_RefreshToken_ExpiresAt
|
||
|
|
ON dbo.RefreshToken (ExpiresAt)
|
||
|
|
WHERE RevokedAt IS NULL;
|
||
|
|
GO
|
||
|
|
|
||
|
|
EXEC sys.sp_addextendedproperty
|
||
|
|
@name = N'MS_Description',
|
||
|
|
@value = N'Refresh tokens opacos (SHA-256 hash) con rotacion y chain revocation por familia',
|
||
|
|
@level0type = N'SCHEMA', @level0name = N'dbo',
|
||
|
|
@level1type = N'TABLE', @level1name = N'RefreshToken';
|
||
|
|
GO
|
||
|
|
|
||
|
|
PRINT 'Table dbo.RefreshToken created successfully.';
|
||
|
|
GO
|