Protect my sql server database
-
Hello ! I have created an application that use a sql server database. But the problem is that a user can open sql server management studio and can do anything that I don't like , for example : 1) Can modify values inside tables. 2) can delete records 3) Can see and can get my database structure..... ...... How can I protect my database , so that users can modify this database only inside my application , and can do anything outside my application ? I don't know does exist a way to protect my database with a password or.... ??? for example when I was using Access for database , i have protected Access files with a password , and after only my application can open this database . Is possible to include a protection in the sql server database file ( so even the file is imported to another computer to remain protected ? I have a specific situation : My application is designated to manage several "office". In my application folder , i have a empty backup file ( offline) of my database called "Model". And inside my application a user can create "A new office " and when he do this the application restore a copy of "Model" inside sql server with a specific name (for example "Office1". Another time the user can create another "Office" using again a copy of "Model" So inside Sql server may be different databases ( all of them with the same structure ) like "Model" So in this situation how can i Protect my databases and the "Model" . Because i think i should apply a Protection" inside the offline backup file "Model" ( if it's possible , all the databases that have created from "Model" will have the protection ) or ?????? What should i do in this case ? Thank you !
If your users have SA rights using windows authentication then you cannot stop them. However you can restrict user access by implementing a reasonable security/password profile within SQL Server. I hate to just dump a link but the subject is way too large for a forum discussion, do some research into SQL Server Security[^]
Never underestimate the power of human stupidity RAH
-
Hello ! I have created an application that use a sql server database. But the problem is that a user can open sql server management studio and can do anything that I don't like , for example : 1) Can modify values inside tables. 2) can delete records 3) Can see and can get my database structure..... ...... How can I protect my database , so that users can modify this database only inside my application , and can do anything outside my application ? I don't know does exist a way to protect my database with a password or.... ??? for example when I was using Access for database , i have protected Access files with a password , and after only my application can open this database . Is possible to include a protection in the sql server database file ( so even the file is imported to another computer to remain protected ? I have a specific situation : My application is designated to manage several "office". In my application folder , i have a empty backup file ( offline) of my database called "Model". And inside my application a user can create "A new office " and when he do this the application restore a copy of "Model" inside sql server with a specific name (for example "Office1". Another time the user can create another "Office" using again a copy of "Model" So inside Sql server may be different databases ( all of them with the same structure ) like "Model" So in this situation how can i Protect my databases and the "Model" . Because i think i should apply a Protection" inside the offline backup file "Model" ( if it's possible , all the databases that have created from "Model" will have the protection ) or ?????? What should i do in this case ? Thank you !
-
If your users have SA rights using windows authentication then you cannot stop them. However you can restrict user access by implementing a reasonable security/password profile within SQL Server. I hate to just dump a link but the subject is way too large for a forum discussion, do some research into SQL Server Security[^]
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
If your users have SA rights using windows authentication then you cannot stop them.
If users have local administrator rights in Windows, but no rights in SQL, you still can't stop them. :) http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx[^] http://sql-articles.com/articles/troubleshooting/how-to-recover-sa-password-when-you-forget-it-sql-2005/[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
-
Hello ! I have created an application that use a sql server database. But the problem is that a user can open sql server management studio and can do anything that I don't like , for example : 1) Can modify values inside tables. 2) can delete records 3) Can see and can get my database structure..... ...... How can I protect my database , so that users can modify this database only inside my application , and can do anything outside my application ? I don't know does exist a way to protect my database with a password or.... ??? for example when I was using Access for database , i have protected Access files with a password , and after only my application can open this database . Is possible to include a protection in the sql server database file ( so even the file is imported to another computer to remain protected ? I have a specific situation : My application is designated to manage several "office". In my application folder , i have a empty backup file ( offline) of my database called "Model". And inside my application a user can create "A new office " and when he do this the application restore a copy of "Model" inside sql server with a specific name (for example "Office1". Another time the user can create another "Office" using again a copy of "Model" So inside Sql server may be different databases ( all of them with the same structure ) like "Model" So in this situation how can i Protect my databases and the "Model" . Because i think i should apply a Protection" inside the offline backup file "Model" ( if it's possible , all the databases that have created from "Model" will have the protection ) or ?????? What should i do in this case ? Thank you !
You can setup the system to only be accessed through stored procedures that use "execute as". Then just give the permissions that they need to execute the stored procedures. This may mean you need to create a role then grant execute.
-
You can setup the system to only be accessed through stored procedures that use "execute as". Then just give the permissions that they need to execute the stored procedures. This may mean you need to create a role then grant execute.
-
Not really. The local admin has all the rights on the machine. The person that owns the server, owns the databases, and the data. Buy a good server, install it SQL Server, put it in a large box, lock it, and hand that to the customer. That way you would be the local admin. Or host the server from your place, and have them connect to your "SQL Cloud".
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Not really. The local admin has all the rights on the machine. The person that owns the server, owns the databases, and the data. Buy a good server, install it SQL Server, put it in a large box, lock it, and hand that to the customer. That way you would be the local admin. Or host the server from your place, and have them connect to your "SQL Cloud".
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
if you have read my case , what can I do with "Model" file that is offline ? Someone can get and restore to another PC. I read somewhere about encrypting the database. Is this true and how can be done.Can this be a help in my situation ?
-
Hello ! I have created an application that use a sql server database. But the problem is that a user can open sql server management studio and can do anything that I don't like , for example : 1) Can modify values inside tables. 2) can delete records 3) Can see and can get my database structure..... ...... How can I protect my database , so that users can modify this database only inside my application , and can do anything outside my application ? I don't know does exist a way to protect my database with a password or.... ??? for example when I was using Access for database , i have protected Access files with a password , and after only my application can open this database . Is possible to include a protection in the sql server database file ( so even the file is imported to another computer to remain protected ? I have a specific situation : My application is designated to manage several "office". In my application folder , i have a empty backup file ( offline) of my database called "Model". And inside my application a user can create "A new office " and when he do this the application restore a copy of "Model" inside sql server with a specific name (for example "Office1". Another time the user can create another "Office" using again a copy of "Model" So inside Sql server may be different databases ( all of them with the same structure ) like "Model" So in this situation how can i Protect my databases and the "Model" . Because i think i should apply a Protection" inside the offline backup file "Model" ( if it's possible , all the databases that have created from "Model" will have the protection ) or ?????? What should i do in this case ? Thank you !
Dilkonika, One approach is to NOT give users direct access to the machine where database system is running. Have a middle tier server application(eg: tomcat) hiding your database from end users. Users may connect to middle tier server and invoke services. You may implement authentication using some standard framework within middle tier server application. Best of luck!
-
Hello ! I have created an application that use a sql server database. But the problem is that a user can open sql server management studio and can do anything that I don't like , for example : 1) Can modify values inside tables. 2) can delete records 3) Can see and can get my database structure..... ...... How can I protect my database , so that users can modify this database only inside my application , and can do anything outside my application ? I don't know does exist a way to protect my database with a password or.... ??? for example when I was using Access for database , i have protected Access files with a password , and after only my application can open this database . Is possible to include a protection in the sql server database file ( so even the file is imported to another computer to remain protected ? I have a specific situation : My application is designated to manage several "office". In my application folder , i have a empty backup file ( offline) of my database called "Model". And inside my application a user can create "A new office " and when he do this the application restore a copy of "Model" inside sql server with a specific name (for example "Office1". Another time the user can create another "Office" using again a copy of "Model" So inside Sql server may be different databases ( all of them with the same structure ) like "Model" So in this situation how can i Protect my databases and the "Model" . Because i think i should apply a Protection" inside the offline backup file "Model" ( if it's possible , all the databases that have created from "Model" will have the protection ) or ?????? What should i do in this case ? Thank you !
If I understood your question correctly, you're worried about several, different things. As Mycroft said, the topic is way too large to discuss extensively in a post. However, few things I'd like to point out:
- Use proper security definitions on the server file system. No-one but admins should be let to operate with the files. Sql Server users don't need access to files See: http://msdn.microsoft.com/en-us/magazine/cc982153.aspx[^]
- Use either Sql Server or Windows authenticated logins along with proper database roles to limit the statements See: http://msdn.microsoft.com/en-us/library/ms189121.aspx[^] and http://msdn.microsoft.com/en-us/library/ms187965.aspx[^]
- Use different logins for admins
- If needed, encrypt the files See: http://msdn.microsoft.com/en-us/library/bb934049.aspx[^]
