2

We have the 2 databases.

  • Reporting
  • HR

There are some users who want to extract data from HR database. But we don't want to give direct access to that HR database.

So, we created the special views in Reporting database and the users have dbowner right for that Reporting database.

But, when they try to run the query, the error message is showing... 

The server principal "test" is not able to access the database "HR" under the current security context.

When I grant them as the db_datareader reader for HR database, it's all fine and they can run the query. But it breaks the security and we don't want them to get access to the HR database directly. That's why we made the special views in Reporting database.

How can I enable those users to run the view/query without giving direct access to the HR database?

Solomon Rutzky
  • 68,731
  • 8
  • 152
  • 292
TTCG
  • 123
  • 5
  • try to execute the following command this may help you: GRANT SELECT ON dbo.MyViewName TO username – Ahmad Abuhasna Dec 17 '14 at 16:21
  • The user has already got the db_owner right to the database where the view sits. – TTCG Dec 17 '14 at 16:26
  • I would just give limited access to a view in the HR database, it's no less secure than your proposed method and a lot easier. – LowlyDBA - John M Dec 17 '14 at 16:31
  • Yeah, it will work. But what I am confused is that... I have always known that we create Views to hide the sensitive columns/rows/data in the original table. But if we have to give the READ access to those tables which the view is using, doesn't it break the original idea of the views? – TTCG Dec 17 '14 at 16:36
  • 1
    TTCG: @JohnM is saying to create the Views in the HR database and give SELECT permission on those, not to the underlying tables. Your views are just in the wrong database as permissions do not transfer across DB lines, at least not by default. I did mention this in my answer but proposed a better method of separation. – Solomon Rutzky Dec 17 '14 at 17:37

1 Answers1

5

Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-wise to other databases. Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft.

Instead, you should create a certificate that can be used to sign modules and act as a proxy for permissions between the databases. The catch is that they work on stored procedures, function (non-Inline TVFs), assemblies, and triggers. But this method allows for those Users that should not have any access to the [HR] database to actually not have any access themselves: it is the code (i.e. the module) that has the access.

The basic concept:

  • Create certificate in [Reporting]
  • Create stored procedure in [Reporting] (that selects directly from tables in [HR])
  • Use ADD SIGNATURE to sign the new stored procedure using the certificate
  • Backup the certificate to a file
  • Create certificate in [HR] FROM the backup file you just created (the certificate needs to be identical in both places!)
  • Create user in [HR] FROM the certificate
  • GRANT SELECT permission on appropriates tables in [HR] (i.e. the ones references in the new stored procedures in [Reporting]) to the new certificate-based user
  • Go home and enjoy the rest of the day :)

Some resources:

OR, if you prefer to stick with the simplicity of using views, then your views are in the wrong database. You need to instead:

  • Add the appropriate User(s) to the [HR] database so that there is basic access, but do not grant any permissions to any tables (and hopefully no group or role membership will imply such permissions)
  • Create the desired Views in the [HR] database
  • GRANT SELECT only to these Views, and not to anything else, to these Users
Solomon Rutzky
  • 68,731
  • 8
  • 152
  • 292
  • "Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft." I have not seen in any of Microsoft's documentation that they discourage use of db chaining. Do you have a source for this? – ARich Sep 18 '18 at 18:04
  • @ARich I could have sworn that one of the linked documents specifically stated that module signing is the preferred / more secure approach. I can't find that at the moment, but you can start to get a sense of things from the "threats" section here. Also, I address it here: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining. – Solomon Rutzky Sep 18 '18 at 19:21
  • @ARich I will keep looking for where I saw that... – Solomon Rutzky Sep 18 '18 at 19:22
  • Thank you for the links! The first link I've seen before and it describes potential threats, but I'm really interested to see documentation where Microsoft actually discourages the use of db chaining. It seems to me that, like most things, db chaining is a feature that (when implemented improperly or lazily) can open a database to threats, but the same could be said of many features in SQL Server. Perhaps I'm merely too naive here, but that's why I'm hoping you can find a link to that documentation. – ARich Sep 18 '18 at 19:43