Re: login security question

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/30/04 

Date: Wed, 30 Jun 2004 16:42:56 +0200

<<1. Say the password for the Application role is found, can a user access the database through the
Query Analyser or Enterprise Manager using the application rolde/password.>>

Yes.

<<2. At present the application tracks the user who makes the changes to the database through the
application. The application passes the userid to the stored procedures. But if I put in a
Applciation role in between, will I still have the actual userid to track who actually did the
inserts and updates through the front-end.>>

Yes. You can see the login id for the users, and you can use the SYSTEM_USER function in, for
example, a trigger to get the login name. The user name, however, will be the app role name. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@discussions.microsoft.com> wrote in message
news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@microsoft.com...
> Bob,
>
> Thanks for your reply. I kinda understand this Application Role approach. Could you please explain
the following:
>
> 1. Say the password for the Application role is found, can a user access the database through the
Query Analyser or Enterprise Manager using the application rolde/password.
>
> 2. At present the application tracks the user who makes the changes to the database through the
application. The application passes the userid to the stored procedures. But if I put in a
Applciation role in between, will I still have the actual userid to track who actually did the
inserts and updates through the front-end.
>
> Thanks in advance,
> Sunanda.
>
>
> "Bob Simms" wrote:
>
> > "Sunanda" <Sunny@discussions.microsoft.com> wrote in message
> > news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@microsoft.com...
> > > Hi,
> > >
> > > I have an application which connects to the SQL server. We have several
> > users logging into this application. All of their user-id, passwords are
> > validated and converted to an owner profile, which is then used throughout
> > the application.
> > >
> > > My problem is, this owner profile should be prevented from accessing the
> > database directly using Enterprise Manager or Query Analyser. The database
> > should be accessible only from the application for this owner/global
> > profile.
> > >
> > > How do I go about achieving this. The application was set up like this by
> > a person long time back who is not with us anymore. Also, I do not know SQL
> > Server Administration. So, please detail out what information I have to look
> > up and what steps I will have to follow.
> >
> > If you can alter the code in the client application, you can use application
> > roles.
> >
> > 1) Use Enterprise Manager to access the database / roles.  New Role.  click
> > the Application Role radio button and give it a nice secure, obscure
> > password.
> >
> > 2) Give the Application Role the appropriate permissions.
> >
> > 3) Revoke the users' permissions
> >
> > 4) in the code of the application, put in a call to a stored procedure
> > called (I think, from memory) sp_setAppRole  (F1 for application role to see
> > what the stored proc is called) using the secret password for the App Role
> > (which you don't share with the end users).
> >
> > Now your users will have the appropriate permissions when using your app,
> > but not when using QA or any other app.
> >
> > On an entirely different tack, you can try Group Policies.  Use a GP to tie
> > down their desktop so that they are not allowed to run Quey Analyzer or
> > Enterprise Manager.
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
> >
> >
> >