You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
425 lines
10 KiB
425 lines
10 KiB
IF COL_LENGTH('[dbo].[OrganizationUser]', 'AccessSecretsManager') IS NULL |
|
BEGIN |
|
ALTER TABLE |
|
[dbo].[OrganizationUser] |
|
ADD |
|
[AccessSecretsManager] BIT NOT NULL CONSTRAINT [DF_OrganizationUser_SecretsManager] DEFAULT (0) |
|
END |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[OrganizationUser_Update] |
|
@Id UNIQUEIDENTIFIER, |
|
@OrganizationId UNIQUEIDENTIFIER, |
|
@UserId UNIQUEIDENTIFIER, |
|
@Email NVARCHAR(256), |
|
@Key VARCHAR(MAX), |
|
@Status SMALLINT, |
|
@Type TINYINT, |
|
@AccessAll BIT, |
|
@ExternalId NVARCHAR(300), |
|
@CreationDate DATETIME2(7), |
|
@RevisionDate DATETIME2(7), |
|
@Permissions NVARCHAR(MAX), |
|
@ResetPasswordKey VARCHAR(MAX), |
|
@AccessSecretsManager BIT = 0 |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
UPDATE |
|
[dbo].[OrganizationUser] |
|
SET |
|
[OrganizationId] = @OrganizationId, |
|
[UserId] = @UserId, |
|
[Email] = @Email, |
|
[Key] = @Key, |
|
[Status] = @Status, |
|
[Type] = @Type, |
|
[AccessAll] = @AccessAll, |
|
[ExternalId] = @ExternalId, |
|
[CreationDate] = @CreationDate, |
|
[RevisionDate] = @RevisionDate, |
|
[Permissions] = @Permissions, |
|
[ResetPasswordKey] = @ResetPasswordKey, |
|
[AccessSecretsManager] = @AccessSecretsManager |
|
WHERE |
|
[Id] = @Id |
|
|
|
EXEC [dbo].[User_BumpAccountRevisionDate] @UserId |
|
END |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[OrganizationUser_UpdateWithCollections] |
|
@Id UNIQUEIDENTIFIER, |
|
@OrganizationId UNIQUEIDENTIFIER, |
|
@UserId UNIQUEIDENTIFIER, |
|
@Email NVARCHAR(256), |
|
@Key VARCHAR(MAX), |
|
@Status SMALLINT, |
|
@Type TINYINT, |
|
@AccessAll BIT, |
|
@ExternalId NVARCHAR(300), |
|
@CreationDate DATETIME2(7), |
|
@RevisionDate DATETIME2(7), |
|
@Permissions NVARCHAR(MAX), |
|
@ResetPasswordKey VARCHAR(MAX), |
|
@Collections AS [dbo].[SelectionReadOnlyArray] READONLY, |
|
@AccessSecretsManager BIT = 0 |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
EXEC [dbo].[OrganizationUser_Update] @Id, @OrganizationId, @UserId, @Email, @Key, @Status, @Type, @AccessAll, @ExternalId, @CreationDate, @RevisionDate, @Permissions, @ResetPasswordKey, @AccessSecretsManager |
|
-- Update |
|
UPDATE |
|
[Target] |
|
SET |
|
[Target].[ReadOnly] = [Source].[ReadOnly], |
|
[Target].[HidePasswords] = [Source].[HidePasswords] |
|
FROM |
|
[dbo].[CollectionUser] AS [Target] |
|
INNER JOIN |
|
@Collections AS [Source] ON [Source].[Id] = [Target].[CollectionId] |
|
WHERE |
|
[Target].[OrganizationUserId] = @Id |
|
AND ( |
|
[Target].[ReadOnly] != [Source].[ReadOnly] |
|
OR [Target].[HidePasswords] != [Source].[HidePasswords] |
|
) |
|
|
|
-- Insert |
|
INSERT INTO |
|
[dbo].[CollectionUser] |
|
SELECT |
|
[Source].[Id], |
|
@Id, |
|
[Source].[ReadOnly], |
|
[Source].[HidePasswords] |
|
FROM |
|
@Collections AS [Source] |
|
INNER JOIN |
|
[dbo].[Collection] C ON C.[Id] = [Source].[Id] AND C.[OrganizationId] = @OrganizationId |
|
WHERE |
|
NOT EXISTS ( |
|
SELECT |
|
1 |
|
FROM |
|
[dbo].[CollectionUser] |
|
WHERE |
|
[CollectionId] = [Source].[Id] |
|
AND [OrganizationUserId] = @Id |
|
) |
|
|
|
-- Delete |
|
DELETE |
|
CU |
|
FROM |
|
[dbo].[CollectionUser] CU |
|
WHERE |
|
CU.[OrganizationUserId] = @Id |
|
AND NOT EXISTS ( |
|
SELECT |
|
1 |
|
FROM |
|
@Collections |
|
WHERE |
|
[Id] = CU.[CollectionId] |
|
) |
|
END |
|
GO |
|
|
|
IF OBJECT_ID('[dbo].[OrganizationUserView]') IS NOT NULL |
|
BEGIN |
|
EXECUTE sp_refreshsqlmodule N'[dbo].[OrganizationUserView]'; |
|
END |
|
GO |
|
|
|
CREATE OR ALTER VIEW [dbo].[OrganizationUserUserDetailsView] |
|
AS |
|
SELECT |
|
OU.[Id], |
|
OU.[UserId], |
|
OU.[OrganizationId], |
|
U.[Name], |
|
ISNULL(U.[Email], OU.[Email]) Email, |
|
U.[TwoFactorProviders], |
|
U.[Premium], |
|
OU.[Status], |
|
OU.[Type], |
|
OU.[AccessAll], |
|
OU.[AccessSecretsManager], |
|
OU.[ExternalId], |
|
SU.[ExternalId] SsoExternalId, |
|
OU.[Permissions], |
|
OU.[ResetPasswordKey], |
|
U.[UsesKeyConnector] |
|
FROM |
|
[dbo].[OrganizationUser] OU |
|
LEFT JOIN |
|
[dbo].[User] U ON U.[Id] = OU.[UserId] |
|
LEFT JOIN |
|
[dbo].[SsoUser] SU ON SU.[UserId] = OU.[UserId] AND SU.[OrganizationId] = OU.[OrganizationId] |
|
GO |
|
|
|
CREATE OR ALTER VIEW [dbo].[OrganizationUserOrganizationDetailsView] |
|
AS |
|
SELECT |
|
OU.[UserId], |
|
OU.[OrganizationId], |
|
O.[Name], |
|
O.[Enabled], |
|
O.[PlanType], |
|
O.[UsePolicies], |
|
O.[UseSso], |
|
O.[UseKeyConnector], |
|
O.[UseScim], |
|
O.[UseGroups], |
|
O.[UseDirectory], |
|
O.[UseEvents], |
|
O.[UseTotp], |
|
O.[Use2fa], |
|
O.[UseApi], |
|
O.[UseResetPassword], |
|
O.[SelfHost], |
|
O.[UsersGetPremium], |
|
O.[UseCustomPermissions], |
|
O.[UseSecretsManager], |
|
O.[Seats], |
|
O.[MaxCollections], |
|
O.[MaxStorageGb], |
|
O.[Identifier], |
|
OU.[Key], |
|
OU.[ResetPasswordKey], |
|
O.[PublicKey], |
|
O.[PrivateKey], |
|
OU.[Status], |
|
OU.[Type], |
|
SU.[ExternalId] SsoExternalId, |
|
OU.[Permissions], |
|
PO.[ProviderId], |
|
P.[Name] ProviderName, |
|
SS.[Data] SsoConfig, |
|
OS.[FriendlyName] FamilySponsorshipFriendlyName, |
|
OS.[LastSyncDate] FamilySponsorshipLastSyncDate, |
|
OS.[ToDelete] FamilySponsorshipToDelete, |
|
OS.[ValidUntil] FamilySponsorshipValidUntil, |
|
OU.[AccessSecretsManager] |
|
FROM |
|
[dbo].[OrganizationUser] OU |
|
LEFT JOIN |
|
[dbo].[Organization] O ON O.[Id] = OU.[OrganizationId] |
|
LEFT JOIN |
|
[dbo].[SsoUser] SU ON SU.[UserId] = OU.[UserId] AND SU.[OrganizationId] = OU.[OrganizationId] |
|
LEFT JOIN |
|
[dbo].[ProviderOrganization] PO ON PO.[OrganizationId] = O.[Id] |
|
LEFT JOIN |
|
[dbo].[Provider] P ON P.[Id] = PO.[ProviderId] |
|
LEFT JOIN |
|
[dbo].[SsoConfig] SS ON SS.[OrganizationId] = OU.[OrganizationId] |
|
LEFT JOIN |
|
[dbo].[OrganizationSponsorship] OS ON OS.[SponsoringOrganizationUserID] = OU.[Id] |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[OrganizationUser_Create] |
|
@Id UNIQUEIDENTIFIER OUTPUT, |
|
@OrganizationId UNIQUEIDENTIFIER, |
|
@UserId UNIQUEIDENTIFIER, |
|
@Email NVARCHAR(256), |
|
@Key VARCHAR(MAX), |
|
@Status SMALLINT, |
|
@Type TINYINT, |
|
@AccessAll BIT, |
|
@ExternalId NVARCHAR(300), |
|
@CreationDate DATETIME2(7), |
|
@RevisionDate DATETIME2(7), |
|
@Permissions NVARCHAR(MAX), |
|
@ResetPasswordKey VARCHAR(MAX), |
|
@AccessSecretsManager BIT = 0 |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
INSERT INTO [dbo].[OrganizationUser] |
|
( |
|
[Id], |
|
[OrganizationId], |
|
[UserId], |
|
[Email], |
|
[Key], |
|
[Status], |
|
[Type], |
|
[AccessAll], |
|
[ExternalId], |
|
[CreationDate], |
|
[RevisionDate], |
|
[Permissions], |
|
[ResetPasswordKey], |
|
[AccessSecretsManager] |
|
) |
|
VALUES |
|
( |
|
@Id, |
|
@OrganizationId, |
|
@UserId, |
|
@Email, |
|
@Key, |
|
@Status, |
|
@Type, |
|
@AccessAll, |
|
@ExternalId, |
|
@CreationDate, |
|
@RevisionDate, |
|
@Permissions, |
|
@ResetPasswordKey, |
|
@AccessSecretsManager |
|
) |
|
END |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[OrganizationUser_CreateWithCollections] |
|
@Id UNIQUEIDENTIFIER, |
|
@OrganizationId UNIQUEIDENTIFIER, |
|
@UserId UNIQUEIDENTIFIER, |
|
@Email NVARCHAR(256), |
|
@Key VARCHAR(MAX), |
|
@Status SMALLINT, |
|
@Type TINYINT, |
|
@AccessAll BIT, |
|
@ExternalId NVARCHAR(300), |
|
@CreationDate DATETIME2(7), |
|
@RevisionDate DATETIME2(7), |
|
@Permissions NVARCHAR(MAX), |
|
@ResetPasswordKey VARCHAR(MAX), |
|
@Collections AS [dbo].[SelectionReadOnlyArray] READONLY, |
|
@AccessSecretsManager BIT = 0 |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
EXEC [dbo].[OrganizationUser_Create] @Id, @OrganizationId, @UserId, @Email, @Key, @Status, @Type, @AccessAll, @ExternalId, @CreationDate, @RevisionDate, @Permissions, @ResetPasswordKey, @AccessSecretsManager |
|
|
|
;WITH [AvailableCollectionsCTE] AS( |
|
SELECT |
|
[Id] |
|
FROM |
|
[dbo].[Collection] |
|
WHERE |
|
[OrganizationId] = @OrganizationId |
|
) |
|
INSERT INTO [dbo].[CollectionUser] |
|
( |
|
[CollectionId], |
|
[OrganizationUserId], |
|
[ReadOnly], |
|
[HidePasswords] |
|
) |
|
SELECT |
|
[Id], |
|
@Id, |
|
[ReadOnly], |
|
[HidePasswords] |
|
FROM |
|
@Collections |
|
WHERE |
|
[Id] IN (SELECT [Id] FROM [AvailableCollectionsCTE]) |
|
END |
|
GO |
|
|
|
IF TYPE_ID(N'[dbo].[OrganizationUserType2]') IS NULL |
|
BEGIN |
|
CREATE TYPE [dbo].[OrganizationUserType2] AS TABLE( |
|
[Id] UNIQUEIDENTIFIER, |
|
[OrganizationId] UNIQUEIDENTIFIER, |
|
[UserId] UNIQUEIDENTIFIER, |
|
[Email] NVARCHAR(256), |
|
[Key] VARCHAR(MAX), |
|
[Status] SMALLINT, |
|
[Type] TINYINT, |
|
[AccessAll] BIT, |
|
[ExternalId] NVARCHAR(300), |
|
[CreationDate] DATETIME2(7), |
|
[RevisionDate] DATETIME2(7), |
|
[Permissions] NVARCHAR(MAX), |
|
[ResetPasswordKey] VARCHAR(MAX), |
|
[AccessSecretsManager] BIT |
|
) |
|
END |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[OrganizationUser_CreateMany2] |
|
@OrganizationUsersInput [dbo].[OrganizationUserType2] READONLY |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
INSERT INTO [dbo].[OrganizationUser] |
|
( |
|
[Id], |
|
[OrganizationId], |
|
[UserId], |
|
[Email], |
|
[Key], |
|
[Status], |
|
[Type], |
|
[AccessAll], |
|
[ExternalId], |
|
[CreationDate], |
|
[RevisionDate], |
|
[Permissions], |
|
[ResetPasswordKey], |
|
[AccessSecretsManager] |
|
) |
|
SELECT |
|
OU.[Id], |
|
OU.[OrganizationId], |
|
OU.[UserId], |
|
OU.[Email], |
|
OU.[Key], |
|
OU.[Status], |
|
OU.[Type], |
|
OU.[AccessAll], |
|
OU.[ExternalId], |
|
OU.[CreationDate], |
|
OU.[RevisionDate], |
|
OU.[Permissions], |
|
OU.[ResetPasswordKey], |
|
OU.[AccessSecretsManager] |
|
FROM |
|
@OrganizationUsersInput OU |
|
END |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[OrganizationUser_UpdateMany2] |
|
@OrganizationUsersInput [dbo].[OrganizationUserType2] READONLY |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
UPDATE |
|
OU |
|
SET |
|
[OrganizationId] = OUI.[OrganizationId], |
|
[UserId] = OUI.[UserId], |
|
[Email] = OUI.[Email], |
|
[Key] = OUI.[Key], |
|
[Status] = OUI.[Status], |
|
[Type] = OUI.[Type], |
|
[AccessAll] = OUI.[AccessAll], |
|
[ExternalId] = OUI.[ExternalId], |
|
[CreationDate] = OUI.[CreationDate], |
|
[RevisionDate] = OUI.[RevisionDate], |
|
[Permissions] = OUI.[Permissions], |
|
[ResetPasswordKey] = OUI.[ResetPasswordKey], |
|
[AccessSecretsManager] = OUI.[AccessSecretsManager] |
|
FROM |
|
[dbo].[OrganizationUser] OU |
|
INNER JOIN |
|
@OrganizationUsersInput OUI ON OU.Id = OUI.Id |
|
|
|
EXEC [dbo].[User_BumpManyAccountRevisionDates] |
|
( |
|
SELECT UserId |
|
FROM @OrganizationUsersInput |
|
) |
|
END |
|
GO
|
|
|