禁止用户的DDL操作

linuxboy823

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-