Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL Permissions

Author  Topic 

2fire
Starting Member

9 Posts

Posted - 2013-03-06 : 12:07:34
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-06 : 12:27:24
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

52326 Posts

Posted - 2013-03-06 : 12:39:11
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

9 Posts

Posted - 2013-03-06 : 18:12:14
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-06 : 19:15:47
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
   

- Advertisement -