| Author |
Topic |
|
poncho
Yak Posting Veteran
62 Posts |
 Posted - 2004-04-07 : 16:16:43
|
| We are a software development co. that has been using Pervasive as out back end. We are now moving over to MS SQL and have a question. Is there any way to use MS SQL as your back end and only allow access to the database through our app? Not even the SYSADMIN of the SQL server in question would be able to view that database in Enterprise Admin. Our database has a user ID/password that we manage internally. We don't want local admins to be able to access/view/change any data or structure of our database. Basically can you remove all access to the database except via our VB application?Thanks in advance for any help...I have been searching the last few days but the search terms I am using turn up irrelavent info. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 16:21:30
|
| You can remove the BUILTIN\Administrators group from SQL Server. This will prevent local admins from having sysadmin privileges. Anyone who knows the password of the application account will still be able to get into the database using the SQL client tools.Personally, I wouldn't recommend buying a third party product that didn't allow a DBA to do their job. Even if it's a third party product, the DBA is still responsible for the health of the SQL Server, which means checking and running things. You should keep in mind that you will have an agreement with the customer not to change anything inside the database. Anything that they change, they are responsible for fixing since they broke the contract.Tara |
 |
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-04-07 : 16:26:18
|
| We don't won't do block out the DBA from the entire server...just our particular database. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 16:30:28
|
| That is not possible then. A DBA has sysadmin privileges which means all permissions to do everything. DBAs need to be able to check the integrity of the database, update statistics for performance, and defragment the database. These things require access to the database. If you are really going to go down this route, you are going to need your own dedicated database server at each customer site so that you can control permissions. This also means that you guys would be the DBAs for the system.Tara |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-04-07 : 16:34:36
|
| That's not good....we don't mind a DBA have administrative access to perform maintenance...but what about access to the actual data? Can/should we encrypt the data? What if we go the route of installing another named instance of the SQL Server? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 16:40:27
|
| Encrypting the data is up to you. You'll find a lot of headaches down the road including performance. Search these forums for encrypting data. You'll see a lot of reasons why not to do it.If you install a named instance, the DBA will still need access to the server if they are the DBA for that system. The DBA will usually have local admin privileges which means sysadmin on all SQL instances.I have worked with a lot of third party vendors before not so much at my current company but a lot at my last company. None of them encrypted data. It's the customer's data, so why can't the customer have access to their data?It all comes down to your company signing a contract with the customer.Tara |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-04-07 : 16:46:33
|
| Currently our contract stipulates that the data is only accessible through our app. While yes it is the customers data, it is our application that they are licensing for use. If we allow them to freely access data, other software can too, thus allowing internal apps or other companies software access to our data and possibly corrupting it, changing its structure. There is no way we can guarantee integrity that way. Also alot of the data is of a sensitive nature and not all our customers want their DBAs to have access to the data. Our appps allow the company director to manage rights to the database through our software. Most of our customers do not want their tech dept. to have access to these databases. I find it hard to beleive that this isn't a common issue with SQL. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 16:50:22
|
| They violate the agreement if they modify the data manually, meaning not through your application. They also violate the agreement if they alow other companies' software to access your database. So that wouldn't be your responsibility to fix since they violated the contract.They need to hire DBAs that they trust with the sensitive data. At my last company, everyone hired had to go through a security check by the government due to the data that they could access. If you couldn't get clearance, you weren't hired. A company needs to educate their IT personnel about the confidentiality of the data.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-07 : 16:56:45
|
| You CANNOT do it. PERIOD. That's why you aren't finding information on it. If you install a database at a client site, the DBA not only has the right, but the responsibility to view both your database structure and procedures. I would immediately kill any project that requires anything other than this. You DO NOT have any ideas so original that they can't be reviewed by the DBA. The originality and business value of your application is in the application. The database is a structure for effectively storing, presenting, and manipulating the data needed for that application. You're not doing anything earth-shattering that nobody has ever thought of before with your database.The DBA often gets stuck managing half-ass third-party applications that have a horrible design because they don't have the influence they should. And, quite frankly, that's the only reason I can think of to hide the entire design and code of your database from a customer.Please enlighten me as to other relevant reasons we should be stuck managing the performance of your system without being able to see it. Make sure you explain in detail how this is even possible, because I don't see how you could expect someone to accomplish this.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-07 : 17:02:29
|
| Currently our contract stipulates that the data is only accessible through our app. Really? Have you already sold things with this contract? How do you enforce it?While yes it is the customers data, it is our application that they are licensing for use. Exactly, meaning they should be able to see the data.If we allow them to freely access data, other software can too, thus allowing internal apps or other companies software access to our data and possibly corrupting it, changing its structure. Or possibly making something better.There is no way we can guarantee integrity that way. Yes you can, you have a base. If it's been changed when you get called in for servicing, you show them the changes, wave the contract in front of them, and ask them how much they want to pay for you to fix their screwup. It's a really great idea actually.Also alot of the data is of a sensitive nature and not all our customers want their DBAs to have access to the data. Then they should fire them.Our appps allow the company director to manage rights to the database through our software. How? Apparently, you don't know how SQL Server security works.Most of our customers do not want their tech dept. to have access to these databases. I find it hard to beleive that this isn't a common issue with SQL.The tech departments do not. The DBA does. There's a big difference.Does your company have a SQL Server DBA?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-04-07 : 17:10:32
|
| I realise that we are not doing anything earth-shattering, that's not the point. Our database systems include sensitive information about payroll, social insurance numbers and the such. Our own customers do not want their DBAs accessing this data. "How? Apparently, you don't know how SQL Server security works."We have not yet deployed on SQL.BTW we have hundreds of customers that sign our current contract agreement. We have been servicing the government and educational sector for over 20 years. We simply want to move off Pervasive and onto SQL server, but would like to maintain this data security. I do any of you actaully deal with data that is highly sensitive or all you all developing cooking recipe database? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 17:15:32
|
| I used to deal with government data as I was a government employee/DBA. On a VAX system, other DBAs had access to juvenile court records (can't get much more sensitive than that). I did not have this access as I wasn't a VAX DBA. I had access to the voters database, payroll information, human resources, property information, tax information, etc. (the list goes on and on and on). At my current company, the data isn't highly sensitive although it is mission critical. And what makes you think that the local admins didn't have access to the data in Pervasive? Isn't Pervasive file based?Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-07 : 17:19:00
|
| Then build encryption into your application and encypt the data. That protects the data and still allows the DBA to have access to do his job. I don't know about everyone on the forum. I know some of us have jobs involved in:HRNational SecurityPharmaceuticalsMortgage IndustryInsuranceTelecommunicationsI guess that would cover a few. I do have a recipe database though if you want it.You couldn't do what you are talking about in Pervasive SQL either btw. I administered it for several years as well.Under existing network architectures including NT security structure and Active Directory structure, there is a level of security reserved for a key top executive and select members of the IT staff, which allows access to any resource on the domain. It's extremely important that trust be placed in these key people because of this and they be monitored if necessary as an extra measure. This principle applies regardless of the database vendor you choose.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-04-07 : 17:38:46
|
| Our Pervasive databases are password protected. The only way to access them the actual DATA is through the DB engine which validates a logon ID and password. If the user has that logon and password then he/she will gain access. We store that user ID password in our code.We supply our customers with a read only password for ODBC access and th can authorise whomever they wish for that access. We used to give a read/write password but too often someone "brilliant" inhouse programmer/developer would decide to change a few things around and mess things up. We have no plans on going down that road again.We are thinking of encrypting. We were concerned with performance issues using encryption. There is no other none way of doing it? All we want is that only our app can read/write the data. How does that affect the DBAs job? By the same token, should I have access to Windows source code? How else can I properly deploy Windows desktops in my network if I don't have complete control over it? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 17:47:19
|
| It affects the DBA's job because the DBA can not run maintenance routines against the database without the access. How will the DBA be able to defragment the database either manually or through a scheduled job? How about corruption checks? How about backups? All of this requires access. If the database becomes corrupt at 3am, how is the DBA supposed to troubleshoot the problem without access? Is he/she supposed to call the third party vendor for them to fix it?Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-07 : 18:35:49
|
| You control the database read/write access through your application. You have the application set up accounts of read/write vs. read as needed by the application.The setup of the application should setup the database and add the user your application will use. The application should then have the interface built into it to set up the other necessary accounts and priviliges. Part of the DBAs job is to make sure no permissions are granted outside of this application. He is the gatekeeper. His accounts access to the SQL Server allows him to fulfill this role.If they mess this part up, you charge them an exhorbitant fee for maintenance outside of the SLA. They fire the DBA and hire one who knows what he's doing. It's really a good idea for everyone involved.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-04-07 : 20:03:32
|
| I had a similar discussion when I was working with the City of Seattle. The Ethics and Elections Commission had very sensitive data that they needed protected from everyone imaginable, especially other employees in the city. They were quite dismayed when I explained to them that if they put their database on "my" server then I could see everything there because I was the sysadmin. We came up with two solutions for them. 1) Encrypt the little bit of data that was truly sensitive (most of the data is not that sensitive, there were just a few fields that were critical) and/or 2) Install your own SQL Server and hire your own trusted DBA who is the only one that gets sysadmin privilege to that server. Then I wouldn't be able to get in, but there's still someone who can. But it doesn't have to be the entire IT or DBA staff that works on all the other servers, if it's really that big of a deal. Regardless, somebody has to be the sysadmin of the server, and that person can do anything and everything on the server. Also, look into the difference between encrypting and hashing. Hashing is a one-way encryption, useful for things like passwords, but not useful for things that have to be decrypted and displayed.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-08 : 02:29:16
|
| Where I work now, our HR server is pretty locked down. The people in HR have access through the application. I have access to develop. Two developers have been hired and work directly for HR to develop for them.Other than that, I have to receive a written confirmation from a director or VP in HR to allow even read access to that server. It's all about processes. And, quite frankly it's up to each individual company how much they want to control that.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-04-08 : 08:45:05
|
I am afraid I do not understand.The basis for this is not to trust the DBA?This is a big problem from the get go. If a customer has or hires out a DBA and they jazz up the data or structure it is their resposibility not yours.quote:
Currently our contract stipulates that the data is only accessible through our app.
A Pervasive database is sill ODBC and can be gotten into with out your front end Password protected or not. Been there done that we owned it.Encryption can also be broken if someone wants that data bad enough.The only way to keep the data safe is to make it unavailable I.E. close the access to the storage device.Sorry Poncho but if this is the basis for your policys they will never do busness with any IT admin I know. JimUsers <> Logic |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-04-08 : 11:05:15
|
| "A Pervasive database is sill ODBC and can be gotten into with out your front end Password protected or not."No they can't ....the database is password protected not our application. Even via ODBC access they need to have the user name and password. We supply a "read only" password via ODBC to our customers so they may run custom reports and such."The basis for this is not to trust the DBA?"No the basis for this is that most of our CUSTOMERS (we have hundreds BTW) don't trust their DBAs with access to their confidential data. "The only way to keep the data safe is to make it unavailable I.E. close the access to the storage device"Why do you need to actually see the data to manage the server? You can still perform all tasks concerning the database, no? I mean you can compact, defrag, backup and restore without ever viewing actual data...why the emphasis on actual data access? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-08 : 11:37:54
|
quote:
Originally posted by poncho[brWhy do you need to actually see the data to manage the server? You can still perform all tasks concerning the database, no? I mean you can compact, defrag, backup and restore without ever viewing actual data...why the emphasis on actual data access?
It depends on what you mean by viewing. In order to perform certain functions, the DBA needs access to the database which usually means db_owner role on the database. db_owner role means access to everything. So the only thing that you can do to prevent the DBA from reading the data is encrypting it.Tara |
|
|
|
Next Page
|
NewB Development Question