Using application rolls in SQL Server

  • Thread starter Thread starter =?iso-8859-1?Q?Jan_Weing=E4rtner?=
  • Start date Start date
?

=?iso-8859-1?Q?Jan_Weing=E4rtner?=

Hi,

i have just read something about "application rolls" in MSSQL Server.
Is it possible to use application rolls in ADO.Net?

best regards,
Jan
 
Yes.
You create the role on SQL Server.
Then in your Connection String you use the UID and Password of a user in
that Role.
You use this connection string in your app so that every single app user has
the Role and permissions that you assigned. You also get the benefit of
connection pooling since all connections use the same string.
 
Joe,
You use this connection string in your app so that every single app user has
the Role and permissions that you assigned.
Click to expand...

what i want to achieve is to separate the permission to login and
the permissions to the databases/tables.
Only the application should have permission to the tables.
If i add the application roll to the user, the user has all permissions of
the roll - even without the application (i.e. by using of query analyser).

What i had in mind was any kind of using of "sp_setapprole" ... ?

best regards,
Jan
 
Using an application role will achieve your desired results. There are two different types of users being discussed here. The "user" used in the connection string that is a member of the application role is different than the application "user" who you do not want to have access to the tables.
However, you can achieve the same results without using an application role. In fact, you don't even have to give your application users a login to the SQL server at all. You could let your application authenticate and authorize its users and then use a SQL connection string with a fixed *SQL* user that has required access (but is unknown to application users).
 
Jan,
If you really want to use sp_setapprole in your application (IMO not a good
idea) I would highly recommend you turn pooling off, roles cannot be reset
(this would be a security bug) so you can really mess up your pool.

I would also like to warn you that most solutions to separate login
permission from access permission at the application level are going to be
easily defeated. If you really don't want the user to have access to the
database consider a web service solution where the server is not visible to
the user at all.
 
You could let your application authenticate and authorize its users and then use a SQL connection
string with a fixed *SQL* user that has required access (but is unknown to application users).
Click to expand...

This is my solution in any case. The problem is, that the transmitted password is _not_
really encrypted if i use standard security (it's easy to crack login and password) and
i dont want to use trusted/integrated security. So my hope is to increase security by using
application rolls.

best regards,
Jan
 
If you really don't want the user to have access to the
database consider a web service solution where the server is not visible to
the user at all.
Click to expand...

Yes, i agree with you. But the application is not like a slim web
interface and i'm afraid to loose performance(?), if i add an additional
layer between the application an the database.

best regards,
Jan
 
There are performance implications, but don't fool yourself into thinking
that your application is more secure because you are using application
roles. We highly recommend Integrated Security in these types of
applications, and yes, that means that the user will have the same access to
the database that your application has. If you do not trust the user that
much then you should not allow him to see the database at all, any other
solution is a security vulnerability waiting to be exploited.

In any case if you must use app roles I would recommend turning pooling off.
Hope this helps.
 
Hi.
If you do not trust the user that
much then you should not allow him to see the database at all, any other
solution is a security vulnerability waiting to be exploited.
Click to expand...

The basic problem is: SQL-Server does not support permission restrictions
on row level. This is ok, because this would be hard to manage. But i need
such restrictions and in that case the _application_ has to enforce the
restrictions. That's why the application needs access to the whole database
and i can not trust the user.

The other problem is, that SQL-Server does not support encryption of password
in standard security mode - btw: why not?

best regards,
Jan
 
Back
Top