creating user in mysql using java

Question

In MySQL command line client after logging in as root, I typed:

connect mydb;
grant all privileges on mydb.* to 'admin'@'localhost' identified by 'pass';

Now within Java, I succesfully connect to the db using the admin userid using drivers.

 Statement put=connect.createStatement();

 //**WORKS succesfully**
 put.execute("insert into mydb.emp values(100,joe)");   

 //**does NOT work**
 put.execute("grant all privileges on mydb.* to 'john'@'localhost' identified by 'pass'");

Why does an insert command work but grant command never work through Java?

Please help.

One thing that I see immediately is that your INSERT query is a string, while your GRANT query is not. — BLaZuRE
– BLaZuRE, Commented Jul 10, 2013 at 10:19
@Xperiaz X as root you mean from linux terminal for example? — r.piesnikowski
– r.piesnikowski, Commented Jul 10, 2013 at 10:19
Not all databases support DDL commands via JDBC, so you might not be able to modify the database structure via JDBC at all. — beny23
– beny23, Commented Jul 10, 2013 at 10:25
no to enter sql command line client in windows we need to enter a root password. MySQL root not linux root. — Xperiaz X
– Xperiaz X, Commented Jul 10, 2013 at 10:39

Ruchira Gayan Ranaweera · Accepted Answer · 2013-07-10 10:24:12Z

1

put.execute(MySQL query)

in here you can execute only MySQL query but

grant all privileges on mydb.* to 'admin'@'localhost' identified by 'pass';

Not a MySQL query it is just a command for MySQL.

answered Jul 10, 2013 at 10:24

Ruchira Gayan Ranaweera

35.7k17 gold badges78 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Xperiaz X Over a year ago

Oh, I see. So is it possible to send commands to MySQL via java?

Ruchira Gayan Ranaweera Over a year ago

You have to set user privileges via MySQL admin terminal(command line)

Xperiaz X Over a year ago

Is it not possible using java? My application is adding an employee (thus adding a DB user). The employee then has access to other tables in the database

Ruchira Gayan Ranaweera Over a year ago

You can't set privileges via java for MySQL user.

Xperiaz X Over a year ago

Yes, finally I implemented it. for my program ,we can set all privileges except grant option via java as long as we have root id and password. Anyway this answer really helped me a lot. Thank you :)

|

nrainer · Accepted Answer · 2013-07-10 10:19:21Z

0

You just forgot the quotation marks.

answered Jul 10, 2013 at 10:19

nrainer

2,6432 gold badges25 silver badges38 bronze badges

2 Comments

Xperiaz X Over a year ago

My bad, In the code I had put quotation but here I forgot to type it

W A K A L E Y Over a year ago

@XperiazX in that case you should look my answer. Check the ExtendedAnsiSQL flag

W A K A L E Y · Accepted Answer · 2013-07-10 10:27:55Z

0

Try following

put.execute("grant all privileges on mydb.* to 'john'@'localhost' identified by 'pass'");

And also make sure you have ExtendedAnsiSQL flag to 1.

answered Jul 10, 2013 at 10:27

W A K A L E Y

8671 gold badge10 silver badges14 bronze badges

1 Comment

Xperiaz X Over a year ago

Sorry,In the code I had put quotation but here I forgot to type it here. ExtendedAnsiSQL flag? please do tell what it is?

grobmotoriker · Accepted Answer · 2014-03-12 12:49:31Z

I tried working with query parameters - what I found somewhat confusing is that you have to put a blank between the parameter and @localhost for example - this here worked for me: (jpaEm is a JpaEntityManager here)

// Create user
String sql = "CREATE USER ? @localhost";
Query query = jpaEm.createNativeQuery(sql);
query.setParameter(1, user.getUserName());
jpaEm.getTransaction().begin();
query.executeUpdate();
jpaEm.getTransaction().commit();

// Set password
sql = "SET PASSWORD FOR ? @localhost = PASSWORD(?)";
query = jpaEm.createNativeQuery(sql);
query.setParameter(1, user.getUserName()); 
query.setParameter(2, user.getPassword());
jpaEm.getTransaction().begin();
query.executeUpdate();
jpaEm.getTransaction().commit();

Luis Arias · Accepted Answer · 2024-02-16 17:12:45Z

Maybe this can help someone so far. I resolved it with this code below.

 String db = "data";
 String us = "Luis";
 String pas = "123456";
 //-----Connection to the server------------
 Class.forName("com.mysql.cj.jdbc.Driver");
 cntConnec = 
 DriverManager.getConnection("jdbc:mysql:///","admin","admin");
 Statement stmMysql = cntConnec.createStatement();
 JOptionPane.showMessageDialog(null, "Successful Connection");
 //-------Create a new DB------------
 stmMysql.execute("CREATE DATABASE "+db);
 stmMysql.execute("USE "+db);
 stmMysql.execute("CREATE TABLE bobo_table (f00 char(31))");
 //-------Create a user with all privileges--------------
 stmMysql.executeUpdate("CREATE USER "+us+"@'localhost' IDENTIFIED BY 
                '"+pas+"'");
 stmMysql.executeUpdate("GRANT ALL PRIVILEGES ON "+db+".* TO 
                "+us+"@'localhost' WITH GRANT OPTION");
 stmMysql.executeUpdate("FLUSH PRIVILEGES");
 stmMysql.close();

Collectives™ on Stack Overflow

creating user in mysql using java

5 Answers 5

6 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related