how to GRANT ALL PRIVILEGES by c# code

Question

1.00/5 (1 vote)

See more:

hi,

how i can write code to GRANT ALL PRIVILEGES to user "root" on all mysql database by c# ??
then i went to create database and import my .sql file to it

i tried with this code but error appear 'Host 'JE-PC' is not allowed to connect to this MySQL server'

C#

MySqlConnection connection001 = new MySqlConnection("Data Source=" + Publics.server + ";UserId=" + Publics.uid + ";PWD=" + Publics.password + "topSecretPassword;");
string query = "INSERT INTO users(user_name, password, admin) VALUES('" + Userdd + "','" + Pass + "', '1')";
MySqlCommand command = new MySqlCommand(query, connection001);
connection001.Open();
command.ExecuteNonQuery();


query = "CREATE USER '" + Userdd + "' IDENTIFIED BY '" + Pass + "'";
command = new MySqlCommand(query, connection001);
command.ExecuteNonQuery();

query = " grant all privileges on mydb.* to ttt8@'%' identified by 'ttt';";
command = new MySqlCommand(query, connection001);
command.ExecuteNonQuery();

thank you.

Posted 1-Dec-15 11:47am

Golden Basim

Updated 2-Dec-15 11:25am

v5

Add a Solution

Comments

Patrice T 1-Dec-15 19:13pm

Hacker dream !
Not a proper question

Golden Basim 1-Dec-15 19:22pm

i don't went the customer create database using XAMPP and write code to GRANT ALL PRIVILEGES to user "root" manually ,
what i need that my program make this thing by code ..
customers don't like more complex steps to install my program on it's computer.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2015-12-01T13:26:00

Solution 1

Granting rights on a database is done via a SQL script.
So, the first thing you have to do is to get the correct syntax.
You can find how to do that on MySQL 5.7 Reference Manual / ... / GRANT Syntax[^].

Then you have to get how to execute a MySQL query from a .NET assembly.
You will need:
- to download Connector/Net[^].
- to have a look at MySQL Connector/Net Developer Guide[^], and especially its Chapter 5 Connector/Net Tutorials[^].

Good work :)

[Update]
You have to define your user as 'username'@'computernameorip', not just 'username'.
In the present case, that would give

C#

string computerNameOrIp = "%";
string login = string.Format("'{0}'@'{1}'", Userdd, computerNameOrIp); // login = 'username'@'%'
query = string.Format("CREATE USER {0} IDENTIFIED BY '{1}'", login, Pass);
command = new MySqlCommand(query, connection001);
command.ExecuteNonQuery();

query = string.Format("GRANT SELECT, SHOW DATABASES ON database.* TO {0}", login);
command = new MySqlCommand(query, connection001);
command.ExecuteNonQuery();

In MySQL, there is one account per tuple (username, source).

Posted 1-Dec-15 13:26pm

phil.o

Updated 2-Dec-15 10:50am

v4

Comments

Golden Basim 1-Dec-15 19:34pm

thank you for your answer,
i have MySQL Connector and i can insert ,edit ,select,.. using c# and i can Grant PRIVILEGES on database manually.
my problem is how i can make this by c# code ( i went all this steps happen in the first form).

phil.o 1-Dec-15 19:37pm

You just have to read the tutorials on how to use Connector/NET. They have plenty of examples. Moreover, since it's ADO.NET, it will not be any different from using any other database system's ADO.NET connector.
Basically, that's just a script; so no difference from a SELECT, or INSERT, or any other type of query.

Golden Basim 2-Dec-15 16:23pm

i tried with this code but error appear 'Host 'JE-PC' is not allowed to connect to this MySQL server'

MySqlConnection connection001 = new MySqlConnection("Data Source=" + Publics.server + ";UserId=" + Publics.uid + ";PWD=" + Publics.password + "topSecretPassword;");
string query = "INSERT INTO users(user_name, password, admin) VALUES('" + Userdd + "','" + Pass + "', '1')";
MySqlCommand command = new MySqlCommand(query, connection001);
connection001.Open();
command.ExecuteNonQuery();

query = "CREATE USER '" + Userdd + "' IDENTIFIED BY '" + Pass + "'";
command = new MySqlCommand(query, connection001);
command.ExecuteNonQuery();

query = "GRANT SELECT, SHOW DATABASES ON database.* TO '" + Userdd + "'@'%'";
command = new MySqlCommand(query, connection001);
command.ExecuteNonQuery();

phil.o 2-Dec-15 16:41pm

Please do not put code in comments. They are not meant for that. Improve your question with actual code and see my updated answer.

Golden Basim 2-Dec-15 16:46pm

ok

Patrice T 1-Dec-15 19:42pm

+5

BillWoodruff 1-Dec-15 21:31pm

+5

Golden Basim 2-Dec-15 17:07pm

thank you now i can create new user ,, this error appear in the second part of the code during grant the rights.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database.* TO 'ttt3'@'%'' at line 1

phil.o 2-Dec-15 17:13pm

Then you could try to put the computer IP address, or subnet, instead of just %?
You could at least see if that is the cause of the problem.

Golden Basim 2-Dec-15 17:17pm

same error with "computer name" or "ip" or "localhost"

Golden Basim 2-Dec-15 17:27pm

after updating the query line no error appear but all privileges have (No) value