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)
>