Re: Application Role
From: Ross Presser (rpresser_at_NOSPAMgmail.com.invalid)
Date: 06/08/05
- Next message: Paul: "Cross DB ownership chaining"
- Previous message: David Crone: "SQL Server Security - Firewalls etc"
- In reply to: David Crone: "Application Role"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 8 Jun 2005 10:33:06 -0400
On Wed, 08 Jun 2005 04:56:51 -0700, David Crone wrote:
> My VB.NET application is using SQL Helper class, which basically seems
> to be using Connection Pooling, and creating a new connection for each
> transaction (if none in the pool). I dont want to set the APplication
> Role to be activated in every single call to the DB, as this will slow
> down the application completely, but I would like to somehow implement
> an Application Role.
I think you're going to have to give up the SQLHelper class (the Data
Access Application Block), or at least extend it / replace it with your own
class. Frankly I found the whole Patterns & Practices library to be bloated
and mostly useless ... come on, every connection I want to use has to store
its connection string in App.config? And I should only use app.config
through the classes? Ridiculous.
It sounds like your app would be happy with one SQLConnection. Try this:
Public Class SQLHelp
Public shared cxn as SQLConnection
Public shared ConnectionString as String = ....
Public shared Sub Init()
cxn = New SQLConnection(ConnectionString)
cxn.Open
dim pass as String = ...
dim cmd as String
sqlcmd = String.Format( _
"set_approle @rolename='{0}', @password='{1}'" , _
"MyRole", pass
cxn.Execute cmd
End Sub
Public shared Function ExecDataset(sqlcmd as String) as Dataset
dim ds as new Dataset
dim da as new SQLDataAdapter(sqlcmd, cxn)
da.Fill(ds)
return ds
End Function
End Module
That should get you started. It provides SQLHelp.Init() to open the
connection and set the app role, and SQLHelp.ExecDataset to run a SQL
command and return a dataset. Steal more code from the MS SQLHelper class
if you need it.
> Also, does anyone know - if I set up an Application Role, can Any
> application that has the ApplicationRole password get in and Activate
> the Role, or is there a way to limit it somehow?
The only limitation is that the application must first gain access to the
Database before it can call set_approle. In other words, a valid user in
the database has to open the connection - if Ross Presser isn't defined as
a user (or isin't a member of a windows group that is defined as a user,
etc.) then he can't run approle.
Best practice is usually to store the password encrypted in a file or
registry setting rather than hardcode it into the app, and to change it
periodically.
- Next message: Paul: "Cross DB ownership chaining"
- Previous message: David Crone: "SQL Server Security - Firewalls etc"
- In reply to: David Crone: "Application Role"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
