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>