Security. Oh that most painful of topics. I discussed it a few months earlier when I discussed the need to give rights only through roles to users, so everything is the same in dev and prod except the users who are placed in each role (SQL Server Database Security And Source Control). As I was updating some of our security to use this method, the problem arose that it is hard to know if we got it right. How to make sure that userX who had access to a certain set of objects, still does, even though we have given them access via a different method. This is something that we often wish to know. “Can user X access resource Y?”
So, I went about to build a small utility, most of which is part of this blog. SQL Server gives up tools to determine if a principal has access to an object, so I will introduce them, and then use them to check the “normal” security case, which is trying to determine a user’s rights to access to objects in the database. In a later blog, I will attempt to expand the security beyond object access to all of the various types of security a user may have that we need to track.
SQL Server provides us with two functions that can be used effectively to discover the rights that a user has to an object. It doesn’t tell us HOW the achieved this right, but it tells us if they do have it (how is a bit more complex, and something I plan to tackle some other day!) For example, say a user has SELECT access to TableY. This user could have it directly granted, inherited from a grant at the schema level, inherited through a user-defined role’s access, or even from a built-in role (database or server!)
This is fairly complex, but the general need I am aiming to solve today is to determine what a user has access to. The functions (available in the on-premises and Azure versions) allow you to determine what the current security principal has access to. They are:
- HAS_PERMS_BY_NAME – This is a scalar function that you can pass in various things like tables and columns, database and server rights.
- fn_my_permissions – This is a table valued function that you can use to return the rights you have for a certain object
The parameters for both include the securable you are checking, and securable class (and subclass for certain things like column level security. The securable class I will be focusing on in this blog is simply ‘OBJECT’, for objects in the database such as tables, procedures, etc. There are quite a few other ones, if you were interested in a complete view of all of the rights a user may have: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, ENDPOINT, FULLTEXT CATALOG, LOGIN, MESSAGE TYPE, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVER, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. For more details, check: HAS_PERMS_BY_NAME and fn_my_permissions.
To get started, I will build a small scenario that will allow me to grant rights, and then change rights around and verify that things match as desired.
CREATE DATABASE PermissionsTest; GO USE PermissionsTest; GO CREATE SCHEMA Demo; GO CREATE TABLE Demo.Table1 (Table1Id int); CREATE TABLE Demo.Table2 (Table2Id int); GO CREATE PROCEDURE Demo.Procedure1 AS SELECT Table1Id FROM Demo.Table1; GO --To this database, we will add a few logins users, and --give them direct access to several of the objects. CREATE LOGIN Login1 WITH PASSWORD = '12345'; CREATE USER User1 FROM LOGIN Login1; CREATE LOGIN Login2 WITH PASSWORD = '12345'; CREATE USER User2 FROM LOGIN Login2; CREATE LOGIN DboLogin WITH PASSWORD = '12345'; CREATE USER DboUser FROM LOGIN DboLogin; --Give User1 access to Table1 and procedure, User2 to Table2 GRANT SELECT ON Demo.Table1 TO User1; GRANT EXECUTE ON Demo.Procedure1 TO User1; GRANT SELECT ON Demo.Table2 TO User2; --put DboUser in the dbo role ALTER ROLE db_owner ADD MEMBER DboUser;
If you want to check if a different user other than the one logged in has access to an object, you will either need to:
- Login as that user
- Impersonate that user
As we typically will be working as a database owner/system administrator when configuring security, we will usually use impersonation to check the user’s rights. For example, if you want to ask SQL Server about a particular object, you might use HAS_PERMS_BY_NAME, in the following manner:
EXECUTE AS USER = 'User1';
--verify the user that you are effectively executing as,
--when testing to avoid mistakes
SELECT SUSER_NAME() AS server_principal,
USER_NAME() AS database_principal;
--There are additional parameters to check column level
--permissions if you need them
SELECT HAS_PERMS_BY_NAME('Demo.Table1','OBJECT','SELECT');
This returns:
server_principal database_principal
-------------------- ------------------------
Login1 User1
-----------
1
Now, checking to see if User1 can access Table2:
SELECT HAS_PERMS_BY_NAME('dbo.Table2','OBJECT','SELECT');
This returns:
———–
0
This function works great if you are checking one item, like from an application before trying to execute it. But if you want to see all of a user’s effective rights on an object, use fn_my_permissions.
For example, if we need to see User1 rights to Table1, we can execute (still in the security context of User1 from the previous code):
SELECT *
FROM fn_my_permissions('Demo.Table1', 'OBJECT') AS permissions;
This returns the following, with the row with subentity_name as ” being table permissions, and Table1Id being the column permission:
entity_name subentity_name permission_name
---------------- ----------------- ------------------------
dbo.Table1 SELECT
dbo.Table1 Table1Id SELECT
Now, let’s change back to the security context of the user that is a member of the db_owner role:
REVERT;
EXECUTE AS USER = 'DboUser';
SELECT permission_name
FROM fn_my_permissions('Demo.Table1', 'OBJECT') AS permissions
WHERE permissions.subentity_name = ''; --ignore column rights
This will show that the dbo user has every possible, logical, right to the table:
permission_name
--------------------------------
SELECT
UPDATE
REFERENCES
INSERT
DELETE
VIEW CHANGE TRACKING
VIEW DEFINITION
ALTER
TAKE OWNERSHIP
CONTROL
Finally, taking this to the final step for this blog, let’s get all of the object level rights for a user in the entire database:
REVERT;
To get all of the user’s rights, take all of the objects in the database, ignoring any objects that have a parent_object_id such as triggers or constraints, and use CROSS APPLY to execute the function for every object.
To make this easier to user, I am going to compile this into a view, called Utility.EffectiveSecurity (and give rights to it to Public, so every user can execute it.)
CREATE SCHEMA Utility;
GO
CREATE OR ALTER VIEW Utility.EffectiveSecurity
AS
WITH objects AS (
SELECT objects.name AS object_name,
schemas.name AS schema_name,
object_id, objects.type_desc AS object_type
FROM sys.objects
JOIN sys.schemas
ON objects.schema_id = schemas.SCHEMA_ID
WHERE objects.parent_object_id = 0
--no constraints that have the parent_object_id reference
--or triggers
)
SELECT object_type,
schema_name,
object_name,
permissions.permission_name
FROM objects
CROSS APPLY fn_my_permissions(schema_name +
'.' + OBJECT_NAME, 'Object') AS permissions
--I am ignoring column level permissions.
WHERE permissions.subentity_name = ''
--hide this object from view
AND NOT (objects.schema_name = 'Utility'
AND objects.object_name = 'EffectiveSecurity');
GO
--let every user check their permissions
GRANT SELECT ON Utility.EffectiveSecurity TO PUBLIC;
Now I can execute:
EXECUTE AS USER = 'User1';
GO
SELECT SUSER_NAME() AS server_principal,
USER_NAME() AS database_principal;
SELECT *
FROM Utility.EffectiveSecurity;
REVERT;
This returns:
object_type schema_name object_name permission_name
----------------------- --------------- --------------- ---------------------------
USER_TABLE Demo Table1 SELECT
SQL_STORED_PROCEDURE Demo Procedure1 EXECUTE
The real value here, is that what if we need to verify a change in security works.
The following example is similar to what I recently have done for our system (though a bit less derived!) Let’s take User1, and put the security that it currently has into two roles, one that can execute the stored procedure, one that can select from the table. We first execute:
EXECUTE AS USER = 'User1'; SELECT * INTO #preChangeSecurity --Or save to a permanent table FROM Utility.EffectiveSecurity; REVERT;
This saves off the current security, giving us the ability to compare User1’s rights before the security change. Now, make your changes to the security:
REVOKE SELECT ON Demo.Table1 FROM User1; REVOKE EXECUTE ON Demo.Procedure1 FROM User1;
Query from Utility.EffectiveSecurity as User1 and you will see no output as User1 has been stripped of all rights, other than the rights to select from the EffectiveSecurity view. Then execute the following:
--create a role to access Table1 (and “accidentally include Table2”) CREATE ROLE Table1Reader; GRANT SELECT ON Demo.Table1 TO Table1Reader; GRANT SELECT ON Demo.Table2 TO Table1Reader; --create the role to access Procedure1 CREATE ROLE Procedure1Executor; GRANT EXECUTE ON Demo.Procedure1 TO Procedure1Executor; --put User1 in the two roles ALTER ROLE Table1Reader ADD MEMBER User1; ALTER ROLE Procedure1Executor ADD MEMBER User1;
And now check to make sure you got the security right, in that it matches what User1 previously had for security:
EXECUTE AS USER = 'User1';
SELECT CASE WHEN EffectiveSecurity.object_name IS NULL
THEN 'DELETED'
WHEN #preChangeSecurity.object_name IS NULL
THEN 'NEW'
ELSE 'Same' END AS permission_disposition,
COALESCE(#preChangeSecurity.object_type, EffectiveSecurity.object_type) AS object_type,
COALESCE(#preChangeSecurity.schema_name, EffectiveSecurity.schema_name) AS schema_name,
COALESCE(#preChangeSecurity.object_name, EffectiveSecurity.object_name) AS object_name,
COALESCE(#preChangeSecurity.permission_name, EffectiveSecurity.permission_name)
AS permission_name
FROM #preChangeSecurity
FULL OUTER JOIN Utility.EffectiveSecurity
ON EffectiveSecurity.object_name = #preChangeSecurity.object_name
AND EffectiveSecurity.object_type = #preChangeSecurity.object_type
AND EffectiveSecurity.permission_name = #preChangeSecurity.permission_name
AND EffectiveSecurity.schema_name = #preChangeSecurity.schema_name
WHERE EffectiveSecurity.object_name IS NULL
OR #preChangeSecurity.object_name IS NULL;
REVERT;
You can see that I added a new permission in the mix when I was creating my roles, so this code outputs:
permission_disposition object_type schema_name object_name permission_name
---------------------- --------------- -------------- ------------------- ----------------------
NEW USER_TABLE Demo Table2 SELECT
Remove that right:
REVOKE SELECT ON Demo.Table2 FROM Table1Reader;
Run the previous query again, and you will see that everything is the same in the current security configuration for User1 as it was before we started. So we have achieved the desired effect by removing rights from User1, and giving back rights through a couple of roles.
The post Calculating a Security Principal’s Effective Rights appeared first on Simple Talk.
from Simple Talk https://ift.tt/2IeD9wl
via
