SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 NewB Development Question
 New Topic  Topic Locked
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 8

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 04/09/2004 :  10:09:21  Show Profile
I have read this post with great interest (allthough it took me forever!) but I have yet to see a simple answer to this guys question! And acording to what I have managed to pull out from this the answer would be "no, it is not possible to hide the data from everyone".

This beeing said one question arise that I don't recall beeing answered thuroughly (or how the h€ll you spell that): why don't you host the sql-server yourself?? This will reduce the possible hacks/mods/"improvements" to an absolute minimum and the number of dba's from hundreds to only a few that you have full control over yourself... Your problem here is exactly the problem of a webdeveloper which not make use of custom dll's or something. If you sell the website you give away the code. If you don't wanna do that then host it yourself...I might be missing something here but to me this is plain and simple.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/09/2004 :  10:23:15  Show Profile
I don't get it...what if you have problems...

Who's going to investigate it...

Ever have data anomloies?

Do you plan to use the front end to do database maintenance?

Your concern is understandable...but you need a dev, qa and prod environment

I usually test everything out, send it to qa, using change control, and the ship it off to a prod dba group who's sole jobe to to apply script code and or data scrubs...they do not interact with the db except to keep it healthy...

That's just me though...

(My first reaction was to post some gut reaction stuff about IT Directors though...)

You mention you use Pervasive...never heard of it...

What's you model today?

How do you protect your data in case of disaster?

Do you have a back up site?



Brett

8-)
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

USA
255 Posts

Posted - 04/09/2004 :  12:28:31  Show Profile  Visit RoLYroLLs's Homepage
quote:
Originally posted by Lumbago

This beeing said one question arise that I don't recall beeing answered thuroughly (or how the h€ll you spell that): why don't you host the sql-server yourself?? This will reduce the possible hacks/mods/"improvements" to an absolute minimum and the number of dba's from hundreds to only a few that you have full control over yourself... Your problem here is exactly the problem of a webdeveloper which not make use of custom dll's or something. If you sell the website you give away the code. If you don't wanna do that then host it yourself...I might be missing something here but to me this is plain and simple.



I agree with Lumbago. Currently I do side jobs for a friend working on e-commerce websites. We do not sell our code so we host it off our own hosting company. This gives also gives me the ability to easily manage, maintain, update, upgrade both the site and the db when changes are needed. Bu the client. Of course changes are easier on a web application than to a program.

I thought about mentioning the hosting the sql server yourself, but I thought I read somewhere you didn't want to do that (i forgot, this posting was soooooo long )

- RoLY roLLs
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 04/09/2004 :  18:54:54  Show Profile  Visit AjarnMark's Homepage
Poncho, a couple of thoughts based on various posts in this thread...

1) Even within Pervasive, somebody must have "God's Rights" to the database and they can do anything they want. I don't know much about Pervasive, but it seems to me that if your client had a Pervasive DBA, and other databases, that that DBA would have "administrator" privileges which would allow him/her to do anything within your database regardless of the logins and passwords you set. If you "fixed" this situation by having a dedicated database server that even the company's DBA didn't have access to, then you could do the same thing with SQL Server. Have them install a separate server. But if it's going on their existing server, then the DBA has full rights.

2) It's not a matter of us "wanting" access to everything. It's a matter of reality that within the SQL Server security model, the DBA (a.k.a. sysadmin) has access to everything. And this is important for him/her to fully do their job.

3) If you don't like SQL Server, then go to Oracle. But I'd bet you your corporations total annual revenues that Oracle also has a sysadmin type of security role that has access to everything.

4) Seriously, if a company cannot trust its most senior DBA with access to all the data, then they've got the wrong guy/gal. It's possible to have different levels of DBAs where there might be only one who has permissions on the super-secret production server, but it will have to be a separate server from the ones that the lower level DBAs have access to.

5) The way we protect ourselves are through trust relationships and insurance. I am an independent contractor who does development and DBA work. Yes, I have had my nice developed application screwed up by an incompetent DBA. But see my solution above... Fire him! And I have had developers write junk that would have jeopardized servers I've been responsible for if I hadn't blocked it. And I've done work for the government where I was the acting DBA for 2 years over their SQL Servers. How did I protect myself from lawsuits? With lots and lots of liability insurance, and by closely monitoring what was happening on the server to identify security threats and take care of them before they became an issue. Oh, and of course, I always had rolling backups so we could recover from any attack or other disruption.

--------------------------------------------------------------
Find more words of wisdom at http://weblogs.sqlteam.com/markc
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 04/09/2004 :  19:03:25  Show Profile  Visit AjarnMark's Homepage
Hey, here's an idea, Poncho. Since you don't seem to like the answers we're giving you (after all, you have accused us of being arrogant DBAs just trying to protect our jobs) then go ask your question on several other SQL Server discussion forums. I just don't think you'll find an answer that we haven't covered. And I'll bet you find that we're not just arrogant DBAs, but folks who are just speaking truth. As I recall, the following options have been given to accomplish your goal:

1) Encrypt the data
2) Install a separate server and lockdown access to it so that even the company's own DBA can't get in.
3) Host the database server outside of the client company (yourself or other service). Note that this is really a variation on number 2, just with a different physical location for the server.

If you come up with something better, please come back and educate us. I'm willing to humble myself and say, "Damn, I didn't even know that was possible." if you can.

--------------------------------------------------------------
Find more words of wisdom at http://weblogs.sqlteam.com/markc
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

USA
255 Posts

Posted - 04/09/2004 :  19:22:35  Show Profile  Visit RoLYroLLs's Homepage
Amen to that!

- RoLY roLLs
Go to Top of Page

poncho
Yak Posting Veteran

Canada
62 Posts

Posted - 04/09/2004 :  19:30:24  Show Profile
"1) Encrypt the data
2) Install a separate server and lockdown access to it so that even the company's own DBA can't get in.
3) Host the database server outside of the client company (yourself or other service). Note that this is really a variation on number 2, just with a different physical location for the server.

If you come up with something better, please come back and educate us. I'm willing to humble myself and say, "Damn, I didn't even know that was possible." if you can"

I didn't mean to call you guys Arrogant DBAs, but the first set of answers I got were arrogant answers, not really addressing my original question. Telling me "why would you want to block out access" is not answering how to do it. Telling me the SQL Model doesn't allow it, is.

Us hosting the solution is not really an answer. We have over a thousand clients with large databases and bandwith would be a huge issue for everyone inlvolved. Some of clients databases are in the GBs so it's a no go.

We don't want to block out access to everyone, we just want to maintain control on the access, currently even if you have full rights to the physical files, you still need to go through the Pervasive DB Engine to read them and you need the USer ID and PW to access the files.

Yes our clients have backups, I don't see why it's so hard to understand that user accounts don't need to be able to read the files outside the DB Engine to be able to do regular maintenance. A backup is a binary open, read and write. Anyone can see the actual file, if they have access to the files but it's not legible unless accessing it through the DB Engine....isn't MS SQL the same? In MS SQL if you go to the physical file can you read the data? You access it through the DB Engine, and that's the access we want to control.

All we and our customers want to do is not allow local DBAs/Techs/LAN Admins to modify the data or datastructure outside of our app. I thought this would be a common thing to do, I guess not.

We sell "usage rights" for our apps, and the data is the customers, but to protect ourselves and our clients from all sorts of scenarios, the access to the data is only permitted from our app, or read access from an ODBC password. Even that is troublesome, when you have amateur inhouse programmers writing queries through ODBC that end up crashing the DB Engine or choking it with a query that ends up in a loop.

Yes we have a dev, QA and prod environment, and some of our bigger customers do as well, but the samller ones don't and they all depend on us to deliver an app that is stable and secure.

BTW, on the MS SQL newsgroup on the MS site, there are others inquiring about the exact same thing...I guess we are not the only ones in "left field" with this...

Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 04/09/2004 :  20:24:57  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Yes our clients have backups, I don't see why it's so hard to understand that user accounts don't need to be able to read the files outside the DB Engine to be able to do regular maintenance.

This is not true in SQL Server or any of the major databases. The reason is that they are not just a file system for storing data. You have indexes that need reindexed, statistics that need updated, database integrity checks that need performed, and a host of other maintenance items. Each of these allows SQL Server, Oracle, etc to perform at a much higher level with large amounts of data. All of the tools are tightly integrated into the very files that contain the data. Does that make sense?


A backup is a binary open, read and write. Anyone can see the actual file, if they have access to the files but it's not legible unless accessing it through the DB Engine....isn't MS SQL the same? In MS SQL if you go to the physical file can you read the data? You access it through the DB Engine, and that's the access we want to control.

Actually, we're talking apples and oranges here. The only thing you can do with a SQL Server backup is restore from it. You can password protect the file if you wish to not allow a restore unless the password is entered. The physical files in MS SQL (not the backup files) do contain the data and you can only read them through the DB Engine. The DB architecture has security built in, which the DB Engine utilizes. To control the access, you control it through a combination of NT Security or Active Directory and SQL Server security.

You can get a very granular level of control if you understand and control this correctly. So, I think you might just need to learn how the different security models differ from each other. You should be able to then find a model you are happy with.

All we and our customers want to do is not allow local DBAs/Techs/LAN Admins to modify the data or datastructure outside of our app. I thought this would be a common thing to do, I guess not.

It is. Only the DBAs can modify data outside of the application. We have in place traces though that monitor when people log in and out of a system and what they do. This is to protect ourselves as well as other people.

An interesting thing happened today. We use a third-party HR system at our work. HR accidentally "terminated" an individual in their system. Since they needed to allow the person in the building, allow them to do their job, and allow them to get paid, and the great third-party app didn't let you "unterminate" them, guess who they called.

After receiving director level permission, I made the change manually in the database. We didn't have time to wait on the third-party company to make the change for us.

Even that is troublesome, when you have amateur inhouse programmers writing queries through ODBC that end up crashing the DB Engine or choking it with a query that ends up in a loop.

As DBAs we deal with that all day long. It's even worse when it's a third-party programmer we have no control over.

They all depend on us to deliver an app that is stable and secure.


They depend on us for a stable, secure environment as well.

Something you might consider is really offering expert advise on the Windows security model and integration with database systems for secure HR and security applications.

In Active Directory, you have much more flexibility. When we implement Active Directory this year, the databases will set in their own tree. Only the DBA group will have access as administrators to this area. All other access to this area will have to be setup and approved by us, including outside administrators.

You can do the same thing with NT security, but it requires setup of seperate domains and is much harder to manage.



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

poncho
Yak Posting Veteran

Canada
62 Posts

Posted - 04/09/2004 :  23:12:43  Show Profile
now we're getting some good info exchanged...thanks.
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1534 Posts

Posted - 04/10/2004 :  13:47:29  Show Profile  Visit JimL's Homepage
About Pevasive

I Still have one Pervasive DB. It is for an employee Timclock Program for our union employees. As this was purchased before I took over I had no say in the matter.

As I do not currently have time or available budget to replace it so I am stuck with it for a while.

I have placed the Db on its own dedicated server and still it crashes about every two weeks. Not to mention the problems with interface.

I have had the my current SQL server (same hardware) running the rest of the plant and offices.Running for 2 years and have not had a crash yet. (I only perform a Maint Reboot about every other month)

So maybe this is why I came off a little strong, in short I hate Pervasive with a Passion. (and good reason)


Jim
Users <> Logic
Go to Top of Page

poncho
Yak Posting Veteran

Canada
62 Posts

Posted - 04/10/2004 :  21:47:03  Show Profile
Personally so do I, hate Pervasive...but we started coding our apps on Novell Servers with Btrieve back in the day...Pervasive was the natural migration path 5-6 years ago for us.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15637 Posts

Posted - 04/11/2004 :  12:45:12  Show Profile  Visit robvolk's Homepage
BTW, I also curse the name Pervasive/Btrieve, and you can open any of the files in Notepad and read pretty much any of the data in there. Any character fields are stored as plaintext. The other data will be a jumble, but if you're determined you can probably decode quite a bit. Since Btrieve is a file based system, if you have permissions to open the data through an application, you have permissions to open the file directly and bypass every password and other lockout.

SQL Server works as a service, so that the actual files that store the database can be made inaccessible to everyone outside of using SQL Server. This is certainly a helluva lot more secure than Pervasive.
Go to Top of Page

poncho
Yak Posting Veteran

Canada
62 Posts

Posted - 04/13/2004 :  11:33:23  Show Profile
I have a question...I have a 3rd party app running our Point of Access system(card readers for entry to building). It runs on a SQL v7 database server. The database is there, I see the physical files, but in Enterprise Manager it see no database listed...how come? Everything you guys have told me says thats impossible...I can create a new database and see that, but I don't see the existing databases. I am sysadmin and local admin as well. Can someone explain this?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35954 Posts

Posted - 04/13/2004 :  12:23:37  Show Profile  Visit tkizer's Homepage
If you run this from QA, do you see it listed:

SELECT name
FROM master.dbo.sysdatabases



Tara
Go to Top of Page

poncho
Yak Posting Veteran

Canada
62 Posts

Posted - 04/13/2004 :  13:07:10  Show Profile
Once I opened SQL Q&A I see all the databases listed....why don't I see them in Enteprise Manager?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35954 Posts

Posted - 04/13/2004 :  13:11:04  Show Profile  Visit tkizer's Homepage
What account are using to connect to the database server in EM? Right click on the server, go to edit. What account did you use to connect to the database server in QA?

Tara
Go to Top of Page

poncho
Yak Posting Veteran

Canada
62 Posts

Posted - 04/13/2004 :  13:16:03  Show Profile
Windows authentication...with the Domain Admin Account...same for Q&A I assume...I launched it from within EM...I am able to see other settings for the server, just under database it says "(No Items)". If I create a new database I see it. I assume that this was done on purpose by the vendor to "protect" the database and data....kinda like what I was looking for!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35954 Posts

Posted - 04/13/2004 :  13:20:50  Show Profile  Visit tkizer's Homepage
If you right click on the server in EM, then go to edit, is the option to show system databases and system objects checked?

The database isn't protected. If you can see it in Query Analyzer, then you can see it in Enterprise Manager. We've just got to figure out what's wrong here.

Tara
Go to Top of Page

poncho
Yak Posting Veteran

Canada
62 Posts

Posted - 04/13/2004 :  13:25:28  Show Profile
"is the option to show system databases and system objects checked?"

Yes it is.....BTW this is a SQL v7
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35954 Posts

Posted - 04/13/2004 :  13:30:25  Show Profile  Visit tkizer's Homepage
Run this in Query Analyzer:

SELECT *
FROM master.dbo.sysdatabases
WHERE name = 'YourDBName'

Change YourDBName to the name of the database that isn't listed in EM. Post the results of that query here.

Tara
Go to Top of Page
Page: of 8 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000