Get Security Permission Report for all users and objects in a database
Thursday 04 February 2016
Σχεδόν κάθε μήνα έχω την χαρά να με επισκέπτονται διάφοροι επιθεωρητές που σκοπό έχουν να ελέγξουν την ασφάλεια των συστημάτων. Είμαι θετικός στην διαδικασία αυτή και επιθυμώ προσωπικά τον έλεγχο αυτό καθώς είμαι άνθρωπος και μπορεί κάτι να μου έχει ξεφύγει.
Αυτό που δεν μπορώ όμως είναι το γεγονός έρχονται με διάφορα 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*/