melu
asked on
using application roles in vb
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.CreateParam eter("Retu rn", adInteger, adParamReturnValue)
cmdApplication.Parameters. Append paramApplicationReturn
Set paramApplication1 = cmdApplication.CreateParam eter("@str Username", adChar, adParamInput, 20)
cmdApplication.Parameters. Append paramApplication1
cmdApplication.Parameters( 1).Value = "SalesApp"
Set paramApplication2 = cmdApplication.CreateParam eter("@str Psd", adChar, adParamInput, 20)
cmdApplication.Parameters. Append paramApplication2
cmdApplication.Parameters( 2).Value = "SalesTest"
cmdApplication.ActiveConne ction = 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
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
cmdApplication.CommandType
Set paramApplicationReturn = cmdApplication.CreateParam
cmdApplication.Parameters.
Set paramApplication1 = cmdApplication.CreateParam
cmdApplication.Parameters.
cmdApplication.Parameters(
Set paramApplication2 = cmdApplication.CreateParam
cmdApplication.Parameters.
cmdApplication.Parameters(
cmdApplication.ActiveConne
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
angelIII (or anyone else), what are the benefits of using application roles?
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
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
... 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
CHeers
ASKER
Hi All,
To angelIII - no that doesn't seem to work. What i mean that
(refering to the code)
cmdApplication.ActiveConne ction = 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
To angelIII - no that doesn't seem to work. What i mean that
(refering to the code)
cmdApplication.ActiveConne
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
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
CHeers
ASKER
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,adLockOptimi stic, adCmdText
i.e use the Application Roles security context.
Rg,
Mehul
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,adLockOptimi
i.e use the Application Roles security context.
Rg,
Mehul
ASKER
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