Dear netters,
We are looking to build a SQL Server database that will be hooked up to
a Web server (WebLogic or a .NET).
The database will house data for multiple customers, and the
requirement is to have no customer see other customer data. Web server
will be responsible for authenticating users (ids and passwords will be
maintained there/possibly stored in the database, but will not be
actual database logins) -- and establishing pooled connections to the
database, using some sort of a proxy login.
Ideally, we do not want to have the database itself visible to "the
world", it should reside behind a firewall, and be accessible through
the Web server, which will do all authentication and routing.
We want to have either tables or views defined in such a way that:
1. Users can only see their own data and NEVER can see anybody else's.
2. Users' access to the database is logged (this includes updates as
well as queries).
3. We would like to provide some sort of a reporting mechanism -- where
users can form their own queries. So, something like a Crystal or an
Access front end, that users are typically familiar with... The problem
is that this requires users having direct access to the database (?),
and opens up tables (unless, there is a way to create parameterized
views that will always restrict access to a subset of records).
Any advice on how to best implement this is greatly appreciated.
Thank you in advance! 4 1439
Step 1 will be to hire an experienced database developer. Not trying
to sound sarcastic, but with a project of this size, you'll need
someone with experience. Otherwise, you'll open up a lot of security
holes as you are attempting to figure it out.
Second, read up on stored procedures. These should be used as your
method of retrieving information from the database, and they should be
written to require user-specific parameters, therefore mitigating your
exposure.
Third, there are a number of reporting tools that offer client-side
GUI. We're working with XtraReports, but I'm sure there are others.
Your application design should retrieve the data (via stored
procedures) into client-specific resultsets. Your clients can then use
the reporting tool to analyze ONLY the data that you provide in the
structure that you provide.
HTH; I'm sure others have some opinions that they'll share.
Stu
Stu,
I totally agree with your comment about the experienced DB developer.
However, for us to be able to make the determination, we need to be
prepared and understand what architectural solutions others are using.
Therefore, my questions still remain -- what are the best practices
that people in the industry use for similar types of applications?
Thanks!
--Alex
I guess I don't understand your question; it's late, and I probably
shouldn't be trying to help anybody at this point :) I'll try to
answer the two questions you posed previously using some experience
from my past, but I don't know how much detail to provide.
We have our web server on a DMZ, and our database on the inside of a
firewall. We restrict port access from the DMZ to the firewall using
SSH, and we use SQL Server logins (as opposed to Windows
Authentication) to avoid opening up every freakin port in the book
(note to Microsoft; is there a better solution to Windows
Authentication across a DMZ?).
We use stored procedures to access the database; a primary component of
each stored procedure is a UserID (which is associated with a user's
login). Users are restricted to their data by that userID; in other
words, user X cannot view User Y's data because their id's don't match.
Our UserID's are GUID's, in order to prevent "ID guessing"
Logging gets a bit tricky, because every stored procedure is executed
using the context of the SQL Server ID used in the connection string.
Our logging needs have alwayse been simple, and so they are met by a
transaction queue, where the userID and data action details are
written.
Is that what you mean by "best practices?" I guess I'm just trying to
assure you that what you want to do is possible, and this is A method
for doing so. The actual mechanics of implementing this methodology
can be complex, depending on the nature of the database.
HTH,
Stu
[posted and mailed, please reply in news]
Alex (av*****@gmail. com) writes: The database will house data for multiple customers, and the requirement is to have no customer see other customer data. Web server will be responsible for authenticating users (ids and passwords will be maintained there/possibly stored in the database, but will not be actual database logins) -- and establishing pooled connections to the database, using some sort of a proxy login.
If security is your prime concern, I would recommend having one database
per customer. In theory, it is not a big to deal to key tables with
user id etc to separate customers, but bugs occur, and the result
can be fatal.
Of course, having multiple databases brings on other problems, particularly
when it comes to maintaining tables, stored procedures etc. Good routines
for configuration management is essential.
1. Users can only see their own data and NEVER can see anybody else's.
This can be achieved with views relatively easy. However, it is possible
for a skilled person to get some information from such a view, even he
does not have direct access to the data. As long as access is only
through the application, you can probably ignore this risk. But next
you talk about users being able to create their own reports, which
would mean that they have access to some query tool and direct access
to the database.
2. Users' access to the database is logged (this includes updates as well as queries).
Look at http://www.lumigent.com. Their Entegra product can handle this.
It't may not work too well, if you use a proxy login though.
3. We would like to provide some sort of a reporting mechanism -- where users can form their own queries. So, something like a Crystal or an Access front end, that users are typically familiar with... The problem is that this requires users having direct access to the database (?), and opens up tables (unless, there is a way to create parameterized views that will always restrict access to a subset of records).
I can't see how a proxy login would work in this case.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Sarah Tanembaum |
last post by:
I was wondering if it is possible to create a secure database system
using RDBMS(MySQL, Oracle, SQL*Server, PostgreSQL etc) and web
scripting/programming language(Perl, PHP, Ruby, Java, ASP, etc) combination?
I have the following in mind:
I wanted to store all my( and my brothers and sisters) important
document
information such as birth certificate, SSN, passport number, travel
documents, insurance(car, home, etc) document, and other...
|
by: Steve Jorgensen |
last post by:
In a recent thread, RKC (correctly, I believe), took issue with my use of
multiple parameters in a Property Let procedure to pass dimensional
arguments on the basis that, although it works, it's not obvious how the
code works if you don't know the intricacies of the Property Let/Get
syntax. Likewise, I dislike (and code to minimize the use of) the VB/VBA
syntax of returning a value by referring to the function name as if it were
a...
|
by: Annette Massie |
last post by:
I have a database that contains complaint information in regards to
Law Enforcement. They need to have this database secure and out of
reach for all but two users. (including me, they say). In other words,
the data they place in this table is higly sensitive and no one other
then a few users must have access to it. If there are design changes,
they would like to give me access to make the changes and they lock it
down again.
Suggestions...
|
by: Norm |
last post by:
Hi All,
I have an MDB file which I want to remain secure. It checks for certain
parameters upon startup, and will automatically exit if the program is
opened/executed by an unauthorized user.
My concern is that somebody could read the MDB file using a utility such as
MDB Tools (http://mdbtools.sourceforge.net/) and then export the data from
my file. I have not used this tool, so is that actually possible using
this or some other...
|
by: Lyle Fairfield |
last post by:
There is an MS-SQL table named Bugs_Comments_and_Suggestions.
There is a form named Bugs_Comments_and_Suggestions.
To allow John Doe to use this form, we GRANT him LOGIN and ACCESS permissions
to the db and SELECT permissions on the stored procedure which is the record
source for the BOUND form.
To allow John Doe to "UPDATE, INSERT, DELETE" using the BOUND form, we GRANT
John Doe "UPDATE, INSERT, DELETE" permissions on the table,
| |
by: Jason Smith |
last post by:
I have recently designed an application in Ms Access with the
folllowing security:
1) Database is split into a front-end / backend with linked tables
2) All modules are password protected
3) Bypass shift code used to disable bypassing startup routines
4) Users enter database through login screen which checks login from
an Access table. This table is imported; not linked in the front-end.
5) The menubar on the startup form has been set...
|
by: Seth |
last post by:
I have noticed that the id of my session object changes
when I switch from a non-secure to a secure connection.
What I'm trying to do:
I have a cookie that is built on the non-secure side of
things. What I need to do is to switch to a secure
connection and then later on while still in that secure
connection delete the cookie that was created on the non-
secure side. I need to do this because I can not
reference the non-secure cookie...
|
by: alex.hatzisavas |
last post by:
Dear all,
I'm trying to do a selective deletion from an 'Archive' table based on
dates.
Here is the setup:
Table 'Archive' has a date field ( ).
Another table ('Cutoffs') defines the Low and High Cut-off dates
(fields: , respectively).
|
by: =?Utf-8?B?Q3JhaWc=?= |
last post by:
If I have an application that I send out to users, and the application
interacts with the database (behind the scenes, no direct sql creation by the
users)....do webservices make the app more secure? I always thought of
webservices as just a good way to allow users to have an API for them to
interact with the database, but are webservices useful if the user never
really knows that they are there?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |