Database Security Question - Can this be done?

Hello,
I am new to MS SQL Server and I am in the process of implementing a
database system which introduces an interesting security issue that I
was hoping some one could advise me on.
BACKGROUND: I am developing a client / server application that which
requires users to be able to download data from a global database and
then save this information in a local database. This enables them to
work offline and upload their local data to the global database at a
later date. FYI: The global DB is MS SQL, and the local database is
Paradox.
THE PROBLEM: The issue is that I dont want to give users the
ability/permissions to update, delete records from the global database
- because this would make it easy for hackers to simply corrupt the
database (i.e. delete * from <table> ). Also, the global database
contains data from a selection of companies and I must ensure that each
user can not see the other company's data.
So to summarise I have the following issues?
1. How do I restrict what users can see?
2. How do I prevent users from accessing data I dont want them to
manipulate (ie. restricting update / delete statements).
I would be gratful for any assistance you can provide.
Best regards
Spencer
(satest@.hotmail.com)The short answer is to use stored procedures and place execute permission on
those.
You can then filter out what can be seen by who.
Other methods include views.
Basically don't permission directly on the base tables.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Spence" <satest@.hotmail.com> wrote in message
news:1130760231.566603.254620@.g43g2000cwa.googlegroups.com...
> Hello,
> I am new to MS SQL Server and I am in the process of implementing a
> database system which introduces an interesting security issue that I
> was hoping some one could advise me on.
> BACKGROUND: I am developing a client / server application that which
> requires users to be able to download data from a global database and
> then save this information in a local database. This enables them to
> work offline and upload their local data to the global database at a
> later date. FYI: The global DB is MS SQL, and the local database is
> Paradox.
> THE PROBLEM: The issue is that I dont want to give users the
> ability/permissions to update, delete records from the global database
> - because this would make it easy for hackers to simply corrupt the
> database (i.e. delete * from <table> ). Also, the global database
> contains data from a selection of companies and I must ensure that each
> user can not see the other company's data.
> So to summarise I have the following issues?
> 1. How do I restrict what users can see?
> 2. How do I prevent users from accessing data I dont want them to
> manipulate (ie. restricting update / delete statements).
> I would be gratful for any assistance you can provide.
> Best regards
> Spencer
> (satest@.hotmail.com)
>|||From what you have described, the users don't really need access to the
Global database at all. In fact, they don't even need a login to the server.
What you can use is a DTS package that exports the appropriate from the
Global database to a distributed offline Paradox database located on a
network folder that is accessable by the users. Once the users have finished
inserting/updating/deleting the Paradox database, another DTS package can
migrate the data back into the Global database.
Also, you may want to consider using MS Access instead of Paradox for
the front end application/database. I don't know that much about Paradox,
but I would bet it's options for integrating with SQL Server are much more
limited than MS Access. Here is an article that describes the concepts of an
architecture for migrating data to and from a distributed MS Access
database.
http://www.microsoft.com/technet/pr...bldsysarch.mspx
"Spence" <satest@.hotmail.com> wrote in message
news:1130760231.566603.254620@.g43g2000cwa.googlegroups.com...
> Hello,
> I am new to MS SQL Server and I am in the process of implementing a
> database system which introduces an interesting security issue that I
> was hoping some one could advise me on.
> BACKGROUND: I am developing a client / server application that which
> requires users to be able to download data from a global database and
> then save this information in a local database. This enables them to
> work offline and upload their local data to the global database at a
> later date. FYI: The global DB is MS SQL, and the local database is
> Paradox.
> THE PROBLEM: The issue is that I dont want to give users the
> ability/permissions to update, delete records from the global database
> - because this would make it easy for hackers to simply corrupt the
> database (i.e. delete * from <table> ). Also, the global database
> contains data from a selection of companies and I must ensure that each
> user can not see the other company's data.
> So to summarise I have the following issues?
> 1. How do I restrict what users can see?
> 2. How do I prevent users from accessing data I dont want them to
> manipulate (ie. restricting update / delete statements).
> I would be gratful for any assistance you can provide.
> Best regards
> Spencer
> (satest@.hotmail.com)
>

database reusability with sql server

Hi,

The company I work for often develops databases for clients. Those
databases always need to do similar tasks. One company might need a
database to do task 1 and task 2, while another company might need it
for task 2 and 3. Instead of having to redevelop each database from
scratch, I'd like to be able to develop several "modules" (kind of
mini-databases) representing each of this task, and then chose the
modules which are relevant. Then all I would have to develop would be
the final layer putting together all the information gathered from
those modules. I'm sure this has been done heaps of time before but I
can't really find any information on it. Any of you have any pointers
or experience you would like to share. I'd like to have a good think
about how I'm going to go about it, and the limitations that might
arise before I get stuck into it.

Thanks for your help,

MaelleYou could do this in several ways, depending on your exact requirements and
the tools you have available. Are you going to put all your modules in every
client database, for example, and only 'activate' the ones that client
really needs? Or do you plan to set up a different code base for each
client, based on a different selection of modules every time? (The second
approach could become very complex to manage to support.) Will you have a
common module with global data and code to be used in all other modules?
Etc.

Either way, if you want to 'modularize' your code, it's really up to you to
track dependencies carefully, and ensure you always know exactly which
objects go into each module. Some tools can help with this, e.g. Erwin
allows you to split your data model into functional areas, and you can view
them separately or together. In your source control system, you would group
related tables/procs/functions etc. together.

A good naming convention can also help, perhaps by giving related objects a
prefix to indicate that they are related. You will have to look at the
resources you have available, and how you want to manage code once it's been
deployed - it's difficult to give a more specific answer, as you know your
own application best.

Simon

"Maellic" <maellic2002@.yahoo.fr> wrote in message
news:15aa032f.0306231501.5af14872@.posting.google.c om...
> Hi,
> The company I work for often develops databases for clients. Those
> databases always need to do similar tasks. One company might need a
> database to do task 1 and task 2, while another company might need it
> for task 2 and 3. Instead of having to redevelop each database from
> scratch, I'd like to be able to develop several "modules" (kind of
> mini-databases) representing each of this task, and then chose the
> modules which are relevant. Then all I would have to develop would be
> the final layer putting together all the information gathered from
> those modules. I'm sure this has been done heaps of time before but I
> can't really find any information on it. Any of you have any pointers
> or experience you would like to share. I'd like to have a good think
> about how I'm going to go about it, and the limitations that might
> arise before I get stuck into it.
> Thanks for your help,
> Maelle|||Maellic (maellic2002@.yahoo.fr) writes:
> The company I work for often develops databases for clients. Those
> databases always need to do similar tasks. One company might need a
> database to do task 1 and task 2, while another company might need it
> for task 2 and 3. Instead of having to redevelop each database from
> scratch, I'd like to be able to develop several "modules" (kind of
> mini-databases) representing each of this task, and then chose the
> modules which are relevant. Then all I would have to develop would be
> the final layer putting together all the information gathered from
> those modules. I'm sure this has been done heaps of time before but I
> can't really find any information on it. Any of you have any pointers
> or experience you would like to share. I'd like to have a good think
> about how I'm going to go about it, and the limitations that might
> arise before I get stuck into it.

We are building our product in this fashion. Admittedly the core is still
the largest building stone, but the idea is that a customer that does
not need the XYZ functionality does neither get the tables or the stored
procedures for it.

We've found that this is fairly easy to do with new development, but
as I mentioned the core is fairly large, and if we ever manage to
break it up remains to see. The difficult thing is to get the
dependencies right. If you have modules A and B, you can permit
references in one direction, but not in two.

What we have put quite some effort in, is to have a tool that supports
this concept for installation and updates. I don't know if this is an
issue for you, but we regularly send new versions of our product to
our customers.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Database Performance

Hi, I wonder if someone can answer a quesiton for me: I'm modifying a
database with the purpose of adding the new feature of address change
history. My model would consist of a table for keeping client
name/logon (for a public site) info in one table, and address info in
another table because the login info would likely be more frequently
accessed/changed than address updates. Now a group that does data
entry internally through a web interface always need to see the
address.

For the first stage I don't want to change the old table, just have a
new one for now. But moving forward, I thought it would be neat to
have all address update records in one table and have a Profile type
value to distinguish whether data entry or a public website user
created the update record.

However, a thought occured to me: If one table is responsible for
showing current address as well as adding records whenever there is an
address change, would it hurt performance? Would I get better
performance, splitting the record types into two tables, or does it
matter since the table I'm thinking of creating would have no
deletions: Only insersions and modifying an expiry date field so we
know which record to use. I'm not a specialist on database performance
so if any of you database gurus out that can advise me on that that
would be GREAT. Thanks a million guys.

Jonah A. Libsterjlibster (jlibster@.jalsolutions.com) writes:
> Hi, I wonder if someone can answer a quesiton for me: I'm modifying a
> database with the purpose of adding the new feature of address change
> history. My model would consist of a table for keeping client
> name/logon (for a public site) info in one table, and address info in
> another table because the login info would likely be more frequently
> accessed/changed than address updates. Now a group that does data
> entry internally through a web interface always need to see the
> address.
> For the first stage I don't want to change the old table, just have a
> new one for now. But moving forward, I thought it would be neat to
> have all address update records in one table and have a Profile type
> value to distinguish whether data entry or a public website user
> created the update record.
> However, a thought occured to me: If one table is responsible for
> showing current address as well as adding records whenever there is an
> address change, would it hurt performance? Would I get better
> performance, splitting the record types into two tables, or does it
> matter since the table I'm thinking of creating would have no
> deletions: Only insersions and modifying an expiry date field so we
> know which record to use. I'm not a specialist on database performance
> so if any of you database gurus out that can advise me on that that
> would be GREAT. Thanks a million guys.

With only a narrative and no table descriptions it is a little difficult
to follow.

However, while it is always good to keep performance in mind, first
focus on getting the functionality right. If one way gives you far
less programming than the other, go that road. If performance proves
to be bad, you can improve later. If you take a more complex method
directly, you run a greater risk for bugs.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <993e03ef.0310061301.25705318@.posting.google.com>,
jlibster@.jalsolutions.com says...
> My model would consist of a table for keeping client
> name/logon (for a public site) info in one table,
> and address info in another table because the login
> info would likely be more frequently accessed/changed
> than address updates.

That seems like an unnatural division to me. How does having two tables
(unless these are going to be *very* large tables) equate to better
performance than one table?

-- Rick