Хранение учетных записей пользователей системы KooBoo в базе данных SQL Server

По умолчанию учетные записи пользователей системы KooBoo хранятся в XML файлах. Для большинства веб-сайтов этого достаточно, если доступ к сайту имеет лишь небольшая группа пользователей. Но если необходимо работать с большим количеством пользователей, то KooBoo также может с этим справиться. Провайдера учетных записей можно изменить. В данном случае рекомендуется хранить учетные записи в базе данных SQL Server.

В KooBoo реализована поддержка SQL Server провайдера для управления учетными записями. Также система позволяет внедрить другие типы провайдеров.

Установка SQL Server провайдера учетных записей:

1) Скачайте файл "User_Providers.zip". Скопируйте из него файлы "EntityFramework.dll", "Kooboo.Connect.Providers.SqlServer.dll" и "Kooboo.CMS.Account.Persistence.SqlSever.dll" в директорию "BIN", расположенную в корневой папке вашей системы KooBoo.

2) Создайте новую базу данных либо используйте существующую. Рекомендуемое название для базы данных
"Kooboo_CMS". Запустите T-SQL скрипт, который создаст необходимую структуру таблиц. Текст скипта можно скопировать из файла "Install_Script.sql", расположенного в том же архиве.

3) Откройте файл Web.config, расположенный в корневой папке вашей системы KooBoo, и добавьте туда две строки подключения к созданной Вами базе данных (заполните их в соответствии с Вашими настройками):

<add name="Kooboo.Connect.Providers.SqlServer.Properties.Settings.KoobooConnect"
connectionString="Server=.\SQLExpress;Database=Kooboo_CMS; 
Trusted_Connection=Yes;"/>
<add name="Kooboo_CMS" connectionString="Server=.\SQLExpress;
Database=Kooboo_CMS; Trusted_Connection=Yes;" 
providerName="System.Data.SqlClient"/>

Приведенный ниже текст скрипта рекомендуется использовать только для ознакомления со структурой, а при установке лучше использовать скрипт из архива, т.к. при обновлении версий системы он может быть изменен.

SQL-Скрипт:

/****** Object: Table [dbo].[Connect_User] Script Date: 05/26/2011 15:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Connect_User]')
 AND TYPE IN (N'U'))
BEGIN
CREATE TABLE [dbo].[Connect_User](
[Name] [nvarchar](50) NOT NULL,
[Email] [nvarchar](100) NOT NULL,
[FirstName] [nvarchar](20) NULL,
[MiddleName] [nvarchar](20) NULL,
[LastName] [nvarchar](20) NULL,
[Gender] [SMALLINT] NOT NULL,
[Birthday] [datetime] NULL,
[Country] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Address] [nvarchar](150) NULL,
[Postcode] [nvarchar](30) NULL,
[Telphone] [VARCHAR](50) NULL,
[Mobile] [VARCHAR](50) NULL,
[CreateDate] [datetime] NOT NULL,
[FailedPasswordAnswerAttemptCount] [INT] NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] [datetime] NULL,
[FailedPasswordAttemptCount] [INT] NOT NULL,
[FailedPasswordAttemptWindowStart] [datetime] NULL,
[IsApproved] [bit] NOT NULL,
[IsLockedOut] [bit] NOT NULL,
[LastLockoutDate] [datetime] NULL,
[LastLoginDate] [datetime] NULL,
[LastPasswordChangedDate] [datetime] NULL,
[Password] [nvarchar](128) NULL,
[PasswordAnswer] [nvarchar](128) NULL,
[PasswordQuestion] [nvarchar](256) NULL,
[PasswordSalt] [nvarchar](128) NULL,
[CustomerId] [CHAR](36) NULL,
[Comment] [nvarchar](2000) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Connect_User] ([Name], [Email], [FirstName], 
[MiddleName], [LastName], [Gender], [Birthday], [Country], 
[City], [Address], [Postcode], [Telphone], [Mobile], [CreateDate], [FailedPasswordAnswerAttemptCount], 
[FailedPasswordAnswerAttemptWindowStart], [FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], 
[IsApproved], [IsLockedOut], [LastLockoutDate], [LastLoginDate], 
[LastPasswordChangedDate], [Password], [PasswordAnswer], 
[PasswordQuestion], [PasswordSalt], [CustomerId], [Comment]) 
VALUES (N'admin', N'admin@cc.cc', NULL, NULL, NULL, 0, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, 
CAST(0x00009E3300FC20CD AS DateTime), 0, NULL, 0, NULL, 1, 0, 
NULL, NULL, NULL, N'FYOL44w+oWRo53/9NQCnqa8RS00=', 
NULL, NULL, N'aKEwnA2Riwo4SCzyYFfzFw==', NULL, NULL)
/****** Object: Table [dbo].[CMS_Account_Users] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[CMS_Account_Users]') 
AND TYPE IN (N'U'))
BEGIN
CREATE TABLE [dbo].[CMS_Account_Users](
[Email] [nvarchar](128) NOT NULL,
[UserName] [nvarchar](MAX) NULL,
[IsAdministrator] [bit] NOT NULL,
[UICulture] [nvarchar](50) NULL,
[CustomFieldsXml] [text] NULL,
PRIMARY KEY CLUSTERED 
(
[Email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
INSERT [dbo].[CMS_Account_Users] ([Email], [UserName], 
[IsAdministrator], [CustomFieldsXml]) 
VALUES (N'admin@cc.cc', N'admin', 1, N'')
/****** Object: Table [dbo].[CMS_Account_Roles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[CMS_Account_Roles]') 
AND TYPE IN (N'U'))
BEGIN
CREATE TABLE [dbo].[CMS_Account_Roles](
[Name] [nvarchar](128) NOT NULL,
PRIMARY KEY CLUSTERED 
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
INSERT [dbo].[CMS_Account_Roles] ([Name]) VALUES (N'Content manager')
INSERT [dbo].[CMS_Account_Roles] ([Name]) VALUES (N'Developer')
/****** Object: Table [dbo].[CMS_Account_Permissions] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[CMS_Account_Permissions]') 
AND TYPE IN (N'U'))
BEGIN
CREATE TABLE [dbo].[CMS_Account_Permissions](
[Id] [nvarchar](128) NOT NULL,
[RoleName] [nvarchar](128) NOT NULL,
[Name] [nvarchar](MAX) NULL,
[AreaName] [nvarchar](MAX) NULL,
[GROUP] [nvarchar](MAX) NULL,
[DisplayName] [nvarchar](MAX) NULL,
PRIMARY KEY CLUSTERED 
(
[Id] ASC,
[RoleName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Contents__Content', N'Content manager', N'Content', 
N'Contents', N'', N'Content')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Contents__Content', N'Developer', N'Content', 
N'Contents', N'', N'Content')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Contents__Folder', N'Developer', N'Folder', 
N'Contents', N'', N'Folder')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Contents__Schema', N'Developer', N'Schema', 
N'Contents', N'', N'Schema')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Contents__Setting', N'Developer', N'Setting', 
N'Contents', N'', N'Setting')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Extensions_Plugin', N'Developer', N'Plugin', 
N'Sites', N'Extensions', N'Plugin')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Pages_Inline Editing', N'Content manager', 
N'Inline Editing', N'Sites', N'Pages', N'Inline Editing')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Pages_Inline Editing', N'Developer', 
N'Inline Editing', N'Sites', N'Pages', N'Inline Editing')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Pages_Page', N'Developer', N'Page', 
N'Sites', N'Pages', N'Page')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Settings_Custom error', N'Developer', 
N'Custom error', N'Sites', N'Settings', N'Custom error')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Settings_Page url', N'Developer', N'Page url', 
N'Sites', N'Settings', N'Page url')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Settings_Robots.txt', N'Developer', 
N'Robots.txt', N'Sites', N'Settings', N'Robots.txt')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Settings_System', N'Developer', N'System', 
N'Sites', N'Settings', N'System')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Settings_Url redirect', N'Developer', 
N'Url redirect', N'Sites', N'Settings', N'Url redirect')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Settings_User', N'Developer', N'User', 
N'Sites', N'Settings', N'User')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Templates_File', N'Developer', N'File', 
N'Sites', N'Templates', N'File')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Templates_Label', N'Developer', N'Label', 
N'Sites', N'Templates', N'Label')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Templates_Layout', N'Developer', N'Layout', 
N'Sites', N'Templates', N'Layout')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Templates_Script', N'Developer', N'Script', 
N'Sites', N'Templates', N'Script')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Templates_Theme', N'Developer', N'Theme', 
N'Sites', N'Templates', N'Theme')
INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], 
[Name], [AreaName], [GROUP], [DisplayName]) 
VALUES (N'Sites_Templates_View', N'Developer', N'View', 
N'Sites', N'Templates', N'View')
/****** Object: ForeignKey [Role_Permissions] ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
WHERE object_id = OBJECT_ID(N'[dbo].[Role_Permissions]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[CMS_Account_Permissions]'))
ALTER TABLE [dbo].[CMS_Account_Permissions] 
WITH CHECK ADD CONSTRAINT [Role_Permissions] 
FOREIGN KEY([RoleName])
REFERENCES [dbo].[CMS_Account_Roles] ([Name])
ON DELETE CASCADE
GO
IF EXISTS (SELECT * FROM sys.foreign_keys 
WHERE object_id = OBJECT_ID(N'[dbo].[Role_Permissions]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[CMS_Account_Permissions]'))
ALTER TABLE [dbo].[CMS_Account_Permissions] 
CHECK CONSTRAINT [Role_Permissions]
GO

Источник статьи на англ.языке: Setup User Provider

Хранение учетных записей пользователей системы KooBoo в базе данных SQL Server

Похожие статьи