1

I am creating a product software that many people will install on their machines themselves.

It is a VB.Net - SQL Server application.

I certainly don't want a user to have some SQL Server GUI tool like SQL Server Management Studio installed and then fiddling with the database values / definitions directly.

But when my customer will install the application (and the database - and SQL Server instance if there isn't any) with his own (most probably system admin) account, that account is obviously going to be authorized to access, read and modify the database.

Is there any way to prevent it?

We tried to keep .mdf file with app, but that can be attached manually.

We tried to use .sdf file, which allows it's own password to be created, and we loved the idea initially, but that is ok for single project .net solution. For a .net solution with multiple projects (e.g. one for user info and authentication, one for staff payroll etc.), it takes 1 copy of that file for each project, rendering that idea useless.

Improve this question
3
  • If the user is going to "hack" the system (or rather, if such modifications can compromise the system integrity) so trivially then you've already lost .. Commented Jun 8, 2013 at 5:52
  • :) Well, it's not technically sensitive information, but the software is supposed to manage the database, and that is why it is created. Or else I would have preferred only to deploy the database and ask them to manage and modify and insert into it manually... what say?! Well, that is where I can be said to be lost. Commented Jun 8, 2013 at 6:10
  • @MitchWheat, Not really. How can I allow him to EDIT it manually?! I mean, what if he removes his own salary record for last month, and then according to the result from software, either claim for it again, or due to that the customer claims that salary from my company as the software went wrong?! Commented Jun 8, 2013 at 6:12

2 Answers 2

Reset to default
3

The standard way to manage application/database permissions is to create database schemas and roles and assign permissions to those roles, such as ReportViewer (with data reader only access), SensitiveDataViewer (for salaries etc) and remove all default access on your database access.

Then you create logins using domain groups (preferably) or users, and add those to roles. If your are on a domain and use domain groups, you can then manage access through Active Directory user membership of groups.

BUT, you can't stop a sys admin looking at/changing data. If you manage access through roles you are shifting the responsibility of security to the system administrator(s), and that's a good thing.

You could encrypt your entire database, but a sys admin could still circumvent this if they really wanted to.

Database Engine Permission Basics

Manage Access and Roles with Transact-SQL (T-SQL) Commands

Understanding the Difference between Owners and Schemas in SQL Server

Schema, Owner for objects in MS SQL

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

"BUT, you can't stop a sys admin looking at/changing data. If you manage access through roles you are shifting the responsibility of security to the system administrator, and that's a good thing." Two thumbs up. Seems like this needs to be mentioned in EULA, then. :)
1

I do not believe that there is anyway to prevent an database user from viewing or otherwise altering a database installed on a SQL Server instance to which they have administrative access. However, it is possible to make it very difficult (but not impossible) to view or modify stored procedures. Microsoft uses this approach with their GP accounting software.

To create stored procedures that cannot be reverse engineered easily, create them with the ENCRYPTION option. See http://msdn.microsoft.com/en-us/library/ms187926.aspx for more detailed info.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.