MYSQL :: Prevent Users Creating Their Own Databases

Creating New Users
I have added a new user to the mysql.user table and gave it the same permissions as the 'root' user (all Y's in the database).

When I use these details in an asp connection string I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver]Access denied for user 'nmssiteuser'@'localhost' (using password: YES)

Creating Users
I've been having a little trouble creating users cross-platform. I have
a little script that works just fine on Solaris 8 using this command:

eval "/path/to/mysql --user=root --password=*****
--socket=/path/to/socket --database=mysql --execute="INSERT INTO user
(Host, User, Password, Select_priv, Insert_priv, Update_priv,
Delete_priv, Shutdown_priv, File_priv, Create_tmp_table_priv,
Lock_tables_priv) VALUES ('localhost', 'LocalUser', PASSWORD('******'),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')""

With the obvious changes to make the statement actually work. However,
if I move this over to Linux, I get Invalid Authorization Specification
errors when I try to connect as LocalUser after a reload, and using
GRANT seems never actually to grant privileges (e.g. no "Y"s in the user
table). Does anybody know how I can successfully create a user with my
script?

Newbie: Creating Users?
I'm tring to create new users. I've read the docs and still kee recieving the error:

mysql> CREATE USER joeuser IDENTIFIED BY PASSWORD somepass;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER joeuser IDENTIFIED BY PASSWORD somepass' at line 1

How can I check to see if I have access to create users?
I created tables, I don't understand why I can't create users.

13.5.1.1. CREATE USER Syntax
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...

The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new record in the mysql.user table that has no privileges. An error occurs if the account already exists.

The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the keyword PASSWORD. See Section 13.5.1.3, â€œGRANT and REVOKE Syntaxâ€.

Seperating Users And Databases
I have a question regarding databases and users. How would I be able to make USER1 and USER2 and allow each to create additional databases for their website but not be able to see anyone else's databases.

I played around with the privileges in phpmyadmin but was unable to successfully accomplish what I am trying to do. Is this possible with one instance of mysql installed or do I need a separate mysql installation for each website.

Access To All Databases For All Users
I have just started using MySQL and I am a little confused. Is there a way to let User2 have access and edit all databases created by User1? I have a database created by User1 called myDatabase and I want to have User2 access it as well as make additions or modifications to it, i am sure there must be a way but I dont know how to phrase this to look it up on the net.

Creating Databases?
I was looking around at w3schools they showed mysql_create_db but then it said it isn't that anymore or somthing and gave me this code mysql_quary. What does that mean.

I'm pretty new to this and I'm just getting started so don't be supprised when I ask the question- what is a quary?

Creating Databases?
i am making my own membership script for my website and the thing i need is a database, i hae no idea how to make one.

Creating New Databases
I've just installed My Sql 4.0a on OS x.3 and just started getting the thing up. When starting a new database I get this

ERROR 1044: Access denied for user: '@localhost' to database 'menagerie'

Creating New Databases
I enter PHPMyAdmin. Then I click Databases. In the editbox I enter test and then I click Create. I get the following:SQL-query:

CREATE DATABASE `test` ;

MySQL said:

#1044 - Access denied for user: 'ze0nyx@localhost' to database 'test' How can I create the database? What does this error message mean? What have I done wrong? The site is hosted at funpic.org.

Creating Databases
I've successfully created a user and a blank database. I want to grant full access permission to the user I created. How do I accomplish this task?

Creating Databases, Etc. Using .sql Files
I have created a database, db_TEST, which includes a table, tbl_TEST.
I create a text file, using BBEdit, which includes:

DROP TABLE IF EXISTS tbl_TEST;

I logon to mysql as root and at the mysql prompt enter:

mysql > db_TEST < TEST.sql

The result is:

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'db_TEST < TEST.sql' at line 1

This procedure is recommended in "MyWQL" by Paul DuBois. I am using MySQL 4.0.15 on an iMac, OS 10.3.5.

What am I doing wrong?

Prevent Writes
How can I prevent writes to a mysql database but at the same time allow reads? This is in the case where I have a master/slave. The master got hosed so I promoted slave. Now I have the master back up and want to swap it back. So I need to stop writes to the slave, take a mysqldump, put it on the master and then start calling the master for writes.

Prevent MySQL Locking Up
I was doing some major work using MySQL this weekend, and I realized that MySQL is quite fragile. I mean, it can lock up so easily, and it's not funny. How can you prevent MySQL from locking up? What things can we really do so that MySQL doesn't lock up the entire server so easily?

Prevent ON UPDATE CURRENT_TIMESTAMP
Does anybody know if there is a way to prevent an ON UPDATE CURRENT_TIMESTAMP from occuring if I don't want it to?
Only way I can see to do it is by altering the column properties to just a TIMESTAMP field, do the update, then alter the column back.

Create Users Table For Users To Log On Or ...?
I'm pretty new to MySQL, what I was wondering is, for web systems that you need to login to, do developers generally create a users table and store all their users in there, or do they create users such as the root user in mysql ? If so, how do you add extra fields such as firstname last name etc and how would you go about putting them into groups?

How To Prevent Stop Words In A MATCH - AGAINST?
I have a simple search engine for my website, and the MATCH-AGAINST works well when search english words (contained in the `name` and `desc` columns), but when try to search a item-code contained in the `sku` column it seems like mysql handle that code (such as "hp-504") as a stop word and returns zero rows...

Prevent One Field Form Overlaping Another
I have the following table:

lesson {
id int,
lesson_start int,
lesson_end int,

PRIMARY KEY(id)
}

No lesson can overlap another. So I can't have rows like:

id | lesson_start | lesson_end
1 | 12:00 | 14:00
2 | 13:00 | 15:00

Is there any way to deal with it from the MySQL level?

Prevent MySQL From Starting At Startup
I'm starting to play around with MySQL. How can I prevent it from starting up whenever my computer starts? It slows down the startup some, and I'm not always wanting to use it. besides, I can start it myself if I need it.I've had a look on Google for theis, and found quite a few results, but they were fo Linux or Unix. I'm ruinning Windows XP.

Prevent Multiple User Registrations
What's the best way to prevent someone from registering multiple accounts on my website? Should I create a column called "IP Address" in the members table and make it UNIQUE? Are there any complications with this?

MySql Help - Need To Prevent The Duplication Of Information When Inserting To DB
Im am doing the web-based application, where I have to add some information from front-end, and then add it to the database. Now, I would like to get the MySQL statement that will prevent me from adding same thing in 1 table.

How To Prevent MySQL Queries From Taking Too Long
I have a very busy database with some tables having hundreds of thousands of records. I am running MySQL 5.0.16 on Windows. Some of the queries running on my database are not fined-tuned enough, and need improvement. However, the system is live in production and I am looking for a way to prevent MYSQL from committing Harakiri, i.e. from running very slow queries forever, taking the cpu to 50% and beyond for hours. My scripting language is PHP. I found that after a while the end user receives a timeout on the browser, but MySQL continues trying to run the query, sometimes for hours.
My question is, is there a way to specify a timeout for MySQL so that it will abort any queries that it is running that have not returned a result by then. The end user is no longer around anyway, so I am looking for a way to protect the database resources (and in parallel I will start investigating how to optimize the slow queries).

How To Prevent INSERT On Duplicate Rows With 30 Fields
I have a MySQL table with about 30 fields. I am inserting new data, and want to be sure that there are no duplicate rows. A duplicate row would be one with ALL the 30 fields (except the auto-increment index) exactly the same. How do I do that efficiently?

Prevent Copying From Mysql Data Tables ?
Recently we had to move some of our databases to the clients site onto their linux server.

Can someone hint on how to prevent "the clients" from snatching our data and db schema? The drawback: they do own the machine - hence they do have a root password, therefore even if I change the unix permissions on the mysql directories - they are still open to the "root".

Restoring Select Databases/tables From An --all-databases Backup
I use the --all-databases switch to backup my entire database. Sometimes there's a need to restore individual databases or tables form the backup file. What command should I use for this?

How Do I Prevent "too Many Keys Specified" ?
I keep getting the "too many keys specified" error. I am the admin of mysql and the database that i am trying to run a query in. Which file do i have to edit to prevent this, or is there another query that i must run.

Users
This is my first time with MySQL need a point in the right direction ;).
I have a private page where only one user can access. On the page there is a number beside the number there is a button. Each time the user presses the button the number goes one down (and the value should stay like this it shouldn't return when the user comes back!) when it is zero the button is disabled.

Edited 1 time(s). Last edit at 03/11/2006 07:14AM

How To Switch Users
I tryed my sql manual and could not find a solution to my problem.

when i open mysql command line client 5.x it prompts for root password and when supplied i loged in sucessfully. that is ok.

my question is how to login as a diffrent user from mysql prompt?

assume currently i have loged in as root and wanted to login as xyz.

How To Select All Users?
How do i get all the users that are listed in my database to show up in a table?

Would it be something like ?

<?php SELECT username, id FROM 'users' WHERE rank by 'id' ?>

How would I do that? and how would I make it into a table?

List All Users
I'm running through a book on mySQL and have messed up adding users. I've got everything sorted now, but I want to clean up all the screwed up ones I've entered along the way. The only problem is I can't remember what I typed, so don't know what to delete. Is there a way of listing all the users, so I can pull off the ones I don't need?

Merge Users
im completly new to mysql and have not really touched it before.
i have a gaming website running php fusion, i have a main site and several other subsites (one for each game played) what i want is once someone registers on the top site they are registered on all the sites, php fusion isnt yet able to do this so i thought maybe i could do it in mysql, is it possible to have 2 or more tables that will always be the same apart from the names? ie main_user and bf2_user both with the same info in? either this or can you assign more than one name to the same table?

How To Create Users On A Mac?
I have installed MySql on my Mac, running Mac OSX 10.4

MySql is started automatically on system startup, and I can see the
"MySql" icon in the system preferences. So far, everything should run
normally.

Now how can I administrate, in order to specify users? From the
readme file, I concluded that by default, the at least a user
"mysql" should be initially defined with empty password, but when
I enter on the bash command line

mysql -u mysql

I get the error message

'Access denied for user 'mysql'@'localhost' (using password: NO)'

Users Privilege
i noticed that when i connect to MySQL with php using:

mysql_connect();
or:
mysql_connect("dbhost", "dbusername", "dbpassword");

always will be connected and the user can do anything like creating databases or delete.
how can i prevent it? i just want the root user can do it.

Can't Log In B/c Deleted All Users
I installed the mysql-administrator GUI admin program, and my first task was to make it more secure by disabling anonymous logins and deleting/changing root access. I used the mysql-administrator to delete all accounts, and my goal was to then add another admin account right after, but mysql-administrator crashed on me and had a seg fault for some reason.

Now I don't seem to have any login ability as all users are gone. I've found articles online that speak of changing the root password, but I can't even log in as root or any other user (anonymous, included), so how do I get in? Luckily, I just started playing with mysql and have no valuable data. I'm running Fedora Core 4, and did a 'yum remove' of mysql and mysql-server. Then did a 'yum install' of both of tham again.

The problem is it's still acting like the old install (still looking for old config/password files of the old install??). Can someone tell me how to change/add users or completely remove mysql/d, and install again?

Removing Users
I can't seem to figure out how to remove users with php. I tried to do it with this
$query = mysql_query("REVOKE ALL PRIVILEGES, GRANT OPTION FROM user");

Where user is the name of the user I want to delete. That's what it said to do in the GRANT/REVOKE syntax part of the site but it doesnt do anything when I run the script.

Adding New Users
I can't seem to add a new user to my database! I've tried it through phpMyAdmin (under privileges) and also running the query

GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION

which returned no errors and according to phpMyAdmin the user is entered into the `users` table. But when I try to login with that name I get

Access denied for user: 'myusername@localhost' (Using password: YES)

So all I can use to login is 'root' with no password. Not very secure!

DB Design Users
If you have a database with several table and u want users to access the information. What is the available methods to allow these users to access information ( READING and ADDING at least).

Do I add users like the root. Or creating table which hold users informations in which case I wonder how these users access data without mysql user???

No Users In Mysql
I am in the process of setting up a 'testing server' using IIS, PHP, MySQL etc. The server is up and running and PHP is working fine. I have installed MySQL 4.1.12a and the server is up and running, I have managed to connect to the 'mysql' db through the console without any problems.

However, when I installed phpMyAdmin I got the 'client does not support authentication' error message. I have found this in the manual and understand how to deal with it, but on closer inspection there are no users listed within the user table in 'mysql'. As far as I am aware there should be some default users with a new install??

How do I setup a couple of new users and ensure that phpMyAdmin can access the server etc?

Current Users
How can I check what are the current users right now (except for the admin)?
I can't find this in a manual.

Setting Up Users
I have a website and db where people can login get setup and then enter information into the db, everybody has a unique client number, can I create a user for the db when they set themselves up as a user and restrict them from changing anything except data that is connected to their client number?

Users And Permission
Being that no user is accepted from the command line (even root)
using the command:

mysql -u root

does anyone know if editing the 'users' database in /var/log/mysql
with vi would help?

Listing Users
I would like to list the users and their permissions on my MySQL server.

None Of Users Privileges
I have no privileges anymore to create a database or view privileges and users. Does somebody know how I can add a user or give my root all privileges again?

Adding Users
I can add users but how do I make it so the users can not see all the DB, I know I can restrict the user so they cannot veiw the data but I dont want them to even see the other DB's

ADDING NEW USERS
I have set up a linux machine running ubuntu. I finally was able to login to mysql with root, but I am unable to add other users. I use this command

mysql> grant select, insert, update, delete, index, alter, create, drop
-> on books.*
-> to bookorama identified by 'bookorama123';

and the response I get is:

QUERY OK 0 ROWS affected

I have no clue as to what is wrong. I tried logging into the user I thought I created, but it acts like it's not there.

List All Users
im trying to make a activity list that list all users who havent logged in in the last 60 days

i have the following code but it wont only list the last 60 days activity....

SELECT * FROM hq_user
WHERE user_currentvisit < (CURRENT_TIMESTAMP() - INTERVAL 60 DAY);
ORDER BY user_currentvisit ASC;

Ranking Users
i'm trying to rank the users in our site.
table contains:
- userid
- userrates
and with thousands of records.
member with the highest rate goes #1, next is #2 and so on.
i want the query to be like:
Code:
<something goes here> WHERE userid = '$userid' order by userrates desc
from the code above it will show the rank of this user.
then the next user logsin and it shows again his/her ranking.
hope someone understands this
any input is appreaciated