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 14578 articles. 3 followers. janinepelletier (108) is leader. Post Follow

3 Replies
544 Views

Similar Articles

[PageSpeed] 41
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:
Similar Artilces:

Oracle takes the wraps off SME database
Hi, I received an alert from www.techieindex.com Oracle has created a version of its database software tailored for the needs of small and medium-sized enterprises, details of which will be revealed today. Do any one got this news? Scot I can pretty much guess it will still be expensive compared to open source... -- Greg Patnude / The Digital Demention 2916 East Upper Hayden Lake Road Hayden Lake, ID 83835 (208) 762-0762 "Scott" <scottevans@eudoramail.com> wrote in message news:6fa2d4de.0310090544.e08a9d6@posting.google.com... > Hi, > > I received an alert from w...

External Source Control of Basic Modules in MS-Access?
I would like to, at a minimum, be able to export and import MS-Access basic modules so that I can edit them using my own editor. Additionally I'd like to move the routines into a source control mechanism such as CVS. Has anyone ever accomplished such a thing? Pointers would be appreciated. -- Randy Yates Randy Yates <randy.yates@sonyericsson.com> writes: > I would like to, at a minimum, be able to export and import MS-Access > basic modules so that I can edit them using my own > editor. Additionally I'd like to move the routines into a source > control mechanism s...

BITS#5007 Function 2008
BITS intro #5007 in 256 Bytes for F U N C T I O N 2 0 0 8 +-----------------+ |. . .| | . . . . | | . . . . . | | . . . . | | . . . . | | .. . . . . ..| | .. .. .. .. | | ... .. | |..... ....... ...| | ... | | . . . . . | | . . . . .. | |. . . . | | . . . . | | . . . . | | . . . . . | |. . . . .| +-----------------+ bits.atari.org h.samso@gmail.com ...

Problem accessing samba share....
I am having problems accessing a samba share I've created. And it is only my account! If I do an smbclient from the unix server to connect, no problem. Here is the log output of that connection: [2003/12/18 12:04:01, 2] lib/access.c:check_access(329) Allowed connection from (162.86.2.197) [2003/12/18 12:04:01, 2] smbd/reply.c:reply_special(92) netbios connect: name1=USEHUX32 name2=USEHUX32 [2003/12/18 12:04:01, 2] smbd/reply.c:reply_special(111) netbios connect: local=usehux32 remote=usehux32 [2003/12/18 12:04:01, 2] lib/util_sock.c:open_socket_out(874) ...

US-TX-Austin: SQL Database Dev., SQL,SQL Analyzer and Profiler; (45349657602)
US-TX-Austin: SQL Database Dev., SQL,SQL Analyzer and Profiler; (45349657602) ============================================================================= Position: SQL Database Dev. Reference: ZYD00030 Location: Austin TX Duration: Skills: 3+ years experience as a Database Developer with significant SQL 2000 experience Must have the ability to develop, debug, test and troubleshoot queries,macros, and modules Experience and understanding of database design Proficient with SQL Query Analyzer, ...

US-TX-Austin: Oracle/ SQL DBA Developer, SQL DBA, Oracle DBA, Oracle Developer; (45302757603)
US-TX-Austin: Oracle/ SQL DBA Developer, SQL DBA, Oracle DBA, Oracle Developer; (45302757603) ============================================================================================= A strong data base developer / database admin needed for contract to hire opportunity in Austin Texas. Significant Oracle experience would be a must as well as strong MS SQL Server Submit responses to gcameron@eurosoft-inc.com ..45302757603. ...

Re: how do I access f77 libraries on linux (for lapack and blas)? #3
On Mon, 13 Dec 2004, Jan Vorbr=FCggen wrote: > > I suspect that the relevant F77 library is available for Linux and may > > even be installed on one of the machines in our department, I but have > > not been able to locate it. >=20 > AFAIK, that library comes with the Sun Fortran compiler, and the names of > the routines it contains are non-portable. You need to look at what the c= all > to that routine in the POP11 code actually does. Also, I believe f771 is = the > first pass of g77. That wouldn't help - the Pop-11 code doesn't call the...

Network Administrator's Toolkit 9.1
A set of the network management tools designed for the corporate network administration. This toolkit consists of the two components that will monitor your corporate servers availability and performance and inventory user workstations More Info: http://www.alchemy-lab.com/products/nat/?c=archive Download URL: http://www.alchemy-lab.com/products/nat/nat.zip?c=archive Screenshot URL: http://www.alchemy-lab.com/products/nat/shot2.gif Order Page: http://www.alchemy-lab.com/products/nat/register.html?c=archive ...

Help! Access Violation on Accept Loop
Hi Ya'll; I have a problem that I just don't know where to look next. I have a program (hand coded) which in essense processes a survey form. The survey is completely created dynamically. Once the survey is completed, a set of demographics screens are displayed. The demographics screens are text controls, drop lists, and drop combos, again, all created dynamically. The program displays the survey, the survey is answered, the results are calculated and written to disk, the demographics are displayed, the demographics are written to disk, and finally the already tabulated results are d...

US-TX-Austin: Oracle/ SQL DBA Developer, SQL DBA, Oracle DBA, Oracle Developer; (45301758079)
US-TX-Austin: Oracle/ SQL DBA Developer, SQL DBA, Oracle DBA, Oracle Developer; (45301758079) ============================================================================================= A strong data base developer / database admin needed for contract to hire opportunity in Austin Texas. Significant Oracle experience would be a must as well as strong MS SQL Server Submit responses to jgoolsby@eurosoft-inc.com ..45301758079. ...

RE: Migrating 60GB database
You forgot the timezone factor, add extra 60 minutes ... 2 days 16 hours 17 minutes and 8 seconds. > -----Original Message----- > From: Neil Truby [mailto:neil.truby@ardenta.com] > Sent: Monday, September 06, 2004 8:34 AM > To: informix-list@iiug.org > Subject: Re: Migrating 60GB database > > > "Triveni" <btnaidu@yahoo.com> wrote in message > news:ee43e368.0409052225.516eec4d@posting.google.com... > > Hi, > > How much time does it take to migrate 60GB of data from Informix > > database to Oracle database. > ...

Re: SQL Delete with OLE DB
Lionel: Do the tables have any referential integrity constraints? S -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Teed, Lionel Sent: Wednesday, September 23, 2009 11:46 AM To: SAS-L@LISTSERV.UGA.EDU Subject: SQL Delete with OLE DB Fellow SAS-Listers. I have just upgraded to 9.2 and decided to use that occasion as an opportunity to update some of my regular monthly programs. One of the main programs reads some data from month end data files, massages the data a bit, and then exports it out to an MS Access database for report generation. In 9...

sql server 2005 express problem wtih {7twud62389sdksdk} the like
I'm installing sql server 2005 express distribution version on an Asus EEE PC with XP home OS, and doing installing and uninstalling several times, and each time, it generated some abitrariy directory like the subject one, which clustered my hard drive, but they not removable, the finish.rtf is undeletable, how come? Any way to remove them all? The odd thing is, installing it to a laptop with the same OS does not create such unremovable directory and files. Thanks. DL (tatata9999@gmail.com) writes: > I'm installing sql server 2005 express distribution version on an Asus > EE...

sql integrity in ms-sql
(I've also posted this message in TURF: http://www.webinacan.com/turf/viewtopic.php?t=68) Hello, I'm kinda new to MS-SQL, maybe you could give me some directions. Environmet: Uniface 7.2.06, MS-SQL 2000 When generating sql inegrity scripts for MSS I've noticed Uniface does not use the integrity names you have defined in Application Model (when generating to ORA, it does). It uses names like ATABLE_TR_U for the update integrity trigger and ATABLE_TR_D for delete. Problem is: those names duplicate when you have an inter-model relationship. For example: tables A...

Solaris 8 accessing shared NT print queues
I have windows 2000 print queues with "Print Service for UNIX" enabled and SAP R/3 running on Solaris 8. I needs to setup printer devices on SAP side using access method L to access the windows 2000 print queues without creating a local queue on SAP solaris servers. I have several shared print queues and eleven SAP application servers so I am trying to avoid creating each print queue on each application servers. This would create big mess to manage. Is it possible to call lpr or other commands and just point to windows 2000 server and shared print queue name? Thanks! JM "...

Third CEU Summerschool on Advanced Statistics and Data Mining (June 30th-July 11th, 2008)
Dear colleagues, San Pablo - CEU University in collaboration with other five universities (M=E1laga, Polit=E9cnica de Madrid, Pa=EDs Vasco, Complutense, and Castilla La Mancha), Uni=F3n Fenosa, CSIC and IEEE organizes a summerschool on "Advanced Statistics and Data Mining" in Madrid between June 30th and July 11th. The summerschool comprises 12 courses divided in 2 weeks. Attendees may register in each course independently. Registration will be considered upon strict arrival order.For more information, please, visit http://biocomp.cnb.csic.es/~coss/Docencia/ADAM/ADAM.ht...

SQL Server DBI insert silently fails
Hi all I am having problems when trying to insert records using dbi-0.1.0 on Windows XP. I am using the ADO driver to connect to SQL Server, like so: connectString = "DBI:ADO:provider=SQLOLEDB.1;Data Source=10.0.0.1;User id=xxxx; password=xxxx;Initial Catalog=DevDB" I get rows == nil when I run this: qry = "insert into G2.dbo.Device (IPadress, SerialNumber, VehicleID, " + "SerialNumberB, Tag1, Tag2, FwVersion, HwVersion) values (?, ?, ?, ?, ?, ?, ?, ?)" rows = @database.do(qry, device[:ip], device[:serial], device[:serial], device[:serial], 0, 0, 0...

Restoring SQL Views
Hi *ALL, Over the weekend our shop admin restored our training libraries from a tape backup of our production libraries. However, SQL views that had been created are still referencing the production physical files. The restore is done with a RSTOBJ OBJ(*ALL) SAVLIB(PRODLIB) DEV(TAPE) ENDOPT(*REWIND) MBROPT(*ALL) RSTLIB(TRAINLIB). DDS logical files are fine. It appears the job description it was run under only contained the training libraries, not the production libraries. Can anyone out there shed some light on how SQL views are restored? Thanks in advance for any help...... It's easy t...

MS SQL Server 2000 and MS JDBC SQL Server driver problems. (Connection timed out, followed by Error establishing socket)
I have downloaded the MS SQL Server 2000 Enterprise Evaluation edition, and the MS SQL 2000 Server JDBC driver. I have installed and configured the MS SQL Server 2000 onto two computers, which reside on the same network. (In fact, these two computers comprise the entire network). The network server (computer name=SUNCDS1), and the lone node (SUNCDS2), each have their own SQL Server 2000 instance. The SQL Server 2000 Enterprise Manager console on SUNCDS1 shows both SQL Server instances, the (local) instance, which is SUNCDS1, and also SUNCDS2. It may be worthwhile to mention t...

US-PA: Philadelphia-SalesLogix Administrator/Microsoft Web Developer
************************************************************** JobCircle.com - Careers, Content and Community for Technology professionals in the Northeastern United States! Jobcircle.com provides resume submission, classified searches, eLearning, hundreds of career development articles, tech news, and more to tens of thousands of Technology professionals in your area - visit us today at http://www.jobcircle.com! !!! Voted a TOP 50 Career Web Site in 2002 by CareerXRoads !!! ************************************************************** Job Title: SalesLogix Administrator/...

Problem with Cross database join
I hope someone can offer me some advice or additional areas to search for the answer. I'm trying to do a cross database join on a single server. My searches indicate that the way to do this is a database link. Seemed simple enough, however, no matter what I try I get "database link is not active". My understanding of this error indicates that I either have the wrong username and password or I have specified a database that doesn't exist. I finally ended up expanding the query to create the link to the maximum number of parameters. The statement I execute is as follow...

Re: PROC SQL
Perhaps what you want is a NATURAL JOIN. From the V. 9 online documentation: <quote> Natural Joins A natural join selects rows from two tables that have equal values in columns that share the same name and the same type. An error results if two columns have the same name but different types. If join-specification is omitted when specifying a natural join, then INNER is implied. If no like columns are found, then a cross join is performed. The following program demonstrates a natural inner join. proc sql; title 'Natural Inner Join'; select * from table1 natural join...

ppc kernel kernel access of bad area problem
when i load my kernel into ppc755 borad the exception raised. if you can help me about it thansk very much! board info ------------------------------------------------------------------ my board refer board: sandpoint X3 cpu:motolora ppc755 sdram: 512M ide disk:hitachi travelstar 40G serials baud: 115200 ram disk: 4m after uncompress serial console log info ------------------------------------------------------------------ name = eth, unit = 0 Attached TCP/IP interface to eth0. Attaching network interface lo0... done. Loading... Transfer file length is 2752512(0x2a0000...

US-PA: Malvern-Database Developer #2
************************************************************** JobCircle.com - Careers, Content and Community for Technology professionals in the Northeastern United States! Jobcircle.com provides resume submission, classified searches, eLearning, hundreds of career development articles, tech news, and more to tens of thousands of Technology professionals in your area - visit us today at http://www.jobcircle.com! !!! Voted a TOP 50 Career Web Site in 2002 by CareerXRoads !!! ************************************************************** Job Title: Database Developer Job Location: PA:...

Java/J2EE PA(Oracle,SQL,PL/SQL)
Title: Java/J2EE PA(Oracle,SQL,PL/ SQL)Ajax,JSP,Struts,Ibatis,Spring,Unix Skills: Java/J2EE Programmer,Oracle,SQL,PL/ SQL,Ajax,JSP,Struts,Ibatis,Spring Framework,Unix Shell Scripts,Oracle Apps Server 9iAS/10G a Plus,CVS,JUnit,ANT,Log4j,OSCache,Intellij Trinity Consultancy Services is seeking applications from qualified and experienced software engineers with above skills for various requirements with their Clients. Job description: Java Programmer Analyst (Strong Java/J2EE/JSP/Struts) Experience with Ajax/iBatis/Spring Framework, Excellent Oracle SQL/PL SQL Skills, ability to write complex qu...