July 13, 2009 at 12:11 am
Hi,
I'm trying to set up access for a group of people at my organisation to be able to have SELECT access to data within a database.
I don't want them to have access to all the columns in certain tables, so have created a series of views (I call them 'z-views', e.g. zName covers a table called Name) that they can then use to query instead of the tables, and return the data they require.
The dbuser belongs to custom-created schema, 'Reports'. The user is also a member of a db role, 'Reporting', to which the various permissions have been granted, or denied.
I don't mind if they create their own views to help with their extracts, nor do I mind if they create their own tables. Because the user belongs to the schema 'Reports', their objects should be separate to the objects that I'm trying to protect, which all belong to 'dbo'.
What I have so far:
CREATE SCHEMA Reports
CREATE USER [Reports] FOR LOGIN [Reports] WITH DEFAULT_SCHEMA = Reports
CREATE ROLE Reporting AUTHORIZATION db_securityadmin
exec sp_addrolemember @rolename = 'Reporting', @membername = 'Reports'
GRANT CREATE VIEW TO Reporting
GRANT CREATE TABLE TO Reporting
GRANT ALTER ON SCHEMA:: Reports TO Reporting
GRANT SELECT ON SCHEMA:: Reports TO Reporting
DENY ALL, VIEW DEFINITION ON [dbo].[Name] TO Reporting;
DENY ALL ON [dbo].[zName] TO Reporting;
GRANT SELECT, VIEW DEFINITION ON [dbo].[zName] TO Reporting; -- I want them to be able to select and view the definition of the view, but nothing else.
Logging in as 'Reports', I am unable to see, or select from, the table 'Name'. I can, however, see the definition of and select from zName. I cannot alter the view in any way.
However, I CAN create a view called 'vName' that appears as 'Reports.vName' that selects all the columns from the Name table:
if exists (select name from sys.views where name = 'vName')
drop view vName
go
create view vName
as
select top 10 *
from dbo.name
go
select quotename(schema_name(schema_id), '[') + '.' + quotename(name, '[') as ObjectName
from sys.objects
where name = 'vName'
select * from vname
What permission do I need to deny to the users to stop them from being able to get round my security blocks?
Thanks for any help; it's probably something very simple!!!! 🙂
Simon
July 13, 2009 at 7:09 am
Because of ownership chaining, I'm not sure that you can. Permissions are going to be checked at the first object (the view) and if the owner of that view is the same as the table, permissions will not be checked against the table. If you haven't explicitly defined owners, then the owner of the schema to which the object belongs is what SQL Server will use as the owner. So if the owner of the dbo schema and the owner of the Reports schema are one in the same, you've got the ownership chain.
What you can do, but this will allow select against the table... break the ownership chain. Create a user WITHOUT LOGIN that exists only within the database. Make it the owner of the Reports schema. Within the dbo schema for the views you create, use ALTER AUTHORIZATION to change the owner of the view to that same owner. Then, grant explicit access at the column level to the base table against the Reporting role. That will ensure they can create their own views, especially based off of your views, and even against the base table, but they will only have access to the columns you want.
As a proviso, test all of this in non-prod first. Especially when you change ownership of the schema. I don't believe it drops permissions on the objects, but I don't remember for sure.
K. Brian Kelley
@kbriankelley
July 13, 2009 at 8:59 am
I am not able to reproduce you problem, so you must have run some extra SQL that is causing the problem.
Set up an environment:
CREATE TABLE dbo.Foo
( FooIdINT IDENTITY(1,1) NOT NULL
, FooNameVARCHAR(35)NOT NULL
, FooSecret VARCHAR(35)NOT NULL
)
GO
CREATE SCHEMA Reports AUTHORIZATION dbo
GO
CREATE VIEW Reports.Foo_view WITH schemabinding AS
SELECTFooId
,FooName
FROMdbo.Foo
GO
CREATE VIEW dbo.Foo_view WITH schemabinding AS
SELECTFooId
,FooName
FROMdbo.Foo
GO
CREATE USER Reports FOR LOGIN [Carl.Federl] WITH DEFAULT_SCHEMA = Reports
CREATE ROLE Reporting AUTHORIZATION dbo
EXEC sp_addrolemember @rolename = 'Reporting', @membername = 'Reports'
GO
GRANT SELECT , VIEW DEFINITION ON Reports.Foo_view TO Reporting
GO
GRANT SELECT , VIEW DEFINITION ON dbo.Foo_view TO Reporting
GO
Now, run a test:
EXECUTE AS USER = 'Reports'
GO
SELECT TOP 1 * FROM Reports.Foo_view
GO
SELECT TOP 1 * FROM dbo.Foo_view
GO
SELECT TOP 1 * FROM dbo.Foo
go
revert
The messages are ( the third message is the expected error when attempting to select from the underlying table)
(1 row(s) affected)
(1 row(s) affected)
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Foo', database 'SSC', schema 'dbo'.[/quote[
SQL = Scarcely Qualifies as a Language
July 13, 2009 at 8:21 pm
Thanks Brian, Carl,
Using a combination of the information that you provided, I realised that what I wanted to do was as simple as:
CREATE USER [Reports] FOR LOGIN [Reports] WITH DEFAULT_SCHEMA = Reports
CREATE SCHEMA Reports AUTHORIZATION Reports --Auth as Reports was the key piece of information that I had missed.
GO
CREATE ROLE Reporting AUTHORIZATION db_securityadmin
GO
exec sp_addrolemember @rolename = 'Reporting', @membername = 'Reports'
GO
GRANT CREATE VIEW TO Reporting
GRANT CREATE TABLE TO Reporting
GRANT SELECT, VIEW DEFINITION ON [dbo].[zName] TO Reporting;
My users can now use the 'dbo' views that I want them to use, but not the underlying 'dbo' tables, AND they can create their own views and tables as well, without directly referencing or affecting the dbo tables.
Now if I could only get the SQL IFCode shortcut working for me on this site, perhaps I can display my code in a nicer way! A question for a different forum...
Thanks for your help once again.
Simon
April 2, 2012 at 1:47 pm
I have a similar situation here. Instead of col limitation, I have row limitation which is to limit different user to only see their subset of data. Another complexity is my base table is in DB1 (vendor DB) and the views have to be created in DB2 (company DB) using the data/base table in DB1.
How do I limit the user to see the base table in DB1 but able to select the view in DB2?
Thanks in advance.
Carol
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply