Public, Db_denyreader,db_denyDataReader Permissions

[JonathanC]

I have the following scenario:

Co.A maintains a database dbA and he wants Co.B to have Select permissions on a view vwForB.
Co.B is not allowed to see the structure of tables,stored procedures, views and other objects on dbA.

I assigned db_denywriter,db_denyreader on Co.B and have assigned select permission on public on vwForB on dbA.

Why I can't Co.B execute vwForB...the error says...Select Permission denied on object 'vwForB', database 'dbA', owner 'dbo'.

How can I solve this problem?

Thanks.

 

[tlbroadbent]

You told SQL that Co.B couldn't read data in Co.A DB when you assigned the CO.B user to the db_denyreader role. As I explained in another post, If you create a view in the Co.B DB that selects from the view in CO.A's DB then CO.B should be able to see select from that view.

I've not tried assigning the Select Permissions on the Co.A view to Public. That may work. I know that granting select permissions to the user works. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JonathanC]

I've already granted select permission to Co.B on CO.A's View.

Then I tried creating another db for Co.B that contains the a select to the view of Co.A using select dbA.dbo.vwForB.

It still gives me an error of select permission denied on object "vwForB", database "dbA", owner dbo.

What are my options....what do I need to check?

 

[tlbroadbent]

Did you grant Select permission on the views on both databases to the Co.B user? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JonathanC]

Yes...I did put select permissions on public and the co.B as user at the same time in both databases.

Based on your recommendation, I put denywriter,denyreader on Co.B in dbA. But Co.B is a dbo in dbB where the view is contained.

I am thinking, does role override object level permissions?
Because I have denywriter,denyreader and at the same time, gave permission on an object (view) to coB.

 

[tlbroadbent]

It is apparent that the scenario I proposed doesn't work. I've tested it on the servers here at work. I set up the same views and users, roles and permissions. The user cannot access the view. I apologize for sending you off in that direction.

I tested the scenario at home where MSDE (SQL 2000 version) is installed. It worked there. At least, I think it did. I'll verify that the setup is is correct.

I've done some research and the database deny roles cannot be overridden by individual or other role permissions. You'll need to remove the user from the denyreader role.

If the Public role has not been granted permissions to view objects, the user will not be able to view data in tables or views. Explicitly DENYing permission shouldn't be required. However, if public has view permissions, I'm not aware of a way to prevent viewing data except DENYing the permissions.

You can use the following script to DENY permissions on all user tables and views in DB_A. Run the script as is to print a list of the actions that will be taken. Make sure it does what you want and need. Uncomment the Exec to run the generated script.

Let me know how it goes and if you ahve any questions. You can contact me via Email if you prefer.

tlbroadbent@hotmail.com

--------------------------------------

-- Script to generate and execute DENY ALL on all
-- user tables and views for a designated user.

Declare @sql varchar(8000)

SET @sql=''

SELECT
@sql=@sql + 'DENY ALL ON ' +
name + ' TO UserA' + char(10)
From sysobjects
Where type in ('u','v')

print @sql

--exec(@sql) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JonathanC]

Denying to view data is not an actual problem for me.

The deny all statement worked. But it went back to my old problem. It's not only the data that is supposed to be exclusive (except the view), the stored procedures and functions,table structures and other object codes should not be seen by Co.B.

In the Deny All statement, though you deny a user on all objects but didn't give him the db_denyreader role...he can still view the stored procedures,table structures and functions.

I am already thinking of other options...I know replication would be a good isolation solution...but I am curious if there's a better way.

 

[RickCole]

I think I can see how you could get round this problem if your databases were on different servers. Don't suppose you want to go down that road do you??

But being on the same server is a little trickier. Interesting problem though. I'll see if a good nights sleep generates any new ideas.

Rick.

 

[JonathanC]

I am stuck on it right now...

I put this problem on my waiting list till somebody could come up with a good solution...

I am already considering replication...if that's the only solution to this problem...

But I am not giving up until I got the perfect reason why it doesn't work.

Any other suggestions?

 

[tlbroadbent]

You can compile Views and Stored Proceudres using the With Encryption option. No one will be able to read the code. You'll need to save a plain text copy of the SQL in a repository of some sort or you won't be able to read or modify the code. I don't find this to be a very appealing solution but offer it as a possibility.

Another thing we do is restrict who has Enterprise Manager and Query Analyzer. Users don't get to use these tools.

It is very clear that SQL Server exposes the structure of tables even if a user is denied access to the data. This is a a major fault that may be corrected in future releases if enough people let MS know they want it. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

Jonathan if I’ve understood your post correctly I think I’ve worked out a solution using Linked Servers.

Linked servers can be defined to point back to the themselves which MS define as a Loopback Linked Server

Take the following scenario

Your server is called NT01. Co.A has a database called DataA and Co.B has a database called DataB

Set up a Login say called LNK using SQL Server authentication with an appropriate DBA password. This login should have db_datareader access to Co.A database DataA

Run the following SQL to link the server to itself

EXEC sp_addlinkedserver @server = N'NT01_LINK',
@srvproduct = N' ',
@provider = N'SQLOLEDB',
@datasrc = N'NT01',
@catalog = N'DataA'

Where @server is the logical name you wish to call your linked server and @datasrc is the actual name of the server.


Run the following SQL to add a linked server login

EXEC sp_addlinkedsrvlogin 'NT01_LINK', 'false', NULL, 'LNK', 'Password'

Where NT01_LINK is the logical name of your linked server. LNK is the login which local users will be mapped through and Password is the appropriate DBA password you supplied earlier to user LNK.

Now create the required views in Co.B’s DataB database using fully qualified tablenames using the logical linked server name:

Create view vwForB
As
Select * from NT01_LINK.DataA.dbo.tablename

Users of Co.B database DataB will now be able to select from the views held in their own database and get the required results. They will be going through the mapped user LNK (which is secure with a DBA password) to the database DataA. They will not be able to see any tables etc with in Co.A’s DataA database.

The above scenario does work does work and I think I’ve put everything on here (barring any typos) as to how I worked round the problem. Looks a bit complicated to start with but once you have loopback linked server and login in place it will work for any future views you may wish to add.

One word of caution is the following: Loopback linked servers cannot be used in a distributed transaction. Attempting a distributed query against a loopback linked server from within a distributed transaction raises an error:

Regards

Rick

 

[RickCole]

Just seen my last post and forgot to take off the Emoticons/Smileys. What it should say where the simley face
is being displayed is @provider = N'SQLOLEDB'

Rick.