关于oracle 出现类似自动授权的情况

模拟情况：

新建用户：

SQL> 
SQL> create user mytpl identified by zhouweizhu#123456;

User created.

SQL> grant connect,resource to mytpl;

Grant succeeded.

SQL>

利用新建用户查看其他用户的表

SQL> conn mytpl/zhouweizhu#123456;
Connected.
SQL> select scott.test;
select scott.test
                *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> select * from scott.test;

	ID NAME
---------- ------------------------
	 1 a
	 1 abcdefgh
	 1 1
	 1 1

从上面例子当中就可以看到我们明明没有给mytpl用户授权，但是它竟然会有scott用户test表的查询权限，极其奇怪

在官方文档当中有那么一段话

Guideline for Handling Privileges for the PUBLIC Role
You should revoke unnecessary privileges and roles from the PUBLIC role. The
PUBLIC role is automatically assumed by every database user account. By default, it
has no privileges assigned to it, but it does have grants to many Java objects. You
cannot drop the PUBLIC role, and a manual grant or revoke of this role has no
meaning, because the user account will always assume this role. Because all database
user accounts assume the PUBLIC role, it does not appear in the DBA_ROLES and
SESSION_ROLES data dictionary views.
Because all users have the PUBLIC role, any database user can exercise privileges that
are granted to this role. These privileges include, potentially enabling someone with
minimal privileges to access and execute functions that this user would not otherwise
be permitted to access directly.

也就是说public角色是一个公有角色，默认情况下该角色没有任何 权限 ，但是一旦你给public角色授权，那么所有用户都具有了这个权限

查看mytlp用户的权限

SQL> col username for a8;
SQL> col privilege for a20;
SQL> select * from user_sys_privs;

USERNAME PRIVILEGE	      ADMIN_
-------- -------------------- ------
PUBLIC	 SELECT ANY TABLE     NO
MYTPL	 UNLIMITED TABLESPACE NO

SQL> 
SQL> 

可以看到结果当中有一个特殊列public，在public列当中有select andy table 权限。

也就是说所谓的自动授权是因为我们把某些权限授给了public，导致其他新用户在刚刚创建的时候就具有了这些权限。

收回权限

SQL> revoke select any table from public;

Revoke succeeded.

注意，如果该权限在使用，会报错

SQL> REVOKE SELECT ANY TABLE FROM PUBLIC;
REVOKE SELECT ANY TABLE FROM PUBLIC
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object


SQL> 

可以试一下关闭数据库，然后启动到mount状态收回权限 ，或者所所有session的kill掉，然后再收回权限（这两种 方法都没有试过）

验证收回权限是否成功

SQL> conn mytpl/zhouweizhu#123456
Connected.
SQL> select * from scott.test;
select * from scott.test
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 

SQL>  col username for a8;
SQL> col privilege for a20;
SQL> select * from user_sys_privs;

USERNAME PRIVILEGE          ADMIN_
-------- -------------------- ------
MYTPL     UNLIMITED TABLESPACE NO

SQL>

收回权限成功

有网上帖子说从public中回收select and table 权限，连接数据库的时候会报ORA-06553错。具体查看http://blog.itpub.net/4227/viewspace-68491/