go backarticles

Articles of SQLschool.gr Team

Get Security Permission Report for all users and objects in a database

Antonios Chatzipavlis

Σχεδόν κάθε μήνα έχω την χαρά να με επισκέπτονται διάφοροι επιθεωρητές που σκοπό έχουν να ελέγξουν την ασφάλεια των συστημάτων. Είμαι θετικός στην διαδικασία αυτή και επιθυμώ προσωπικά τον έλεγχο αυτό καθώς είμαι άνθρωπος και μπορεί κάτι να μου έχει ξεφύγει.

Αυτό που δεν μπορώ όμως είναι το γεγονός έρχονται με διάφορα script ή sql commands/queries που βρίσκουν από εδώ και από εκεί και ζητάνε την εκτέλεση τους και μάλιστα απαιτούν τα αποτέλεσμα όχι σε κάποιο txt ή xlsx αλλά σε print screen!!!! Το αποτέλεσμα είναι να πλημυρίζουν σε πληροφορία και να χρειάζονται μέρες για αν βγάλουν αποτέλεσμα.

Από τη μια μπορώ να τους καταλάβω καθώς είναι καχύποπτοι αλλά από την άλλη όμως θα πρέπει να μάθουν το προϊόν το οποίο θα ελέγξουν και να γράψουν κάτι που να απλοποιεί την ζωή τους

Για όλους αυτούς αλλά και για όλους εσάς έχω γράψει μια stored procedure που την χρησιμοποιώ καθημερινά σε 270+ instances και 3500+ databases που δίνει χαρτί και καλαμάρι αυτό που ο καθένας μας χρειάζεται να γνωρίζει ποιοι είναι οι χρήστες / group χρηστών / database roles που έχουν πρόσβαση στην database και τι δικαιώματα και σε ποια objects έχουν την πρόσβαση αυτή.

Δυστυχώς αυτό δεν βγαίνει από ένα σημείο και θα πρέπει να ρωτηθούν πολλά system views τόσο σε επίπεδο server όσο και σε επίπεδο database.

Tη stored procedure όπως θα δείτε την δημιουργώ στην master database αλλά την κάνω system object με την εκτέλεση της sys.sp_MS_marksystemobject. Μπορείτε να την εκτελέσετε στο database context που θέλετε και θα σας φέρει τα αποτελέσματα για αυτό.
USE master;
GO 
CREATE PROC dbo.sp_GetAllObjectsUsersPerms 
AS
    SELECT
        [UserType]            =    CASE princ.[type]
                                    WHEN 'S' THEN 'SQL User'
                                    WHEN 'U' THEN 'Windows User'
                                    WHEN 'G' THEN 'Windows Group'
                                END,
        [DatabaseUserName]    =    princ.[name],
        [LoginName]            =    ulogin.[name],
        [Role]                =    NULL,
        [PermissionType]    =    perm.[permission_name],
        [PermissionState]    =    perm.[state_desc],
        [ObjectType]        =    CASE perm.[class]
                                    WHEN 1 THEN obj.[type_desc]       
                                    ELSE perm.[class_desc]            
                                END,
        [Schema]            =    objschem.[name],
        [ObjectName]        =    CASE perm.[class]
                                    WHEN 3 THEN permschem.[name]       
                                    WHEN 4 THEN imp.[name]             
                                    ELSE OBJECT_NAME(perm.[major_id])  
                                END,
        [ColumnName]        =    col.[name]
    FROM
        sys.database_principals            AS princ
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = princ.[sid]
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        princ.[type] IN ('S','U','G')
        AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    SELECT
        [UserType]            =    CASE membprinc.[type]
                                    WHEN 'S' THEN 'SQL User'
                                    WHEN 'U' THEN 'Windows User'
                                WHEN 'G' THEN 'Windows Group'
                                END,
        [DatabaseUserName]    =    membprinc.[name],
        [LoginName]            =    ulogin.[name],
        [Role]                =    roleprinc.[name],
        [PermissionType]    =    perm.[permission_name],
        [PermissionState]    =    perm.[state_desc],
        [ObjectType]        =    CASE perm.[class]
                                    WHEN 1 THEN obj.[type_desc]        
                                    ELSE perm.[class_desc]             
                                END,
        [Schema]            =    objschem.[name],
        [ObjectName]        =    CASE perm.[class]
                                    WHEN 3 THEN permschem.[name]       
                                    WHEN 4 THEN imp.[name]             
                                    ELSE OBJECT_NAME(perm.[major_id])  
                                END,
        [ColumnName]        = col.[name]
    FROM
        sys.database_role_members          AS members
        JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
        JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        membprinc.[type] IN ('S','U','G')
        AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

     SELECT
        [UserType]            =    '{All Users}',
        [DatabaseUserName]    =    '{All Users}',
        [LoginName]            =    '{All Users}',
        [Role]                =    roleprinc.[name],
        [PermissionType]    =    perm.[permission_name],
        [PermissionState]    =    perm.[state_desc],
        [ObjectType]        =    CASE perm.[class]
                                    WHEN 1 THEN obj.[type_desc]      
                                    ELSE perm.[class_desc]           
                                END,
        [Schema]            =    objschem.[name],
        [ObjectName]        =    CASE perm.[class]
                                    WHEN 3 THEN permschem.[name]     
                                    WHEN 4 THEN imp.[name]           
                                ELSE OBJECT_NAME(perm.[major_id]) 
                                END,
        [ColumnName]        =    col.[name]
    FROM
        sys.database_principals            AS roleprinc
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND obj.[is_ms_shipped] = 0;
GO

-- make it system object
EXEC sys.sp_MS_marksystemobject sp_GetAllObjectsUsersPerms ;
GO


Υ.Γ. Το άρθρο αυτό είναι αφιερωμένο σε ένα φίλο και συνάδελφο τον Ζήνωνα Σάββα που μας άφησε νωρίς. RIP Ζήνων.

/*antonch*/

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Relative Articles

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.