ASP.NET RBAC实现的SQL Server

1. 创建用户、角色和权限表

sqlCREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    Username NVARCHAR(50) NOT NULL,
    Password NVARCHAR(50) NOT NULL,
    Email NVARCHAR(50) NOT NULL,
    IsActive BIT NOT NULL DEFAULT 1
);

CREATE TABLE Roles (
    RoleID INT PRIMARY KEY IDENTITY(1,1),
    RoleName NVARCHAR(50) NOT NULL,
    Description NVARCHAR(MAX) NULL,
    IsActive BIT NOT NULL DEFAULT 1
);

CREATE TABLE Permissions (
    PermissionID INT PRIMARY KEY IDENTITY(1,1),
    PermissionName NVARCHAR(50) NOT NULL,
    Description NVARCHAR(MAX) NULL,
    IsActive BIT NOT NULL DEFAULT 1
);

CREATE TABLE RolePermissions (
    RoleID INT NOT NULL,
    PermissionID INT NOT NULL,
    CONSTRAINT PK_RolePermissions PRIMARY KEY (RoleID, PermissionID)
);

CREATE TABLE UserRole (
    UserID INT NOT NULL,
    RoleID INT NOT NULL,
    CONSTRAINT PK_UserRole PRIMARY KEY (UserID, RoleID)
);

1. 添加示例数据

sqlINSERT INTO Users (Username, Password, Email, IsActive)
VALUES ('admin', 'admin123', 'admin@example.com', 1);

INSERT INTO Roles (RoleName, Description, IsActive)
VALUES ('Admin', 'Administrator', 1),
       ('Manager', 'Manager', 1),
       ('User', 'User', 1);

INSERT INTO Permissions (PermissionName, Description, IsActive)
VALUES ('CreateUser', 'Create User', 1),
       ('EditUser', 'Edit User', 1),
       ('DeleteUser', 'Delete User', 1),
       ('ViewUser', 'View User', 1);

INSERT INTO RolePermissions (RoleID, PermissionID)
VALUES (1, 1),
       (1, 2),
       (1, 3),
       (1, 4),
       (2, 2),
       (2, 4),
       (3, 4);

INSERT INTO UserRole (UserID, RoleID)
VALUES (1, 1);

1. 创建存储过程

sqlCREATE PROCEDURE sp_CheckUserPermission
    @Username NVARCHAR(50),
    @PermissionName NVARCHAR(50),
    @IsAllowed BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @UserID INT;
    DECLARE @RoleID INT;
    DECLARE @PermissionID INT;

    -- 获取用户ID
    SELECT @UserID = UserID FROM Users WHERE Username = @Username AND IsActive = 1;
    IF (@UserID IS NULL)
    BEGIN
        SET @IsAllowed = 0;
        RETURN;
    END;

    -- 获取用户角色ID列表
    DECLARE @RoleIDs TABLE (RoleID INT);
    INSERT INTO @RoleIDs (RoleID)
    SELECT RoleID FROM UserRole WHERE UserID = @UserID;

    -- 获取权限ID
    SELECT @PermissionID = PermissionID FROM Permissions WHERE PermissionName = @PermissionName AND IsActive = 1;
    IF (@PermissionID IS NULL)
    BEGIN
        SET @IsAllowed = 0;
        RETURN;
    END;

    -- 是否允许访问
    DECLARE @AllowCount INT;
    SELECT @AllowCount = COUNT(*) FROM RolePermissions WHERE RoleID IN (SELECT RoleID FROM @RoleIDs) AND PermissionID = @PermissionID;
    IF (@AllowCount > 0)
    BEGIN
        SET @IsAllowed = 1;
    END
    ELSE
    BEGIN
        SET @IsAllowed = 0;
    END;
END;

1. 在ASP.NET中调用存储过程

csharppublic bool CheckUserPermission(string username, string permissionName)
{
    bool isAllowed = false;

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand("sp_CheckUserPermission", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@Username", username);
            cmd.Parameters.AddWithValue("@PermissionName", permissionName);

            SqlParameter outputParam = new SqlParameter("@IsAllowed", SqlDbType.Bit);
            outputParam.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(outputParam);

            conn.Open();
            cmd.ExecuteNonQuery();

            isAllowed = (bool)outputParam.Value;
        }
    }

    return isAllowed;
}