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.
309 lines
8.0 KiB
309 lines
8.0 KiB
CREATE OR ALTER FUNCTION [dbo].[UserCipherDetails](@UserId UNIQUEIDENTIFIER) |
|
RETURNS TABLE |
|
AS RETURN |
|
WITH [CTE] AS ( |
|
SELECT |
|
[Id], |
|
[OrganizationId] |
|
FROM |
|
[OrganizationUser] |
|
WHERE |
|
[UserId] = @UserId |
|
AND [Status] = 2 -- Confirmed |
|
) |
|
SELECT |
|
C.*, |
|
CASE |
|
WHEN COALESCE(CU.[ReadOnly], CG.[ReadOnly], 0) = 0 |
|
THEN 1 |
|
ELSE 0 |
|
END [Edit], |
|
CASE |
|
WHEN COALESCE(CU.[HidePasswords], CG.[HidePasswords], 0) = 0 |
|
THEN 1 |
|
ELSE 0 |
|
END [ViewPassword], |
|
CASE |
|
WHEN COALESCE(CU.[Manage], CG.[Manage], 0) = 1 |
|
THEN 1 |
|
ELSE 0 |
|
END [Manage], |
|
CASE |
|
WHEN O.[UseTotp] = 1 |
|
THEN 1 |
|
ELSE 0 |
|
END [OrganizationUseTotp] |
|
FROM |
|
[dbo].[CipherDetails](@UserId) C |
|
INNER JOIN |
|
[CTE] OU ON C.[UserId] IS NULL AND C.[OrganizationId] IN (SELECT [OrganizationId] FROM [CTE]) |
|
INNER JOIN |
|
[dbo].[Organization] O ON O.[Id] = OU.[OrganizationId] AND O.[Id] = C.[OrganizationId] AND O.[Enabled] = 1 |
|
LEFT JOIN |
|
[dbo].[CollectionCipher] CC ON CC.[CipherId] = C.[Id] |
|
LEFT JOIN |
|
[dbo].[CollectionUser] CU ON CU.[CollectionId] = CC.[CollectionId] AND CU.[OrganizationUserId] = OU.[Id] |
|
LEFT JOIN |
|
[dbo].[GroupUser] GU ON CU.[CollectionId] IS NULL AND GU.[OrganizationUserId] = OU.[Id] |
|
LEFT JOIN |
|
[dbo].[Group] G ON G.[Id] = GU.[GroupId] |
|
LEFT JOIN |
|
[dbo].[CollectionGroup] CG ON CG.[CollectionId] = CC.[CollectionId] AND CG.[GroupId] = GU.[GroupId] |
|
WHERE |
|
CU.[CollectionId] IS NOT NULL |
|
OR CG.[CollectionId] IS NOT NULL |
|
|
|
UNION ALL |
|
|
|
SELECT |
|
*, |
|
1 [Edit], |
|
1 [ViewPassword], |
|
1 [Manage], |
|
0 [OrganizationUseTotp] |
|
FROM |
|
[dbo].[CipherDetails](@UserId) |
|
WHERE |
|
[UserId] = @UserId |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[CipherDetails_ReadByIdUserId] |
|
@Id UNIQUEIDENTIFIER, |
|
@UserId UNIQUEIDENTIFIER |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
SELECT |
|
[Id], |
|
[UserId], |
|
[OrganizationId], |
|
[Type], |
|
[Data], |
|
[Attachments], |
|
[CreationDate], |
|
[RevisionDate], |
|
[Favorite], |
|
[FolderId], |
|
[DeletedDate], |
|
[Reprompt], |
|
[Key], |
|
[OrganizationUseTotp], |
|
MAX ([Edit]) AS [Edit], |
|
MAX ([ViewPassword]) AS [ViewPassword], |
|
MAX ([Manage]) AS [Manage] |
|
FROM |
|
[dbo].[UserCipherDetails](@UserId) |
|
WHERE |
|
[Id] = @Id |
|
GROUP BY |
|
[Id], |
|
[UserId], |
|
[OrganizationId], |
|
[Type], |
|
[Data], |
|
[Attachments], |
|
[CreationDate], |
|
[RevisionDate], |
|
[Favorite], |
|
[FolderId], |
|
[DeletedDate], |
|
[Reprompt], |
|
[Key], |
|
[OrganizationUseTotp] |
|
END |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[CipherDetails_ReadWithoutOrganizationsByUserId] |
|
@UserId UNIQUEIDENTIFIER |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
SELECT |
|
*, |
|
1 [Edit], |
|
1 [ViewPassword], |
|
1 [Manage], |
|
0 [OrganizationUseTotp] |
|
FROM |
|
[dbo].[CipherDetails](@UserId) |
|
WHERE |
|
[UserId] = @UserId |
|
END |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[CipherDetails_Create] |
|
@Id UNIQUEIDENTIFIER, |
|
@UserId UNIQUEIDENTIFIER, |
|
@OrganizationId UNIQUEIDENTIFIER, |
|
@Type TINYINT, |
|
@Data NVARCHAR(MAX), |
|
@Favorites NVARCHAR(MAX), -- not used |
|
@Folders NVARCHAR(MAX), -- not used |
|
@Attachments NVARCHAR(MAX), -- not used |
|
@CreationDate DATETIME2(7), |
|
@RevisionDate DATETIME2(7), |
|
@FolderId UNIQUEIDENTIFIER, |
|
@Favorite BIT, |
|
@Edit BIT, -- not used |
|
@ViewPassword BIT, -- not used |
|
@Manage BIT, -- not used |
|
@OrganizationUseTotp BIT, -- not used |
|
@DeletedDate DATETIME2(7), |
|
@Reprompt TINYINT, |
|
@Key VARCHAR(MAX) = NULL |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
DECLARE @UserIdKey VARCHAR(50) = CONCAT('"', @UserId, '"') |
|
DECLARE @UserIdPath VARCHAR(50) = CONCAT('$.', @UserIdKey) |
|
|
|
INSERT INTO [dbo].[Cipher] |
|
( |
|
[Id], |
|
[UserId], |
|
[OrganizationId], |
|
[Type], |
|
[Data], |
|
[Favorites], |
|
[Folders], |
|
[CreationDate], |
|
[RevisionDate], |
|
[DeletedDate], |
|
[Reprompt], |
|
[Key] |
|
) |
|
VALUES |
|
( |
|
@Id, |
|
CASE WHEN @OrganizationId IS NULL THEN @UserId ELSE NULL END, |
|
@OrganizationId, |
|
@Type, |
|
@Data, |
|
CASE WHEN @Favorite = 1 THEN CONCAT('{', @UserIdKey, ':true}') ELSE NULL END, |
|
CASE WHEN @FolderId IS NOT NULL THEN CONCAT('{', @UserIdKey, ':"', @FolderId, '"', '}') ELSE NULL END, |
|
@CreationDate, |
|
@RevisionDate, |
|
@DeletedDate, |
|
@Reprompt, |
|
@Key |
|
) |
|
|
|
IF @OrganizationId IS NOT NULL |
|
BEGIN |
|
EXEC [dbo].[User_BumpAccountRevisionDateByCipherId] @Id, @OrganizationId |
|
END |
|
ELSE IF @UserId IS NOT NULL |
|
BEGIN |
|
EXEC [dbo].[User_BumpAccountRevisionDate] @UserId |
|
END |
|
END |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[CipherDetails_CreateWithCollections] |
|
@Id UNIQUEIDENTIFIER, |
|
@UserId UNIQUEIDENTIFIER, |
|
@OrganizationId UNIQUEIDENTIFIER, |
|
@Type TINYINT, |
|
@Data NVARCHAR(MAX), |
|
@Favorites NVARCHAR(MAX), -- not used |
|
@Folders NVARCHAR(MAX), -- not used |
|
@Attachments NVARCHAR(MAX), -- not used |
|
@CreationDate DATETIME2(7), |
|
@RevisionDate DATETIME2(7), |
|
@FolderId UNIQUEIDENTIFIER, |
|
@Favorite BIT, |
|
@Edit BIT, -- not used |
|
@ViewPassword BIT, -- not used |
|
@Manage BIT, -- not used |
|
@OrganizationUseTotp BIT, -- not used |
|
@DeletedDate DATETIME2(7), |
|
@Reprompt TINYINT, |
|
@Key VARCHAR(MAX) = NULL, |
|
@CollectionIds AS [dbo].[GuidIdArray] READONLY |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
EXEC [dbo].[CipherDetails_Create] @Id, @UserId, @OrganizationId, @Type, @Data, @Favorites, @Folders, |
|
@Attachments, @CreationDate, @RevisionDate, @FolderId, @Favorite, @Edit, @ViewPassword, @Manage, |
|
@OrganizationUseTotp, @DeletedDate, @Reprompt, @Key |
|
|
|
DECLARE @UpdateCollectionsSuccess INT |
|
EXEC @UpdateCollectionsSuccess = [dbo].[Cipher_UpdateCollections] @Id, @UserId, @OrganizationId, @CollectionIds |
|
END |
|
GO |
|
|
|
CREATE OR ALTER PROCEDURE [dbo].[CipherDetails_Update] |
|
@Id UNIQUEIDENTIFIER, |
|
@UserId UNIQUEIDENTIFIER, |
|
@OrganizationId UNIQUEIDENTIFIER, |
|
@Type TINYINT, |
|
@Data NVARCHAR(MAX), |
|
@Favorites NVARCHAR(MAX), -- not used |
|
@Folders NVARCHAR(MAX), -- not used |
|
@Attachments NVARCHAR(MAX), |
|
@CreationDate DATETIME2(7), |
|
@RevisionDate DATETIME2(7), |
|
@FolderId UNIQUEIDENTIFIER, |
|
@Favorite BIT, |
|
@Edit BIT, -- not used |
|
@ViewPassword BIT, -- not used |
|
@Manage BIT, -- not used |
|
@OrganizationUseTotp BIT, -- not used |
|
@DeletedDate DATETIME2(2), |
|
@Reprompt TINYINT, |
|
@Key VARCHAR(MAX) = NULL |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
DECLARE @UserIdKey VARCHAR(50) = CONCAT('"', @UserId, '"') |
|
DECLARE @UserIdPath VARCHAR(50) = CONCAT('$.', @UserIdKey) |
|
|
|
UPDATE |
|
[dbo].[Cipher] |
|
SET |
|
[UserId] = CASE WHEN @OrganizationId IS NULL THEN @UserId ELSE NULL END, |
|
[OrganizationId] = @OrganizationId, |
|
[Type] = @Type, |
|
[Data] = @Data, |
|
[Folders] = |
|
CASE |
|
WHEN @FolderId IS NOT NULL AND [Folders] IS NULL THEN |
|
CONCAT('{', @UserIdKey, ':"', @FolderId, '"', '}') |
|
WHEN @FolderId IS NOT NULL THEN |
|
JSON_MODIFY([Folders], @UserIdPath, CAST(@FolderId AS VARCHAR(50))) |
|
ELSE |
|
JSON_MODIFY([Folders], @UserIdPath, NULL) |
|
END, |
|
[Favorites] = |
|
CASE |
|
WHEN @Favorite = 1 AND [Favorites] IS NULL THEN |
|
CONCAT('{', @UserIdKey, ':true}') |
|
WHEN @Favorite = 1 THEN |
|
JSON_MODIFY([Favorites], @UserIdPath, CAST(1 AS BIT)) |
|
ELSE |
|
JSON_MODIFY([Favorites], @UserIdPath, NULL) |
|
END, |
|
[Attachments] = @Attachments, |
|
[Reprompt] = @Reprompt, |
|
[CreationDate] = @CreationDate, |
|
[RevisionDate] = @RevisionDate, |
|
[DeletedDate] = @DeletedDate, |
|
[Key] = @Key |
|
WHERE |
|
[Id] = @Id |
|
|
|
IF @OrganizationId IS NOT NULL |
|
BEGIN |
|
EXEC [dbo].[User_BumpAccountRevisionDateByCipherId] @Id, @OrganizationId |
|
END |
|
ELSE IF @UserId IS NOT NULL |
|
BEGIN |
|
EXEC [dbo].[User_BumpAccountRevisionDate] @UserId |
|
END |
|
END |
|
GO
|
|
|