Setting MySQL User Permissions

I have a question about database access in mysql on DH. Here is the scenario:

We have a site with an ASP model that stores information about users, real estate properties, and related info. The property specific stuff is broken out by userids so that we can pull information that applies to a specific client.

Ideally I want to make some sort of a webservice in php that will generate an xml string that is client specific based on the id that is passed to this page. On the client sites I'll have a simple php page that can parse the xml and load it into php objects. I don't have much of any XML experience unfortunately so this is going to be an uphill battle.

In the mean time I was thinking about simply adding a read-only password that would allow the client sites to query the database directly. When setting the new mysql account up it looks like I can only apply permissions at the database level and not at the table level. This doesn't work so good because I wouldn't want that account to read the user data. Is there any way that these permissions can be set on individual tables or is that not something I can do on the shared host? Any input would be appreciated.

In reply to:

---

Is there any way that these permissions can be set on individual tables

---

As far as I'm aware, Dreamhost doesn't let you this. What Dreamhost lets you do instead is create database user accounts, and each user account has various permissions that apply to every database where that account has access. Indeed, the only way to get per-database permissions is to create database users that only have access to a single database.

Unfortunately, I think what you'll have to end up doing is just making sure to be careful that you only write your queries to reference tables that you intend your database uesr to be allowed to access. It's a bit like driving without a seatbelt, but if you're careful about validating your client input then you should end up ok.

Dreamhost offers the mysqli interface to MySQL which I personally find helps me avoid many of the common stupid mistakes I'm prone to with the older style mysql interface that most PHP developers are more used to. If having the system enforce security for you is a concern, I definitely recommend checking out the newer interface.

Another option which just occurred to me is to do something known as a Cross-Database JOIN. What this basically means is that you would build up your overall database from several smaller databases; in an extreme case, you could do it to where each database contains only a single table. From there, since you can assign permissions on each database through careful use of user accounts, you can effectively get table level permissions.


Since that was likely pretty confusing, let me try to illustrate with an example:

Say you have three tables: One for client info (ci), one for posts a client has made (cp), and one for notes an administrator has made about particular users (an). The client is entitled to view any client's info and any user posts, but no client may ever view any administrator notes -- those are for admin only.

We can organize the database as follows:
client_db contains ci and cp
admin_db contains an

Further, we will need database users:
client_db_user with read/write on client_db
admin_db_user with read/write on client_db and admin_db

Now, if a client wants to see all of his posts, he uses the client_db_user account and issues a 'SELECT * FROM client_db.cp posts WHERE posts.client_id = ?'
If a client wants to see all of the admin notes logged against him, he can try to 'SELECT * FROM admin_db.an notes WHERE notes.client_id = ?' but he'll get a permission denied error because client_db_user cannot read from admin_db.

However, if an admin wants to have all of a user's informaiton, and a list of the admin notes logged against a user, the admin can use the admin_db_user to 'SELECT * FROM client_db.ci info JOIN admin_db.an notes ON notes.client_id = info.client_id WHERE info.client_id = ?'


As a disclaimer, please keep in mind that I've never used this technique in practice, and pretty much just dreamed it up on the spot. I think it will work (I did check to see that Dreamhost allows cross-database joins), but I'll make no guarantees about efficiency or maintainability. Indeed, for what you've described in your post, this is likely overkill. I also don't guarantee that the code aboveis spot-on accurate, though I think it would work.

I appreciate your detailed and well-thought-out response to my question, Alpicola. The technique you're suggesting could indeed be used to solve my problem, but I can see where it might become a logistical foo for a couple of reasons.

One particular reason which was evident from the beginning, is the fact that I would have to maintain and distribute essentially duplicate code to each of the client sites. Once this fact sunk in I chose to roll up my sleeves and figure out how to design the web service type call that would execute the query on my hub site, and return the results in XML format. It wasn't such a dismal affair and I was then able to develop a pretty simple parsing algorithm for the client sites, which limits the amount of specialized code that needs to be syndicated onto all of the client sites.

Since I am only creating web methods that access data intended for public consumption, it was pretty straight-forward to build something that seems like a reasonably safe implementation.

Thanks again for taking the time to think about my situation and to put together a clear response for me. This forum is always great for that sort of courtesy.

Given the kind of work it sounds like you're doing, it definitely sounds like coding the security controls yourself was the right way to go. Glad to hear you came up with an implementation that works for you!