MySQL: Setting WriteOnly database permissions?

I need to create a MySQL database, which will be accessible by my program through an ADO (Visual Basic 6) interface.
The database have to be WriteOnly, i.e. users should be able to only add records, but must not be able to list the database content, read or delete records, or be able to change anything in database structure.

-How to do this? I need advice how to create such a database and how to specify such access permissions.
-Ideally, for my needs, the database would have TWO access levels with TWO passwords: one will be for the administrator (i.e. me) which would give an unlimited access; the other would be for the users, which should have only the specified limited access.
-I have some (limited) experience working with MySQL, but nothing extensive; I’ve been creating MySQL databases I need by using phpMyAdmin, so the advice how to create such a database using this tool would be appreciated.
-A link to a good tutorial regarding this would be helpful.
npaunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gheistCommented:
0
npaunAuthor Commented:
huh, well, I've already seen that, and was hopping to get here some more detailed and elaborate suggestions...
0
Dave BaldwinFixer of ProblemsCommented:
You don't create a database with 'write-only' or INSERT privileges, you create a user that has only those privileges.  Note that that has to be an additional user because you can't admin the database or table without a user with more complete privileges.  In phpMyAdmin you can create such a limited user for your application and also use the 'root' user to maintain the database and tables.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
Use a stored procedure to insert your data.

In my understanding write-only means not to retrieve any information about stored data. Thus when you try to insert an existing row, you'll get a primary key violation. This is a side-channel which allows to retrieve information from your table. This case can only be handled by a stored procedure. Okay, not having unique keys would do it also, but such a table is often useless.
0
Steve BinkCommented:
ste5an's suggestion at #40374782 is the correct one.  Allow me to demonstrate.  Let's say you have a database that records requests coming from a user.  It records the IP, and associates a location with the IP provided.  The locations are stored in a lookup table:
mysql> create database writeonly;
Query OK, 1 row affected (0.00 sec)

mysql> use writeonly;
Database changed
mysql> grant all privileges on writeonly.* to writeonly_admin identified by 'writeonlyadmin' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> create table location (id int not null auto_increment primary key, trans_ip int not null, loc_name varchar(50) not null default 'No Location Name');
Query OK, 0 rows affected (0.00 sec)

mysql> insert into location (trans_ip,loc_name) values (inet_aton('10.0.0.1'),'location 1'),
    -> (inet_aton('10.0.0.2'),'location 2'),
    -> (inet_aton('10.0.0.3'),'location 3'),
    -> (inet_aton('10.0.0.4'),'location 4'),
    -> (inet_aton('10.0.0.5'),'location 5'),
    -> (inet_aton('10.0.0.6'),'location 6'),
    -> (inet_aton('10.0.0.7'),'location 7'),
    -> (inet_aton('10.0.0.8'),'location 8'),
    -> (inet_aton('10.0.0.9'),'location 9'),
    -> (inet_aton('10.0.0.10'),'location 10');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> create table requests (id int auto_increment not null primary key, ip int not null, location_id int not null);
Query OK, 0 rows affected (0.00 sec)

mysql> grant insert on writeonly.requests to writeonly_user identified by 'writeonlyuser';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

#> mysql -u writeonly_user -p
Enter password:

mysql> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for writeonly_user@%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'writeonly_user'@'%' IDENTIFIED BY PASSWORD '*18E9832D7E434B6DAE1F754BE8E41AA64F2C0332' |
| GRANT INSERT ON `writeonly`.`requests` TO 'writeonly_user'@'%'                                                |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use writeonly;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> describe requests;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| ip          | int(11) | NO   |     | NULL    |                |
| location_id | int(11) | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into requests (ip,location_id) values (inet_aton('10.0.0.1'),1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from requests;
ERROR 1142 (42000): SELECT command denied to user 'writeonly_user'@'localhost' for table 'requests'
mysql> exit
Bye

#> mysql -u writeonly_admin -p
Enter password:

mysql> use writeonly;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from requests;
+----+-----------+-------------+
| id | ip        | location_id |
+----+-----------+-------------+
|  1 | 167772161 |           1 |
+----+-----------+-------------+
1 row in set (0.00 sec)

Open in new window

At this point, everything is OK - the user can insert into requests, but cannot select, and you have an admin user with full access.  However, you want to be able to insert the IP and match the location automatically - your INSERT query should not have to manually specify the location_id.  After all, that's why the table is there:
#> mysql -u writeonly_user -p
Enter password:

mysql> use writeonly
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO requests (trans_ip,location_id) SELECT trans_ip, id FROM location WHERE trans_ip=inet_aton('10.0.0.2');
ERROR 1142 (42000): SELECT command denied to user 'writeonly_user'@'localhost' for table 'location'

Open in new window

The answer is to create a stored procedure with enough access to get the job done, and provide writeonly_user with access to execute it:
#> mysql -u writeonly_admin -p
Enter password:

mysql> use writeonly;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP PROCEDURE IF EXISTS sp_Proxy_AddRequest;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //
mysql> CREATE DEFINER=CURRENT_USER PROCEDURE `sp_Proxy_AddRequest`(IN `newip` VARCHAR(15))
    -> LANGUAGE SQL
    -> NOT DETERMINISTIC
    -> CONTAINS SQL
    -> SQL SECURITY DEFINER
    -> COMMENT 'Proxy procedure to add a record to requests'
    -> BEGIN
    -> INSERT INTO requests (ip,location_id) SELECT trans_ip, id FROM location WHERE trans_ip=inet_aton(newip);
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> GRANT EXECUTE ON PROCEDURE sp_Proxy_AddRequest TO writeonly_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

#> mysql -u writeonly_user -p
Enter password:

mysql> use writeonly;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for writeonly_user@%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'writeonly_user'@'%' IDENTIFIED BY PASSWORD '*18E9832D7E434B6DAE1F754BE8E41AA64F2C0332' |
| GRANT INSERT ON `writeonly`.`requests` TO 'writeonly_user'@'%'                                                |
| GRANT EXECUTE ON PROCEDURE `writeonly`.`sp_proxy_addrequest` TO 'writeonly_user'@'%'                          |
+---------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> call sp_Proxy_AddRequest('10.0.0.3');
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye

#> mysql -u writeonly_admin -p
Enter password:

mysql> use writeonly
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from requests;
+----+-----------+-------------+
| id | ip        | location_id |
+----+-----------+-------------+
|  1 | 167772161 |           1 |
|  2 | 167772163 |           3 |
+----+-----------+-------------+
2 rows in set (0.00 sec)

Open in new window


So, now you have a system by which a user does not have access to select, but can insert into tables even though they technically require select abilities.

Also, as mentioned, Dave Baldwin's suggestion (#40374446) of having a secondary administrative user with full access is necessary.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
npaunAuthor Commented:
@Dave Baldwin
Can you recommend a link to some page(s) explaining how to create such a user with limited privileges in phpMyAdmin?
0
Steve BinkCommented:
@npaun: Did you examine the code snippets I included in my last post (#40375041)?  The first snippet includes the SQL to create a write-only user, and the third snippet includes the additional permission required for this design strategy.  Those SQL statements can be run in phpMyAdmin.
0
Dave BaldwinFixer of ProblemsCommented:
@Steve Bink's info is a good place to start because phpMyAdmin uses the privileges info from MySQL.  I don't know of a tutorial for this particular thing.  Tutorials would be for the general case of creating users.  I never heard of anyone creating a 'write-only' setup before.
0
Steve BinkCommented:
Really?  A "B", after demonstrating the problem and solution with 1-1/2 pages of hand-holding code?

Wow...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.