ITPUB網友問題：

http://www.itpub.net/thread-1941574-1-1.html

使用如下命令新建立一個使用者，怎麼限制這個使用者不能檢視 all_users這張表？
-- Create the user
create user TEST1
default tablespace USERS
temporary tablespace TEMP;
-- Grant/Revoke role privileges
grant connect to TEST1;

結論

1,直接以syssdba執行如下命令
revoke select on all_users from public;

從public回收對於select on all_users的許可權即可

2,分析思路：
A,獲取all_users的底層DDL基表定義，嘗試找到線索，未果
B，查詢dba_objects找到all_users隸屬何種物件，嘗試找到線索，未果
C，以all_users為關鍵詞，在dba_tab_privs找到授予此表以及接受此表許可權的資訊

SQL> select grantor,grantee,table_schema,table_name from all_tab_privs where table_name='ALL_USERS';

GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME
-------------------- -------------------- -------------------- ------------------------------------------------------------
SYS PUBLIC SYS ALL_USERS
SYS APEX_030200 SYS ALL_USERS

D, 基於C的情況，回收許可權

SQL> revoke select on all_users from public;

Revoke succeeded.

E，驗證此回收許可權不會對於其它資料庫使用者造成影響
F，要理解PUBLIC這個角色的含義

測試：

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> create user test1 default tablespace users temporary tablespace temp identified by system;

User created.

SQL> grant connect to test1;

Grant succeeded.

SQL> conn test1/system
Connected.

SQL> select count(*) from all_users;

COUNT(*)
----------
31

SQL> set long 99999999
SQL> set pagesize 300
SQL> select view_name,text from dba_views where view_name='ALL_USERS';

VIEW_NAME
------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
ALL_USERS
select u.name, u.user#, u.ctime
from sys.user$ u, sys.ts$ dts, sys.ts$ tts
where u.datats# = dts.ts#
and u.tempts# = tts.ts#
and u.type# = 1

SQL> show user
USER is "SYS"

SQL> select distinct type# from user$ order by 1;

TYPE#
----------
0
1

可見type#=0是資料庫的角色
SQL> select user#,name,type# from user$ where name not in (select username from dba_users) order by 1;

USER# NAME TYPE#
---------- ------------------------------------------------------------ ----------
1 PUBLIC 0
2 CONNECT 0
3 RESOURCE 0
4 DBA 0
6 SELECT_CATALOG_ROLE 0
7 EXECUTE_CATALOG_ROLE 0
8 DELETE_CATALOG_ROLE 0
10 EXP_FULL_DATABASE 0
11 IMP_FULL_DATABASE 0
12 LOGSTDBY_ADMINISTRATOR 0
13 DBFS_ROLE 0
15 AQ_ADMINISTRATOR_ROLE 0
16 AQ_USER_ROLE 0
17 DATAPUMP_EXP_FULL_DATABASE 0
18 DATAPUMP_IMP_FULL_DATABASE 0
19 ADM_PARALLEL_EXECUTE_TASK 0
20 GATHER_SYSTEM_STATISTICS 0
22 RECOVERY_CATALOG_OWNER 0
23 SCHEDULER_ADMIN 0
24 HS_ADMIN_SELECT_ROLE 0
25 HS_ADMIN_EXECUTE_ROLE 0
26 HS_ADMIN_ROLE 0
27 GLOBAL_AQ_USER_ROLE 0
28 OEM_ADVISOR 0
29 OEM_MONITOR 0
33 WM_ADMIN_ROLE 0
34 JAVAUSERPRIV 0
35 JAVAIDPRIV 0
36 JAVASYSPRIV 0
37 JAVADEBUGPRIV 0
38 EJBCLIENT 0
39 JMXSERVER 0
40 JAVA_ADMIN 0
41 JAVA_DEPLOY 0
44 CTXAPP 0
47 XDBADMIN 0
48 XDB_SET_INVOKER 0
49 AUTHENTICATEDUSER 0
50 XDB_WEBSERVICES 0
51 XDB_WEBSERVICES_WITH_PUBLIC 0
52 XDB_WEBSERVICES_OVER_HTTP 0
58 ORDADMIN 0
59 OLAPI_TRACE_USER 0
60 OLAP_XS_ADMIN 0
62 OLAP_DBA 0
63 CWM_USER 0
64 OLAP_USER 0
66 SPATIAL_WFS_ADMIN 0
68 WFS_USR_ROLE 0
69 SPATIAL_CSW_ADMIN 0
71 CSW_USR_ROLE 0
73 MGMT_USER 0
77 APEX_ADMINISTRATOR_ROLE 0
80 OWB$CLIENT 0
81 OWB_DESIGNCENTER_VIEW 0
82 OWB_USER 0
88 _NEXT_USER 0

57 rows selected.

SQL>

SQL> select user_id,username from all_users order by 1;

USER_ID USERNAME
---------- ------------------------------------------------------------
0 SYS
5 SYSTEM
9 OUTLN
14 DIP
21 ORACLE_OCM
30 DBSNMP
31 APPQOSSYS
32 WMSYS
42 EXFSYS
43 CTXSYS
45 XDB
46 ANONYMOUS
53 ORDSYS
54 ORDDATA
55 ORDPLUGINS
56 SI_INFORMTN_SCHEMA
57 MDSYS
61 OLAPSYS
65 MDDATA
67 SPATIAL_WFS_ADMIN_USR
70 SPATIAL_CSW_ADMIN_USR
72 SYSMAN
74 MGMT_VIEW
75 FLOWS_FILES
76 APEX_PUBLIC_USER
78 APEX_030200
79 OWBSYS
83 OWBSYS_AUDIT
84 SCOTT
87 TEST1
2147483638 XS$NULL

31 rows selected.

SQL> select object_name,object_type from dba_objects where object_name='ALL_USERS';

OBJECT_NAME OBJECT_TYPE
-------------------------------------------------- --------------------------------------
ALL_USERS VIEW
ALL_USERS SYNONYM

SQL> select grantor,grantee,table_schema,table_name from all_tab_privs where table_name='ALL_USERS';

GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME
-------------------- -------------------- -------------------- ------------------------------------------------------------
SYS PUBLIC SYS ALL_USERS
SYS APEX_030200 SYS ALL_USERS

SQL> revoke select on all_users from public;

Revoke succeeded.

SQL> conn test1/system
Connected.
SQL> select count(*) from all_users;
select count(*) from all_users
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select count(*) from user_users;

COUNT(*)
----------
1

SQL> select count(*) from all_tables;

COUNT(*)
----------
99

SQL> select count(*) from user_jobs;

COUNT(*)
----------
0

SQL> conn scott/system
Connected.
SQL> select count(*) from all_users;

COUNT(*)
----------
31

itpub網友問題--如何讓某資料庫使用者不能查詢all_users

ITPUB網友問題：

結論

測試：

相關文章