在触发器上为表应用列权限 [英] Applying column permissions for a table over a trigger

问题描述

现在，我有一个名为members的表，其中包含一些东西，这些东西被拆分为多个数据，银行数据....
现在，管理员应该能够创建，更新，删除用户，这些用户保存在另一个表中，该表只能访问管理员.用户应该拥有自己的mysql用户帐户，管理员还应该能够设置权限，例如那些不能访问银行数据的用户.
现在，我做了一个触发器，该触发器创建并删除了用户帐户.现在我想将权限应用于触发器，但是我没有找到一个有前途的解决方案
我已经很努力地设置列权限，但是如果我想添加一列，我将有另一件事，我必须对其进行编辑在这种情况下.
我也强调了1:1关系，但是我没有找到有效的代码.

For now, i have a table called members, which contains stuff, which is splitted in conact datas, bank datas....
Now, the admin should be able to create, update, delete users, which are saved in another table, which can only access the administrator. The users should get their own mysql user account and the admins should also be able to set permissions, like that users arent able to access bank datas.
For now, I made a trigger, which creates and deletes the user accounts. Now I wanted to apply the permissions over a trigger, but I didnt find a promising solution
I tought already about setting column permissions, but If i want to add a column, I would have another thing, which i would have to edit in this case.
I also tought about 1:1 Relationships, but I didnt find a working code.

还有其他解决方法吗?

推荐答案

简短的答案:不要让您的用户直接访问数据库.他们应该永远都无法连接.只有负责维护和操作的人员才能访问生产数据库.这是出于安全原因.在几乎所有将信息存储在数据库中的情况下，都有一个应用程序控制所有访问，处理实际更新并执行您选择的业务逻辑.

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.

有些数据库系统(例如Oracle)擅长让您存储和在数据库本身内部应用许多业务逻辑.但是，这是针对不同类型的应用程序和不同的系统构建方法.

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并没有所有使这些操作变得如此容易的工具.如果您尝试在触发器，存储过程和视图中编写应用程序逻辑，然后为您的用户提供直接访问数据库的权限，请相信我，当您告诉您您正在为维护噩梦做准备时.

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.

您上次签署某项东西时是什么时候才获得直接数据库访问权限? Twitter，Netflix，Groupon，Facebook-您正在与基于Web的应用程序进行交互，该应用程序将应用业务规则并代表您将数据读写到数据库中.

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:

将权限系统构造为三个表:user，group，user_group.用户拥有您系统中的用户帐户，组拥有各种访问级别，例如管理员"，客户端"，匿名"等.组是您为用户分配访问级别的方式.

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

现在定义一些组

`insert into `group` (name) values ('admin'), ('user'), ('anonymous');`

和一个用户，然后将它们添加到管理组:

And a user, then add them to the admin group:

`insert into user (email) values ('[e-mail]');`
`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:

加载用户的网上论坛:

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.

这篇关于在触发器上为表应用列权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！