Role Permissions Filtered View t-sql
Is it possible to filter the content of a t-sql view based on SELECT GRANTS assigned to the database roles of end users without generating SELECT permissions exceptions?
If so how?
Pseudo:
CREATE TABLE Beer(a(x), b(y)); GRANT SELECT ON Beer to BeerOnlyRole;
CREATE TABLE Wine(a(x), b(y)); GRANT SELECT ON Wine to WineAndBeerRole;
GRANT SELECT ON Beer to WineAndBeerRole;
CREATE VIEW SimpleAlcoholSearch
(
SELECT a AS BrandName
,b AS Strength
FROM Beer
UNION
SELECT a AS BrandName
,b AS Strength
FROM Wine
)开发者_高级运维
GRANT SELECT ON SimpleAlcoholSearch to BeerOnlyRole;
GRANT SELECT ON SimpleAlcoholSearch to WineAndBeerRole;
....
AS BeerOnlyRole : SELECT * FROM SimpleAlcoholSearch : BEER1 1% BEER2 2%
AS WineAndBeerRole : SELECT * FROM SimpleAlcoholSearch : BEER1 1% BEER2 2% WINE1 10% WINE2 11%
Thanks for reading this...
I solved this problem with a quick and dirty check on various catalog views for each table:
SELECT a AS BrandName
,b AS Strength
FROM Beer
WHERE 1 =
(
SELECT TOP 1 1
FROM sys.database_permissions sy_dpe
JOIN sys.objects sy_o ON sy_dpe.major_id = sy_o.object_id
JOIN sys.schemas sy_sc ON sy_o.schema_id = sy_sc.schema_id
JOIN sys.database_principals sy_dpr ON sy_dpe.grantee_principal_id = sy_dpr.principal_id
JOIN sys.database_role_members sy_drm ON sy_drm.role_principal_id = sy_dpr.principal_id
JOIN sys.database_principals sy_dpr2 ON sy_dpr2.principal_id = sy_drm.member_principal_id
WHERE sy_dpr2.name = SYSTEM_USER
AND sy_o.name = 'Beer'
AND sy_sc.name = '[n]'
AND sy_dpe.type = 'SL'
AND sy_dpe.state = 'G'
)
UNION
....
Anyone got a tidier approach?
Thanks
精彩评论