Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to prevent our database to access any person Expand / Collapse
Author
Message
Posted Sunday, September 19, 2010 1:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 10:49 PM
Points: 92, Visits: 340
Hi everyone,

I have an issue with my database securities. Please help me regarding below comments,

I have a database, in which lot of tables, procedures, views & functions. All the thing in my system like sql 2005 and i am admin for the own system. I have every rights for my self PC.


Suppose i am giving my database to other person after taking backup (.bak), who using own system. person is admin for own system (he is using another laptop & also sql 2005 there) .


The problem is coming when i give own database to other person, he can see my database with SSMS environment. he can view my all database tables, procedures, views & functions after restoration of data in own system(Laptop).


Can any body help to set any password on own database by which other person(who has sysadmin right and he can login with sa) can't access my database whenever he restore my database to own system.

How i can prevent those person to doesn't open my database.

Please help me.



Thanx & Regards,
Sachin.
Post #988871
Posted Sunday, September 19, 2010 2:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
You can't. There are things you can do to slow them down, but if a person is a SQL administrator, they have complete and total control over the server and you can't keep them out of the DB if they want to look.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #988878
Posted Sunday, September 19, 2010 6:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 10:49 PM
Points: 92, Visits: 340
Hi Gail,

I have .net application as well as database for the same.

For application, we are converting it as dll and then send to our client.

After converting in dll, client not able to see code behind the application, while he will use applicaton.

Just like same, i aslo want same activity in my database,

Is any way by which i can convert or prevent database tables, procedures, funcations, views from client.

I don't want client see my database in own system where SSMS is available as well as client itselft System administrator.

If Answer is no, then i think microsoft should be work for the same. Like password security on database. client only restore the database for the application. By which application can use the database path through the web Config only.

Eeve client should not be rights to run select command on the particular database.

I want only this database is used by application not client.

Because i am only giving database for application, not for sharing information like what & how
i have done in database.


Thanx & Regards,
Sachin.



Post #988898
Posted Sunday, September 19, 2010 7:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
There is no way you can prevent someone who is a SQL sysadmin from doing what they want with the database. A sysadmin by definition has full permissions to everything on the server.

If you don't want people seeing the DB, you should be hosting it, not giving it to the client.

If it's that you don't want your code copied and changed, put a licence agreement with penalties for violation, like just about every ISV that uses SQL databases does.

You can encrypt views and procedures, but it's easily reversible and at best will slow someone down.

p.s. If you think that your .net code is safe because you gave someone a dll, think again. Unless you used some form of obfuscater, it is trivial to decompile a .net dll and get back the source code. There are free tools that do it. Eg http://www.red-gate.com/products/reflector/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #988901
Posted Sunday, September 19, 2010 7:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
I know the feeling...you put a lot of hard work and time into designing the database objects, and you don't want anyone to look at them because they could infer how to do it themselves, and steal/adapt your ideas;

As Gail said, all you can do is slow them down from looking at your code or data; you cna use the WITH ENCRYPTION command when building your procs/views/functions, but the sysadmin on the box can decrypt them with a tiny bit or research or knowledge.

even your .NET DLL's can be reverse engineered and the code can be read...each project,class, function and proc ....even comments.
scary, huh?

A lot of it comes down to trust and professionalism. if you don't trust someone, you'll want to host the database yourself. most professional DBA's and developers don't care about your code, as long as it runs efficiently.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #988902
Posted Sunday, September 19, 2010 7:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
Lowell (9/19/2010)
I know the feeling...you put a lot of hard work and time into designing the database objects, and you don't want anyone to look at them because they could infer how to do it themselves, and steal/adapt your ideas;


Personal opinion: Worrying about that is silly.

If someone wanted to write an app, they would. If they're buying yours it's because they don't have time/skills to do it themselves.

Take for example Microsoft sharepoint. Written in .net and SQL, all the source code is accessible. Does that mean I'm likely to spend hours pouring over the code to write a sharepoint clone for myself instead of using Sharepoint itself? I could, but it would be utterly stupid to do so.

It's highly unlikely that there's anything so revolutionary in your code/db design that it's worth someone reverse engineering and taking. If you feel differently, host the databases yourself.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #988903
Posted Sunday, September 19, 2010 10:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 10:49 PM
Points: 92, Visits: 340
I hope microsoft will provide, any facility for the same in future.

Sometime such cases come for DBA or developer. He don't want to share own database


Sachin
Post #988947
Posted Sunday, September 19, 2010 10:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
kumar.sachu08 (9/19/2010)
I hope microsoft will provide, any facility for the same in future.

Sometime such cases come for DBA or developer. He don't want to share own database


Sachin


the technology already exists. you can load/unload an xml file to and from your datasets (dataset.ReadXML and dataset.WriteXml methods), and encrypt/decrypt the xml file so no one can look at it.

Remember the objective of an agency having SQL Server is they have control over their own data. if you don't agree with that premise...that the clients data is theirs even though you built the framework the data will reside in, then you'll need to shift to less popular but more secure formats.

you just cannot use SQL Server and hide everything from the administrator of that system.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #988948
Posted Sunday, September 19, 2010 10:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
kumar.sachu08 (9/19/2010)
Sometime such cases come for DBA or developer. He don't want to share own database


In those cases the DBA or developer should host the database themselves. If you give a database to someone else, you are giving them full control over it.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #988950
Posted Sunday, September 19, 2010 11:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 10:49 PM
Points: 92, Visits: 340
GilaMonster (9/19/2010)
kumar.sachu08 (9/19/2010)
Sometime such cases come for DBA or developer. He don't want to share own database


In those cases the DBA or developer should host the database themselves. If you give a database to someone else, you are giving them full control over it.



Other Person (Client) not give me permission to access own server for the security point of view. he is self sql administrator. So, we can't do any such activity. we only give .bak file nothing more.
Post #988955
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse