?
Solved

Simple MySQL command for Schema Privilages

Posted on 2005-04-04
7
Medium Priority
?
890 Views
Last Modified: 2008-02-01
I am creating a simple webpage that requires me to make users for everyone who needs to login. And they have there own small database that they have god rights to, but nothign else.

Everything works perfectly besides they have no rights to anything. I create the user and they are blank (i create it by editing the mysql db which I know isn't the prefered method.)

It had to be done with PHP so it's via command line.

It has to be something like this right?:
GRANT ALL PRIVILEGES ON SCHEMA andy TO andy@%"
I've done other variations to get syntax correct. Nothing works in Query Browser or via PHP, which is similar.
0
Comment
Question by:BiSHGoD
[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
  • 3
7 Comments
 
LVL 10

Expert Comment

by:kupra1
ID: 13704583
Did you try this:

GRANT ALL ON test.mydb TO 'andy'@'localhost' (assuming test.mydb is ur schema name)
0
 

Author Comment

by:BiSHGoD
ID: 13704623
Well I don't know the full name of the database.
It says andy. Just like there is a mysql, phpnuke, and test database. How do I know the extension?

Also It has to be from all other hosts % (since it's a webpage and accessed all over right?).
0
 
LVL 10

Accepted Solution

by:
kupra1 earned 600 total points
ID: 13704627
or use this:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'
IDENTIFIED BY 'password' WITH GRANT OPTION;
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 10

Expert Comment

by:kupra1
ID: 13704643
>>>> Also It has to be from all other hosts % (since it's a webpage and accessed all over right?).

Then use this:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%'
IDENTIFIED BY 'password' WITH GRANT OPTION;

replace username and password with the right values.
0
 

Author Comment

by:BiSHGoD
ID: 13704671
It seems *.* would give god access.
Is that right?
It would make sense it is
GRANT ALL PRIVILEGES ON andy.* TO 'andy'@'%'
IDENTIFIED BY 'password' WITH GRANT OPTION;

I tried that I think. I'll try it again and post the error.
0
 
LVL 10

Expert Comment

by:kupra1
ID: 13704684
yea.. it should.
0
 

Author Comment

by:BiSHGoD
ID: 13717729
Error 1133. Can't find any matching row in the user table.

Although in mysql database and the user table 'andy' is there. Password is correct.

What I typed was:

GRANT ALL PRIVILEGES ON andy.* TO 'andy'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

As one long string into the Query Browser. Hit execute and that error happens.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 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