The short answer: don't give your users direct access to the database. They should never be able to connect. Only the people responsible for maintenance and operations should have access to the production database. This is for security reasons. In almost every case where information is stored in a database, there is an application which controls all access, it handles doing the actual updates, and it enforces the business logic that you choose.
Don't mix data with business logic.
There are some database systems, such as Oracle which excel at letting your store and apply much of your business logic inside the database itself. However, this is for a different type of application, and a different approach to building systems.
MySQL doesn't have all those tools to make doing this as easy. Trust me when I tell you that you're setting yourself up for a maintenance nightmare if you try to write your application logic in triggers and stored procedures and views, then give your users direct access to the database.
When was the last time you were given direct database access when you signed up for something? Twitter, Netflix, Groupon, Facebook -- you're interacting with a web-based application which applies the business rules and reads and writes data into the database on your behalf.
There are plenty of tools which make writing your application software easier: debugging, profiling, source control for code and collaborative development, unit testing, continuous integration and deployment tools. If you try to write everything into the database, you'll lose all of that.
Here's a quick example of how this would work:
Structure your permissions system as three tables: user, group, user_group. User holds the user accounts in you system, group holds the various levels of access such as "admin", "client", "anonymous", etc. Groups are how you assign access levels to users.
`CREATE TABLE `user` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(64) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `group` (
`group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_group` (
`user_id` int(10) unsigned NOT NULL,
`group_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`user_id`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;`
Now to define some groups
`insert into `group` (name) values ('admin'), ('user'), ('anonymous');`
And a user, then add them to the admin group:
`insert into user (email) values ('admin@yoursite.com');`
`insert into user_group (user_id,group_id) values (1,1);`
Now this permissions model says that a user can belong to one or more security groups. You application would check for those groups and preform different actions based on the results. Let's see some psuedo-code:
Load a user's groups:
class User {
private $user_id;
private $groups;
private $db;
function load_groups() {
// query the database
$result = $db->query("SELECT name FROM `group` g JOIN user_group ug USING (group_id) WHERE user_id={$this->user_id}");
// save an array of group names
while ($row = $result->fetchrow()) {
$this->groups[] = $row['name'];
}
}
function is_member($group) {
if (in_array($group, $this->groups) {
return true; // user group includes this value
}
return false; // user is not in the group
}
Now in your application, you might have a function to view the data, but it would produce different results depending on the user's groups:
function display_data($user_object) {
display_basic_data(); // everyone sees the basic data
if ($user_object->is_member('admin')) {
// if the user is an admin, then display bank data too
display_bank_data();
}
}
Similarly, your functions to modify data should verify that the users has permissions to change things.
