Solved

Create new user and give privileges in mySQL database

Posted on 2011-03-19
7
728 Views
Last Modified: 2012-05-11
Create user in mySQL database:

How do I know if the current user account has the privilege to create new user?

I get error when I try to create a new user. I need to know if it is my limited privilege or my syntax is incorrect:

CREATE USER 'acuser' IDENTIFIED BY 'mypassword123';

CREATE USER 'acuser'@'localhost' IDENTIFIED BY 'mypassword123';

In both cases I get this error:
ERROR 1064 (HY000): You have an error in your SQL syntax near

I was using the syntax mentioned here:
http://dev.mysql.com/doc/refman/5.0/en/create-user.html

Also I wanted the new user to have only read-only (SWLWCT) access to one existing table and not to any other.
0
Comment
Question by:toooki
  • 4
  • 2
7 Comments
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
Well your syntax looks ok to me.

CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

then to grant privillages

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost';

That gives them ALL privillages on ALL databases but you want to limit this to a particular database so


GRANT ALL PRIVILEGES ON database.tablename TO 'monty'@'localhost';

That will restrict the user to just that database and that table

if you need to view your privilages you can type

SHOW GRANTS for user;

Change "user" for your username.

Hope this helps
0
 

Author Comment

by:toooki
Comment Utility
Thank you.

If I try this command:
mysql> SHOW GRANTS for www;
| Grants for www@%
                       |
+-------------------------------------------------------------------------------
-----------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'www'@'%' IDENTIFIED BY PASSWORD '7c92555a25063
27c' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
-----------------------+
1 row in set (0.00 sec)

But create user fails:
mysql> CREATE USER 'acuser'@'localhost' IDENTIFIED BY 'myPass1233';
ERROR 1064: You have an error in your SQL syntax near 'USER 'acuser1'@'localhost
' IDENTIFIED BY 'myPass1233'' at line 1

From the above granted privilege, does the account www look like having privilege to create now user?
0
 

Expert Comment

by:13cactus
Comment Utility
Which version of MySQL are you using?

I suspect you are using a version that is older than 5.0.2 as the CREATE USER method was added in that version. Earlier releases do not understand that instruction. This is also documented in the MySQL manual for the CREATE USER statement at the end:

http://dev.mysql.com/doc/refman/5.0/en/create-user.html
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:toooki
Comment Utility
Thank you. Checking.
I am using MySQL version 3.23.57 .

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 3.23.57   |
+-----------+
1 row in set (0.01 sec)
0
 

Author Comment

by:toooki
Comment Utility
I went to the link http://dev.mysql.com/doc/refman/5.0/en/create-user.html
But there the link for "MySQL 3.23/4.0/4.1 Manual" points to the current page only -- so there is no MySQL 3.23/4.0/4.1 Manual it seems.

0
 

Accepted Solution

by:
13cactus earned 400 total points
Comment Utility
But there the link for "MySQL 3.23/4.0/4.1 Manual" points to the current page only -- so there is no MySQL 3.23/4.0/4.1 Manual it seems.
There is  a manual for your version:

http://dev.mysql.com/doc/refman/4.1/en/

But it does not contain information for the CREATE USER syntax as it simply did not exist back then (as I already explained).

You should use the GRANT PRIVILEGES syntax as that should work on your version, here is the direct link to the manual of your version for it:

http://dev.mysql.com/doc/refman/4.1/en/grant.html

As a side not I seriously suggest updating your MySQL server. You are two major versions behind and a lot of bugs and security issues have been fixed in the mean time.
0
 

Author Comment

by:toooki
Comment Utility
Thank you. I got the link for the MySQL 3.23 manual.
As you said there is no create user command.

mysql>GRANT SELECT ON www.* TO 'acadmin'@'%' IDENTIFIED BY 'mypass123';

The above command worked. Thank you for the suggestion -- I will try to upgrade the database.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now