禁止用户的DDL操作
Kevin Zou
2011-9-21
给用户赋权过大有时就是一把双刃剑,虽然可以减少DBA的操作,但如果用户操作失误,把就要DBA介入来做恢复 。合理的授权一直我们DBA倡导的,人只要做合适的事情,不要做过分的事情。
一般用户就是拥有INSERT/DELETE/UPDATE 的权限,如果需要做DDL,那就交给DBA来操作好了。如何禁止用户对TABLE/INDEX等对象进行DDL的操作,有多种方法,常见的有两种:
1)SCHEMA的ONWER和读写分离,就是有两个USER,其中一个USER拥有这些对象,另外一个USER只能对这些对象进行DML;
2)在ORACLE 8I开始,ORACLE 允许在DDL做触发器。在对象级别加入触发器,禁止对象的修改。
这里只列出OPETION 2 的例子:
SQL> select * from v$version where rownum < 2;
BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> conn /as sysdba
Connected.
SQL> create user kk identified by kk;
User created.
SQL> grant connect,resource to kk;
Grant succeeded.
SQL> conn kk/kk
Connected.
SQL> create table test (id int);
Table created.
SQL> insert into test values(100);
1 row created.
SQL> commit;
SQL> conn /as sysdba
Connected.
SQL> grant select on v_$session to kk;
Grant succeeded.
SQL> grant execute on dbms_system to kk;
SQL> CREATE or replace TRIGGER db_ddl_trigger
2 before ddl on kk.schema
3 declare
4 n number;
5 l_trace number;
6
7 BEGIN
8 if ora_dict_obj_name() = 'TEST' then
9 raise_application_error(-20001,'You can not execute ddl on '||
ora_dict_obj_name );
10 end if;
11
12 END;
13
14 /
Trigger created.
SQL> conn kk/kk
Connected.
SQL> alter table test add (age char(20));
alter table test add (age char(20))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You can not execute ddl on TEST
ORA-06512: at line 7
在触发器里ora_dict_obj_name 是ORACLE 自定义的系统定义事件属性,代表Name of the dictionary object on which the DDL operation occurred.
还有很多函数,详细的列表:
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg14evt.htm
如果要运行用户执行DDL,怎么办? 呵呵。凉拌。。
DISABLE 触发器呗。
想要DISABLE这样独立的触发器,必须有ALTER TRIGGER的权限。
SQL> alter trigger db_ddl_trigger disable;
触发器已更改
SQL> conn /as sysdba
Connected.
SQL> alter trigger db_ddl_trigger disable;
Trigger altered.
SQL> conn kk/kk
Connected.
SQL> alter table test add (age char(20));
Table altered.
-THE END-