MYSQL :: Copy User Privileges To A Newly Created User.

How To Display User Variable Created During Session?
I have created some "@named" user variables during a query session using the MySQL console.

Typing in the queries works just fine. So does the setting of the various user variables.

Yet, there doesn't seem to be simple way to see a list of the user variables I have created.

"show variables" does not include them.

How to view these user-created variables all at once?

User Privileges
How do I need to set Privileges so that a user can create and modify their own databases?

I am running MySQL 3.23.53 with phpMyAdmin 2.5.4. I have multiple DBs and users. I setup up a new user and database with no problem. The user can only see the new database as it should be. I would like to allow the user to be able to create their own databases. Right now in phpmyadmin, when they create a new database, they cannot create tables in it, and they can not see the database they just created in their list of databases. Although I can see the database they created with my admin user.

When the user First creates a database and tries to create a table they get "select command denied to user". I gave the user global select, but as soon as I do they can see all the databases.

I was guessing this is just a Privilege issue, but I am still learning about SQL privileges.

A User Dba Privileges
I need to give a special user admin rights to the databases he
creates.

As there is no dedicated MySQL database server for his needs yet, we
created a special user on a multi-purpose MySQL server; this special
user has the rights to do everything only on databases he creates.

To distinguish his databases among others, we require his databases'
names to be prefixed by "PNCC_". Here's how we did the trick:

Once logged in as DBA:

mysql> GRANT ALL ON PNCC_.* TO fred@my.fqdn.com IDENTIFIED BY
'some_password' WITH GRANT OPTION;
(The query ran OK)
mysql> UPDATE db SET Db='PNCC_%' WHERE db='PNCC_';
(The query ran OK)

(We had to do two different commands, as GRANT does not seem to accept
wildcards).

From now on, after having flushed privileges, user fred@my.fqdn.com
can create databases with names prefixed by "PNCC_", create tables
within these databases, and populates them.

But this user would like to have the possibility to create users with
special privileges on the databases/tables he previously created. The
question is: how can I grant fred to create users with rights ONLY on
PNCC_<some name> databases, and nothing else (ie. no access to other
databases, nor on mysql.*)? Do I need to insert a special row within
mysql.user? If so, which one?

User Privileges
I'm in the root user account. Lets just say I have three databases called "database1","database2","database3". I also have three users called "user1","user2","user3". Now what I'm trying to do is to only allow user1 to acces database1. So when they login all they see is their database. I want them to add tables and do everything along those lines, but I don't want them to see other databases or create databases themsevles. I can't seem to get this right. How do I go about this? I am using phpMyAdmin version 2.6.1.

How To Export User Privileges
I am working with MySQL 5.0 and phpMyAdmin. I know how to export (dump) the tables and data of the database with phpMyAdmin, but I couldn't find a way to do the same for the user accounts and their privileges.

Root User Without Privileges?
Anyone know when it will be considered production stable?

Changed User Privileges And Now I Can't Log In
I read several web sites and O'Reilly's book on MySQL about securing the
system tables.

I removed several users and, as advised inthe book and web sites, changed
the user "root" to another name with another password.

I removed the site name as a host but kept the IP address.

Now when I use phpMySdmin to get back in, it let's me past the login popup
BUT it gives me the message:

Error
MySQL said: Access denied for user: 'root@localhost' (Using password: YES)

The data in the database is not important but the database
structure/definition is. I'd like to find a way to back up or dump that.
Then I can reload MySQL and start over.

Any ideas?

Changed User Privileges And Now I Can't Log In
I removed several users and, as advised inthe book and web sites, changed
the user "root" to another name with another password.

I removed the site name as a host but kept the IP address.

Now when I use phpMySdmin to get back in, it let's me past the login popup
BUT it gives me the message:

Error
MySQL said: Access denied for user: 'root@localhost' (Using password: YES)

The data in the database is not important but the database
structure/definition is. I'd like to find a way to back up or dump that.
Then I can reload MySQL and start over. Any ideas?

Shoudl I be able to get to it using shell login?

MySQL User / Privileges
1. Does it cause problems if there are multiple users with the same user name?

2. If 'localhost' is specified for the user host, does this mean that they can only gain access if they are logging in from the same host address where mysql is running?

Assigning User Privileges Error
I created a user 'cody' % and localhost and try to assign privileges to his database 'cal_cody' For some reason it don't work, posting this weird thing in forums didn't help either.

so I tried:

mysql> GRANT ALL PRIVILEGES ON cal_cody.* TO 'cody'@% IDENTIFIED BY 'access';
Query OK, 0 rows affected (0.00 sec)

also tried:

mysql> GRANT USAGE ON cal_cody.* TO 'cody'@'%' IDENTIFIED BY 'access'
Query OK, 0 rows affected (0.00 sec)

then tried the same for 'localhost' then even flashed the privileges and even reloaded the mysql service doesn't do jack

Is there something wrong with my command, or is there a way to do this through phpmyadmin?

Mysql Injections And User Privileges
I'm having a friendly debate with someone I adopted some code from. Well, I noticed that he was using the $_GET variable directly in his mysql queries. So, I told him I thought that was a security vulnerability. He says its not because the user connecting to the database has select only privileges.

So my question is, is giving a user select only privileges safe enough to not have to do any data validation or scrubbing to protect from mysql injections?

User Privileges In Version 4.1.7(linux)
sql:
GRANT USAGE ON *.* TO 'shinestb'@'localhost' IDENTIFIED BY 'shinestb';
FLUSH PRIVIELGES;

an error return at login time:
./mysql -ushinestb -p
ERROR 1045 (28000): Access denied for user 'shinestb'@'localhost' (using password: NO

why? version < 4.1 is ok.

Random Password Vs. User Created Password For Site Login
Are there any security issues or other concerns that make one preferable? The client is pushing for user created passwords.

I'm mapping out the basic functionality and front-end for a MySQL/PHP back-end that will be completed by a third party. I'm a novice to MySQL but familiar with PHP and their interaction. Mainly looking for anything to support one method over the other.

What Privileges Need A User To Run "SHOW SLAVE STATUS" ?
i use mysql 4.1.22
i need to create an user who must have rights to run only "SHOW SLAVE STATUS" query.
from online documentation i wasn't able to find what privilege i must grant to this user to be able to do only this query.

Root User Denied Access To Fetch User List.
I can still log onto MySQL administrator or the command line program but I somehow appear to have lost all priveliges to alter or view the user list from this account. Even under the command line interface I can login but it tells me access is denied when I try to alter "root" permissions on a database.

I cannot seem to log onto any of the other accounts created but obviously if I could get into the user admin I could solve this.

Retrive Category Names Where Categoryid Is Different From User To User
I am trying to retrive category names where categoryid is different from user to user

I know LIKE works with one int(1), but I want it to work with more ints (1,3,5)
SELECT * FROM `categories` WHERE categoryid LIKE (1) Working

SELECT * FROM `categories` WHERE categoryid LIKE ( 1, 5 ) Not working

Deleted Root User From 'user' Database!
I so stupidly deleted the root user from my mysql > user database. Now I can't login to mysql. I uninstalled the rpm and reinstalled it but it still won't let me setup a new root user. How can I delete the mysql data files so that I start again, fresh? Where are these files located? I can't find them anywhere after removing the RPM but they are obviously there because upon reinstalling the RPM I'm still in the same position--I try to do

mysqladmin -u root password 'password'

but it says "Access denied for user root@localhost"

Deleted Root User From Mysql.user !!!
While trying to grant access to root user, i deleted all entries for root from mysql.user.

I've tried to use root% (anonimous built-in administrator user) but to no good).

Is there another admin user i could use to run mysql monitor and insert root records to mysql.user? I've read

Could I use a copy of the mysql.user table?

I'm using version 5.0.16-standard on Linux Fedora Core 3.

Before ruinin the root user, i made a backup of a databasefrom the same server (not mysql database); i was wondering if that backup contains the mysql.user information somehow?

User@localhost And User@domain.com
I have been following a tutorial for a mail server which uses mysql, mainly postfix

all through the tutorial i have been asked to put a user and password in the files for the database..

which i have done

root and pass1

in the log files the error shows as

warning access denied for user root@domain.com ( using password : yes )

when i log on from the shell, to mysql

and purpously get the password wrong, I get
access denied for user root@localhost ( using password : no )

i have checked the password is correct..

is the part after the @ the issue, and how can i tell mysql that root@domainname.com is a valid user???

Selecting User,password From User
When I do: select User,Password from user in MySQL, I get encrypted password. How can I see the real value?

And, what is the proper (secure) way to access MySQL database from PHP or other scripting languages?

User Problems... Only Root User "works".
i have been trying to figure this strange problem out for a week or so. here it is.

i have a set of users with various permissions for different reasons. about 2 weeks ago, my hardware suffered a fairly nasty failure. i recovered everything. things seemed to work out. i didnt think to check my phpBB forums or my blog (or other mysql based stuff). never had any problems with the DB... so, maybe i am missing something simple here. i hope so.

what my users can do:

they can log in via command line, a gui i use on mac os x and it does not matter if it is local, or remote. they work just fine.

what all but one users cannot do:

none of my users can run phpbb or the blog stuff.. except for the root user. any other user returns the error message written in the code of which ever script i try to access telling me that "could not connect to DB" etc..

root works fine.

now, i thought maybe it was a problem with the forums and other stuff. but i cant be. same thing happens with all things... even a game server i occasionally run that keeps account info in a DB.

all these users are set up properly and have worked just fine in the past. nothing new has been installed, no conf changes..im at a loss...

MYSQL User Information: Error While Storing The User Information
I have a proplem creating a new user and/or adding additional
information for root user in MySQL Administrator 1.0.19. When I do I
get this message: error while storing the user information.

Never the less, when I close the Admin program and open it again, the
changes I tryed to make are saved, (althoug the program asked me if I
wanted to save changes and I said no)

Is this a know bug, should I be alarmed, any solutions ?

I´m running
MySQL 4.3.11
Admin 1.0.19
Query 1.1.5
Apache2
php5

Changing User From One User To Another
Currently i have 2 Users in My MySQL(root,user1)...Whenever i am clicking command prompt Client its directly taking me to root user. If i want to change user (to user1) from there how i need to proceed...(Like connect in Oracle what we need to be use in mysql.

User And Use DB
Using PHP and MySQL:

I know I can use a connection string such as :
$mysqli = mysqli_connect("SQLSvr01","User", "Pass", "dB", Port);

Will I have a confined Query based on this connection or if my web site makes a query for a seperate dB at the same time from another user will they conflict.

or let me refrase

Is this connection isolated from all other requests, regardless of how many times the request is made, or is it dependant on the user or sever or database?

My SQL User
I juz install mysql4 with a window xp pro,
I cannot make database with "CREATE DATABASE;"

New User
I tried to connect remotely through the mysql server through MySQL Query Browser using the root user is perfectly fine. But I couldn't connect if i used my own created user. May i know what had happened??

Trying To Add New User
New to Mysql and I am trying to add a new user. I am using the MySQL Control Center to do this. Any time I try to add the new user to any database, I get the following error:

[admin] ERROR 1045: Access denied for user: '@localhost' (Using password: NO)

New User
I have a table in mysql where I have several columns and user name is the primary key.
I want to create a new user for a table and copy the attributes of one of the existing user to the new user.
Instead of copying one by one, is there a command in MYSQL, where I can copy all the attributes of one user to the new user

User: Any
I've noticed a user called Any in the MySQL privileges table. This user has 'useage' priv on host 'localhost' and 'build' ... should I delete this? Does it pose a security risk?Also, what's the host 'build' ?

User Name
I'm trying to connect to mySQL through Dreamweaver, and got as far as the mySQL Connection box, where I type in my connection name, server, user name, and password, then search for a database. I'm using a tutorial for an online class, but it's not very clear about the user name. How do I get one/do I already have one? Could it be one I set up with phpMyAdmin?

Please keep in mind that I am as noob as they come, so I may need the techono-speak dumbed down a bit

How To Get The ID Of Newly Inserted Record...
mysql_insert_id();

When u insert a record, this PHP function returns the ID(Primary Key, Auto increament) of the newly inserted record.

But now I want to use it in stored procedures, is there any mysql function to get the id of newly inserted record....

The Key Of A Newly Inserted Datarow
How can I get back the auto_generated key of a newly inserted line in a table?

I work with PHP, and when sending an INSERT request to the DB, I would like to get back the newly generated key automatically, instead of having to query again the DB to look for this key (which is not always possible...).

The PHP API for programming MySQL does not seem to provide such a functionality.... or am I blind?!?!?

Many IP Address' For One User
How do you setup a series of allow ip address' for a mysql user instead of having only one ip address?

Storing User CSS
I'm working on a social networking site with a friend and am curious about something: how much bandwidth do mysql queries typically consume? I'd like to make the CSS of user profiles open for editting, but that would mean a lot of css settings would have to be stored and constantly queried. Does this take up a lot of bandwidth and are there any ways to open up the CSS without consuming too much?

Getting Max User Error

I'm getting new error on my mysql app:
Warning: mysql_connect(): User my has already more than 'max_user_connections' active connections in /home/my/public_html/config.php on line 7
User my has already more than 'max_user_connections' active connections

Any ideas how to fix this error?

User Doesn't Have Permissions?
I install a mysql backup utility to backup my database if things go wrong. I went to test it to make sure it would work as it emails me the sql file, I deleted my database (using phpmyadmin) and tried to import the sql file which shoots me an error giving me an error that I didn't have permission to create a database. How do I give myself permission to create a database?

Create User In 5.1
i have created a user in phpmyadmin and given the account a password but no matter what I seem to try the user cannot connect with that password (they get in without specifying a password.

This is a fresh installation of fedora core 5 in case it matters.

Add A User To A Database
I would like to add a user to a database. A very simple thing you would think, but I can't get it to work.

This is what I have:

GRANT ALL ON database_name.* TO 'user'@'localhost' IDENTIFIED BY 'my password'

flush Privileges;

But for some reason this doesn't work.

The user already exists, so could that be the reason? I just want to give it permissions to this new database i've created now.

WHERE `user` = " . $user . "
Is there a way to check that in a column there is an entry with the same string indepentant of capitalization??

User.MYD Broken
While doing a couple of grant statements I suddenly got the message that
user.MYD could not be opened. So I copied it back from yesterdays backup but
no luck. I restored user.MYD, user.MYI and user.frm and after that the whole
mysql directory in /var/lib/mysql but the server now refuses to start. What
can I do?

User Updation
how to use mysql and make it where my client can update thier own website, looking at the page they are updating.

New User Can't See Any Databases
I'm starting with administrating MySQL, and the first problem arises. I installed MySql and everything works fine. With the root-user I can do what I want.

I created a very simple DB with three tables and a new User (via PhpMyAdmin). He has no global rights, but SELECT, UPDATE and INSERT rights for my test-db.

I can connect with my testuser to the server, but he is unable to see my test-db (SHOW DATABASES return empty set)... which right did I forget to grant him, so that he can see and use the test.db?

User Maintenance From App
I have problem with adding, updating and deleting users from my application.
There is an internal table (to hold address etc.) and I also need to add users to MySQL.

Everything seems to work except the "drop user" part.

I'm not sure about the @'localhost' and @'%' but I think I have to add both so the user can reach from anywhere in our lan. Code:

Backup User
I have created a backup script and I want to do a dump of my database right before the backup. I need to create a user that has access to all the databases. How can I create a user that has access to all the databases but cant do anything but dump the database to a backup? Code:

Creating New User
I am running MySQL 4.1.22 on SUSE 9 and Administrator 1.0.22. I have just installed it and created a new schema. I am now trying to create a new user - appears to have been successful. but when I try to change any details (password, schema priveleges) I get a duplicate user name error. I only have 3 users - the new user, anonymous and root.

Copy User Privileges To A Newly Created User.

I have just created a user with specific permissions to certain tables in different databases. Now I have to create serveral more users with the same permission. Is there a command in MySQL that allows me to create the new user by copying the privileges from a template user?

I have just created a user with specific permissions to certain tables
in different databases. Now I have to create serveral more users with
the same permission. Is there a command in MySQL that allows me to
create the new user by copying the privileges from a template user?