September 26, 2013 at 1:41 pm
Is there a practical way to house a small SQL Server database on a removable, networked Flash Drive? I am looking for a way to keep sensitive, Executive payroll data from almost all users, including the SysAdmin. If the authorized payroll operator were to insert the drive, then I (the Developer) could write code to re-link the tables. Then when the operator is done, I unlink the tables and he removes the Flash Drive. Any other ideas are appreciated.
September 27, 2013 at 3:46 am
It's possible, yes. It's just not terribly practical. You could have the data files there, not the tables, and you could attach and detach the database. But, it's going to have to either be a very small database, or you have to do cross database queries.
What about just creating a separate schema within your database and restrict access to it?
Also, you posted this question in a forum dedicated to SQL Azure. That's very different than SQL Server, so you might not get as many answers because different people monitor different forums.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 27, 2013 at 5:59 am
If I set up a separate schema, wouldn't the SysAdmin have access to it? Couldn't he put himself in the group the schema is restricted to?
Also, the "remote" database wouldn't have to be on a flash drive. It just occurred to me that it could reside on the payroll operator's local drive. When he starts to process executive payroll, the Front End could dynamically link to the tables in the database on his local drive, giving the linked tables the name for programs are looking for.
Does this alter your previous response?
September 27, 2013 at 6:11 am
EddieN1 (9/27/2013)
If I set up a separate schema, wouldn't the SysAdmin have access to it? Couldn't he put himself in the group the schema is restricted to?Also, the "remote" database wouldn't have to be on a flash drive. It just occurred to me that it could reside on the payroll operator's local drive. When he starts to process executive payroll, the Front End could dynamically link to the tables in the database on his local drive, giving the linked tables the name for programs are looking for.
Does this alter your previous response?
Yes & no. The biggest issue for me is that a removable device or a remote system are going to be very, very easy to corrupt. If you don't do a proper shutdown and yank the removeable disk or turn off or disconnect or even sleep, the remote system, BOOM! You're suddenly not looking at carefully secured data, but at a corrupted database that is offline with inaccessible data. Which, brings up backups. How are you managing those? Are they encrypted and locked away from the sysadmin?
Generally, most companies recognize the fact that certain people are going to be able to hack into the systems if they choose to. You make it somewhat difficult for them to do it and you set up auditing, and then you go with the fact that these people have been hired into positions of responsibility as professionals and will be expected to behave as such or could face firing and even prosecution. Pretty standard stuff. Most legal auditing requirements that I've seen don't require you to prevent all access, but rather have a mechanism or restricting it (restricting) and auditing it. Keep the list of who can have access very small, maintain that list, know who can do it, know who has done it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 27, 2013 at 6:26 am
SSChampion, I totally agree and will proceed as necessary to "reasonably" protect the visibility of the data and "absolutely" protect the data integrity. Is there a way to "hide" tables in SQL Server (like there is in Access) and at least require the operator to know to "unhide" them. The point is that if the casual user doesn't know they exist, they won't bump into them and open them out of curiosity. Thanks for your comments and suggestions.
September 27, 2013 at 6:47 am
Just through restricting access to it. You have a lot more granular security settings in SQL Server than Access. You'll want to read up on it.
Oh, and my name is Grant, not Champion. See where your handle is on your posts. Otherwise your name is Newbie. Ha!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 27, 2013 at 7:17 am
Grant, thanks, you've been quite helpful. Eddie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply