oracle+禁用ddl,禁止用户的DDL操作

禁止用户的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-