| Author |
Topic |
|
byrmol
Shed Building SQL Farmer
1591 Posts |
 Posted - 2007-06-06 : 19:40:44
|
| I can't believe I am asking this but I have been "requested" to.Scenario:Database based app installed on client siteGoal:Protection of database schema.Solution:Obfuscate? - Any tools available?Host db in secure location - Not going to happen!Any thoughts?DavidMProduction is just another testing cycle |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-06 : 19:45:18
|
| I've seen some applications (Actually Access based) where they lock the files with some password.In SQL server you can still query the sys catalogs. Perhaps denying SELECT's on any sys catalogs will help?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-06-06 : 19:49:02
|
| Thanks Dinakar.The primary problem with using SQL Server permissions sets is that an Evil DBA takes backup, restores to the Evil DBA box and brings forth the apocalypse...DavidMProduction is just another testing cycle |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-06 : 19:53:16
|
| (1) The files cannot be copied while in use (2) the files have to be locked. Just thinking out loud...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-06-06 : 19:55:47
|
| >>Just thinking out loud...Thanks mate.I know this is a futile question. The only solution I can think of is the SAP solution... Really, really, REALLY crappy table names.... But that is so far down the list of practically solutions..DavidMProduction is just another testing cycle |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-06 : 20:00:46
|
well, you can at least create everything WITH ENCRYPTION. that gets you part way there...
elsasoft.org |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-06-06 : 20:11:53
|
| >>WITH ENCRYPTION.To be honest, I don't really care about the procs and functions... It's the database schema that is the real concern...DavidMProduction is just another testing cycle |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-06 : 21:15:50
|
I don't know of any schema obfuscators. usually it's the business logic that people want to protect, but you've got the reverse case.you could always rename all tables/columns to a varying number of spaces: create table [ ]( [ ] int primary key not null, [ ] varchar(30) not null, [ ] datetime not null) good luck trying to maintain a mess like that!  reminds me of the whitespace language...
elsasoft.org |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-06-06 : 21:24:52
|
| >> usually it's the business logic that people want to protect, but you've got the reverse case.The database schema contains nearly all the business logic!There are cases where I have had to use triggers and udf's to implement some of the more complex rules. But I consider them part of schema. (Views, Tables, Triggers and specialised UDF's)The procs act as a security gateway for client interaction and usually contain simple CRUD actions.DavidMProduction is just another testing cycle |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-07 : 00:13:02
|
maybe I'm wrongheaded (I did come to SQL originally from the world of C++ ) but I've always had this analogy in the back of my mind:I think of a database like a class library. the table columns are like the private fields in those classes. clients can't access fields directly - they have to go through the public methods. in a database, the public methods are the procs. In a class, there's not much logic in the fields - they just say what kind of data is possible to store, and what the structure of that data is, how it's related. almost all the logic (code) is in the methods. so that's why I said the business logic is in the procs.
elsasoft.org |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-06-07 : 01:30:53
|
| I can understand that thinking. But it wastes so much of the power that any decent RDBMS has.My goals/thoughts when I am building a database are:1) Zero front-end validation - I assume that the user will be using query analyser which forces me to use very strict domains and keeps the rules as close to the table/view as possible.2) All business rules to be expressed as BULK INSERT friendly - A utopian goal that is very hard to meet in practice.3) Prepare for hostile external users - Data that can be exposed to 'unknown' users (ie another company). This forces me to examine the security model of each object in the db.Following these ideas, I have found that by the time the front-end gang get coding, they can concentrate more on UI features and worry less about handling data.Thanks for the link! Exactly my thoughts...DavidMProduction is just another testing cycle |
|
|
|
|
Obfuscate database schema