触发器可以实现禁止用户删除某个表数据?

Question

任务"
如何让一个表只能insert 数据,不能删除和更新?表为
CREATE TABLE `t` (
  `a` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;准备用触发器来做,但是具体该如何写 才能禁止删除   ?????
create trigger trigger_t-del before delete on t
 for each row
 begin
if new.a>0 then
这里该如何写 才能禁止删除   ?????
end if;  
end;触发器可以实现禁止用户删除某个表数据?

Answer 1

感谢楼上
CREATE TRIGGER `tdel` BEFORE DELETE ON `t` FOR EACH ROW if old.a>0 then
insert into t values(old.a);
end if;;CREATE TRIGGER `tdel` BEFORE DELETE ON `t` FOR EACH ROW if old.a>0 then
insert into t values(old.a);
end if;;7.0.0.1~root@localhost~test>create table trigger_test(a int);
Query OK, 0 rows affected (0.06 sec)127.0.0.1~root@localhost~test>delimiter //
127.0.0.1~root@localhost~test>create trigger trigger_no_insert before insert on trigger_test
    -> for each row
    -> begin
    -> if new.a>100 then
    -> insert into trigger_test values(0);
    -> else
    -> set new.a=new.a+100;
    -> end if;
    -> end;
    -> //
Query OK, 0 rows affected (0.01 sec)带下划线的部分,就是拦截部分。如果A大于100，那么进行一个本表的插入，由于MYSQL在触发器内不允许对自身的修改，所以会产生一个1442的错误,插入失败。127.0.0.1~root@localhost~test>insert into trigger_test values(1);
Query OK, 1 row affected (0.00 sec)127.0.0.1~root@localhost~test>select * from trigger_test;
+------+
| a |
+------+
| 101 |
+------+
1 row in set (0.00 sec)插入100以内的，触发器正常工作。再插入一条大于100的。127.0.0.1~root@localhost~test>insert into trigger_test values(200);
ERROR 1442 (HY000): Can't update table 'trigger_test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
127.0.0.1~root@localhost~test>select * from trigger_test;
+------+
| a    |
+------+
|  101 |
+------+
1 row in set (0.00 sec) 
引起一个错误，200没有插入到表，成功拦截了下来。但是这样在上层程序操作的时候，会得到一个ERR提示。不知道是否有办法可以处理掉这个错误提示，不声不响的把数据操作给拦截下来。待考证。1、加入LABEL
LABEL: BEGIN
  SELECT * FROM WHERE ..
  IF ROW_COUNT() = 1 THEN
  LEAVE label1;
  END IF;
END
2、
没有提交就没有回滚3、
没有提交，估计修改没有存入，可以测试一下，一般要 COMMIT才会提交
4、测试一下就知道了，
http://topic.csdn.net/u/20101217/10/4bd4f9a3-6b32-4019-85ee-f10971a59ef8.html
http://blogold.chinaunix.net/u3/116107/showart.php?id=2326234

Answer 2

但是这样在上层程序操作的时候，会得到一个ERR提示。不知道是否有办法可以处理掉这个错误提示，不声不响的把数据操作给拦截下来。待考证。在程序中能否 捕获错误？

Answer 3

revoke delete,update on dbname.tbname from myuser@%

Answer 4

可以捕获到错误。
错误提示如下：
[MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.和命令行提示的错误是一样的。

Answer 5

MySQL 中如何在触发器里中断记录的插入或更新？
http://blog.csdn.net/ACMAIN_CHM/archive/2009/07/25/4380183.aspx

Answer 6

回  www 测试revoke delete,update on dbname.tbname from myuser@% 失败 
我 的操作过程
先 grant all on db.* to myuser@%localhost identified by 'aa';
之后 revoke delete,update on dbname.tbname from myuser@%localhost 但 是mysql -umyuser -paa
成功登录  select 所有表 成功
 insert 表成功
但是delete 表 a 也成功 ----------------------？？？ 楼上可以测试的 
所以判断失败 。  环境linux mysql5.022
给用户授权是比较好的选择我的 库 高达300多个表 
上周在线测试 ，如果给某个表赋予 仅仅selecr 权限，那么该库里其他的表都需要进行一个表一个表进行grant select insert 等赋权操作  .  但可惜操作时间过长

Answer 7

上面的表述有问题
grant all on db.* to bb@%localhost identified by 'aa';
之后只能执行
revoke delete,update on db.* from bb@%localhost   这个 是成功的 但是revoke delete,update on db.a from bb@%localhost 提示失败
错误过程如下 [h@de ~]$ mysql -uroot -pbe
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 604256 to server version: 5.0.22-logType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> revoke delete,update on db.a from bb@localhost;
ERROR 1147 (42000): There is no such grant defined for user 'bb' on host 'localhost' on table 'a'
mysql> revoke delete,update on db.a from bb;
ERROR 1141 (42000): There is no such grant defined for user 'bb' on host '%'www 的方法 测试revoke delete,update on dbname.tbname from myuser@% 失败  --------反复验证

Answer 8

你可以直接 grant select on db1.*

Answer 9

这个库 里300多张 除了1个表 限制update/delete其他都正常能select insert delete update的测试revoke delete,update on dbname.tbname from myuser@% 失败 --------反复验证

Answer 10

revoke delete,update on dbname.tbname from myuser@% 失败
提示错误ERROR 1147 (42000): There is no such grant defined for user 'bb' on host 'localhost' on table 'a'

Answer 11

ySQL用户管理
2010-11-19 15:45:43  www.hackbase.com  来源：互联网
MySQL管理员应该知道如何设置MySQL用户账号，指出哪个用户可以连接服务器，从哪里连接，连接后能做什 么。MySQL 3.22.11开始引入两条语句使得这项工作更容易做：GRANT语句创建MySQL用户并指定其权限，而 ...
    MySQL管理员应该知道如何设置MySQL用户账号，指出哪个用户可以连接服务器，从哪里连接，连接后能做什
    么。MySQL 3.22.11开始引入两条语句使得这项工作更容易做：GRANT语句创建MySQL用户并指定其权限，而
    REVOKE语句删除权限。两条语句扮演了mysql数据库的前端角色，并提供与直接操作这些表的内容不同的另一
    种方法。CREATE和REVOKE语句影响4个表：
    授权表内容：
    user 能连接服务器的用户以及他们拥有的任何全局权限
    db 数据库级权限
    tables_priv 表级权限
    columns_priv 列级权限
    还有第5个授权表（host），但它不受GRANT和REVOKE的影响。
    当你对一个用户发出一条GRANT语句时，在user表中为该用户创建一条记录。如果语句指定任何全局权限（管
    理权限或适用于所有数据库的权限），这些也记录在user表中。如果你指定数据库、表和列级权限，他们被
    分别记录在db、tables_priv和columns_priv表中。
    在下面的章节中，我们将介绍如何设置MySQL用户账号并授权。我们也涉及如何撤权和从授权表中删除用户。
    一、创建用户并授权
    GRANT语句的语法看上去像这样：
    GRANT privileges (columns)
    ON what
    TO user IDENTIFIED BY "password"
    WITH GRANT OPTION
    要使用该语句，你需要填写下列部分：
    privileges
    授予用户的权限，下表列出可用于GRANT语句的权限指定符：
    权限指定符 权限允许的操作：
    ALTER 修改表和索引
    CREATE 创建数据库和表
    DELETE 删除表中已有的记录
    DROP 抛弃（删除）数据库和表
    INDEX 创建或抛弃索引
    INSERT 向表中插入新行
    REFERENCE 未用
    SELECT 检索表中的记录
    UPDATE 修改现存表记录
    FILE 读或写服务器上的文件
    PROCESS 查看服务器中执行的线程信息或杀死线程
    RELOAD 重载授权表或清空日志、主机缓存或表缓存。
    SHUTDOWN 关闭服务器
    ALL 所有；ALL PRIVILEGES同义词
    USAGE 特殊的“无权限”权限
    columns
    权限运用的列，它是可选的，并且你只能设置列特定的权限。如果命令有多于一个列，应该用逗号分开它们.
    what
    权限运用的级别。权限可以是全局的（适用于所有数据库和所有表）、特定数据库（适用于一个数据库中的
    所有表）或特定表的。可以通过指定一个columns字句是权限是列特定的。
    user
    权限授予的用户，它由一个用户名和主机名组成。MySQL中的一个用户名就是你连接服务器时指定的用户名，
    该名字不必与你的Unix登录名或Windows名联系起来。缺省地，如果你不明确指定一个名字，客户程序将使用
    你的登录名作为MySQL用户名。这只是一个约定。你可以在授权表中将该名字改为nobody，然后以nobody连接
    执行需要超级用户权限的操作。
    password
    赋予用户的口令，它是可选的。如果你对新用户没有指定IDENTIFIED BY子句，该用户不赋给口令（不安全）。
    对现有用户，任何你指定的口令将代替老口令。如果你不指定口令，老口令保持不变，当你用IDENTIFIED BY
    时，口令字符串用改用口令的字面含义，GRANT将为你编码口令，不要象你用SET PASSWORD 那样使用password()
    函数。
    WITH GRANT OPTION子句是可选的。如果你包含它，用户可以授予权限通过GRANT语句授权给其它用户。你可
    以用该子句给与其它用户授权的能力。
    用户名、口令、数据库和表名在授权表记录中是大小写敏感的，主机名和列名不是。
    举例：创建一个超级用户test1
    mysql> grant all privilleges on *.* to test1@localhost identified by '123456' with grant option;
    创建一个只能查询的用户 test2
    mysql> grant select on *.* to test2@localhost identified by '9876543';
    二、撤权并删除用户
    要取消一个用户的权限，使用REVOKE语句。REVOKE的语法非常类似于GRANT语句，除了TO用FROM取代并且没有
    INDETIFED BY和WITH GRANT OPTION子句：
    REVOKE privileges (columns) ON what FROM user
    user部分必须匹配原来GRANT语句的你想撤权的用户的user部分。privileges部分不需匹配，你可以用GRANT
    语句授权，然后用REVOKE语句只撤销部分权限。REVOKE语句只删除权限，而不删除用户。即使你撤销了所有
    权限，在user表中的用户记录依然保留，这意味着用户仍然可以连接服务器。要完全删除一个用户，你必须
    用一条DELETE语句明确从user表中删除用户记录：
    #mysql -u root mysql
    mysql>DELETE FROM user
    ->WHERE User="user_name" and Host="host_name";
    mysql>FLUSH PRIVILEGES;
    DELETE语句删除用户记录，而FLUSH语句告诉服务器重载授权表。（当你使用GRANT和REVOKE语句时，表自动
    重载，而你直接修改授权表时不是。）
    举例：删除用户test1
    mysql> revoke all on *.* from test2@localhost;
    mysql> use mysql;
    mysql> delete from user where user='test' and host='localhost';
    mysql> flush privileges;
    MySQL数据库备份
    在数据库表丢失或损坏的情况下，备份你的数据库是很重要的。已经知道表被破坏，用诸如vi或Emacs等编辑
    器试图直接编辑它们，这对表绝对不是件好事！
    备份数据库两个主要方法是用mysqldump程序或直接拷贝数据库文件（如用cp、cpio或tar等）。每种方法都
    有其优缺点：
    mysqldump与MySQL服务器协同操作。直接拷贝方法在服务器外部进行，并且你必须采取措施保证没有客户正
    在修改你将拷贝的表，一般在数据库关闭情况下做。mysqldump比直接拷贝要慢些。mysqldump生成能够移植
    到其它机器的文本文件，甚至那些有不同硬件结构的机器上。直接拷贝文件可以移植到同类机器上，但不能
    移植到其它机器上，除非你正在拷贝的表使用MyISAM存储格式。
    一、使用mysqldump备份和拷贝数据库
    当你使用mysqldump程序产生数据库备份文件时，缺省地，文件内容包含创建正在倾倒的表的CREATE语句和包
    含表中行数据的INSERT语句。换句话说，mysqldump产生的输出可在以后用作mysql的输入来重建数据库。
    Mysqldump参数如下：
    #mysqldump -u 用户名-p'密码' 数据库名 [表名] > 操作系统下文件名
    举例：#./mysqldump -u root -p'123456' samp_db>samp.db.txt
    输出文件的开头看起来象这样：
    # MySQL Dump 6.0
    #
    # Host: localhost    Database: samp_db
    #---------------------------------------
    # Server version 3.23.2-alpha-log
    #
    # Table structure for table 'absence'
    #
    CREATE TABLE absence(
    student_id int(10) unsigned DEFAULT '0' NOT NULL,
    date date DEFAULT '0000-00-00' NOT NULL,
    PRIMARY KEY (student_id,date)
    );
    #
    # Dumping data for table 'absence'
    #
    INSERT INTO absence VALUES (3,'1999-09-03');
    INSERT INTO absence VALUES (5,'1999-09-03');
    INSERT INTO absence VALUES (10,'1999-09-08');
    ......
    文件剩下的部分有更多的INSERT和CREATE TABLE语句组成。
    输出单个的表：
    #mysqldump samp_db student score event absence >grapbook.sql
    #mysqldump samp_db member president >hist-league.sql
    缺省地，mysqldump在写入前将一个表的整个内容读进内存。这通常确实不必要，并且实际上如果你有一个大
    表，几乎是失败的。你可用--quick选项告诉mysqldump只要它检索出一行就写出每一行。为了进一步优化倾
    倒过程，使用--opt而不是--quick。--opt选项打开其它选项，加速数据的倾倒和把它们读回。
    二、使用直接拷贝数据库的备份和拷贝方法
    另一种不涉及mysqldump备份数据库和表的方式是直接拷贝数据库表文件。典型地，这用诸如cp、tar或cpio
    实用程序。本文的例子使用cp。
    %cd DATADIR
    %cp -r samp_db /usr/archive/mysql
    单个表可以如下备份：
    %cd DATADIR/samp_db
    %cp member.* /usr/archive/mysql/samp_db
    %cp score.* /usr/archive/mysql/samp_db
    ....
    当你完成了备份时，你可以重启服务器（如果关闭了它）或释放加在表上的锁定（如果你让服务器运行）。
    要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适
    当数据目录下即可。要确保文件是MyIASM格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机
    上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。
    建一个sh文件bakmysql.sh
    #!/bin/sh
    cd /usr/local/mysql/
    tar cvf  /mount2/mysqlvar.tar var
    可以设到操作系统自动运行。
    root用户crontab文件
    /var/spool/cron/crontabs/root增加以下内容
    0 2 1 * * /mount2/bakmysql.sh
    #/etc/rc2.d/S75cron stop
    #/etc/rc2.d/S75cron start
    重新击活Sun Solaris自动处理进程
    三、用备份恢复数据
    数据库损坏的发生有很多原因，程度也不同。如果你走运，你可能仅损坏一两个表（如掉电），如果你倒霉
    ，你可能必须替换整个数据目录（如磁盘损坏）。在某些情况下也需要恢复，比如用户错误地删除了数据库
    或表。不管这些倒霉事件的原因，你将需要实施某种恢复。
    如果表损坏但没丢失，尝试用myisamchk或isamchk修复它们，如果这样的损坏可有修复程序修复。
    1、 恢复整个数据库
    Mysqldump参数如下：
    #mysqldump -u 用户名-p'密码' 数据库名 < 操作系统下文件名
    举例：
    先在Mysql里创建另一个数据库
    mysql> create database test;
    然后将备份的数据导入
    #mysqldump -u root -p'123456' test2 < samp.db.txt  http://junsansi.itpub.net/post/29894/507578