Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL: Setting WriteOnly database permissions?

Posted on 2014-09-30
10
Medium Priority
?
585 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 62

Expert Comment

by:gheist
ID: 40352428
0
 

Author Comment

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

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 450 total points
ID: 40374446
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 36

Assisted Solution

by:ste5an
ste5an earned 450 total points
ID: 40374782
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
 
LVL 51

Accepted Solution

by:
Steve Bink earned 600 total points
ID: 40375041
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
ID: 40379475
@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 51

Expert Comment

by:Steve Bink
ID: 40380053
@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 84

Expert Comment

by:Dave Baldwin
ID: 40380516
@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 51

Expert Comment

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

Wow...
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
What we learned in Webroot's webinar on multi-vector protection.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

885 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