Giving users limited Select privileges

Hi,

I’m toying with the idea of allowing my users (the users will actually be organizations) to self-generate reports from my database. There would of course be documentation in terms of the structure of the tables and their columns and the organization would be expected to know how to general MYSQL queries. Then, they would provide the select statement, I’d do the query and spit back the result. I’m wondering what might be a good approach to this. One thought that I had was to create tables nightly with the data specific to that organization, teasing out the organization’s user-based information from each of my tables. The new tables would be named (as an example) table_0o@$!^#&#&!#$#34 where the funky bit at the end is a code specific to that organization, created for security purposes.

It would be great to have some advice/thoughts on this idea; I’m not quite sure if the concept of creating user specific tables is either practical or the best way of doing things. And if it helps, my website will be hosted on Amazon. In addition, I can’t imagine having more than 50 new organizations per year (though would it even matter if it’s on Amazon?).

Thank you for your thoughts.

Instead of creating tables for the organisations, I would suggest you create VIEWs for them. A view is a sort of like a virtual table that contains all data that is the result of query. Therefore it is always up to date and you don’t have to do an update every day. Also you can then create a user that has access to their specific VIEW only and be sure they can’t see anyone else’s data.

Practically though, I think you should first look at what your audience is. If for example they are only marketing people, chances aren’t great they’ll know SQL, and you’ll probably be creating more confusion than solving problems by letting them run their own queries.

Thank you for both of your thoughts, especially the second one, regarding my audience. To be honest, I’m not quite sure what that will ultimately be: if I work with large organizations, there will certainly be some tech folks around, but with smaller ones, it’s not likely. However, I like knowing about the View concept: I think that it could potentially work well with I’m trying to accomplish.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.