4 Replies - 1054 Views - Last Post: 12 June 2013 - 11:36 PM Rate Topic: -----

#1 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Setting privelege level on Database users

Posted 08 May 2013 - 12:17 AM

Hi
In my VB.net application i have a login form with two textboxes, One for the username and one for the password.
login form: Attached Image
I also have a form setting where people are given the right whether to use the application or not and on top of that when sumeone is given the right to use the application he needs to be given a privelege level(IsManager,IsAdmin,Employee)
here's the form use to add and give the user the right privelege to the Application:
Attached Image
On my Database i have a table called Users, its a DB where all the users infos of the application are store
The users table's columns are EmployeeId,Name,Title,IsManager,IsAdmin,Employee,Username,Password
Ismanager,Isadmin,employee are boolean columns meaning their values can only be 0 or 1 and the result is displayed as a checkbox.
When the user logs into the application using his username and password i want it to check whether is a manager,admin or employee.
If all 3 conditions are true then the user can have access to every function of the application such as Add,Edit,Delete,Print etc.....
I haven't done alot on this but i would like to achieve this by using select case statement
here's what i started
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        Dim con As New SqlClient.SqlConnection(My.Settings.NewEvapcoDBConnectionString.ToString)
        Dim cmd As New SqlClient.SqlCommand("Select Username From Users Where [Users].username Like '%" + UsernameTextBox.Text + "%' and [Users].password Like '%" + PasswordTextBox.Text + "'", con)
        Dim a As Integer
        Dim b As Integer
        Dim c As Integer
        Try
            con.Open()
            Dim ObjReader As SqlClient.SqlDataReader
            ObjReader = cmd.ExecuteReader
            If ObjReader.HasRows = False Then
                UsernameTextBox.Text = ObjReader("Username")
                'MsgBox("User does not exists! Please add new user")
            ElseIf UsernameTextBox.Text = "" Or PasswordTextBox.Text = "" Then
                MsgBox("Please make sure both username and password are filled in", MsgBoxStyle.Critical, "Error")
            Else
                Select Case a
                    Case b
I AM STUCK HERE CAN SUMEONE HELP ME
                End Select
                EvapcoSAMDI.Show()
            End If
        Catch ex As Exception
            'System.Windows.Forms.MessageBox.Show(ex.Message)
            MsgBox("Enter correct Username and Password", MsgBoxStyle.Critical, "Error Login")
        End Try


THANKS IN ADVANCE

Is This A Good Question/Topic? 0
  • +

Replies To: Setting privelege level on Database users

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Setting privelege level on Database users

Posted 08 May 2013 - 05:02 AM

First off, if you have this information stored in the database, it would make sense to select that information so that you know the value of isAdmin, isManager and isEmployee when a user successfully logs in. Currently you are only grabbing the UserName in your query.
Once you have don this, you would then just check their boolean value and set their access limitations from those settings.

Second... Why are you performing LIKES against a username and password. I would think it more secure to look for EXACT strings that the user has entered.

The way your Query is written I could easily cycle through single letters in each field and would have a high probability of getting access to a user.

For example pretend you had a user andrew who had a password of b&1fXL3b
Now that looks like a pretty strong password but your query would allow me to find it by simply supplying 'a' for the user and 'b' for the password.
Where name like '%a%' is the same as saying name contains a so does andrew contain a ?
and password like '%b' is the same as saying password end with b so does b&1fXL3b end with b ?

Also, your application is not secure at all, any user with a little bit of knowledge of how databases work could easily gain access to your database because you are concatenating your user input into your SQL statement. This opens you up to SQL Injection (see the link in my signature).

Use parameters in your SQL Statements. There really is no reason to not do it properly.

This post has been edited by CharlieMay: 08 May 2013 - 05:03 AM

Was This Post Helpful? 0
  • +
  • -

#3 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: Setting privelege level on Database users

Posted 08 May 2013 - 11:04 PM

Tx CharlieMay for your reply
i have a question how can i rewrite my query to match the exact txtbox entry...and then i can i select one information and then check if the user ismanager,isadmin,isemployee after he has login.
Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Setting privelege level on Database users

Posted 09 May 2013 - 04:00 AM

You would use = instead of like and remove the %

If I wanted to get the color of all cars that were of a certain make I could use:
SELECT Color FROM Cars WHERE Make = 'Ford'


You would SELECT the columns you want to return where the name is = to and the password is = to.
Was This Post Helpful? 0
  • +
  • -

#5 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: Setting privelege level on Database users

Posted 12 June 2013 - 11:36 PM

Hi,
i modified my code on the OK button in the login form to look like this:
 Private Sub BtnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnOK.Click
        Dim con As New SqlClient.SqlConnection(My.Settings.NewEvapcoDBConnectionString.ToString)
        Dim cmd As New SqlClient.SqlCommand("Select * From [Users] Where [Users].username = '" + UsernameTextBox.Text + "' and [Users].password = '" + PasswordTextBox.Text + "'", con)
        'Dim cmd As New SqlClient .SqlCommand ("Select * From [Users]")
        Try
            con.Open()
            Dim ObjReader As SqlClient.SqlDataReader
            ObjReader = cmd.ExecuteReader
            If ObjReader.HasRows = False Then
                'UsernameTextBox.Text = ObjReader("Username") And PasswordTextBox.Text = ObjReader("Password")
                If UsernameTextBox.Text.Equals(ObjReader("Username")) And PasswordTextBox.Text.Equals(ObjReader("Password")) Then
                    If ObjReader("IsManager") = True And ObjReader("IsAdmin") = True And ObjReader("Employee") = True Then
                        c = 3
                        ElseIf ObjReader("IsAdmin") = True And ObjReader("Employee") = True And ObjReader("IsManager") = False Then
                        c = 2
                        Else 'ObjReader("IsAdmin") = False And ObjReader("Employee") = True And ObjReader("IsManager") = False Then
                        c = 1
                        'nothing
                    End If
                End If
            End If
            Me.Visible = False
            EvapcoSAMDI.Show()
        Catch ex As Exception
            'System.Windows.Forms.MessageBox.Show(ex.Message)
            If UsernameTextBox.Text = "" Or PasswordTextBox.Text = "" Then
                MsgBox("Please make sure both username and password are filled in", MsgBoxStyle.Critical, "Missing Info")
                UsernameTextBox.Focus()
            Else
                MsgBox("Enter correct Username and Password", MsgBoxStyle.Critical, "Error Login")
                UsernameTextBox.Text = ""
                PasswordTextBox.Text = ""
                UsernameTextBox.Focus()
                Return
            End If
        End Try

the variable c is declared as public
then on the customer form (in the load events) where the add,delete and save must be enable or disable based on the privilege that the user has i put this code:
 If LoginForm.c = 3 Then 
            Me.BindingNavigatorAddNewItem.Enabled = True
            Me.BindingNavigatorDeleteItem.Enabled = True
            Me.CustomersBindingNavigatorSaveItem.Enabled = True
        ElseIf LoginForm.c = 2 Then 
            Me.BindingNavigatorAddNewItem.Enabled = True
            Me.BindingNavigatorDeleteItem.Enabled = True
            Me.CustomersBindingNavigatorSaveItem.Enabled = True
        Else
            LoginForm.c = 1 
            Me.BindingNavigatorAddNewItem.Enabled = False
            Me.BindingNavigatorDeleteItem.Enabled = False
            Me.CustomersBindingNavigatorSaveItem.Enabled = False
        End If

but its looks like only the last condition is the one executing and the first two are not!!can anyone tell me what am doing wrong,please
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1