Assume I have two databases, call them 'A' and 'B' running in the same
instance of SQL Server 2008.

I want to lock down 'A' so that no one, not even an admin, can get in
and look around.

I want to grant SELECT on a number of views in 'A' to anyone who has
successfully connected to database 'B'.

Is it possible to do something like this with certificates? Of course
_someone_ has to have access to 'A', and if that access required a
certificate, it would be a neat way to handle the problem.

I am not interested in encrypting the data itself, just keeping people
out of database 'A', while allowing people connected to database 'B'
to SELECT against objects from database 'A'.


Thanks,

Bill

bill (billmaclean1 (AT) gmail (DOT) com) writes:
If you with "admin" mean someone who is just a Windows admin, well
you can drop the login BUILTIN\Administrators (and on SQL 2008 it is
not added by default.)

However, he could still stop the service and copy the database files
to an instance where he is sysadmin.

If you mean some who is member of the fixed server role "sysadmin" in
SQL Server, just forget about it.

If you have DB chaining enabled, and the databases have the same owners,
it is doable.

With stored procedures, yes. But as I recall, you cannot sign a view.
The best you can do is a multi-statement function, but it would have
performance implications.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erland, thanks for the info.

I probably should have made the question more open-ended and just
described the problem, instead of hinting at a potential (and maybe
not feasible or not very good) solution.

I want database ‘A’ to be a “black box” that performs operations
related to database ‘B’. ‘A’ has some control data, and a number of
stored procs, views, and table-valued functions that help the users of
database ‘B’ do their jobs. The objects in ‘A’ have to act
(sometimes) on the tables in database ‘B’. There is a lot of logic
and intellectual property in ‘A’ that I need to protect from prying
eyes.

I think that the WITH ENCRYPTION option is basically worthless for
objects, because a number of tools can easily break that encryption.

Very simplified example (Assume databases ‘A’ and ‘B’ are in the same
instance of SQL Server, and sorry that I can't boldface, notice that
the view is in 'A' but the tables are in 'B') :

I have a view in ‘A’ defined thus:

CREATE VIEW A.schema.ip_view AS
SELECT
x.column_1
,y.column_2
FROM
B.schema.TABLE_X AS x
INNER JOIN
B.schema.TABLE_Y AS y
ON x.col1 = y.col5

1. I want the user of database ’B’ to be able to SELECT from
A.schema.ip_view and use it in his/her own queries with other tables
in database ‘B’.
2. I don’t want to trash the optimizer’s ability to make the join work
fast.
3. Ideally, a display of the plan would just show joins to
A.schema.ip_view, not the constituent objects of that view.
4. Some stored procs in database ‘A’ will make/update tables and dump
them into database ‘B’. (I could avoid this if it is a big problem,
but it would be nice to do)
5. I don’t want the user of database ‘B’ to know anything about the
workings of view A.schema.ip_view. To that user, A.schema.ip_view
should just be a “black box” that returns data. The user shouldn’t be
able to see the source A.schema.ip_view, nor object dependencies,
etc.
6. I don’t want to make it difficult to develop in ‘A’ for authorized
developers.
7. I want to be able to remotely connect to ‘A’ to create or alter
objects as needed.

I thought that locking down access to database ‘A’ would be the best
way, and I could just grant SELECT privileges on the appropriate
objects in database ‘A’, but maybe there is a better or easier way to
accomplish the goal.

bill (billmaclean1 (AT) gmail (DOT) com) writes:
What you most of all need is a license agreement that controls what
users can do and not.

That is true. However, it can be a supplement to a license agreement
and act as a sign saying "no trespassing".

Then again, using encryption means a lot of hassle for anyone who
needs to do performance tuning.

As I mentioned this can be addressed with DB-chaining. However, this is
not on by default, and the DBA may not like enabling it.

Hm, there is a SHOWPLAN permission on database level. Really how this
works if a user that has this permission runs a crossdb query to a
database where he does not have this permission, I don't know.

But of course, this does not apply to admin users.

As long as DB-chaining is enabled, this should work.

You need to revoke VIEW DEFINITION to the users. Although it would
probably be easier to put objects in B that invokes those in A.

Just grant those people the necessary permissions.

Just keep in mind that you can never lock out people with admin rights.
Regular non-priv users you can keep out. Then again, it is not clear
why you have two databases in the first place. You can put your objects
in the same database, in different schemas. Then you grant users necessary
permissions in one schema only, and they can access the other objects
through objects in the first schema thanks to ownership chaining.

As for SHOWPLAN, the easist is to deny them this permission on schema
level.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx