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
Solved

Create new user and give privileges in mySQL database

Posted on 2011-03-19
7
743 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 400 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

789 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