44 lines
1.3 KiB
MySQL
44 lines
1.3 KiB
MySQL
|
-- V009__activate_permisos_overrides.sql
|
||
|
|
-- Activates Usuario.PermisosJson as explicit overrides {grant, deny} on top of role permissions.
|
||
|
|
-- Idempotent: safe to run multiple times.
|
||
|
|
|
||
|
|
SET QUOTED_IDENTIFIER ON;
|
||
|
|
SET ANSI_NULLS ON;
|
||
|
|
GO
|
||
|
|
|
||
|
|
-- 1. Drop old default constraint if it exists (handles any previous shape)
|
||
|
|
IF EXISTS (
|
||
|
|
SELECT 1 FROM sys.default_constraints
|
||
|
|
WHERE name = 'DF_Usuario_Permisos'
|
||
|
|
AND parent_object_id = OBJECT_ID('dbo.Usuario')
|
||
|
|
)
|
||
|
|
BEGIN
|
||
|
|
ALTER TABLE dbo.Usuario DROP CONSTRAINT DF_Usuario_Permisos;
|
||
|
|
PRINT 'Dropped DF_Usuario_Permisos.';
|
||
|
|
END
|
||
|
|
GO
|
||
|
|
|
||
|
|
-- 2. Re-add default constraint with canonical shape
|
||
|
|
IF NOT EXISTS (
|
||
|
|
SELECT 1 FROM sys.default_constraints
|
||
|
|
WHERE name = 'DF_Usuario_Permisos'
|
||
|
|
AND parent_object_id = OBJECT_ID('dbo.Usuario')
|
||
|
|
)
|
||
|
|
BEGIN
|
||
|
|
ALTER TABLE dbo.Usuario
|
||
|
|
ADD CONSTRAINT DF_Usuario_Permisos
|
||
|
|
DEFAULT('{"grant":[],"deny":[]}') FOR PermisosJson;
|
||
|
|
PRINT 'Added DF_Usuario_Permisos with new shape {"grant":[],"deny":[]}.';
|
||
|
|
END
|
||
|
|
GO
|
||
|
|
|
||
|
|
-- 3. Migrate legacy values to new canonical shape
|
||
|
|
UPDATE dbo.Usuario
|
||
|
|
SET PermisosJson = '{"grant":[],"deny":[]}'
|
||
|
|
WHERE PermisosJson IN ('[]', '["*"]', '')
|
||
|
|
OR PermisosJson IS NULL
|
||
|
|
OR LTRIM(RTRIM(PermisosJson)) = '';
|
||
|
|
|
||
|
|
PRINT 'Migrated legacy PermisosJson rows to canonical shape.';
|
||
|
|
GO
|