im trying to let users login to an application i created in vb.net using the user table of the application's database in mysql
i heard that The usual way to do this is to have just one MySQL user called "[my_app_name]" with the relevant permissions. Then my application uses it's own user table to control access to the application, and the one MySQL user to access the database. but i dont know how to do it, can someone please help me with it. im new to all this.
thanks
If you're making a login form that will check for authetication with users and their passwords on a MySQL database just follow this code I wrote for my own login form.
Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
Dim conn As MySqlConnection
'Connect to the database using these credentials
conn = New MySqlConnection
conn.ConnectionString = "your server goes here; user id=userid goes here; password=self explanatory; database=the database where the credential information is located"
'Try and connect (conn.open)
Try
conn.Open()
Catch myerror As MySqlException 'If it fails do this... (i.e. no internet connection, etc.)
MsgBox("Error connecting to database. Check your internet connection.", MsgBoxStyle.Critical)
End Try
'MySQL query (where to call for information)
Dim myAdapter As New MySqlDataAdapter
'Tell where to find the file with the emails/passes stored
Dim sqlquery = "SELECT * FROM your database with info WHERE Email = '" & txtEmail.Text & "' AND Password = '" & txtPassword.Text & "'"
Dim myCommand As New MySqlCommand
myCommand.Connection = conn
myCommand.CommandText = sqlquery
'Start query
myAdapter.SelectCommand = myCommand
Dim myData As MySqlDataReader
myData = myCommand.ExecuteReader
'See if the user exists
If myData.HasRows = 0 Then
MsgBox("Invalid email address or password.", MsgBoxStyle.Critical)
'Insert your settings change here. (i.e. My.Settings.LoggedIn = False)
Else
MsgBox("Logged in as " & txtEmail.Text & ".", MsgBoxStyle.Information)
'Another settings change: My.Settings.LoggedIn = True
Me.Close() 'close the login form
End If
Be sure to change the control names and also add a setting.
Open up MySQL Workbench and towards the bottom right of the first screen, under the "Server Administration" column, you'll see "Manage Security". Click on that. In the menu to the left you will see "Users and Privileges". Click on that. You'll see a button that says "Add Account" which, when clicked, will allow you to create a new user. In the Schema Privileges tab you can modify the user's permissions.
The user you created here can be used in your connection string. Here is an example MySQL connection string from one of my applications. The username in the connection string below, repair, was created with "Login Name:" as "repair" and "Limit Connectivity to Hosts Matching:" as "%".
"Server=10.0.0.113;uid=repair;pwd='password&92';database=repair"
I hope this helps. Have a great day.
