MYSQL :: Specifying A User To Be Able To GRANT On A Specific Table

Grant Users Privileges To Specific Database
system used:
Win2003srv, iis6, mysql4.1.7, phpmyadmin4.3.10

description:
I tried to grant a user 'cody' privileges to specific database 'cal_cody', but it won't do anything

Here are the steps I did:
created the user using phpmyadmin localhost and %
changed user password using the shell/console
user can login using the shell but not the phpmyadmin, no idea why
tried GRANT USAGE ON cal_cody.* TO 'cody'@'localhost' IDENTIFIED BY 'codypassword';
then reloaded the mysql service, didn't do anything.

Using GRANT To Add User
I have mySQL 4.1.1 running on Ubuntu 5.04
I type in the command to add a user like so:

mysql> GRANT select, insert, update, delete, index, alter, create, drop
-> ON books.*
-> TO bookorama IDENTIFIED BY 'bookorama123';

I get the response:

Query ok, 0 rows affected (0.00 secs)

Grant User
When doing a grant to username, is it possible to see if the username already exists and to stop the overwrite of that users priveleges?

Grant To User
I have a few mysql 4.1 intalls here running under both Windows and Linux.
Somehow, I've never been able to get grants to a user@'%' to work. I'm using something like:

grant all on mydatabase.* to some_user@'%';

But when I try to log into that server with the mysql command line using the correct password I get "Access denied for 'some_user'@'source_hostname".
If I expressly grant permission using the source host name I can log in fine.
Am I doing something wrong there?

Rated By A Specific User
I'll ask in this thread since the problem is somewhat similar.
What I have is

one table named products with fields product_id and product_name

one table named ratings with product_id, user_id, rating

and one table named users with user_id and username.

Ratings will contain entries added by users and what I need to do is select all products from the first table which have not been rated yet by a certain users identified by user_id.

What Is The Difference Between CREATE USER And GRANT ?
What is the difference between CREATE USER and GRANT ? When should which one be used?

Lock A User To A Specific Database
How can I create a new user, that only have access to his/hers database?

It's because I host my brother and my own site on my own server, but I have access to his databases, and he has access to my. How can I solve this?

Access Denied Even Though User Is Set In The Grant Tables
I have a MySQL directory uploaded to a remote domain so that: www."mydomain".com/mysql is where it's stored.

I have set the grant tables up to give me access. In fact I have played around with the grant tables in every which way I can to try and grant me access! Each time, I have uploaded the mysql directory (inside the data dir) and tried again to get in.

I've tried it with a password, without, with a specific host name, with %, etc etc. I have tried to access the db from the command prompt of my local machine and using Navicat. Each time I get "access denied for "me"@"host".

I do appear to be connecting in some way to mysql as I do get asked for a password. But it seems like the info in the grant tables is wrong somehow? I'm baffled.

The connection data I use is the IP of my domain (I've tried the URL as well) my username and password, and the port 3306.

The reason I need to access MySQL is to start it so that the phpBB also installed on my domain can function. I have seen people talk about Telnet access to get in via the command prompt but I dunno how to do that (blush) so as a stop-gap can anyone tell me how to do that?!

Restrict User Access To A Specific Database
Currently I have several users set up and they can see all databases available. How do I set it up so that a user can only view a specific database? (my isp is using MySQL 3.23.56)

Grant Table
I was trying out phpMyAdmin and accidently deleted all the users. I have rebuilt the tables using mysql_install_db, run mysql_fix_privilege_tables, flushed tables, flushed hosts, reset all users including root user from phpMyAdmin, ran mysqladmin to set root user password.

Problem is that I can only connect to the mysql server if it is started in safe mode with skip grant table option. If start server normally cant connect even as root. Anyone have an idea?

Specific Table
Hello MySQL gurus,

This is the command :

mysqldump -u$user -p$pass $database >$backup

Can you exclude a specific table, say table UnNecessaryAndHugeTable, from the dumping process?

that is mysqldump all tables except table UnNecessaryAndHugeTable

Problem With Table Name Grant
I'm required in an assignment to create a table with the name Grant - however as this is a keyword mySQL is not allowing me to do this, and I am having no luck finding any sort of workaround. Is there a solution?

Can't Add New Users & Passwords To Grant Table
I am running MySQL 4.1.7 on my Windows XP system. I can create databases, ect. on the command line, but when I try to run a PHP script I receive the 'Access denied' error message. I have tried using the Grant command to add a new user but nothing comes up when I show SHOW GRANT except the root user. How do I add the new users for running the PHP script?

Removed Root From Grant Table
I was messing about with a database that was not working correctly with phpmyadmin.
I do not know what I was thinking, but I removed all of the users from the database, figuring, I guess, that I would just reload it if I needed to.

Imagine my suprise when I discovered that I could no longer get access to ANY of my databases or phpmyadmin (the single most dangerous tool in all of the computing world)!

I read one site that told me that basically I threw away my master set of keys and I should add skip-grant-tables to my.cnf, which I did, but now what?

Can I recreate the root account? Or do I really have to remove and reinstall MySQL?

GRANT SELECT ON Db.table - Error!
I'm trying to grant select privilege to a user on a certain table using the following format:

GRANT SELECT ON mydb.mytable TO 'myuser'@'localhost';

But I keep getting the error "Can't find any matching row in the user table". I've tried the following:

select * from mysql.user where user = 'myuser' and host = 'localhost';

it brings back a result no problem. The database 'mydb' exists, the table 'mytable' exists on that database. I've tried this through phpMyAdmin and the mysql command line tool but get the same error on both.

How To Disable Binlog For Specific Table
For security reasons, we need a single table (containing sensible information) to be excluded from going to binlog. I have found out as much, that I can use SET SQL_LOG_BIN = {0|1} to switch logging on/off per connection, but I would prefer if I could simply exclude the table all together. The table is a ENGINE=MEMORY type table btw.

Display Table Status With Specific Columns
For mysql, "show table status" displays all table information such as NAME, TYPE, ROW_FORMAT, ROWS.....
How can I only display certain columns(not display all), for example only shows NAME, ROWS?

Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following:

-----------------------------------
SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC

while(FetchRowAssoc())
{
SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id

echo cat_it, cat_name, itemcount, blah... etc.
}
------------------------------------

I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table.

Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category?

"SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.

Grant Lock Tables On Single Table Gives ERROR 1144
I can't understand why mysql allows me to grant lock tables to all
tables in a particular database, while it fails to accept the same
command applied to a single table only. I've tested on MySQL versions
4.1.20 and 5.0.33 (both running CentOS 4.4 linux on Intel-32) with
exact same results.

Whats wrong with grant lock tables on a single table?

mysqlSELECT VERSION()G
*************************** 1. row ***************************
VERSION(): 4.1.20
1 row in set (0.00 sec)

mysqlSELECT CURRENT_USER()G
*************************** 1. row ***************************
CURRENT_USER(): root@localhost
1 row in set (0.00 sec)

mysqlCREATE DATABASE grant_test;
Query OK, 1 row affected (0.00 sec)

mysqlUSE grant_test;
Database changed
mysqlCREATE TABLE t (i INTEGER PRIMARY KEY NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysqlGRANT SELECT ON grant_test.t TO grant_test_user IDENTIFIED BY
'Ahelluwapassw0rd';
Query OK, 0 rows affected (0.00 sec)

mysqlGRANT LOCK TABLES ON grant_test.t TO grant_test_user IDENTIFIED
BY 'Ahelluwapassw0rd';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the
manual to see which privileges can be used
mysqlGRANT LOCK TABLES ON grant_test.* TO grant_test_user IDENTIFIED
BY 'Ahelluwapassw0rd';
Query OK, 0 rows affected (0.00 sec)

Grant Lock Tables On Single Table Gives ERROR 1144
Tested on MySQL 4.1.20 and 5.0.33 with similar results. I don't
understand why I cannot grant LOCK TABLES on a single table, while
granting LOCK TABLES on all tables work fine.

mysqlSELECT VERSION()G
*************************** 1. row ***************************
VERSION(): 4.1.20
1 row in set (0.00 sec)

mysqlSELECT CURRENT_USER()G
*************************** 1. row ***************************
CURRENT_USER(): root@localhost
1 row in set (0.02 sec)

mysqlCREATE DATABASE grant_test;
Query OK, 1 row affected (0.04 sec)

mysqlUSE grant_test;
Database changed
mysqlCREATE TABLE t (i INTEGER PRIMARY KEY NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysqlGRANT SELECT ON grant_test.t TO grant_test_user IDENTIFIED BY
'foobar';
Query OK, 0 rows affected (0.12 sec)

mysqlGRANT LOCK TABLES ON grant_test.t TO grant_test_user IDENTIFIED
BY 'foobar';

ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the
manual to see which privileges can be used

mysqlGRANT LOCK TABLES ON grant_test.* TO grant_test_user IDENTIFIED
BY 'foobar';

Query OK, 0 rows affected (0.03 sec)

Relating User Table With Content Table
I have a mysql table with the curriculum vitae (CV) information of about 50 researchers. I would like each researcher to be able to edit and update his/her information. In order to do this I need to create a membership system for the researchers to access their respective CV page.

Before I do anything I want to plan this out, hopefully with a little help of more experienced developers and programmers. So I am open to any ideas.

This is the thing that stumps me the most right now:

Right now I have a CV table (with all the curriuculum vitae information) and need to create a users table that will somehow link with the CV table. Eventually, what I would like is with a login and password (that they choose) they will automatically open their CV page. These are the questions I have:

What fields in the user table should I use to link the user table with the CV table?
Once that is established, how, by providing their login and password can I direct them to their own page to update? Should one of the fields in one of the tables contain the (already existing) address of their CV page?

User Table In 5.0
I have a question about the "grande" entries in the 'user' table of
MySql 5.0.0-alpha.What is this?
Also, why are there two rows with "root" (one with localhost and one
with "grande")? Why two entries?

Question About User Table In 5.0
I have a question about the "grande" entries in the 'user' table of
MySql 5.0.0-alpha.

What is this?

Also, why are there two rows with "root" (one with localhost and one
with "grande")? Why two entries?

User Table And Version
I am using mysql 3.23.54 that came bundled with RH9. When I do a show =
databases there is no user table, I am working through the reference =
manual for 4.0 is the reason there is no user table because of the =
version? also I have tried updating to version 4.0 using the RPM =
available from the mysql site but on logon the version still says =
3.23.54 and still no user table; any ideas?

Mysql User Table?
I have number of customer and they should have different access levels, some would access only few pages. I thought if i create a table:users in my database: maindata-> table: loginuser: will solve the problem, but something is not right when i saw the default mysql user table.

maindata->table loginuser: columns are
user id autoincrement
userfname varchar
userlname varchar
email varchar
userurl varchar
accessgroup (tinyint) = 1,2,3,4
Depending on this value they will access the specific url with.

However after reading code below, I am bit confused about storing all users, should i use default mysql user table cause it has got all privaleges by default ie., insert, del, modify etc. and sounds more secured cause if by mistake users can type admin.php url? I am new to all this and sort of confused.

PHP Code: ....

Mysql User Table For Authentication
What is the best way to store usernames/password information within a mysql Table? Is there a way to encrypt the columns that house this data?

Sorting A Table According To User Criteria.
I was looking for a some kind of simple solution to this issue. The application requires that certain tables should be showed ordered by the user criteria, for example categories. the user will insert a new category and then he could move up or down the item to the position he wants to show it.

In order to do this, I was thinking to add a column, let's call it 'sort_id' this column will save the position of the record. (this is the best i could come up with, I would love any suggestions). This might create some concurrency problems, but since this is an administration module, I don't think more than one would modify the same table at the same time. Anyway, I couldn't find an easy way to insert a new record in the table with the last position + 1.

Usernames And Password In User Table
I had multiple rows in 'User' table in mysql DB.

namely
% root <no password>
localhost root <no password>
% <null> <null> //anonymous user

Now i want to connect to the server through my application.

I deleted anonymous user, and entry for % root
and set password for localhost root.
Then i created a normal user
'% testuser testpwd' with all the previliges.

So that if i want to connect to the server from any other machine than server, i have to use 'testuser' account. In my application i am connecting to server using 'testuser'.

Database - User Table - Propagation
I've started a new website and I want to have a database that works with all my other user tables in my other databases. This is my idea.

Background. Example Setup Tree
--Main Page:
-->Forums
-->Mail
-->Collaboration
-->Bug Reporting
-->Etcetera
-->Etcetera
-->Etcetera

*NOTE: Each subsection has it's own database with their own user tables

-------------------------------------------------------------------------------

UserX visits my site. There's a register screen and UserX performs the standard Registration which is then added to the userdb in the usertable.

I want to run a query that will update all subsequent user tables of their own databases.

So, one query or multiple queries will register UserX's info into all those databases.

Once the person is logged into the site, the don't need to register nor login into the other portions of my site, they just click on 'said' link and have access to that portion of the site.

Creating A User-defined Table
I am wanting to create a user defined table in mySQL. The general idea is that they all would have an asset number (PK). After that, the items can be different.

For instance, I may be keeping track of a table, in paticular it's color, size, and cost. OR I might keep track of a computer system, the OS it has installed, who owns it, etc.

Restore Default User Table
For some reason the mysql database does not contain any tables;

How do i restore the default user table?

I tryed to reinstall mysql:

yum remove mysql httpd php php-mysql mysql mysql-server

yum install mysql httpd php php-mysql mysql mysql-server

service mysqld start

[root@localhost] ~ : mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 30 to server version: 4.1.20

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> show tables;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql'
mysql>

But i still dont have permission to create a new database, cause mysql database have no tables.

Configure 2 Sites To Access One User Table
I have two databases on the same server with different domains. I would like both sites to access the same user database table. They have the same fields.

I thought that it might just be done with an mysql command, which will point one table to the other.

Create User Table With An Encrypted Variable
I'm trying to come up with a user database for logging onto my server where the passwords stored in the table under the column "password" are encrypted. I also need another script I have to understand when it's been 30 days since the password was changed, so I need to capture the date the password is changed. I've looked at a few reference manuals and tried the code:
CODEcreate database auth;
use auth;
create table auth (
name varchar(7) not null,
pass varchar(9) ENCRYPT not null,
fullname varchar(30) not null,
date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
deptnumber varchar(4),
phonenumber varchar(10),
cellnumber varchar(10),
primary key (name)
);
insert into auth values
('name', ENCRYPT 'pass', 'fullname', 'NOW()', 'deptnumber', 'phonenumber', 'cellnumber', );

insert into auth values
( 'someuser', ENCRYPT ('test12345'), 'Joe User', 'NOW()', '3421', '504 257-0000', 'none' );

Table Creation Based On User Login
I'm trying to build a website that will allow the addition of users (w/ username, password, etc.).

That seems simple enough.

But is it possible to set something up (in PHP, for example) that will create a new table in the database for each new user?

Ex. John Smith signs up, is verified, and a PHP script (or Java, or whatever) creates a table specifically for him.

I know I'll need a master table of usernames, passwords, etc. These new tables would store certain user-specific data, such as preferences and other data the user might want to restrict access to.

I know I could store all of this information in a master table, but I'm trying to think ahead. Worst case scenario, if there are millions of users (unlikely, but not impossible), I'd hate to have all that data in one table, but if I have to, I can.

Error 1036: Table 'user' Is Read Only
For some odd reason, the user table that contains all the priveleges and such is read only... I can't write to it... thus I can't change my password or permissions or anything of the sort.

I've tried reinstalling the databases, but no luck, same thing... i checked permissions on /var/lib/mysql/mysql/ and all the files under it and i gave write access 2 mysql.mysql... so i dont know whats going on.

Finding Field In Table That Matches User Supplied Input
I've come across match/against, field, and locate. I've been unable to use these to match against columns.

As an example, I have a table with two columns, an index number and a domain name. The user supplies a FQDN (fully qualified domain name). I would like to compare all of the domainname rows to see if they match against the FQDN and return a go/no-go value.

The match function is close, but works in reverse. I cannot see a way to get locate or field to work with fields.

GRANT
I try to create a user benutzer on my mysql-server with this statement :
GRANT USAGE ON testdb.* TO benutzer@"%" IDENTIFIED BY 'testpass'
The User should work with the database tetdb.

After the statement, the user benutzer is created, but he has no rights to work with the database testdb. Has anybody an idea ?

Grant
this query works fine, except for one thing, it creates the new user, but it doesn't give him any privilages.

$sql_grant='GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON '.$dbname.'.* TO '.$host.' IDENTIFIED BY "'.$password.'"';

GRANT
I'm developing a C application that uses the MySQL API. The aim is to distribute the application and among the end users, to allow them to participate in database updates. The problem is, currently through the Cpanel, I'm restricted to add users through the interface or add with the GRANT command. I want to be able to say GRANT SELECT ON *.* TO adriens_user@* IDENTIFIED BY "password";
But this doesn't work, erroring out because of syntax. What I'm trying to do is say anybody who uses the app get's to use the database, because the user and password is set in the binary file.

GRANT
i used this query to grant access, using the root user:

grant select,insert,update on siem.* to 'laptop'@'RAMONLAP' identified by 'pass';

with the user laptop i can insert rows but i can delete them too, what can i do to grant only select, insert and update but not execute a delete or other queries?

Grant
i made a thread about this before and i was told to add a flush privileges line.
well that worked...once....and never again.

here's my code:
PHP Code:
$host="'$username'@'localhost'";
$sql_grant='GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON '.$dbname.'.* TO '.$host.' IDENTIFIED BY "'.$password.'"';
$flush=mysql_query("FLUSH PRIVILEGES") or die(mysql_error());
$result_grant=mysql_query($sql_grant) or die(mysql_error());
echo"Your account has been activated, Thank you for choosing Majd-GFX.";
exit();
now it's creating the user....but with no privileges.....

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.

Grant Statements...
i understand Grant statement is used to specify access limitation to certain users in what they can do to the tables/database ~
i have a table as shown in the statement below:-

create table admin(UserID int(4) zerofill not null auto_increment,
UserName varchar(15) not null,
Password varchar(15) not null
primary key(userId)
);

i can create a set of access privileges for one particular username ~ what if there are some new users being registered into this table??does it means that i have to type the grant statement for each and every new users again and again~ to specify the limit for each new users?

Another Problem With Grant
ok so i made a thread about this before and i was told to add a flush privileges line.

well that worked...once....and never again.

$host="'$username'@'localhost'";
$sql_grant='GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON '.$dbname.'.* TO '.$host.' IDENTIFIED BY "'.$password.'"';
$flush=mysql_query("FLUSH PRIVILEGES") or die(mysql_error());
$result_grant=mysql_query($sql_grant) or die(mysql_error());
echo"Your account has been activated, Thank you for choosing Majd-GFX.";
exit();

now it's creating the user....but with no privileges.

GRANT Priviledges
I have a user who I have set to have GRANT option, but when I login as that user and try to grant other users priviledges I get an error.

This is my setup:

It's all based on a registration form written in php. A person fills in a form, the data is written to the database, then I wish to immediately grant that user SELECT priviledge.

But when I try to I am given the error "Access denied for user 'account'@'localhost' to database 'accounts'.

My grant string is such:

//grant string
$query_string='GRANT SELECT, UPDATE ON accounts.advisors TO ' . $_POST['username'] . '@localhost IDENTIFIED BY '' . $_POST['pass'] . '' ';

//grant push
$q_result=mysql_query($query_string) or die (mysql_error() . ' ::::: ' . $query_string);

If you echo the grant string you'd get something like:
GRANT SELECT, UPDATE ON accounts.advisors TO tom@localhost IDENTIFIED BY 'topbanana'

Using Grant In Phpmyadmin
i installed mysql w/ phpmyadmin on my localhost. i created a database using phpmyadmin. now i want to create a user and password for it. how would i do that?

i did read the mysql's documentation. it said somethign about executing the Grant command. well, i tried that but always get some kind of error. Would any kind person just show me a simplee way to add a user and password to a database in myql?

Grant Privileges
I somehow remocved grant privileges for my root account. Can someone tell me how to reset root's privileges?

How To Remove GRANT
After running following command;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO
'vmailuser'@'localhost' IDENTIFIED BY 'password123';
Query OK, 0 rows affected (0.00 sec)

I found I made a mistake to run it. Please advise how to remove GRANT given to vmailuser to test database.

Specifying A User To Be Able To GRANT On A Specific Table

I tried giving said user GRANT power on a table, but it isn't working. He currently has SELECT-only on this table, and nothing more. the command: GRANT GRANT OPTION ON db.table TO username Any idea why it isn't working?

I tried giving said user GRANT power on a table, but it isn't working.

He currently has SELECT-only on this table, and nothing more.

the command: GRANT GRANT OPTION ON db.table TO username

Any idea why it isn't working?