Application based security
-
I work in a large development environment and am trying to find a good way to secure the application logins. Each application written by the development staff (.NET) is given a specific application login which has only those permissions which the application requires.
The problem is that the userid & password for that application are put in the connection string of the application, so basically any developer can open that code, look at the login/password and connect using that account. Putting the information in a UDL is no different, becuase a UDL is clear text anyway.
Does anyone have any suggestions on accomplishing this, so the developers have no idea what the username/password is for the account?
-
We actually use the UDL method but we place the UDL files in a secured location that can only be opened by the DBA group.
-
A better way is to use NT Groups in your active directory. Set up to use Integrated Security on the database. Create a group, add your application users, then add the group name to a role in the database. Then, there is no need to put the userid/password into the connection string and it will keep out anyone not in the group.
There is a little extra overhead of adding and removing users to the group, but if you make it part of your new employee/exiting employee process its not too burdensome.
-
By using Windows Authenication, how do you differentiate whether the user is updating through an application vs client tools. If through the application you require the user to update, but through client tools you want read only. Anyway to accomplish this?
-
Using Windows authentication you can't. The only way you could differentiate permissions is by resorting to an application role. However, that has its own issues.
K. Brian Kelley
@kbriankelley -
if you know the SPID of the process currently executing, you could look at the program_name column from master.dbo.Sysprocesses in your stored procedures.
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame)) as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
from master.dbo.sysprocesses (nolock)
-
True, but that can easily be faked.
Create a file DSN and you can set the app name to anything you wish.K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply