May 20, 2009 at 3:44 pm
Hi all,
I need some ideas, hints or the so called "all in one device suitable for every purpose". 😀
Background: our external developers made some kind of service level agreement for a new application to make sure that no data can be manipulated, not even by the admins.
Well, I have no interests in manipulating any data, but I am interested in maintaining the SQL server as good as I can, e.g. backing up the database, the logs, reorg or rebuild indices and so on.
So what can either I or the programmers do to make sure that the data is safe?
Since the management did not read Brians article "You must trust your dbas" I have to find a way to have full access with every administrative functionality but without being able to see or manipulate "user data". :unsure:
Securing SQL server in that way is new to me, so I am not sure if it is possible to fulfill that requirement with the help of schemata or do we need encryption at data level?
As long as I have no solution the interim solution is to narrow down the group of people who will get administrative access to the database servers. (trust your dbas...)
Regards and thanks in advance
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
May 21, 2009 at 11:46 pm
Hi,
In SQL 2008, you can audit virtually anything (including reads) iirc. So a simple way is to upgrade - oops, it's an Enterprise feature of course, but you have to pay more for more fun 🙂
I met an interesting solution in the past: a SQL trace started up all the time the server started and ran continuously and filtered to the DBAs. The run of the trace was monitored and there was an alert if for some reason it died. It required some oversizing in terms of hardware resources, but the company paid for it. It was extremely weird, but worked.
-- Erik http://blog.rollback.hu
May 22, 2009 at 12:26 pm
To my knowledge there is no way to do all the DBA tasks without granting the someone the ability to manipulate the data. You can audit everything as Eric has said and some of that auditing is already done by the Default Trace. I'd probably work on "saving" the Default Trace data and then compliment it with another trace that audits the events you need that the Default Trace does not audit.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 22, 2009 at 12:39 pm
I'm not sure which kind of security you are looking for. If you try to avoid that somebody accidentally manipulates any data you should try "Read-Only" property of your database.
May 22, 2009 at 2:45 pm
Thanks for the replies so far.
Well, it is quite difficult to explain the situation. The external developers made their SLAs and they do not trust the dbas.
So even the dba should not have the chance to change or see the data.
It is very frustrating that the management actually is trying to please the developers than to listen to the own people.
So I have to find a possibility both sides can live with.
I suppose that this goal cannot be solved by using schemata (I have no idea how to deal with it)
Encryption may mask the data, but it would still be possible to change data (without any sense)
Or, as mentioned here, I´ll try to trace the server access and audit dba actions.
But who will audit these information? And who will audit the auditor? It is more a political than a technical question.
Regards
Dirk
P.S. Does anyone else also had these kind of problem in the past?
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
May 22, 2009 at 2:51 pm
Have someone outside the DBA group, maybe in finance or accounting, look through the logs. Show them how to query, how to search, let them examine traces.
Have the traces written to a folder that the DBAs AND the SQL service account cannot write to or read.
May 22, 2009 at 2:55 pm
Sounds to me like you need to buy a dedicated server for that application and let the outside developers manage it, since you can't put it on an existing SQL Server where the DBA's have sysadmin rights.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 23, 2009 at 5:50 am
Thx @ all for the advise so far.
I will try to get some more information from the developers next week, not only via a third person all the time.
Regards
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
May 23, 2009 at 7:51 am
A separate instance seems the way to go, since you cannot exclude sysadmins authority, unless you are willing to pay the price of encryption.
And even then it is just a first fence, experienced sysadmins can look through.
Maybe my little article on SQLserver and SOx can help a bit.
http://www.sqlservercentral.com/articles/Security/3203/
Although evaluating the results will be a burden, no dba can assist, because (s)he is an involved party 😉
Even with a separate instance, just wait until the system gets choking :hehe:
It wouldn't be the first "external" company that - even with tight sla, performance promises, .... - doesn't meet to what has been bargained for.
And in many cases, the internal client will call on the "unthrustworthy" (internal) dba to figure out what is going wrong.
This kind of discussion returns frequently in forums.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 27, 2009 at 1:23 pm
Hello again,
finally I´ve found the time to get more information.
In the end nothing changed...
The dba team and I have to ensure that no data can be manipulated, e.g. not changing payroll information, working hours and so on.
So I think it will be a combination of encryption and auditing.
Regards
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
May 27, 2009 at 2:16 pm
just make sure they don't encrypt everything.
That would be total overkill !!
Every column that gets encrypted will be converted (implicit) to varbinary(max) !!
These columns cannot be indexed,....
Also mention, you as a dba, cannot support data content issues if it concerns an encrypted column !!!
At least .... not in a normal way (need more time !)
There are also DRP considerations to be taken into account if encryption is involved.
Make sure you know how to restore that kind of databases !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 28, 2009 at 3:03 am
Hi,
maybe the developers can disclaim encryption and use a separate column for the records where they place a checksum jsut to make sure that no data were changed.
We will see.
Thx again for your help
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
May 28, 2009 at 10:51 am
... OR, use SQL Server as a front-end for a database running on Oracle -provided you have deployed Oracle Vault on the back-end Oracle instance 😀
Actually it would be easier than that, Oracle Vault has support for SQL Server.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply