1. 背景描述
在开发环境中,偶尔会出现程序意外将模式下的所有表drop掉,当然可以有好几种方法,如闪回表,闪回数据库,rman等方法都可以解决此问题。但最好的办法还是回收该用户的create table和drop table的权限。
本文介绍私有同义词方法来解决该问题。
2. 操作步骤
2.1 创建两个用户并赋予权限
[oracle@localhost ~]$ sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 7 16:47:01 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user u1 identified by u1;
User created.
SQL> create user u2 identified by u2;
User created.
2.2 给两个用户u1和u2赋予权限
SQL> grant connect,resource to u1;
Grant succeeded.
SQL> grant connect to u2;
Grant succeeded.
SQL> grant create synonym to u2;
Grant succeeded.
2.3 在用户u1下创建表
[oracle@localhost ~]$ sqlplus u1/u1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 7 16:50:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t1(id number);
Table created.
SQL> insert into t1 values(1);
1 row created.
2.4 将u1下的t1表的select、insert、delete、update权限赋予u2
SQL> grant select,insert,delete,update on t1 to u2;
Grant succeeded.
2.5 在u2下查询u1下的表的内容
SQL> show user;
USER is "U2"
SQL> select * from u1.t1;
ID
----------
1
2.6 在u2上创建私有同义词
SQL> create synonym t1 for u1.t1;
Synonym created.
2.7 利用同义词查询u1的表t1
SQL> select * from t1;
ID
----------
1
3. 小结
由于一个用户对其模式下的表是拥有全部的操作权限,因此我们需要转换思路,将表放在另外一个模式下, 将表的所有者和访问者分离即可实现对表权限的细粒度限制。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22700344/viewspace-732181/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22700344/viewspace-732181/