Solved

MySQL: Setting WriteOnly database permissions?

Posted on 2014-09-30
10
493 Views
Last Modified: 2014-10-29
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.
0
Comment
Question by:npaun
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 61

Expert Comment

by:gheist
Comment Utility
0
 

Author Comment

by:npaun
Comment Utility
huh, well, I've already seen that, and was hopping to get here some more detailed and elaborate suggestions...
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 150 total points
Comment Utility
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
 
LVL 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 150 total points
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 50

Accepted Solution

by:
Steve Bink earned 200 total points
Comment Utility
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
 

Author Comment

by:npaun
Comment Utility
@Dave Baldwin
Can you recommend a link to some page(s) explaining how to create such a user with limited privileges in phpMyAdmin?
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
@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
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
@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
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
Really?  A "B", after demonstrating the problem and solution with 1-1/2 pages of hand-holding code?

Wow...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now