上一篇讲了在linux终端如何检查远程主机策略配置,这一篇继续补充说一下linux终端如何检查远程oracle策略配置。
检查环境和上一篇中描述的一样,接下来我们就进入主题。
我们先看一段我检查的记录,可以从中发现一些小窍门。
[root@byhis01 ~]# su - oracle
oracle@byhis01:~$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 1216:56:34 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba;
ERROR:
ORA-12545: Connect failed because target host or objectdoes not exist
通过以上日志可看出我由root切换到oracle用户下,顺利执行sqlplus,但是执行conn / as sysdba 命令报错。如果对报错信息不熟悉的话,第一反应是数据库关闭了os认证,登陆数据库必须输入数据库的用户名和密码。接下来我们就验证一下是否数据库关闭了os认证。
查看$ORACLE_HOME/network/admin/sqlnet.ora配置如下:
-bash-3.2$ more $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=0
NAMES.DIRECTORY_PATH=(TNSNAMES)
TCP.VALIDNODE_CHECKING = YES
TCP.INVITED_NODES=(192.168.1.43,192.168.1.7,192.168.1.2,192.168.1.3,192.168.1.4)
# TCP.EXCLUDED_NODES= ()
由上可知sqlnet.ora文件中为无SQLNET.AUTHENTICATION_SERVICES,这一与os认证相关的配置,u数据库未关闭OS认证。
既然未关闭OS认证,那为什么登陆失败呢。其实我们通过报错的信息可知连接失败是因为目标主机或者对象不存在。即另外一种原因:oracle并非数据库的安装和启动用户。我们可以通过查看系统的进程来判断。日志如下:
oracle@his01:~$ ps -ef|grep ora_
orasrv 2717 1 0Sep17 ? 00:06:14 ora_pz99_orcl1
orasrv 6243 1 0Sep17 ? 00:13:56 ora_j000_orcl1
oracle 6269 4909 016:57 pts/9 00:00:00 grep ora_
orasrv 7128 1 0Sep04 ? 00:46:14 ora_pmon_orcl1
orasrv 7132 1 0Sep04 ? 00:00:39 ora_diag_orcl1
orasrv 7143 1 0Sep04 ? 00:00:06 ora_psp0_orcl1
orasrv 7149 1 0Sep04 ? 00:20:13 ora_lmon_orcl1
orasrv 7155 1 0Sep04 ? 00:26:36 ora_lmd0_orcl1
orasrv 7157 1 0Sep04 ? 01:10:43 ora_lms0_orcl1
orasrv 7173 1 0Sep04 ? 00:00:13 ora_mman_orcl1
orasrv 7191 1 0Sep04 ? 00:42:22 ora_dbw0_orcl1
orasrv 7195 1 0Sep04 ? 00:41:33 ora_dbw1_orcl1
orasrv 7197 1 0Sep04 ? 00:49:17 ora_lgwr_orcl1
orasrv 7199 1 0Sep04 ? 00:11:13 ora_ckpt_orcl1
orasrv 7201 1 0Sep04 ? 00:04:17 ora_smon_orcl1
orasrv 7203 1 0Sep04 ? 00:00:01 ora_reco_orcl1
orasrv 7206 1 0Sep04 ? 00:11:18 ora_cjq0_orcl1
orasrv 7208 1 0Sep04 ? 00:00:24 ora_mmon_orcl1
通过日志可知:当前正在运行的oracle进行的用户不是oracle,而是orasrv。
其实通过查看系统信息页可以看出一些端倪。
[sysroot@his01 ~]$ more /etc/passwd
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
nfsnobody:x:65534:4294967294:Anonymous NFSUser:/var/lib/nfs:/sbin/nologin
sshd:x:74:74:Privilege-separatedSSH:/var/empty/sshd:/sbin/nologin
sabayon:x:86:86:Sabayon user:/home/sabayon:/sbin/nologin
oracle:x:100:101::/usr/local/oracle:/bin/bash
orasrv:x:116:101:orasrv:/df8003/rdbm/orasrv:/bin/bash
sysroot:x:500:500::/home/sysroot:/bin/bash
由上可知:当前有两个数据库用户 oracle和orasrv。那么当oracle用户下切换有问题时,自然要想到是否与orasrv用户有关。
接下来再说一个小窍门,目前只知道root用户的密码,oracle和orasrv的密码都不知道,在oracle用户下如何切换到orasrv用户下呢,答案是oracle先su到root 输入密码,然后再su到orasrv,因为root到orasrv是高向低用户切换,无需密码。如下所示:
oracle@byhis01:~$ su - root
Password:
[root@byhis01 ~]#su - orasrv
-bash-3.2$
之后就顺利通过os认证,无需sys用户的密码即可以以sysdba的角色登陆数据库。
-bash-3.2$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Oct 1216:58:22 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba;
Connected.
接下来进入主题,说一下linux终端如何检查oracle数据库策略配置。
oraclescript.txt
set echo on;
spool oracle.txt
set linesize 512;
set pagesize 1024;
select * from global_name;
archive log list;
select username,profile from dba_users;
select username,account_status from dba_users;
select * from dba_profiles where profile='DEFAULT';
select name,status from v$controlfile;
select group#,status,member from v$logfile;
select name from v$archived_log;
select name,password from user$;
select tablespace_name,sum(bytes)/1024/1024 fromdba_data_files group by tablespace_name;
select tablespace_name,sum(bytes)/1024/1024 fromdba_free_space group by tablespace_name;
show parameter;
show parameter audit;
show parameter os_auth;
show parameter remote_login_passwordfile;
show parameter 07_DICTIONARY_ACCESSIBILITY;
select granted_role from dba_role_privs wheregrantee='PUBLIC';
select grantee,privilege,admin_option from dba_sys_privs;
select grantee,granted_role,default_role fromdba_role_privs;
select grantee||' '||owner||'.'table_name fromdba_tab_privs where grantee='PUBLIC' and table_name like ' UTL_%';
select grantee||' '||owner||'.'table_name fromdba_tab_privs where grantee='PUBLIC' and table_name like ' DBMS_%';
select username,account_status,default_tablespace,temporary_tablespace,profile fromdba_users order by username;
select profile,resource_name,resource_type,limit fromdba_profiles order by profile;
select tablespace_name,sum(bytes)/1024/1024 as FreeSizefrom dba_free_space group by tablespace_name order by tablespace_name;
select tablespace_name,status,contents,logging fromdba_tablespaces order by tablespace_name;
select status||' '||name from v$controlfile;
select group#,status from v$log;
select group#||' '||status||' '||member from v$logfileorder by group by group#;
select name||' '|| value from v$parameter where name like'%archive%';
select stamp ||' '||name from v$archived_log order bystamp;
select sid||':'||serial#||':'||username||':'||command||':'||status||':'||program fromv$session;
select event||' '||sum(seconds_in_wait) fromv$session_wait group by event order by sum(seconds_in_wait) desc;
select wait_class||' '||sum(total_waits) ||''||sum(time_waited) as timeWaited from v$system_wait_class group by wait_classorder by wait_class;
spool off
此脚本的重点是:
1、第一条命令set echo on 至关重要,以为如果不执行这个命令,那么最后的检查结果中只有命令执行后的结果,无执行的命令,会很混乱。
2、 spool oracle.txt 中oracle.txt文件默认是存在了$ORACLE_HOME的目录下。
3、一定要记得最后要用spool off来终止spool的录屏功能。
然后检查一下一些数据库配置文件
cat$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
cat $ORACLE_HOME/network/admin/sqlnet.ora
cat$ORACLE_HOME/network/admin/listener.ora
最后通过scp拷贝出oracle的alert日志,对oracle的报错信息进行分析即可。
文章中以列出了针对风险评估的oracle检查的命令脚本。希望这个能对大家有用。