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

Housing a Small Database on Removable Flash Drive Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 1:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:28 AM
Points: 8, Visits: 25
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.
Post #1499044
Posted Friday, September 27, 2013 3:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 15,541, Visits: 27,919
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1499285
Posted Friday, September 27, 2013 5:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:28 AM
Points: 8, Visits: 25
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?
Post #1499333
Posted Friday, September 27, 2013 6:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 15,541, Visits: 27,919
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1499337
Posted Friday, September 27, 2013 6:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:28 AM
Points: 8, Visits: 25
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.
Post #1499346
Posted Friday, September 27, 2013 6:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 15,541, Visits: 27,919
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1499352
Posted Friday, September 27, 2013 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:28 AM
Points: 8, Visits: 25
Grant, thanks, you've been quite helpful. Eddie
Post #1499368
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse