MSSQL'de Grup, Kullanıcı bazında kimlik doğrulama ve yetki kontrolu (authentication and authorization)

 Burada tablolarıyla birlikte SP'leri (Stored Procedure'ler) de oluşturarak tüm işlemlerin kimlik ve yetki doğrulamasının tüm işlemlerini MSSQL'de yaptıracağız. Biz sadece kullanıcı adı ve parola bilgilerini SP'ye göndererek sonucu alıyor. Sonuç 0 ise yetki yok, 1 veya daha büyükse yetkisi var anlamına geliyor.

Önce tabloları oluşturuyoruz,

create table secUser(
  UserId integer not null identity(1,1) primary key,
  UserName varchar(50) not null,
  Pswd varchar(150) not null,
  IsActive bit not null default 1
);

Örnek veri oluşturuyoruz;

insert into secUser(UserName, Pswd) values ('m1', 'a'), ('m2', 'a'), ('m3', 'a');

Grup tablosu kullanıcıları gruplamak için kullanılacak, Gruplara yetki verildiğinde, o gruba dahil kullanıcılara topluca yetki verilmiş olacak. Bu da yetkilendirmeyi büyük ölçüde kolaylaştıracak.

create table secGroup(
  GroupId integer not null identity(1,1) primary key,
  GroupName varchar(50) not null,
  IsActive bit not null default 1
);

Örnek grup verileri;

insert into secGroup(GroupName) values('Admin'), ('Satış'), ('Muhasebe'), ('Depo');

create table secGroupUser(
  GroupId integer not null, 
  UserId integer not null,
  CONSTRAINT secGroupUser_pk PRIMARY KEY (GroupId, UserId)
);

Grupların kullanıcıları için örnek veriler;

insert into secGroupUser values(1, 1), (2,2), (3,3), (4,3);

Her işlem için yetki kodlarını ve açıklamaları tuttuğumuz tablo. Bu tabloda ParentId alanında bir ağaç yapısı oluşturuyoruz. Örneğin bir kullanıcı için PermissionId 1 verirsek (Tüm İşlemler) bunu referans gösteren alt yetki kodları için de yetkili olacak.

create table secPermission(
  PermissionId integer not null primary key,
  PermDescription nvarchar(50),
  ParentId integer default 0,
  IsActive bit not null default 1
);

Yetki kodları için örnek veriler;

insert into secPermission(PermissionId, PermDescription, ParentId) values 
   (1, 'Tüm işlemler', 0),
   (100, 'Güvenlik yetkileri', 1),
   (101, 'Kullanıcı kayıtları', 100),
   (102, 'Kullanıcı ekleme', 101),
   (103, 'Kullanıcı düzelt', 101),
   (104, 'Kullanıcı sil', 101),
   (105, 'Kullanıcı incele', 101),
   (111, 'Grup kayıtları', 100),
   (112, 'Grup ekleme', 111),
   (113, 'Grup düzelt', 111),
   (114, 'Grup sil', 111),
   (115, 'Grup incele', 111),
   (121, 'İzin kayıtları', 100),
   (122, 'İzin ekleme', 121),
   (123, 'İzin düzelt', 121),
   (124, 'İzin sil', 121),
   (125, 'İzin incele', 121),
   
   (500, 'Döviz işlemleri', 1),
   (510, 'Döviz kodları', 500),
   (511, 'Döviz kodu ekle', 510),
   (512, 'Döviz kodu düzelt', 510),
   (513, 'Döviz kodu sil', 510),
   (514, 'Döviz kodu incele', 510),

   (520, 'Döviz kurları', 500),
   (521, 'Günlük kur girişleri', 520),
   (522, 'Günlük kurları indir', 520),
   (523, 'Günlük kur raporları', 520),

   (1000, 'Ticari sistem tüm işlemler', 1),
   (1010, 'Müşteri işlemleri', 1000),
   (1011, 'Müşteri ekle', 1010),
   (1012, 'Müşteri düzelt', 1010),
   (1013, 'Müşteri sil', 1010),
   (1014, 'Müşteri ekle', 1010),

   (1020, 'Satıcı firma işlemleri', 1000),
   (1021, 'Satıcı firma ekle', 1020),
   (1022, 'Satıcı firma düzelt', 1020),
   (1023, 'Satıcı firma sil', 1020),
   (1024, 'Satıcı firma ekle', 1020),

   (1030, 'Stok grubu işlemleri', 1000),
   (1031, 'Stok grubu ekle', 1030),
   (1032, 'Stok grubu düzelt', 1030),
   (1033, 'Stok grubu sil', 1030),
   (1034, 'Stok grubu incele', 1030),

   (1040, 'Stok işlemleri', 1000),
   (1041, 'Stok ekle', 1040),
   (1042, 'Stok düzelt', 1040),
   (1043, 'Stok sil', 1040),
   (1044, 'Stok incele', 1040),

   (1050, 'Satınalma fatura işlemleri', 1000),
   (1051, 'Satınalma faturası ekle', 1050),
   (1052, 'Satınalma faturası düzelt', 1050),
   (1053, 'Satınalma faturası sil', 1050),
   (1054, 'Satınalma faturası incele', 1050),

   (1060, 'Satış fatura işlemleri', 1000),
   (1061, 'Satış faturası ekle', 1060),
   (1062, 'Satış faturası düzelt', 1060),
   (1063, 'Satış faturası sil', 1060),
   (1064, 'Satış faturası incele', 1060);

Kullanıcılara verilen yetkiler tablosu,

create table secUserPermission(
  UserId integer,
  PermissionId integer,
  CONSTRAINT secUserPermission_pk PRIMARY KEY (UserId, PermissionId),
  CONSTRAINT FK_secUserPermission_secUser FOREIGN KEY (UserId)     
    REFERENCES secUser (UserId),
  CONSTRAINT FK_secUserPermission_secPermission FOREIGN KEY (PermissionId)     
    REFERENCES secPermission (PermissionId)
);

Gruplara verilen yetkiler tablosu,

create table secGroupPermission(
  GroupId integer,
  PermissionId integer,
  CONSTRAINT secGroupPermission_pk PRIMARY KEY (GroupId, PermissionId),
  CONSTRAINT FK_secGroupPermission_secGroup FOREIGN KEY (GroupId)     
    REFERENCES secGroup (GroupId),
  CONSTRAINT FK_secGroupPermission_secPermission FOREIGN KEY (PermissionId)     
    REFERENCES secPermission (PermissionId)
);

Örnek veriler.

insert into secGroupPermission values(1, 1), (2, 1060), (3, 1030), (3, 1040), (3, 1050), (3, 1060);

Bundan sonra SP'leri yazıyoruz; Önce kimlik doğrulaması yapıyoruz;

CREATE PROCEDURE secUserLogin(@UserName varchar(50), @Password varchar(100), @Userid int out, @Result int out)
AS
BEGIN
	SET NOCOUNT ON;
	declare @cnt int;
	set @result=-1;

	select top 1 @userid=UserId from secUser as A where (A.Username=@username) and (A.Pswd=@password) and (A.IsActive is not null and A.IsActive <> 0); 

	if @@ROWCOUNT=0 
	begin
	  set @Result=-101;
	  return -101
	end
	else
	begin
	  set @Result=1;
	  return 1;
	end
END;

Kullanıcıların kendilerine ve Gruplar aracılığıyla verilen yetkiler tek bir tabloda veren SP'yi oluşturuyoruz;

CREATE FUNCTION secinUserBasePermissions (@UserId int) RETURNS @tblRet TABLE 
(
   PermissionId int,
   ParentId int
)
AS
BEGIN
	insert into @tblRet 
	SELECT aUP.PermissionId, P.ParentId from secUserPermission as aUP inner join secPermission as P on (P.PermissionId=aUP.PermissionId)
	where (UserId=@UserId)
	union
	SELECT aGP.PermissionId, P.ParentId from secGroupPermission as aGP inner join secPermission as P on (P.PermissionId=aGP.PermissionId)
	where (GroupId in (SELECT GroupId FROM secGroupUser where (UserId=@UserId)))	 
  	RETURN;
END;

Burada da içinde recursive bir sorgu içeren (Permission'da ParentId olduğu için) son SP'yi oluşturuyoruz. 

create PROCEDURE secUserAuthorize(@UserName varchar(50), @RequiredRights varchar(200))
AS
BEGIN
	declare @retval int;
	declare @UserId int;
	select @UserId=UserId from secUser where UserName=@UserName;
	if ISNULL(@UserId,0)=0 
	  return -1;

    with ctePermissions as(
	  select PermissionId, ParentId, 0 as Level from secinUserBasePermissions(@UserId)
	  union all
	  select p.PermissionId, p.ParentId, Level + 1 from  secPermission p
	     inner join ctePermissions c on c.PermissionId=p.ParentId 
		 where ISNULL(p.[IsActive], 1)=1 
	)
	select @retval=count(*) from ctePermissions where ','+@RequiredRights+',' like '%,'+CAST(PermissionId as varchar(20))+',%'
	return @retval;
END;

En sonunda SP'yi şu şekilde çalıştırırsak sonucu alıyoruz

DECLARE	@return_value int,
		@Result int

EXEC	@return_value = [dbo].[secUserAuthorize]
		@UserName = N'm3',
		@RequiredRights = N'101'

SELECT	'Return Value' = @return_value

Sonuç = 0

DECLARE	@return_value int,
		@Result int

EXEC	@return_value = [dbo].[secUserAuthorize]
		@UserName = N'm1',
		@RequiredRights = N'101'

SELECT	'Return Value' = @return_value

Sonuç = 1

Bu sistem Delphi ile yazdığum Örnek Fatura projesinde arka planda yer almaktadır, ancak kodlarda kullanılmadı. Proje çalıştırıldığında kendisi veri tabanını oluştururken bu SP'leri de oluşturur.

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