August 23, 2017 at 8:56 am
Hi,
I'd like to implement row LevelSecurity.
Users are mapped to one (or more!!!) Groups (either AD Groups or ServerRoles, does not matter which to use)
Each Group should only see the SalesFacts for the corresponding Company.
How can this be achived?
I tried the following, but it did not workound because of binding Errors to sysObject Tables:
use master
--create new database
drop database if exists [ttt_RowLevelSecurity];
create database [ttt_RowLevelSecurity];
GO
USE [ttt_RowLevelSecurity]
GO
--Create users (later, there will be more Users of course
CREATE USER [UserCompanyA] WITHOUT LOGIN;
CREATE USER [UserCompanyB] WITHOUT LOGIN;
ALTER ROLE [db_datareader] ADD MEMBER [UserCompanyA]
ALTER ROLE [db_datareader] ADD MEMBER [UserCompanyB]
create role AllowCompanyA
create role AllowCompanyB
--add Users to roles (IMPORTANT: Later there will be users who belong to more than one Role!!)
ALTER ROLE AllowCompanyA ADD MEMBER [UserCompanyA]
ALTER ROLE AllowCompanyB ADD MEMBER [UserCompanyB]
create table tbSales (Company varchar(50), Turnover int)
insert into tbSales values('CompanyA', 100)
insert into tbSales values('CompanyB', 80);
GO
--- Here's what I tried, but it did not work because of binding Errors:
-- Created a function to get all roles for the User
-- due to the fact it uses Sys.objects schemabinding is not allowed
create function dbo.GetGesellschaftFromRole()
returns table
as
return SELECT right(r.name, 8) as CompanyOK
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
INNER JOIN sys.database_principals AS u
ON u.principal_id = m.member_principal_id
WHERE u.name = SYSTEM_USER and r.name in ('AllowCompanyA', 'AllowCompanyB')
/*
here comes the error:
Msg 4513, Level 16, State 2, Procedure fn_securitypredicate, Line 5 [Batch Start Line 56]
Cannot schema bind table valued function 'Security.fn_securitypredicate'. 'Security.GetGesellschaftFromRole' is not schema bound.
*/
create FUNCTION Security.fn_securitypredicate(@Company AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @Company in (select CompanyOK from dbo.GetGesellschaftFromRole()) ;
--- this would be next if the above would work....
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE dbo.fn_securitypredicate(Company)
ON dbo.tbSales
WITH (STATE = ON);
August 23, 2017 at 9:30 am
That part where you have RIGHT(r.name, 8) is likely the reason. You can't schemabind to a function on a field.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 23, 2017 at 10:39 am
There are 2 issues
1. You can not create a function with SCHEMABINDING which reference function which is not SCHEMABINDING. so function 2 cant be created since function 1 is not SCHEMABINDING.
2. you cant create SCHEMABINDING function which is using system objects. so even if you change the first function to SCHEMABINDING, you cant create it
August 24, 2017 at 12:45 am
Avi1 - Wednesday, August 23, 2017 10:39 AMThere are 2 issues
1. You can not create a function with SCHEMABINDING which reference function which is not SCHEMABINDING. so function 2 cant be created since function 1 is not SCHEMABINDING.
2. you cant create SCHEMABINDING function which is using system objects. so even if you change the first function to SCHEMABINDING, you cant create it
yes, exactly the Point 2 is the Problem. is there any Workaround to get Information about roles /AD Groups of the current user without using system-objects or any other stuff that Permits schemabinding?
August 24, 2017 at 1:41 pm
Andreas Michael - Thursday, August 24, 2017 12:45 AMAvi1 - Wednesday, August 23, 2017 10:39 AMThere are 2 issues
1. You can not create a function with SCHEMABINDING which reference function which is not SCHEMABINDING. so function 2 cant be created since function 1 is not SCHEMABINDING.
2. you cant create SCHEMABINDING function which is using system objects. so even if you change the first function to SCHEMABINDING, you cant create ityes, exactly the Point 2 is the Problem. is there any Workaround to get Information about roles /AD Groups of the current user without using system-objects or any other stuff that Permits schemabinding?
As per my understanding you can achieve only through system tables. there is a workaround, you can dump the data from that query in a table (may be couple of times a day) and use that table in you function.
August 28, 2017 at 2:37 am
As suggested by Avi1 I put the result of dbo.GetGesellschaftFromRole() to a Table wich can be accessed with schemabinding.
Works, a littlebit complicated because of the need to refresh the table, but OK.
After investigating, I found another way to solve my Problem.
with the help of "is_rolemember"
the function Security.fn_securitypredicate in the above example is replaced by the following; works fine:
CREATE FUNCTION Security.fn_securitypredicate (@Company AS SYSNAME)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS fn_securitypredicate_result
WHERE (
@Company = 'CompanyA' AND IS_ROLEMEMBER('AllowCompanyA') = 1
)
OR (
@Company = 'CompanyB' AND IS_ROLEMEMBER('AllowCompanyB') = 1
)
June 21, 2019 at 11:29 am
For users who are members of db_owner group IS_ROLEMEMBER ignores the DB Role membership and falsely returns '0', so unfortunately it's useless in those cases. What if I want to apply Row-Level Security also for the db_owner users?
USE [master]
GO
CREATE DATABASE [Test_IS_ROLEMEMBER]
GO
USE [Test_IS_ROLEMEMBER]
GO
CREATE ROLE [Test_Role]
GO
CREATE USER [DOMAIN\AdGroupName] FOR LOGIN [DOMAIN\AdGroupName]
GO
ALTER USER [DOMAIN\AdGroupName] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [Test_Role] ADD MEMBER [DOMAIN\AdGroupName]
GO
EXECUTE AS LOGIN = 'DOMAIN\UserName'
SELECT
SUSER_SNAME()AS [SUSER_SNAME]
,IS_MEMBER('DOMAIN\AdGroupName') AS [AdGroupName]
,IS_ROLEMEMBER('db_owner') AS [db_owner] -- this shows 1
,IS_ROLEMEMBER('Test_Role') AS [Test_Role] -- this shows 0 even though I made DOMAIN\UserName a member of Test_Role (???)
REVERT
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply