Solved

MySQL: Setting WriteOnly database permissions?

Posted on 2014-09-30
10
511 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 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 150 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 150 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 50

Accepted Solution

by:
Steve Bink earned 200 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 50

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 83

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 50

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
AWS RDS Backups? 3 51
Is Dropbox the only way to upload large files? 3 43
corrupt Databases 9 65
Xenapp 7 creating SQL databases using generated script 7 32
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…

860 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