?
Solved

Create new user and give privileges in mySQL database

Posted on 2011-03-19
7
Medium Priority
?
758 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 20

Expert Comment

by:Mark Brady
ID: 35174790
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
ID: 35175734
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
ID: 35176199
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:toooki
ID: 35176240
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
ID: 35176248
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 1600 total points
ID: 35176282
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
ID: 35177681
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

771 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