SyteLine Authorizations

实施Syteline system, 在authorizations时，经常需要写的几个SQL语句，因此把它写成存储过程，在使用时，执行这些存储过程即可，纯粹是为了方便工作。

取到所有用户：

CREATE PROCEDURE [dbo].[usp_SyteLine_GetUserNames]
AS 
SELECT [userId],[username],[userDesc] FROM [dbo].[UserNames] ORDER BY [username]

 
取得用户信息By UserId:

CREATE PROCEDURE [dbo].[usp_SyteLine_GetUserNamesByUserId]
(
    @UserId DECIMAL(11,0)
)
AS 
SELECT [userId],[username],[userDesc] FROM [dbo].[UserNames] WHERE UserId = @UserId

GO

  

取得所有组信息：

CREATE PROCEDURE [dbo].[usp_SyteLine_GetGroupNames]
AS 
SELECT [GroupId],[GroupName],[GroupDesc] FROM [dbo].[GroupNames] ORDER BY [GroupName] ASC

GO

 
取得用户信息By GroupId:

CREATE PROCEDURE [dbo].[usp_SyteLine_GetUserNamesByGroup]
(
    @GroupId DECIMAL(11,0)
)
AS 
SELECT [userId],[username],[userDesc] FROM [dbo].[UserNames] WHERE UserId IN 
(
    SELECT [UserId] FROM [dbo].[UserGroupMap] WHERE [GroupId] = @GroupId
)
GO

  

取得权限组By UserId：

CREATE PROCEDURE [dbo].[usp_SyteLine_GetGroupNamesByUser]
(
    @UserId DECIMAL(11,0)
)
AS 
SELECT [GroupId],[GroupName],[GroupDesc] FROM [dbo].[GroupNames] WHERE [GroupId] IN 
(
    SELECT [GroupId] FROM [dbo].[UserGroupMap] WHERE [UserId] = @UserId
)

GO

  

取得权限组By GroupId:

CREATE PROCEDURE [dbo].[usp_SyteLine_GetGroupNamesByGroup]
(
    @GroupId DECIMAL(11,0)
)
AS 
SELECT [GroupId],[GroupName],[GroupDesc] FROM [dbo].[GroupNames] WHERE [GroupId] = @GroupId

GO

取得用户息By ObjectName: 

CREATE PROCEDURE [dbo].[usp_SyteLine_GetUserNamesByObject]
(
    @ObjectName NVARCHAR(50)
)
AS 
SELECT [userId],[username],[userDesc] FROM [dbo].[UserNames] WHERE UserId IN 
(
SELECT [Id] FROM [dbo].[AccountAuthorizations]
WHERE [UserFlag] = 1 AND [ObjectType] = 0 AND ObjectName1 LIKE '%' +@ObjectName + '%')

   
取得权限组By ObjectName:

CREATE PROCEDURE [dbo].[usp_SyteLine_GetGroupNamesByObject]
(
    @ObjectName NVARCHAR(50)
)
AS 
SELECT GroupId,GroupName,GroupDesc FROM [dbo].[GroupNames] WHERE GroupId IN(
SELECT [Id] FROM [dbo].[AccountAuthorizations]
WHERE [UserFlag] = 0 AND [ObjectType] = 0 AND ObjectName1 LIKE '%' +@ObjectName + '%') 

  

取得用户拥有form对象By userId：

CREATE PROCEDURE [dbo].[usp_SyteLine_GetAccountAuthorizationsByUser]
(
    @UserId DECIMAL(11,0)
)
AS 
SELECT [ObjectName1],
CASE WHEN [ReadPrivilege] = 1 THEN 'Granted' WHEN [ReadPrivilege] = 0 THEN 'Revoked' END AS [ReadPrivilege],
CASE WHEN [UpdatePrivilege] = 1 THEN 'Granted' WHEN [UpdatePrivilege] = 0 THEN 'Revoked' END AS [UpdatePrivilege],
CASE WHEN [BulkUpdatePrivilege] = 1 THEN 'Granted' WHEN [BulkUpdatePrivilege] = 0 THEN 'Revoked' END AS [BulkUpdatePrivilege],
CASE WHEN [InsertPrivilege] = 1 THEN 'Granted' WHEN [InsertPrivilege] = 0 THEN 'Revoked' END AS [InsertPrivilege],
CASE WHEN [DeletePrivilege] = 1 THEN 'Granted' WHEN [DeletePrivilege] = 0 THEN 'Revoked' END AS [DeletePrivilege],
CASE WHEN [EditPrivilege] = 1 THEN 'Granted' WHEN [EditPrivilege] = 0 THEN 'Revoked' END AS [EditPrivilege],
CASE WHEN [ExecutePrivilege] = 1 THEN 'Granted' WHEN [ExecutePrivilege] = 0 THEN 'Revoked' END AS [ExecutePrivilege] 
FROM [dbo].[AccountAuthorizations] 
WHERE [UserFlag] = 1 AND [Id] = @UserId

GO

  

取得组拥有form对象By GroupId:

CREATE PROCEDURE [dbo].[usp_SyteLine_GetAccountAuthorizationsByGroup]
(
    @GroupId DECIMAL(11,0)
)
AS 
SELECT [ObjectName1],
CASE WHEN [ReadPrivilege] = 1 THEN 'Granted' WHEN [ReadPrivilege] = 0 THEN 'Not Granted' END AS [ReadPrivilege],
CASE WHEN [UpdatePrivilege] = 1 THEN 'Granted' WHEN [UpdatePrivilege] = 0 THEN 'Not Granted' END AS [UpdatePrivilege],
CASE WHEN [BulkUpdatePrivilege] = 1 THEN 'Granted' WHEN [BulkUpdatePrivilege] = 0 THEN 'Not Granted' END AS [BulkUpdatePrivilege],
CASE WHEN [InsertPrivilege] = 1 THEN 'Granted' WHEN [InsertPrivilege] = 0 THEN 'Not Granted' END AS [InsertPrivilege],
CASE WHEN [DeletePrivilege] = 1 THEN 'Granted' WHEN [DeletePrivilege] = 0 THEN 'Not Granted' END AS [DeletePrivilege],
CASE WHEN [EditPrivilege] = 1 THEN 'Granted' WHEN [EditPrivilege] = 0 THEN 'Not Granted' END AS [EditPrivilege],
CASE WHEN [ExecutePrivilege] = 1 THEN 'Granted' WHEN [ExecutePrivilege] = 0 THEN 'Not Granted' END AS [ExecutePrivilege]
FROM [dbo].[AccountAuthorizations] 
WHERE [UserFlag] = 0 AND [ObjectType] = 0 AND [Id] = @GroupId

GO