SQL Integration - Scripts for Tables and Views

USE [master]
GO
 
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'directory-spi-sql')
DROP DATABASE [directory-spi-sql]
GO
 
USE [master]
GO
 
CREATE DATABASE [directory-spi-sql]
GO
 
ALTER DATABASE [directory-spi-sql] SET COMPATIBILITY_LEVEL = 90
GO

USE [directory-spi-sql];
 
IF OBJECT_ID('History') IS NOT NULL
 DROP TABLE History;
 
IF OBJECT_ID('Users') IS NOT NULL
 DROP TABLE Users;
 
IF OBJECT_ID('Terms') IS NOT NULL
 DROP TABLE Terms;
 
GO
 
CREATE TABLE Users(
 uid nvarchar(255) NOT NULL,
 userpassword nvarchar(max),
 lastlogontime datetime,
 badlogoncount int,
 badlogontime datetime,
 credentialslastset datetime,
 cantchangecredentials bit,
 dontexpirecredentials bit,
 enabled bit,
 notbefore datetime,
 notonorafter datetime,
 mail nvarchar(max),
 mobile nvarchar(max),
 givenname nvarchar(max),
 surname nvarchar(max),
 displayname nvarchar(max),
 statustext nvarchar(max),
 CONSTRAINT PK_Users PRIMARY KEY ( uid )
);
 
CREATE TABLE History(
 uid nvarchar(255) NOT NULL,
 timestamp datetime,
 userpassword nvarchar(max)
);
 
CREATE TABLE Terms(
 uid nvarchar(255) NOT NULL,
 mustaccept bit,
 accepted datetime
);

USE [directory-spi-sql];
 
IF OBJECT_ID('Attributes') IS NOT NULL
 DROP TABLE Attributes;
 
CREATE TABLE Attributes(
 uid nvarchar(255) NOT NULL,
 name nvarchar(255) NOT NULL,
 value nvarchar(255),
 policy nvarchar(255)
);

USE [directory-spi-sql];
 
IF OBJECT_ID('UbiloginAccountStatus') IS NOT NULL
 DROP VIEW UbiloginAccountStatus;
 
GO
 
CREATE VIEW UbiloginAccountStatus
AS SELECT 
uid AS login,
uid AS uniqueid,
ISNULL(enabled, 1) AS enabled,
badlogoncount,
DATEDIFF(SECOND, badlogontime, CURRENT_TIMESTAMP) AS badlogonage,
CASE WHEN DATEDIFF(YEAR, credentialslastset, CURRENT_TIMESTAMP)<68 THEN DATEDIFF(SECOND, credentialslastset, CURRENT_TIMESTAMP) END AS credentialsage,
ISNULL(dontexpirecredentials, 0) AS dontexpirecredentials,
ISNULL(cantchangecredentials, 0) AS cantchangecredentials,
CASE WHEN CAST(credentialslastset AS INT)=0 THEN 1 ELSE 0 END AS mustchangecredentials,
mail,
mobile,
statustext
FROM Users;

USE [directory-spi-sql];
 
IF OBJECT_ID('UbiloginAttributes') IS NOT NULL
 DROP VIEW UbiloginAttributes;
 
GO
 
CREATE VIEW UbiloginAttributes
AS SELECT 
uid AS uniqueid,
'mail' AS attributename,
mail AS attributevalue
FROM Users
WHERE mail IS NOT NULL;

USE [directory-spi-sql];
 
IF OBJECT_ID('UbiloginPassword') IS NOT NULL
 DROP VIEW UbiloginPassword;
 
GO
 
CREATE VIEW UbiloginPassword
AS SELECT 
uid AS uniqueid,
userpassword,
badlogoncount,
badlogontime,
lastlogontime,
credentialslastset
FROM Users;

USE [directory-spi-sql];
 
IF OBJECT_ID('UbiloginPasswordHistory') IS NOT NULL
        DROP VIEW UbiloginPasswordHistory;
 
GO
 
CREATE VIEW UbiloginPasswordHistory
AS SELECT    
uid AS uniqueid,
timestamp,
userpassword
FROM History;

USE [directory-spi-sql];
 
IF OBJECT_ID('UbiloginTerms') IS NOT NULL
        DROP VIEW UbiloginTerms;
 
GO
 
CREATE VIEW UbiloginTerms
AS SELECT    
uid AS uniqueid,
CASE WHEN mustaccept=1 AND accepted IS NULL THEN 1 END AS mustaccept,
accepted
FROM Terms;

USE [directory-spi-sql];
 
IF OBJECT_ID('UbiloginAuthorizer') IS NOT NULL
        DROP VIEW UbiloginAuthorizer;
 
GO
 
CREATE VIEW UbiloginAuthorizer
AS SELECT    
uid AS uniqueid,
'uid' AS name,
uid AS value,
null AS friendlyname,
null AS nameformat,
null AS policyid
FROM Users
 
UNION
 
SELECT    
uid AS uniqueid,
'mobile' AS name,
mobile AS value,
null AS friendlyname,
null AS nameformat,
null AS policyid
FROM Users
 
UNION
 
SELECT    
uid AS uniqueid,
name AS name,
value AS value,
null AS friendlyname,
null AS nameformat,
policy AS policyid
FROM Attributes;

USE [directory-spi-sql];
 
IF OBJECT_ID('AuditLogonSuccess') IS NOT NULL
    DROP PROCEDURE AuditLogonSuccess;
GO
 
IF OBJECT_ID('AuditLogonFailure') IS NOT NULL
    DROP PROCEDURE AuditLogonFailure;
GO
 
IF OBJECT_ID('CredentialsChange') IS NOT NULL
    DROP PROCEDURE CredentialsChange;
GO
 
IF OBJECT_ID('CredentialsReset') IS NOT NULL
    DROP PROCEDURE CredentialsReset;
GO
 
IF OBJECT_ID('CredentialsHistory') IS NOT NULL
    DROP PROCEDURE CredentialsHistory;
GO
 
IF OBJECT_ID('AuditTermsAccepted') IS NOT NULL
    DROP PROCEDURE AuditTermsAccepted;
GO
 
 
CREATE PROCEDURE AuditLogonSuccess (
    @uniqueid nvarchar(255)
)
AS
 
    UPDATE
    UbiloginPassword
    SET
    badlogoncount=NULL,
    badlogontime=NULL,
    lastlogontime=CURRENT_TIMESTAMP
    WHERE
    uniqueid=@uniqueid;
 
GO
 
 
CREATE PROCEDURE AuditLogonFailure (
    @uniqueid nvarchar(255),
    @lockoutthreshold int,
    @lockoutduration int
)
AS
 
    UPDATE
    UbiloginPassword
    SET
    badlogontime = CURRENT_TIMESTAMP,
    badlogoncount = CASE WHEN badlogoncount > 0 THEN badlogoncount + 1 ELSE 1 END
    WHERE
    uniqueid=@uniqueid;
 
GO
 
 
CREATE PROCEDURE CredentialsHistory (
    @uniqueid nvarchar(255),
    @userpassword nvarchar(max),
    @historycount int
)
AS
 
    IF @historycount IS NOT NULL BEGIN
 
        INSERT INTO UbiloginPasswordHistory
        (uniqueid,timestamp,userpassword)
        VALUES
        (@uniqueid,CURRENT_TIMESTAMP,@userpassword);
 
        DELETE FROM UbiloginPasswordHistory
        WHERE uniqueid=@uniqueid AND timestamp NOT IN
        (
            SELECT TOP(@historycount) timestamp
            FROM UbiloginPasswordHistory
            WHERE uniqueid=@uniqueid
            ORDER BY timestamp DESC
        );
       
    END;
 
GO
 
 
CREATE PROCEDURE CredentialsChange (
    @uniqueid nvarchar(255),
    @userpassword nvarchar(max),
    @historycount int
)
AS
 
    IF @historycount IS NOT NULL BEGIN
        EXEC CredentialsHistory @uniqueid,@userpassword,@historyCount;
    END;
 
    UPDATE
    UbiloginPassword
    SET
    badlogoncount=NULL,
    badlogontime=NULL,
    lastlogontime=CURRENT_TIMESTAMP,
    credentialslastset=CURRENT_TIMESTAMP,
    userpassword=@userpassword
    WHERE
    uniqueid=@uniqueid;
 
GO
 
 
CREATE PROCEDURE CredentialsReset (
    @uniqueid nvarchar(255),
    @userpassword nvarchar(max),
    @historycount int
)
AS
 
    IF @historycount IS NOT NULL BEGIN
        EXEC CredentialsHistory @uniqueid,@userpassword,@historyCount;
    END;
 
    UPDATE
    UbiloginPassword
    SET
    badlogoncount=NULL,
    badlogontime=NULL,
    credentialslastset=CURRENT_TIMESTAMP,
    userpassword=@userpassword
    WHERE
    uniqueid=@uniqueid;
 
GO
 
 
CREATE PROCEDURE AuditTermsAccepted (
    @uniqueid nvarchar(255),
    @accepted bit
)
AS
       
    IF @accepted = 1 BEGIN
        UPDATE
        UbiloginTerms
        SET
        accepted=CURRENT_TIMESTAMP
        WHERE
        uniqueid=@uniqueid;
     END;
 
GO