SEO

I'm trying to setup some user accounts via a Java application but the
permissions I set don't seem to get enforced.

As an example user 'Fred'@'%' was created using via a JDBC connect:

create user 'Fred'@'%'

To each of the tables in database tciworks permissions were granted for
Fred using grant statements such as:

grant select on tciworks.patient to 'Fred'@'%';


Here's the status of the user account:



lvandenberg@CKPharmlap ~
$ mysql -u root -P
c:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe: option '-P'
requires an argument

lvandenberg@CKPharmlap ~
$ mysql -u root -p
Enter password: ************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

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

mysql> connect mysql;
Connection id: 2
Current database: mysql

mysql> show grants for fred;
ERROR 1141 (42000): There is no such grant defined for user 'fred' on
host '%'
mysql> show grants for Fred;
+-------------------------------------------------------------------------------------+
| Grants for Fred@%
|
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'Fred'@'%'
|
| GRANT SELECT ON `tciworks`.`theta` TO 'Fred'@'%'
|
| GRANT SELECT, INSERT, UPDATE, DELETE ON `tciworks`.`chistory` TO
'Fred'@'%' |
| GRANT SELECT ON `tciworks`.`courseview` TO 'Fred'@'%'
|
| GRANT SELECT ON `tciworks`.`michaelismentendrug` TO 'Fred'@'%'
|
| GRANT SELECT ON `tciworks`.`extravasculardrug` TO 'Fred'@'%'
|
| GRANT SELECT, INSERT, UPDATE ON `tciworks`.`patient` TO 'Fred'@'%'
|
| GRANT SELECT, INSERT, UPDATE, DELETE ON `tciworks`.`prevcoursethetas`
TO 'Fred'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `tciworks`.`eventhistory` TO
'Fred'@'%' |
| GRANT SELECT ON `tciworks`.`twocompdrug` TO 'Fred'@'%'
|
| GRANT SELECT ON `tciworks`.`event` TO 'Fred'@'%'
|
| GRANT SELECT ON `tciworks`.`ivdrug` TO 'Fred'@'%'
|
| GRANT SELECT ON `tciworks`.`drugs` TO 'Fred'@'%'
|
| GRANT SELECT, INSERT, UPDATE ON `tciworks`.`appvalues` TO 'Fred'@'%'
|
| GRANT SELECT, INSERT, UPDATE ON `tciworks`.`courses` TO 'Fred'@'%'
|
| GRANT SELECT, INSERT, UPDATE, DELETE ON `tciworks`.`dhistory` TO
'Fred'@'%' |
+-------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)



However, despite this line:

GRANT SELECT ON `tciworks`.`drugs` TO 'Fred'@'%'


The user actually has update privileges:

$ mysql -u Fred

mysql> connect tciworks;
Connection id: 10
Current database: tciworks

mysql> select * from drugs;
+--------------------------+------+----------------------------------+-----------------+------------+------------------+
-------------+------+------+----------+--------------+------+------+---------------+--------+---------+---------+-------
--+---------+--------------+------------+------------+-------------+
| name | f_eq | clearance_eq |
volume_eq | input_type | num_compartments |
elimination | CLCR | DWT | additive | proportional | CL | Vc |
fracintakebsv | dunits | doseMin | doseMax | concMi
n | concMax | doseInterval | targetCmin | targetCmax | description |
+--------------------------+------+----------------------------------+-----------------+------------+------------------+
-------------+------+------+----------+--------------+------+------+---------------+--------+---------+---------+-------
--+---------+--------------+------------+------------+-------------+
| Enoxaparin | 0.95 | THETA(1)*CLCR/6+0.2*DWT/70 |
THETA(2)*DWT/70 | 2 | 2 |
1 | 2 | 2 | 0.524 | 20 | 32.7 | 34.4 |
0.000 | 0 | 5 | 150 | 0.
1 | 20 | 12 | 2 | 10 | |
| Gentamicin | 1 | THETA(1)*CLCR+0.009*DWT |
THETA(2)*DWT | 1 | 1 |
1 | 1 | 2 | 0.25 | 15 | 35 | 33 |
0.000 | 0 | 0 | 800 |
0 | 30 | 24 | 0 | 20 | |
| Gentamicin Haemodialysis | 1 | THETA(1)*CLCR/0.53+4.69*EVENT(1) |
THETA(2)*DWT | 1 | 1 |
1 | 1 | 4 | 0.081 | 28 | 51 | 16 |
0.000 | 0 | 0 | 800 |
0 | 20 | 24 | 0 | 0 | |
+--------------------------+------+----------------------------------+-----------------+------------+------------------+
-------------+------+------+----------+--------------+------+------+---------------+--------+---------+---------+-------
--+---------+--------------+------------+------------+-------------+
3 rows in set (0.05 sec)

mysql> update drugs set doseMax=801 where name='Gentamicin';
Query OK, 1 row affected (0.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>


This is not what I want. I thought that MySQL should be preventing this
update?

In addition, user 'Fred' has permission to create user accounts for
anyone, which is also not what I want.

Can anyone tell me what I have done wrong? I have read the documentation
at http://dev.mysql.com/doc/refman/5.0/en/privileges.html
http://dev.mysql.com/doc/refman/5.0/...ing-users.html and
http://dev.mysql.com/doc/refman/5.0/...rivileges.html

amongst many other pages.





Some other info in case it helps:



mysql> select * from user;
+-----------+---------------+-------------------------------------------+-------------+-------------+-------------+-----
--------+-------------+-----------+-------------+---------------+--------------+-----------+------------+---------------
--+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----
------------+------------------+------------------+----------------+---------------------+--------------------+---------
---------+----------+------------+-------------+--------------+---------------+-------------+-----------------+---------
-------------+
| Host | User | Password
| Select_priv | Insert_priv | Update_priv | Dele
te_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv |
Process_priv | File_priv | Grant_priv | References_pri
v | Index_priv | Alter_priv | Show_db_priv | Super_priv |
Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl
_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv |
Create_routine_priv | Alter_routine_priv | Create_u
ser_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject |
max_questions | max_updates | max_connections | max_user
_connections |
+-----------+---------------+-------------------------------------------+-------------+-------------+-------------+-----
--------+-------------+-----------+-------------+---------------+--------------+-----------+------------+---------------
--+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----
------------+------------------+------------------+----------------+---------------------+--------------------+---------
---------+----------+------------+-------------+--------------+---------------+-------------+-----------------+---------
-------------+
| localhost | root | *3C8F822E0A9C0FEDD5F7A4BB1DA15954B2854313
| Y | Y | Y | Y
| Y | Y | Y | Y | Y
| Y | Y | Y
| Y | Y | Y | Y | Y
| Y | Y | Y
| Y | Y | Y | Y
| Y | Y
| | | | |
0 | 0 | 0 |
0 |
| localhost | |
| Y | Y | Y | Y
| Y | Y | Y | Y | Y
| Y | Y | Y
| Y | Y | Y | Y | Y
| Y | Y | Y
| Y | N | N | N
| N | N
| | | | |
0 | 0 | 0 |
0 |
| % | Fred |
| N | N | N | N
| N | N | N | N | N
| N | N | N
| N | N | N | N | N
| N | N | N
| N | N | N | N
| N | N
| | | | |
0 | 0 | 0 |
0 |
| localhost | tciworks_user |
| N | N | N | N
| N | N | N | N | N
| N | N | N
| N | N | N | N | N
| N | N | N
| N | N | N | N
| N | N
| | | | |
0 | 0 | 0 |
0 |
+-----------+---------------+-------------------------------------------+-------------+-------------+-------------+-----
--------+-------------+-----------+-------------+---------------+--------------+-----------+------------+---------------
--+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----
------------+------------------+------------------+----------------+---------------------+--------------------+---------
---------+----------+------------+-------------+--------------+---------------+-------------+-----------------+---------
-------------+
4 rows in set (0.00 sec)

mysql> select * from db;
+-----------+----------+---------------+-------------+-------------+-------------+-------------+-------------+----------
-+------------+-----------------+------------+------------+-----------------------+------------------+------------------
+----------------+---------------------+--------------------+--------------+
| Host | Db | User | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv
| Grant_priv | References_priv | Index_priv | Alter_priv |
Create_tmp_table_priv | Lock_tables_priv | Create_view_priv
| Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv |
+-----------+----------+---------------+-------------+-------------+-------------+-------------+-------------+----------
-+------------+-----------------+------------+------------+-----------------------+------------------+------------------
+----------------+---------------------+--------------------+--------------+
| localhost | tciworks | tciworks_user | Y | Y | Y
| Y | Y | Y
| N | Y | Y | Y | Y
| Y | Y
| Y | Y | Y | Y |
+-----------+----------+---------------+-------------+-------------+-------------+-------------+-------------+----------
-+------------+-----------------+------------+------------+-----------------------+------------------+------------------
+----------------+---------------------+--------------------+--------------+
1 row in set (0.00 sec)

mysql>

I meant to add. I think the problem may not be in the way I'm creating
users as I can connect to MySQL with any user name even if that user
does not exist and it allows me to update the database but not create
new users.

thanks

Lionel.

Thanks to any who observed.

Somehow I missed the fact that user ''@'localhost' had permission to
everything. destroyed that user and problem solved .

Cheers

Lionel.