SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL Permissions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2fire
Starting Member

USA
9 Posts

Posted - 03/06/2013 :  12:07:34  Show Profile  Reply with Quote
We have two groups of report writers, the first has access to the entire database and the second is restricted. When using external reporting tools, built-in application security is bypassed and permissions maintained on individual tables. Is there a way for newly created tables to inherit permissions automatically in this scenario? Because this is an externally developed database, tables can be added at any time, without forewarning. If the 2nd group is restricted at the database level, then changing permissions on the table does not override these settings. My understanding is that permission hierarchy is server, database, schema and then table. I thought of using a schema, but don't have enough experience to know if this is the solution that will work.

Not sure I have explained the problem very well, but any assistance is appreciated.

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 03/06/2013 :  12:27:24  Show Profile  Reply with Quote
You can use schema to grant permissions. Below is a cooked up example (with not many comments, but if you go through that, you will see that granting permissions on the schema works as you described).
USE MyDB
GO

-- create two schemas
CREATE SCHEMA A;
GO
CREATE SCHEMA B;
GO

-- create a table in each schema
CREATE TABLE A.TestA(id INT);
CREATE TABLE B.TestB(id INT);
GO

-- create a user in the database (only public role, no other perms)
CREATE USER [TestPermUser] FOR LOGIN [TestPermUser]
GO

-- change context to the new user
EXECUTE AS USER = 'TestPermUser';
GO

-- try to select - both selects should fail.
SELECT * FROM A.TestA;
SELECT * FROM B.TestB;
GO

-- revert back to the original context.
REVERT;
GO

-- grant permission on schema a to the user
GRANT SELECT ON SCHEMA::A TO TestPermUser
GO

-- change context again to the new user
EXECUTE AS USER = 'TestPermUser';
GO

-- can select from A.TestA, but not from B.TestB
SELECT * FROM A.TestA;
SELECT * FROM B.TestB;
GO

REVERT;
GO
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/06/2013 :  12:39:11  Show Profile  Reply with Quote
Isnt it better to create a single set of login (one for each role) for these external reporting tools. Whenever reporting tool tries to connect it will use single login based on level of user ie restricted oor power. At the db level all of the requests will come through this single login. And its only required to set the permissions for table once for these logins. Any new user at application will be effectively mapped to one of these logins in db.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

2fire
Starting Member

USA
9 Posts

Posted - 03/06/2013 :  18:12:14  Show Profile  Reply with Quote
Thanks for the replies.

Here is what I don't understand about the schema option.

I don't want to create any new tables. Rather, users will report from existing tables. These tables belong to the default dbo schema. If I change this, what is the impact on the user associated wtih the application? Is it just a matter of adding the application login to the schema permission?

What about new tables added to the database by the software developer? Do they need to be manually added to the schema once created?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 03/06/2013 :  19:15:47  Show Profile  Reply with Quote
quote:
If I change this, what is the impact on the user associated wtih the application? Is it just a matter of adding the application login to the schema permission?

That would need a lot of testing and verification to make sure that the change in schema does not break anything. If it is a vendor database, I would recommend that you DO NOT do this.

quote:
What about new tables added to the database by the software developer? Do they need to be manually added to the schema once created?

When a new table is created, it is created in some schema. So if they have permissions to create the tables only in the schema set up specifically for them, then they can create only in that schema.

Given that it is a vendor database (and also because I don't understand the workflow completely), it would be great if Visakh's suggestion would fit your needs. Is that a possibility?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000