f



SQL 2008 Enterprise - Deny access to database for even administrator: How To?

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
0
12/3/2009 12:18:17 AM
comp.databases.ms-sqlserver 14565 articles. 0 followers. janinepelletier (108) is leader. Post Follow

3 Replies
630 Views

Similar Articles

[PageSpeed] 48

bill (billmaclean1@gmail.com) writes:
> 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.

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. 
 
> I want to grant SELECT on a number of views in 'A' to anyone who has
> successfully connected to database 'B'.

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

> Is it possible to do something like this with certificates?  

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@sommarskog.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/prodinfo/previousversions/books.mspx

0
Erland
12/3/2009 10:22:33 PM
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 =91A=92 to be a =93black box=94 that performs operations
related to database =91B=92.  =91A=92 has some control data, and a number o=
f
stored procs, views, and table-valued functions that help the users of
database =91B=92 do their jobs.  The objects in =91A=92 have to act
(sometimes) on the tables in database =91B=92.  There is a lot of logic
and intellectual property in =91A=92 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 =91A=92 and =91B=92 are in the s=
ame
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 =91A=92 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 =3D y.col5

1.	I want the user of database =92B=92 to be able to SELECT from
A.schema.ip_view and use it in his/her own queries with other tables
in database =91B=92.
2.	I don=92t want to trash the optimizer=92s 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 =91A=92 will make/update tables and dump
them into database =91B=92.  (I could avoid this if it is a big problem,
but it would be nice to do)
5.	I don=92t want the user of database =91B=92 to know anything about the
workings of view A.schema.ip_view.   To that user, A.schema.ip_view
should just be a =93black box=94 that returns data.  The user shouldn=92t b=
e
able to see the source A.schema.ip_view, nor object   dependencies,
etc.
6.	I don=92t want to make it difficult to develop in =91A=92 for authorized
developers.
7.	I want to be able to remotely connect to =91A=92 to create or alter
objects as needed.

I thought that locking down access to database =91A=92 would be the best
way, and I could just grant SELECT privileges on the appropriate
objects in database =91A=92, but maybe there is a better or easier way to
accomplish the goal.
0
bill
12/3/2009 10:34:31 PM
bill (billmaclean1@gmail.com) writes:
> 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.

What you most of all need is a license agreement that controls what
users can do and not.
 
> I think that the WITH ENCRYPTION option is basically worthless for
> objects, because a number of tools can easily break that encryption.

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.
 
> 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'.

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.

> 3.     Ideally, a display of the plan would just show joins to
> A.schema.ip_view, not the constituent objects of that view.

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.

> 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)

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

> 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.

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.

> 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.

Just grant those people the necessary permissions.

> 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.

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@sommarskog.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/prodinfo/previousversions/books.mspx

0
Erland
12/4/2009 10:33:10 PM
Reply: