[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Create new user and give privileges in mySQL database

Posted on 2011-03-19
7
Medium Priority
?
770 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
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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…
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…
Suggested Courses

834 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