Solved

MySQL: Setting WriteOnly database permissions?

Posted on 2014-09-30
10
529 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
LVL 34

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 51

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 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 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 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
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…

729 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