Rappels de base :

J'ai déjà évoqué dans mes articles la dyslexie entre connexion et utilisateur, aussi je passe vite sur ces notions :

use master

go

if EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DemoEmploye')

DROP LOGIN DemoEmploye

GO

create login DemoEmploye with password = 'Demo',CHECK_POLICY =OFF,CHECK_EXPIRATION =OFF

go

if EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DemoManager')

DROP LOGIN DemoManager

GO

create login DemoManager with password = 'Demo',CHECK_POLICY =OFF,CHECK_EXPIRATION =OFF

go

Je crée une base pour ce post et deux connexions.

if exists (select 1 from sys.databases where name = 'DemoSecurite')

    drop database DemoSecurite

go

create database DemoSecurite

go

use DemoSecurite

go

Pour cette démo, deux tables suffiront :

-- Créons deux tables

create table DonneesEmployes (Cle int identity(1,1) primary key, Valeur varchar(50))

go

create table DonneesManagers (Cle int identity(1,1) primary key, Valeur varchar(50))

go

insert into DonneesEmployes (Valeur) values ('Voici un employé PAUL'),('Voici un employé PIERRE')

insert into DonneesManagers (Valeur) values ('Voici un manager DOMINIQUE'),('Voici un manager ISABELLE')

select * from DonneesEmployes

go

select * from DonneesManagers

go

Je vais créer deux utilisateurs (l'un est un manager, l'autre est un employé)

-- Il nous faut deux users

create user DemoEmploye for login DemoEmploye

go

create user DemoManager for login DemoManager

go

Vérifions leus accès par défaut :

--- Accès par défaut

execute as user = 'DemoEmploye'

select SUSER_NAME() as NomConnexion, USER_NAME() as NomUtilisateur

select * from DonneesEmployes

select * from DonneesManagers

revert

go

Rien pour l'employé…

execute as user = 'DemoManager'

select SUSER_NAME() as NomConnexion, USER_NAME() as NomUtilisateur

select * from DonneesEmployes

select * from DonneesManagers

revert

go

Et rien pour le manager …

On pourrait raisonner au niveau des individus, mais, je l'évoque dans mes cours, ce n'est pas une bonne pratique….

On crée donc des rôles :

-- Plutôt que de raisonner au niveau des individus, créons des rôles

CREATE ROLE [RoleManagers]

GO

EXEC sp_addrolemember N'RoleManagers', N'DemoManager'

GO

CREATE ROLE [RoleEmployes]

GO

EXEC sp_addrolemember N'RoleEmployes', N'DemoEmploye'

GO

-- Autorisations en lecture

GRANT SELECT ON [DonneesEmployes] TO RoleEmployes

GO

GRANT SELECT ON [DonneesEmployes] TO RoleManagers

go

GRANT SELECT ON [DonneesManagers] TO RoleManagers

go

Que se passe t-il pour notre employé ?

-- Vérification

execute as user = 'DemoEmploye'

select SUSER_NAME() as NomConnexion, USER_NAME() as NomUtilisateur

select * from DonneesEmployes

select * from DonneesManagers

revert

go

C'est ce que l'on souhaite : pas d'accès aux données des managers…

Et pour notre manager :

execute as user = 'DemoManager'

select SUSER_NAME() as NomConnexion, USER_NAME() as NomUtilisateur

select * from DonneesEmployes

select * from DonneesManagers

revert

go

OK : il peut tout voir…

Et si une personne appartient aux deux rôles ?

-- Que se passe t-il pour un EmployéManager ?

if EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DemoEmployeManager')

DROP LOGIN DemoEmployeManager

GO

create login DemoEmployeManager with password = 'Demo',CHECK_POLICY =OFF,CHECK_EXPIRATION =OFF

go

create user DemoEmployeManager for login DemoEmployeManager

go

exec sp_addrolemember N'RoleManagers', N'DemoEmployeManager'

exec sp_addrolemember N'RoleEmployes', N'DemoEmployeManager'

GO

execute as user = 'DemoEmployeManager'

select SUSER_NAME() as NomConnexion, USER_NAME() as NomUtilisateur

select * from DonneesEmployes

select * from DonneesManagers

revert

go

Et si on fait une interdiction explicite ?

-- Maintenant interdisons aux employés de lire les données de managers

DENY SELECT ON [dbo].[DonneesManagers] TO [RoleEmployes]

GO

execute as user = 'DemoEmployeManager'

select SUSER_NAME() as NomConnexion, USER_NAME() as NomUtilisateur

select * from DonneesEmployes

select * from DonneesManagers

revert

go

OK : les interdits l'emportent bien…

Une vision cumulée des droits ?

Cette architecture peut être très complexe, puisqu'un rôle peut inclure des rôles, que l'on peut être owner, que l'on peut donner des privilèges au niveau base, schéma, objet,etc…

Aussi comment être sûr de qui fait quoi ?

C'est là que la fonction système fn_my_permissions ( 'EntitéSécurisable' ou NULL , 'ClasseDeSecurite')

intervient…elle donne les privilèges actuels pour la connexion courante

Les classes de sécurité les plus courantes étant : SERVER, BASE,OBJECT

Par exemple :

-- Vision cumuléee des droits :

execute as user = 'DemoManager'

select SUSER_NAME() as NomConnexion, USER_NAME() as NomUtilisateur

SELECT * FROM fn_my_permissions('dbo.DonneesManagers', 'OBJECT')

ORDER BY subentity_name, permission_name ;

SELECT * FROM fn_my_permissions('dbo.DonneesEmployes', 'OBJECT')

ORDER BY subentity_name, permission_name ;

revert

go

Effectivement, je viens de dire que l'on cumule les privilèges sauf si interdiction explicite.

Ce que l'on vérifie par

execute as user = 'DemoEmployeManager'

select SUSER_NAME() as NomConnexion, USER_NAME() as NomUtilisateur

SELECT * FROM fn_my_permissions('dbo.DonneesManagers', 'OBJECT')

ORDER BY subentity_name, permission_name ;

SELECT * FROM fn_my_permissions('dbo.DonneesEmployes', 'OBJECT')

ORDER BY subentity_name, permission_name ;

revert

go

Il ne peut pas de faire de select sur les employés…

Script de vision consolidée :

declare @tATraiter table (NoATraiter int identity(1,1), NomTable sysname)

declare @NomConnexion sysname,@NomTable sysname,@NoATraiter int,

        @NomEntite sysname,@NomSousEntite sysname,@Droit sysname

set nocount on

    -- Cette table nous servira à parcourir les tables

    insert into @tATraiter(NomTable)

    select '[' + TABLE_SCHEMA + '].[' + table_name + ']' from INFORMATION_SCHEMA.TABLES

    set @NomConnexion = 'sa' -- 'DemoManager'

    print 'Connexion auditée :' + @NomConnexion

    print replicate ('-',30)

    -- On change d'identité

    execute as login = @NomConnexion

    -- Etude des privilèges sur le serveur

    print 'Privilèges sur le serveur SQL :' + @@servername

    print replicate ('-',60)

    DECLARE crDroits CURSOR READ_ONLY for

    SELECT * FROM fn_my_permissions(null, 'SERVER')

        ORDER BY subentity_name, permission_name ;

    open crDroits

    while 1=1

        begin

            fetch crDroits into @NomEntite,@NomSousEntite,@Droit

            if @@FETCH_STATUS != 0

                break

            print replicate(' ',8)+@Droit + ' ' + @NomSousEntite

        end

    close crDroits

    deallocate     crDroits        

    -- Etude des privilèges sur la base

    print 'Privilèges sur la base :' + db_name()

    print replicate ('-',60)

    DECLARE crDroits CURSOR READ_ONLY for

    SELECT * FROM fn_my_permissions(null, 'BASE')

        ORDER BY subentity_name, permission_name ;

    open crDroits

    while 1=1

        begin

            fetch crDroits into @NomEntite,@NomSousEntite,@Droit

            if @@FETCH_STATUS != 0

                break

            print replicate(' ',8)+@Droit + ' ' + @NomSousEntite

        end

    close crDroits

    deallocate     crDroits        

-- Etude des privilèges sur les tables

while 1=1

    begin

        set @NoATraiter = null

        select top 1 @NoATraiter=NoATraiter,@NomTable=NomTable from @tATraiter

        if @NoATraiter is null break

        print replicate(' ',4)+'Privilèges sur la table :' + @NomTable

        DECLARE crDroits CURSOR READ_ONLY for

        SELECT * FROM fn_my_permissions(@NomTable, 'OBJECT')

            ORDER BY subentity_name, permission_name ;

        open crDroits

        while 1=1

            begin

                fetch crDroits into @NomEntite,@NomSousEntite,@Droit

                if @@FETCH_STATUS != 0

                    break

                print replicate(' ',8)+@Droit + ' ' + @NomSousEntite

            end

        close crDroits

        deallocate     crDroits        

        delete from @tATraiter where NoATraiter = @NoATraiter

    end

-- On reprend notre identité

revert

Exemples de lancements :

Avec sa :

Connexion auditée :sa

------------------------------

Privilèges sur le serveur SQL :LTREE24

------------------------------------------------------------

ADMINISTER BULK OPERATIONS

ALTER ANY CONNECTION

ALTER ANY CREDENTIAL

ALTER ANY DATABASE

ALTER ANY ENDPOINT

ALTER ANY EVENT NOTIFICATION

ALTER ANY LINKED SERVER

ALTER ANY LOGIN

ALTER ANY SERVER AUDIT

ALTER RESOURCES

ALTER SERVER STATE

ALTER SETTINGS

ALTER TRACE

AUTHENTICATE SERVER

CONNECT SQL

CONTROL SERVER

CREATE ANY DATABASE

CREATE DDL EVENT NOTIFICATION

CREATE ENDPOINT

CREATE TRACE EVENT NOTIFICATION

EXTERNAL ACCESS ASSEMBLY

SHUTDOWN

UNSAFE ASSEMBLY

VIEW ANY DATABASE

VIEW ANY DEFINITION

VIEW SERVER STATE

Privilèges sur la base :DemoSecurite

------------------------------------------------------------

Privilèges sur la table :[dbo].[DonneesManagers]

ALTER

CONTROL

DELETE

EXECUTE

INSERT

RECEIVE

REFERENCES

SELECT

TAKE OWNERSHIP

UPDATE

VIEW CHANGE TRACKING

VIEW DEFINITION

REFERENCES Cle

SELECT Cle

UPDATE Cle

REFERENCES Valeur

SELECT Valeur

UPDATE Valeur

Privilèges sur la table :[dbo].[DonneesEmployes]

ALTER

CONTROL

DELETE

EXECUTE

INSERT

RECEIVE

REFERENCES

SELECT

TAKE OWNERSHIP

UPDATE

VIEW CHANGE TRACKING

VIEW DEFINITION

REFERENCES Cle

SELECT Cle

UPDATE Cle

REFERENCES Valeur

SELECT Valeur

UPDATE Valeur

Avec DemoManager :

Connexion auditée :DemoManager

------------------------------

Privilèges sur le serveur SQL :LTREE24

------------------------------------------------------------

CONNECT SQL

VIEW ANY DATABASE

Privilèges sur la base :DemoSecurite

------------------------------------------------------------

Privilèges sur la table :[dbo].[DonneesManagers]

SELECT

SELECT Cle

SELECT Valeur

Privilèges sur la table :[dbo].[DonneesEmployes]

SELECT

SELECT Cle

SELECT Valeur

Avec DemoEmployeManager :

Connexion auditée :DemoEmployeManager

------------------------------

Privilèges sur le serveur SQL :LTREE24

------------------------------------------------------------

CONNECT SQL

VIEW ANY DATABASE

Privilèges sur la base :DemoSecurite

------------------------------------------------------------

Privilèges sur la table :[dbo].[DonneesManagers]

Privilèges sur la table :[dbo].[DonneesEmployes]

SELECT

SELECT Cle

SELECT Valeur

On voit bien que les privilèges sur les données des managers sont bien enlevés…

Conclusion :

Voilà plusieurs mois que je promettais à mes auditeurs un tel script, c'est fait.
Il pourra servir de base à une procédure stockée, et, pourquoi pas à un rapport d'audit sur Reporting Services….