I have a SQL Server database (let's call it DB1) with a few stored procedures, and these stored procedures are executed by my Web API (that will be consumed by my mobile app later).

These stored procedures get data from DB1 and from another database (let's call it DB2).

I created a SQL Server login to be used by the Web API with public and dbcreator server roles and only public database role in both DB1 and DB2.

Then I followed this guide and granted Execute permission on the stored procedures for the login.

But when I try to execute my Web API method that uses one of the procedures, I get an exception:

The SELECT permission was denied on the object 'APP_USERS', database 'DB2', schema 'dbo'.

So, do I have to grant the permissions on DB1 and DB2 for this to work, even if I already granted it on the stored procedure? Or am I just granting the wrong permission to the stored procedure?

Note: I used to grant permissions directly on the database for each login, because all applications consulting the database were internal of our enterprise (until now). But this mobile app will be public. I talked to a security expert who told me that this practice is insecure, and advised me to grant the permissions only on the stored procedures.

CodePudding user response：

It looks like you've been introduced (whether you wanted to be or not!) to something called database permissions chaining. At a high level, you're allowed to have objects in your own database reference other objects and only have to grant permissions on the referencing object so long as both the referenced and referencing object are owned by the same database principal (i.e. user). For example, if I have a table that I own, I can write a stored proc doing whatever (say a SELECT) against the table and then grant execute on the proc to another user. When the other user goes to execute the proc, permission chaining kicks in and says "the proc and the table are owned by the same user - the execute permission is sufficient"

But! By default, the permissions chain is broken when the referenced object is in another database. Why? I can only speculate as to creators' intent, but imagine a multi-hosted database server and I'm an malicious actor. If I have my own database, I could write a proc that says select * from OtherDb.dbo.Users;, grant permissions on that proc and exfiltrate data from other users' databases.

There are a couple of ways around this:

1. You can enable cross db ownership chaining at the server level. I don't recommend this, but it is an easy button out of the problem you have.

2. You can grant permissions on the objects referenced in the procedure. This may be okay, depending on why you're gating data access through stored procedures (which, full disclosure, I like to do in general). This would be a simple grant select on dbo.APP_USERS to «some DB2 principal - a user or group»;. The downside here is that the principal to whom the permissions are granted can do any select on the table now, thereby bypassing the proc.

3. You can sign your stored procedures. This is a little more involved, but is the more secure option. It involves creating a certificate or asymmetric key in both databases, creating a user based on the same, granting permissions to that user, and finally calling add signature on the related procs. You'd think you're done, but you'll need to re-apply that signature any time someone changes the procedure definition. Why? Let's say that you sign the proc today but then I change it to do something unintended (either innocently or maliciously). If the signature persisted through an alter procedure, the original proc could be a Trojan horse.

Here is a rough sketch of the module signing dance.

use Db1;

create certificate ModuleSigningCert ...;
add signature to dbo.YourProc by certificate ModuleSigningCert;

use Db2;
-- import ModuleSigningCert - either by backup certificate/create certificate
-- you technically only need the public key portion

create user SigningUser from certificate ModuleSigningCert;
grant select on dbo.YourTable to SigningUser;

For what it's worth, I don't know that "database will be accessed by a public app" necessarily means "and now we need to do cross-database stuff". It may, but it may not. For instance, if the public app still accesses the database through an internal application server, you're not getting much security-wise with the multi-database setup.