Prev: FREE SOFTWARE DOWNLOAD
Next: Deploying an app with SQL Server (installation on client machine)
From: Annonymous Coward on 27 Jun 2008 10:29
I am writing an application which I will deploy to my clients. It is
important for security, support, 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?

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?
From: Erland Sommarskog on 27 Jun 2008 17:32
Annonymous Coward (me(a)home.com) writes:
> I am writing an application which I will deploy to my clients. It is
> important for security, support, 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.


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


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Annonymous Coward on 28 Jun 2008 06:28
Erland Sommarskog wrote:
> Annonymous Coward (me(a)home.com) writes:
>> I am writing an application which I will deploy to my clients. It is
>> important for security, support, 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, support/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?
From: Erland Sommarskog on 28 Jun 2008 06:49
Annonymous Coward (me(a)home.com) writes:
> 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, support/maintenance will become a
> nightmare, if users are able to access the database (outside of my app),
> and "hack around".

I'm still not clear over the scenario. Do you work with an application
that is used inside a corporation and you don't want regular users to
access the database outside the application, but you agree to permit
sysadmin people to access it? Or do you sell a general product, and
don't want your customers to access your database outside the application?
Since you talk about intellectual property rights, I assume that this is
your case.

Again, there is no way that you can prevent anyone who has sysadmin
rights to access your database. You can make things more difficult
by adding the WITH ENCRYPTION clause to stored procedure, but this is
not encryption, only obfustication, and methods to regain the clear
text of the procdures can easily be found on Google.

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

But this is not secure. It the application can decrypt the password, so
can the user, because the application must get the decryption key from
somewhere the user has read access.

You can achieve the same in SQL Server with application roles, but as I
pointed out in my previous post, this is not a secure solution on a
two-tier application, unless security by obscurity is good enough for
you.

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

Yes, they can. And without having seen PostgreSQL, I would be very surprised
if the case is the not same there.

In any case, the one place where you should regulate undesired access to
the database is the license and support agreements.
--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Annonymous Coward on 28 Jun 2008 07:40
Erland Sommarskog wrote:
> Annonymous Coward (me(a)home.com) writes:
>> 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, support/maintenance will become a
>> nightmare, if users are able to access the database (outside of my app),
>> and "hack around".
>
> I'm still not clear over the scenario. Do you work with an application
> that is used inside a corporation and you don't want regular users to
> access the database outside the application, but you agree to permit
> sysadmin people to access it? Or do you sell a general product, and
> don't want your customers to access your database outside the application?
> Since you talk about intellectual property rights, I assume that this is
> your case.
>
> Again, there is no way that you can prevent anyone who has sysadmin
> rights to access your database. You can make things more difficult
> by adding the WITH ENCRYPTION clause to stored procedure, but this is
> not encryption, only obfustication, and methods to regain the clear
> text of the procdures can easily be found on Google.
>
>> 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.
>
> But this is not secure. It the application can decrypt the password, so
> can the user, because the application must get the decryption key from
> somewhere the user has read access.
>
> You can achieve the same in SQL Server with application roles, but as I
> pointed out in my previous post, this is not a secure solution on a
> two-tier application, unless security by obscurity is good enough for
> you.
>
>> 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?
>
> Yes, they can. And without having seen PostgreSQL, I would be very surprised
> if the case is the not same there.
>
> In any case, the one place where you should regulate undesired access to
> the database is the license and support agreements.

Thanks for the clarification. It seems there is little incentive for me
to move (atleast at this stage), from PG to SSE (I have written custom
functions in C that I would have needed to export over to SSE using CLR
with C# and PInvoke). It seems that there is not much to be gained from
the moveover for now, as I need to release a version shortly. But thanks
for your input.
 | 
Pages: 1
Prev: FREE SOFTWARE DOWNLOAD
Next: Deploying an app with SQL Server (installation on client machine)