Question

如何区分oracle自定义用户和默认用户的

Answer 1

在ORACLE11G下默认开启了审计,可通过审计功能来区分是否是自定义用户，下面为代码示例
SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
audit_file_dest string D:\ORACLE\ADMIN\TEST\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB

SQL> select user_name,proxy_name,privilege,success,failure from dba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
---------- ---------- ---------------------------------------- ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
---------- ---------- ---------------------------------------- ---------- ----------
ALTER DATABASE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
---------- ---------- ---------------------------------------- ---------- ----------
ALTER SYSTEM BY ACCESS BY ACCESS

已选择23行。

看到了会自动审计CREATE USER,想到了在ORACLE下通过DBA_USERS好像没有办法判断哪个用户是系统自带的，哪个用户是用户自己创建的，应该可以结合着审计来判断。来做个试验来看看。

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 2月 4 14:43:16 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and Real Application Testing options

SQL> SET LINESIZE 200
SQL> select * from audit_actions where name='CREATE USER';

ACTION NAME
---------- ----------------------------
51 CREATE USER

SQL> select * from dba_audit_trail WHERE ACTION=51;

未选定行

SQL> COL USER_NAME FOR A5
SQL> COL PROXY_NAME FOR A5
SQL> select * from dba_priv_audit_opts where privilege='CREATE USER';

USER_ PROXY PRIVILEGE SUCCESS FAILURE
----- ----- ---------------------------------------- ---------- ----------
CREATE USER BY ACCESS BY ACCESS

SQL> CREATE USER A IDENTIFIED BY A;--创建用户看看，是否产生了审计结果

用户已创建。

SQL> create user b identified by b default tablespace users;

用户已创建。

SQL> select * from dba_audit_trail WHERE ACTION=51;--发现没有结果，刚开始觉得奇怪，后来想到连接的是SYS用户，而ORACLE现在默认对SYS用户的行为不审计，即便开启了审计，也不放在该视图里。

未选定行

SQL> grant dba to a;

授权成功。

SQL> exit

C:\Documents and Settings\Administrator>sqlplus a/a--此处验证了在11G下用户名口令严格区分大小写

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 2月 4 15:15:51 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝

请输入用户名:
C:\Documents and Settings\Administrator>sqlplus A/A

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 2月 4 15:16:04 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and Real Application Testing options

SQL> CREATE USER c identified by c;--用A用户创建了个USER，再来看看审计结果

用户已创建。

SQL> select count(*) from dba_audit_trail WHERE ACTION=51;--已经产生了一条审计记录

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

可以通过结合审计，是可以判断哪些用户是系统默认的，哪些用户是用户创建的。

Answer 2

你是说默认的用户名和密码么
scott 密码是 tiger
sys 密码是 change_on_install
system 密码是 manager
sysman 密码是 oem_temp

自察穗定义用败神卜户即你自己创建的用户，名字都是你自瞎中己取得。

追问

oracle不可能才这么几个用户吧！！！ 我说的是在我们不知道的情况下如何查出来呀！ 是否可以用那个数据字典可看到！！！

追答

oracle默认的用户就这几个，其他的都是未解锁的，你可以通过一下语句查询；
select * from dba_user;
select * from all_users;
select * from user_users;

Answer 3

具体用那个视模哗图不知道，但是你可以用这个大概就旦顷行可以找出来

select * from all_users;
可以根据用乎雹户添加时间来确认。

Answer 4

查看一下数据库的属性

追问

亲 可以说的详细点吗？ 我刚接触oracle 谢谢啦