Erland Sommarskog wrote:
> Annonymous Coward (me@[EMAIL PROTECTED]
) writes:
>> I am writing an application which I will deploy to my clients. It is
>> im****tant for security, sup****t, IP reasons etc, that the users are not
>> able to access my databse schema (i.e. view/modify/run procs etc).
>>
>> What do I have to do to ensure that users will not be able to access my
>> database?
>
> It's a little unclear what you mean here. Someone must be able to access
> the database, or else the database will not be very useful. In any case,
> you can never prevent a user with admin rights in Windows from stopping
> the SQL Server service and attaching the database to another instance.
> But obviously when it comes to normal users, you can of course keep them
> out by not granting them access to the database. That assumes that
> they have no need to access your application.
>
> If your question is how to prevent them from accessing the database from
> outside your application, there are a couple of options, but keep in
> mind that you can never lock out an admin. And all these options require
> that you stick to a certain architecture of your application.
>
> 1) Put all logic in stored procedures, so if a user runs a stored
procedure
> from a query window, nothing evil will happen. That is, the procedure
> should perform all security checks needed.
>
> 2) Use an application role. This solution requires a middle tier on a
> separate machine to be secure. If you do this with a two-tier
solution,
> you need stored the password in the client tier, and obviously it
> can be found, even if you hide it. On a middle tier, you can stored
> the password in a place where users do not have access.
>
> 3) Use a proxy login. Again, this solution requires a middle tier to
> be safe. The middle tier authenticates the users, and then logs into
> the application with its own login. The users do not need to have
> logins in SQL Server.
>
Not to put too fine a point on it (i.e. to be blunt). I don't want end
users looking "inside" the database (i.e. to see table names, stored
proc names, and be able to analyse stored proc logic etc). Additionally,
I do not want them to be able to run my stored procs (outside the app),
in order to attempt to modify/hack the database - apart from
intellectual copyright issues, sup****t/maintenance will become a
nightmare, if users are able to access the database (outside of my app),
and "hack around".
What I currently have (my existing PostgreSQL solution), is that I use
roles grants and sufficiently 'hard' passwords, which are stored
(encrypted) in my application modules. Each application module has a
role, and obtains a db connection from the db connection manager (for
first time access), by providing the pool manager with its credentials.
Furthermore, I use grants liberally throughout the schema, to make sure
only the necessary modules have permission to use particular objects in
the database.
I want to know if I can have a similar security scheme using SSE. I
would like to know whether the administrator of a machine can still
"overide" my security and "use" my database (outside my app), even
though I have grants for specific roles (which require passwords) on the
database objects?
>
>> Also, I am thinking of installing SSE as a seperate instance with a
>> unique name - the idea being that it keeps my database away from any
>> that may exist on the clients machine - and thus provides extra
>> security. Is this a safer alternative than installing SSE under the
>> default SQLServer instance name?
>
> I think you should give the person who installs your application the
choice
> of installing a new instance, or reusing an existing instance. I think
> most users would prefer the latter. I would also suggest that this is
> a more secure solution for the users, since it reduces their surface
> area.
>
>
In the case that the user chooses to reuse an existing instance
(presumably over which they have admin/login rights), does this mean
that the user can interrogate my database in the manner described above
(i.e. looking at tables, stored proc etc), even though they are not
members of the roles granted access to the various database objects?
|
