Saturday, October 15, 2011

create user for mysql database using vb.net 2010

Private Sub MySQLfunction()
Dim MySQLcon As String
Dim xconn As MySql.Data.MySqlClient.MySqlConnection

'txtuname.text
'txtpass.text
'cbopri.text

'code para sa save button ng createuser form
'i check kung pareho ang password sa retype password
'kung hindi pareho ibalik ang focus sa txtpass.text
'at mag exit sub

'kung pareho ilagay ang code sa baba

If txtpass.Text.Trim <> txtretype.Text.Trim Then
MsgBox("Error: Password did not Match!", vbCritical, "Warning!")
txtretype.Clear()
txtpass.Focus()
txtpass.Select()
Exit Sub
End If

Try
If Not xconn Is Nothing Then xconn.Close()

MySQLcon = String.Format("Server = {0};Database={3}; User ID={1}; Password={2};Port={4};", xServer, xUserID, xPassword, "mysql", xPort)
'or
'MySQLcon = String.Format("Server = {0};Database={3}; User ID={1}; Password={2};Port={4};", xServer, xUserID, xPassword, dbname, xPort)
xconn = New MySqlConnection(MySQLcon)
xconn.Open()

Mysqlcmd(MySQLcon, "DELETE FROM USER WHERE `User` LIKE '" & txtuname.Text & "' AND `Host` LIKE 'localhost'")

Mysqlcmd(MySQLcon, "CREATE USER '" & txtuname.Text & "'@'%' IDENTIFIED BY '***';")
Mysqlcmd(MySQLcon, "SET PASSWORD FOR '" & txtuname.Text & "'@'%' = PASSWORD('" & txtpass.Text & "')")

Mysqlcmd(MySQLcon, "CREATE USER '" & txtuname.Text & "'@'localhost' IDENTIFIED BY '***';")
Mysqlcmd(MySQLcon, "GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `" & xDatabase & "`.* TO '" & txtuname.Text & "'@'%' WITH GRANT OPTION")
Mysqlcmd(MySQLcon, "SET PASSWORD FOR '" & txtuname.Text & "'@'localhost' = PASSWORD('" & txtpass.Text & "')")

Mysqlcmd(MySQLcon, "DELETE FROM USER WHERE `User` LIKE '" & txtuname.Text & "' AND `Host` LIKE '%'")


'FOR ADMIN ONLY
Mysqlcmd(MySQLcon, "GRANT ALL PRIVILEGES ON * . * TO '" & txtuname.Text & "'@'%' IDENTIFIED BY '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;")
'END OF ADMIN ONLY


Catch err As Exception
'MsgBox(err.Message.ToString)
End Try
End Sub

0 comments: