Solved

using application roles in vb

Posted on 2002-03-14
8
853 Views
Last Modified: 2008-02-01
hi

i am trying to use vb as my front end and sql server as the back end db. I want to use application roles.

I understand that after executing sp_setapprole the users permissions are ignored and the permissions of the application role are used instead.

I have a table called employee where the username 'ms' has no select permissions but the app role 'SalesApp' has select permissions.

Everything is fine until i try to select. How can i select as 'SalesApp' and not as 'ms'. Below is my code:



Private Sub cmdOK_Click()
    Dim Cmd As New ADODB.Command, cmdApplication As New ADODB.Command
    Dim rs As New ADODB.Recordset, employeeRs As ADODB.Recordset, LogRs As ADODB.Recordset
    Dim ApplicationRs As ADODB.Recordset
    Dim param1 As Parameter
    Dim paramApplicationReturn As Parameter, paramApplication1 As Parameter, paramApplication2 As Parameter
    Dim connectString As String
    Dim blnFound As Boolean, blnPasswordChange As Boolean
    Dim strCriteria As String

    Dim strEmployeeCriteria As String, strMessage As String, strTempUsername As String
    Dim i As Integer

    'Add your startup procedures here.
    About.Visible = False
    About.cmdOK.Visible = False
    About.Show
    About.Refresh


    Screen.MousePointer = vbHourglass
    Set conn = New ADODB.Connection
   
    On Error GoTo LoginFailed:
   
   
         
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=Temp;" _
         & "User ID=ms;Password=" & _
         ";Initial Catalog=HelpdeskCL; ConnectionTimeout = 60"
    conn.Open
   
    If (conn.State = adStateOpen) Then
   
        cmdApplication.CommandText = "sp_setapprole"
        cmdApplication.CommandType = adCmdStoredProc
       
        Set paramApplicationReturn = cmdApplication.CreateParameter("Return", adInteger, adParamReturnValue)
        cmdApplication.Parameters.Append paramApplicationReturn
       
        Set paramApplication1 = cmdApplication.CreateParameter("@strUsername", adChar, adParamInput, 20)
        cmdApplication.Parameters.Append paramApplication1
        cmdApplication.Parameters(1).Value = "SalesApp"
       
       
        Set paramApplication2 = cmdApplication.CreateParameter("@strPsd", adChar, adParamInput, 20)
        cmdApplication.Parameters.Append paramApplication2
        cmdApplication.Parameters(2).Value = "SalesTest"
       
        cmdApplication.ActiveConnection = conn
        Set ApplicationRs = cmdApplication.Execute


    Else
        MsgBox ("Failed Connecting")
        Exit Sub
    End If


    strCriteria = "select * From employee where username =" & Trim(txtUsername.Text)
    rs.Open strCriteria, conn, adOpenDynamic, adLockOptimistic, adCmdText

    MDIForm1.Show
   

LoginFailed:
    If paramApplicationReturn = 1 Then
        strMessage = MsgBox("You have failed to log in!  ", vbExclamation)
    Else
        strMessage = MsgBox(Err.Number & "   " & Err.Description, vbExclamation)
        conn.RollbackTrans
    End If

    Screen.MousePointer = vbArrow
    Unload Me
'Unload MDIForm1
End Sub


Rg,
Mehul
0
Comment
Question by:melu
  • 4
  • 3
8 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 6864351
Your problem could be the following line:
cmdApplication.ActiveConnection = conn

replace it by:
set cmdApplication.ActiveConnection = conn

The difference is that the first line will pass the connection string to the command object, which will open a "new" connection. The setapprole will be executed on that connection, and get lost as soon as this line finishes. The original connection object didn't get the new permission settings.

The second line will pass the connection object to the command, and use that one. The connection object WILL have the new permissions.

CHeers

0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6864363
angelIII (or anyone else), what are the benefits of using application roles?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6864386
BOL:
If ad hoc access to a database is not required, users and Windows NT 4.0 or Windows 2000 groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, standardizing on one system-wide password assigned to an application role is possible, assuming access to the applications is secure

The main difference is that everybody could connect in general to the database but not being able to do anything. Using the application role, the application tasks can be done, but the user shoulnd't be able to use QA to do things "manually".

CHeers
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6864390
... of course, using SP can do the same things also, but if you don't want to use SP, and still have a secure database, then you use Application Roles.

CHeers
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:melu
ID: 6870543
Hi All,

To angelIII - no that doesn't seem to work. What i mean that
(refering to the code)

   cmdApplication.ActiveConnection = conn
   Set ApplicationRs = cmdApplication.Execute

executes fine.

The next line

   strCriteria = "select * From employee where username =" & Trim(txtUsername.Text)
   rs.Open strCriteria, conn, adOpenDynamic, adLockOptimistic, adCmdText

i.e rs.Open gives an error because the user 'ms' has no select permissioms on the
employees table but the appilcation role 'SalesApp' has select permissions on this
table.

How can i open this table as the app role?

Thanks,
Rg
Mehul
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6876149
Do you have a "deny select" for the guest user on that table? I tested your code on my system (obviously changing names and passwords), and all worked fine unless I had a deny select on the table for the user guest. I could have a deny on the user ms...
CHeers
0
 

Author Comment

by:melu
ID: 6876345
No i dont - just that i have not checked the select permissions of user ms (i.e its neutral).

i mean it works b/c if you view the properties of the conn object - its using ms loginname and password etc.

i want it to use:

rs.Open strCriteria, cmdApplication, adOpenDynamic,adLockOptimistic, adCmdText

i.e use the Application Roles security context.

Rg,
Mehul
0
 

Author Comment

by:melu
ID: 6917370

It works fine now. I think this line of code does it for me:

    strCriteria = "sp_setapprole 'SalesApp', 'SalesTest'"
    conn.Execute strCriteria

so even when user ms has no select permissions on the employees table
the appl is able to select using the Appl Role 'SalesApp'.

   

    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=Temp; OLE DB Services= -2;" _
         & "User ID=ms;Password=" & _
         ";Initial Catalog=HelpdeskCL; ConnectionTimeout = 60"
    conn.Open
   

   
   
    If (conn.State = adStateOpen) Then
   
        ' Option 3
        ' Setting the Application Role
        strCriteria = "sp_setapprole 'SalesApp', 'SalesTest'"
        conn.Execute strCriteria

    Else
        GoTo LoginFailed
    End If

   

    Set employeeRs = New ADODB.Recordset
   
    strEmployeeCriteria = "SELECT * FROM employee WHERE UserName= '" & Trim(txtUsername.Text) & "'" & _
                          "AND [Password]= '" & Trim(txtPassword.Text) & "'" & " AND [Active] = 1"
    employeeRs.Open strEmployeeCriteria, conn, adOpenDynamic, adLockOptimistic, adCmdText


Rg,
Mehul
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now