Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create new user and give privileges in mySQL database

Posted on 2011-03-19
7
Medium Priority
?
766 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

618 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