Re: SQL 2000 Windows Authentication - Same User Multiple Groups
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 21 Aug 2006 21:22:40 -0500
How can we configure our database to ensure that User1 can perform CRUD
functionality using our WinForm app and read-only functionality using
our 3rd party app?
SQL Server permissions are not application-aware. There isn't really
anything you can do on the back-end to change user security context
depending on the application the user is running. If you need to change
security context after connecting with Windows authentication, the
application will need to change the context by either:
1) enabling an application role
2) reconnecting with a SQL login
3) invoking EXECUTE AS
For legacy apps, I've used #2 since it's the easiest to implement - perform
the initial connection using Windows authentication and, if successful, use
a more-powerful SQL login for subsequent access.
Keep in mind that a user can also connect using other programs, like Query
Analyzer, VBScript, etc. For this reason, it's usually best so start with
minimal user permissions and then elevate by changing permissions as
described above.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<d_lepre@xxxxxxxxxxx> wrote in message
news:1156172021.086641.93640@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Dan,
Thanks for the response. Here's the real issue ... perhaps my example
confused things but I hope this clears things up.
- We have a user ("User1") that is in two different Windows user
groups: "WinGroup1" and "WinGroup2".
- We have two applications that interface with the same SQL Server
database ... one app is an internally developed VB .NET WinForm app via
ADO.NET ("App1") and the other one is a 3rd party app via ODBC
("App2"). Ideally, both apps should connect via Integrated security
(we don't want to manage individual users).
- User1 is a member of WinGroup1 and WinGroup2, and he is also a user
of App1 and App2.
- App1 contains CRUD functionality using stored procs (EXEC perms on
all stored procs for WinGroup1).
- App2 contains some CRUD functionality using stored procs but we want
to ensure only the read-only stored procs can execute (EXEC perms on
only SELECT stored procs for WinGroup2).
How can we configure our database to ensure that User1 can perform CRUD
functionality using our WinForm app and read-only functionality using
our 3rd party app?
Thanks,
- Dan
Dan Guzman wrote:
We want to allow Dan to execute both the SelectResearch and
UpdateResearch stored procs. However, because we have denied the
Resarch group (of which he is also a member), he won't be able to
execute UpdateResearch.
Due to the "additive" nature of SQL integrated/Windows permissioning,
is there a workaround to the "same user in different groups with
different object permissions" issue?
Personally, I use GRANTs almost exclusively and avoid explicit DENY
except
in special cases. IMHO, GRANTs are easier to understand and manage.
It's unclear to me why you explicitly denied execute on UpdateResearch to
the Research group in your example. If you were to revoke this deny
permission from Research, it seems to me you would achieve the desired
result; Bob could not execute the proc because he is a member of only
Research yet Dan could because of his IT role membership. Is there
another
scenario in your environment that requires the deny?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<d_lepre@xxxxxxxxxxx> wrote in message
news:1155912277.053570.244930@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
We have a SQL 2000 server with multiple databases. We currently manage
security at the user level but we're trying to clean things up and move
to using integrated Windows groups.
My question is: what is the best practice for assigning stored proc and
view-level permissions such that we can permit/deny a database action
on the same database object for a user that belongs to more than one
Windows group?
For example ... we have a Research database that is used by 2 groups of
Windows users: Research and IT. Bob is a member of Research but not
IT. Dan is a member of both Research and IT. There are 2 stored
procedures in Research: SelectResearch (performs a select against 1
table) and UpdateResearch (performs an update to 1 table) ... we have
assigned Execute permission on SelectResearch to Research and IT and
Execute permission on UpdateResearch to only IT (and explicitly Denied
to Research).
Due to his group membership, Bob will be able to execute SelectResearch
but not UpdateResearch ... easy enough because Bob is only in one
Windows group and we have assigned the appropriate group permissions on
each of the procs.
We want to allow Dan to execute both the SelectResearch and
UpdateResearch stored procs. However, because we have denied the
Resarch group (of which he is also a member), he won't be able to
execute UpdateResearch.
Due to the "additive" nature of SQL integrated/Windows permissioning,
is there a workaround to the "same user in different groups with
different object permissions" issue?
FYI - we looked into Application roles but they would involve code
changes and we understand that there are connection pooling and other
ADO issues that might cause problems with some of our legacy (COM+/VB6)
applications.
.
- References:
- SQL 2000 Windows Authentication - Same User Multiple Groups
- From: d_lepre
- Re: SQL 2000 Windows Authentication - Same User Multiple Groups
- From: Dan Guzman
- Re: SQL 2000 Windows Authentication - Same User Multiple Groups
- From: d_lepre
- SQL 2000 Windows Authentication - Same User Multiple Groups
- Prev by Date: Re: Encrypted database deployment
- Next by Date: Re: Encrypted database deployment
- Previous by thread: Re: SQL 2000 Windows Authentication - Same User Multiple Groups
- Next by thread: Re: SQL 2000 Windows Authentication - Same User Multiple Groups
- Index(es):
Relevant Pages
|
|
