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

  • Permalink
  • submit to reddit
  • Email
  • Follow


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
Reply billmaclean1 (83) 12/3/2009 12:18:17 AM

See related articles to this posting


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
Reply 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
Reply 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
Reply Erland 12/4/2009 10:33:10 PM
comp.databases.ms-sqlserver 14552 articles. 3 followers. Post

3 Replies
506 Views

Similar Articles

[PageSpeed] 30


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

FREE SEMINAR on SQL Server 2008 Database Developer & Administrator Track
EVS Professional Training Institute offers Certified SQL Server 2008 Specialist Free Seminar on Saturday June 19, 2010 at 06:00 PM in Lahore Introduction SQL Server offers a robust architecture for enterprise data management, developer productivity and business intelligence. This course provides the knowledge and practical skills needed to maximize the benefits of SQL Server. Participants gain a solid ground on which to build essential SQL Server development and administrative skills, including creating databases, generating Transact-SQL statements, implementing security and reporting throu...

FREE SEMINAR on SQL Server 2008 Database Developer & Administrator Track
EVS Professional Training Institute offers Certified SQL Server 2008 Specialist Free Seminar on Sunday September 26, 2010 at 12:00 PM in Lahore Introduction SQL Server offers a robust architecture for enterprise data management, developer productivity and business intelligence. This course provides the knowledge and practical skills needed to maximize the benefits of SQL Server. Participants gain a solid ground on which to build essential SQL Server development and administrative skills, including creating databases, generating Transact-SQL statements, implementing security an...

Using Access to 'access' an sql database on a webserver
Is this possible? I know about a bit about ODBC and found how to import an ODBC database stored on the computer, however I am after accessing an SQL database stored on a webserver. I'd like to keep it up to date, but that could probably be done with a macro. At the moment I'm just considoring possible options, so just need to know if it can be done, how easy and a rough idea of how. Any help would be much appreciated! Flic On 6 Oct 2006 03:59:25 -0700, "Flic" <FelicityP@gmail.com> wrote: I typically create an ADP, then connect to such db specifying the IP address...

Access database(Jet Engine) and SQL Server or SQL Server express
Hi, I am trying to find out what are the big differences between access database Jet engine and SQL Server? If I have serveral web applications (same content different copies) running as different sites(on IIS), each site using an access as a back-end database, all sites will be running simultaneously, all the access databases are on the same machine, on this approach, does each copy of access has its own Jet engine to handle database transaction or only has one Jet engine with multiple threads handles multiple copies of access database. Also, I'd like to know what kind database, and data...

Accessing SQL databases
Hi everybody, Yesterday I saw a package which gives access to many SQL databases using the same interface but... it's wroted in PHP. I wonder if somebody is trying to do the same using [x]Harbour instead. Ron... anybody..? Till now I only saw interfaces for specific databases like MySQL and Firebird. If you think it's off topic, please answer me to my private e-mail alex_degarate AT hotmail DOT com I apologized for that. Best regards, Alejandro -- Posted via Mailgate.ORG Server - http://www.Mailgate.ORG Alejandro "Alejandro de Garate" <alex_degarate@hotmail.com>...

Problems with MOD-Function by accessing MS-Access Database via SQL in Delphi
Hello, the following problem: I use Delphi 6 to access a MS Access Database. In short, the Delphi Code looks like this: ADOConnection1.Open; ADOQuery1.Close; ADOQuery1.SQL.Text :=3D 'SELECT * FROM database1 WHERE value1=3D1 AND value2=3D ' + inttostr(array[3]); ADOQuery1.Open; Label5.Caption:=3DADOQuery1.Fiel=ADdByName('value4').AsString; Now I only want to select these rows, where the Integer value3 ends on 1=2E So I need the Modulo-Function. I found in the Internet these two functions: MOD(x,y) und x % ...

Accessing a Microsoft SQL 2000 database using Labview Database Connectivity Kit
Hello people,,,... i am new to Labview Database Connectivity Kit. Currently, I Have created a Database using Microsoft SQL 2000, but i am not sure how to let my labview program read and write to this database i have created.... So far, I have only suceeded in using the Database Toolkit to read/write to a Microsoft Access DataBase, but not a Microsoft SQL 2000 Database. thanks a lot... terence There's an online PowerPoint presentation that can help you get started using the SQL commands for the Database Toolkit in LabVIEW, which you can access <a href="http://zone.ni.com/devz...

UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;&q...

UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;Pa...

UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;Pa...

Any one can help me : how can convert Access database to SQL Server's database?
This is a multi-part message in MIME format. ------=_NextPart_000_0068_01C39D9D.2C554D40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable hi:=20 i want use vb 6 to design a interface connecting behind a batabase file, = i had a ready-made Access database file: **.mdb, now the problem is how = i can directly use parts of the database to convert to SQL server's = database ? and how i can conneting the interface to the SQL server = database? if it were difficult, how i could conneting the my vb = interface to Access database....

UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;&q...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283534182)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283534182) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. Wi...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283557612)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283557612) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. Wi...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45284457607)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45284457607) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. Wi...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283832411)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283832411) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. Wi...

How to import from external SQL database to local SQL database
Hello everyone. I have two machines that have SQL Express on them. I have developed a SQL database with two tables, and a lot of data, on my laptop. I want to copy that database and make it available to my local desktop machine. How can I export or save the database on the laptop and then import it so that I can use it on the desktop? ...

Open Access Database Form from within Access database
I have included the following code to open an Access database form from within an exsisting Access database. But when I run the code, it appears that it opens the database because in windows explorer I can see the database has the .ldb file extension. So it appears that it's open, but I can't see the database or the form that I've tried to open. What am I doing wrong. The following is the code: 'This is in the declaration part of the module Dim appAccess As Access.Application ' Initialize string to database path. Const strConPathToSamples = "C:\Documents a...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283857608)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283857608) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. ...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283857608)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283857608) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. ...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283557612)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283557612) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. ...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45284157605)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45284157605) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. ...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283832411)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283832411) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. ...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45284432404)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45284432404) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. ...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283534182)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283534182) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. ...