26 lines
1.1 KiB
SQL
26 lines
1.1 KiB
SQL
|
-- The MySQL baseline predates the access-role table.
|
||
|
|
CREATE TABLE IF NOT EXISTS workbench_access_roles (
|
||
|
|
id VARCHAR(36) PRIMARY KEY,
|
||
|
|
tenant_id BIGINT NOT NULL,
|
||
|
|
name VARCHAR(100) NOT NULL,
|
||
|
|
description TEXT NOT NULL,
|
||
|
|
permissions JSON NOT NULL,
|
||
|
|
version INTEGER NOT NULL DEFAULT 1,
|
||
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE (tenant_id, name)
|
||
|
|
);
|
||
|
|
-- Preserve existing granular roles when applying after an emergency patch.
|
||
|
|
SET @access_role_permission_version_sql = (
|
||
|
|
SELECT IF(COUNT(*) = 0,
|
||
|
|
'ALTER TABLE workbench_access_roles ADD COLUMN permission_version INTEGER NOT NULL DEFAULT 0',
|
||
|
|
'SELECT 1')
|
||
|
|
FROM information_schema.COLUMNS
|
||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
||
|
|
AND TABLE_NAME = 'workbench_access_roles'
|
||
|
|
AND COLUMN_NAME = 'permission_version'
|
||
|
|
);
|
||
|
|
PREPARE access_role_permission_version_stmt FROM @access_role_permission_version_sql;
|
||
|
|
EXECUTE access_role_permission_version_stmt;
|
||
|
|
DEALLOCATE PREPARE access_role_permission_version_stmt;
|