Keith,
Yes, you are right. Usage of the sp_setapprole is really just relying on
anyone having >>its<< password isn't it?
Thanks.
Regards,
Uday
.....................................
Direct: +61-2-98059739
Mobile: +61-401 705907
Email: [Email address protected]
-----Original Message-----
From: Moore Keith
[mailto:mssqldba-ezmlmshield-x41354950.[Email address protected]
Sent: Thursday, 27 October 2005 10:11 AM
To: LazyDBA Discussion
Subject: RE: SQL 2000 - Application role - Help!!!
End-users still need to be able to connect to the database in order to call
sp_setapprole.
I was able to call sp_setapprole on a database to which I only had connect
access.
The security around application roles is dependent on the password. If
someone obtained this password they would effectively have access to the
database (depending on the permissions of the approle).
I'm not saying don't do it, just be aware of the consequences.
_______________________________________________________________
Keith Moore
-----Original Message-----
From: Uday Shivamurthy
[mailto:mssqldba-ezmlmshield-x2454679.[Email address protected]
Sent: Thursday, 27 October 2005 9:52
To: LazyDBA Discussion
Subject: RE: SQL 2000 - Application role - Help!!!
Keith,
Two questions on this note:
Why can't I just drop all end-user logins. Retain only developer login
accounts using integrated security. This way I don't have to go to the
extent of creating groups and assigning the app-role login to those groups.
Are you sure a user with *just* a login to sql and *no* permissions at all
can issue the sp_setapprole after connecting successfully?
Regards,
Uday
.....................................
Direct: +61-2-98059739
Mobile: +61-401 705907
Email: [Email address protected]
-----Original Message-----
From: Moore Keith
[mailto:mssqldba-ezmlmshield-x18080405.[Email address protected]
Sent: Thursday, 27 October 2005 9:08 AM
To: LazyDBA Discussion
Subject: RE: SQL 2000 - Application role - Help!!!
Maybe a better approach would be to create groups and assign proper
permissions to the database objects for those groups. Using the application
role approach a user could still connect to the database using QA and the
call sp_setapprole. If there is only one application role with full
permissions to the database this could be a serious security violation.
_______________________________________________________________
Keith Moore
-----Original Message-----
From: Smith Dano
[mailto:mssqldba-ezmlmshield-x33219179.[Email address protected]
Sent: Thursday, 27 October 2005 2:53
To: LazyDBA Discussion
Subject: RE: SQL 2000 - Application role - Help!!!
I will grossly over-simplify it for you.
Create an app role - See BOL for syntax.
Everywhere the application opens a connection,
The connection object has to have the sp_setapprole procedure called to
"switch" the context to the application role with escalated rights in
the DB's.
So if you are lucky there is a class that has a method "GetConnection"
where this can be done in one place.
If the application is not using a common place to "get" a connection,
everywhere a connection is opened the sp_setapprole will need to be
executed immediately after the cn.Open() call.
Then, in the db, you allow DOM\Users to be valid logins only, with no
rights.
Assign all rights to the app-role. While a user could still connect
with QA, they could not do anything in the db....
Dano Smith
-----Original Message-----
From: Natalia Brett
[mailto:mssqldba-ezmlmshield-x52326139.[Email address protected]
Sent: Wednesday, October 26, 2005 11:55 AM
To: LazyDBA Discussion
Subject: SQL 2000 - Application role - Help!!!
Importance: Low
Hi,
our company has in-house application ("IDT") working with SQL 2000
database.
We use windows authentification.
I need to stop staff from accessing SQL server via any others
applications (such as Query Analyser, Enterprise Manager or Excel).
As I understand the Application role of SQL server should help, but I
dont undestand how to implement it.
Please,
Help!!!
The information within this e-mail is confidential and intended solely
for the use of the recipient(s). If you are not the intended recipient
then please notify Natalia Brett via email at Natalia.[Email address
protected] or by telephone on +44(0)1244 319912. Then delete the email
from your system. Please do not read, copy, print, forward, disclose, or
use the information contained within this email. Note that any views or
opinions presented in this email are solely those of the author and do
not necessarily represent those of Aktiv Kapital. Finally, the recipient
should check this email and any attachments for the presence of viruses.
The company accepts no liability for any damage caused by any virus
transmitted by this email.
Aktiv Kapital (UK) Ltd
Merchants House
Hamilton Place
Chester
CH12BE
UK
www.AktivKapital.co.uk
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------
Legal Notice: This electronic mail and its attachments are intended solely
for the person(s) to whom they are addressed and contain information which
is confidential or otherwise protected from disclosure, except for the
purpose they are intended to. Dissemination, distribution, or reproduction
by anyone other than their intended recipients is prohibited and may be
illegal. If you are not an intended recipient, please immediately inform the
sender and send him/her back the present e-mail and its attachments and
destroy any copies which may be in your possession.
---------------------------------------------------------
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------
Legal Notice: This electronic mail and its attachments are intended solely
for the person(s) to whom they are addressed and contain information which
is confidential or otherwise protected from disclosure, except for the
purpose they are intended to. Dissemination, distribution, or reproduction
by anyone other than their intended recipients is prohibited and may be
illegal. If you are not an intended recipient, please immediately inform the
sender and send him/her back the present e-mail and its attachments and
destroy any copies which may be in your possession.
---------------------------------------------------------
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page