MySQL'de rol kullanıcı ve grup tablolarıyla kimlik doğrulama ve yetki kontrolu

Selamlar,

Buradaki yazımda MySQL'de tablolarıyla birlikte iki Stored Function vasıtasıyla kullanıcıların parola kontrolü ve istenen işlem için yetkili olup olmadığını nasıl kontrol ettiğimi anlatacağım.

1-Öncelikle MySQL tablolarını yaratmamız gerekiyor; SQL cümlecikleri şu şekilde olacak

Kullanıcı kayıtlarını tuttuğumuz tablo

CREATE TABLE user (

  `idUser` int(11) NOT NULL,

  `Username` varchar(45) DEFAULT NULL,

  `Password` varchar(45) DEFAULT NULL,

  `Active` smallint(6)  DEFAULT '1',

  PRIMARY KEY (`idUser`)

);

Kullanıcıları gruplayabilmemiz için kullandığımız tablo

CREATE TABLE `group` (
 `idGroup` int(11) NOT NULL,
 `GroupName` varchar(45) DEFAULT NULL,
 `ParentId` int(11) DEFAULT NULL,
 PRIMARY KEY (`idGroup`)
);

Hangi kullanıcılar hangi gruplara üye onları burada tutuyoruz

CREATE TABLE `groupuser` (

  `idGroupUser` int(11) NOT NULL,

  `GroupId` int(11) DEFAULT NULL,

  `UserId` int(11) DEFAULT NULL,

  PRIMARY KEY (`idGroupUser`)

);

Tüm erişim haklarını tuttuğumuz tablo

CREATE TABLE `permission` (

  `idPermission` int(11) NOT NULL,

  `Description` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`idPermission`)

);

Tüm rolleri tuttuğumuz tablo. Hakları verebilmek için rol kullanıyoruz.

CREATE TABLE `role` (

  `idRole` int(11) NOT NULL,

  `Definition` varchar(100) DEFAULT NULL,

  `Active` smallint(6) DEFAULT '1',

  PRIMARY KEY (`idRole`)


);

Haklar sadece rollere verilebilir. Direkt olarak kullanıcı ya da gruplara verilemez. Daha sonra rollerle kullanıcı ve grupları da eşleştiren tabloyu açacağız.

CREATE TABLE `rolepermission` (

  `idRolePermission` int(11) NOT NULL,

  `RoleId` int(11) DEFAULT NULL,

  `PermissionId` int(11) DEFAULT NULL,

  PRIMARY KEY (`idRolePermission`)

);

Kullanıcı ve Gruplara rol ataması bu tabloda yapılıyor. Haklar, roller vasıtasıyla kullanıcılara verilmiş oluyor.

CREATE TABLE `roleusergroup` (

  `idRoleUserGroup` int(11) NOT NULL,

  `RoleId` int(11) DEFAULT NULL,

  `UserId` int(11) DEFAULT NULL,

  `GroupId` bit(1) DEFAULT NULL,

  `IsUser` smallint(6) DEFAULT '1',

  PRIMARY KEY (`idRoleUserGroup`)

);

Önce kullanıcı adı ve parolayı kontrol eden function'ı yazalım

CREATE FUNCTION IsUserLegal(AUsername varchar(50), APassword varchar(50)) RETURNS int

BEGIN

  declare retval int;

  select count(*) into retval from `user` as A where (A.Username= AUsername) and (A.Password=APassword) and (A.Active is not null and A.Active <> 0); 

RETURN retval;

END;

İkinci olarak da verilen kullanıcı adıyla birlikte bir grup yetkiye sahip mi onu kontrol eden fonksiyonu yazacağız. Yalnız burada ARequiredRights parametresinin formatı önemli.
' 10127 6325 6172 ' şeklinde gönderilecek. Yani yetki kodları varchar olarak gönderilecek (programda integer olarak tutulsa bile stringe çevrilecek), yetki kodlarını birbirinden ayırmak için boşluk karakteri kullanılacak, parametrenin başına ve sonuna boşluk eklenecek. Geri dönen sayı -1 ise kullanıcı adı yok anlamına gelir, 0 ise istenen yetki bu kullanıcıda yok demektir. Pozitif ise string içinde verilen yetki kodlarından en az birine sahiptir.  

CREATE FUNCTION HasPermission(AUsername varchar(50), ARequiredRights varchar(200)) RETURNS int

BEGIN




  declare retval int default 0;

  declare aUserId int default -1;




  select idUser into aUserId from user where Username=AUsername;




  if aUserId<=0 then

    return retval;

  end if;

  SELECT count(PermissionId) into retval from rolepermission 

    where 

    (

    RoleId in (select RoleId from roleusergroup where ((IsUser is null or IsUser=1) and UserId=aUserId) or ((IsUser=0) and (GroupId in (SELECT GroupId FROM groupuser where UserId=aUserId))))

    ) and concat(' ', ARequiredRights, ' ') like concat('% ',CAST(PermissionId as char(20)),' %');

  

  RETURN retval;

RETURN 1;

END;

NOT: Yazarken yaptığım yanlışlar affola. Bildirirseniz düzeltir size de yeni halini gönderirim. Bu sistemi denedim. Yani çalışıyor.

MSSQL'de CLR UDF (User Defined Function) oluşturma

MSSQL Sunucuda kendimiz VS C#la yazdığımız bir dll'i sorgularda kullanabileceğimiz bir fonksiyon olarak tanımlayabiliriz.

Öncelikle VS'da bir library projesi açacağız.
Bunun içinde class'ımızı oluşturup derleyeceğiz (build edilecek).
örneğin bu benim tam kelimeleri bulmak için kullanacağım bir UDF.
using System.Text.RegularExpressions;

public class xxStrings
{
    public static int FindWord(string pattern, string text, bool wholeWord=false, bool ignoreCase=false)
    {
        string s;
        if (wholeWord)
        {
            s = "\b(" + pattern + ")\b";
        }
        else
        {
            s = pattern;
        }

        var r = new RegexOptions();
        r = RegexOptions.None;

        if (ignoreCase)
        {
            r = RegexOptions.IgnoreCase;
        }

        var i = Regex.Match(text, s, r);
        if (i.Success)
       
        {
          return i.Index;
        }
        else
        {
          return -1;
        }
    }
}
Bunun sonrasında, SQL Management Studio açılır ve bir SQL penceresinde bu fonksiyonu SQL'e kaydetmek için burada şu kodlar yazılır.

CREATE ASSEMBLY FindWord FROM 'c:\buraya dosya yolu yazılacak\FindWord.dll';
GO

CREATE FUNCTION FindWord()  RETURNS INT 
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount; 
GO