prevent remote login as sysdba - 禁止SYSDBA远程登录

prevent remote login as sysdba - 禁止SYSDBA远程登录




1.删除密码文件禁止sysdba登录

remote_login_passwordfile参数是EXCLUSIVE
且存在密码文件orapw

SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r-----  1 oracle oinstall 2048 Feb 25 06:46 /home/oracle/app/oracle/product/10.2/dbs/orapwtest

SQL>

删除密码文件

SQL> !mv $ORACLE_HOME/dbs/orapw$ORACLE_SID $ORACLE_HOME/dbs/orapw$ORACLE_SID.old

SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID*
-rw-r-----  1 oracle oinstall 2048 Feb 25 06:46 /home/oracle/app/oracle/product/10.2/dbs/orapwtest.old

SQL>

sysdba,sysoper不能远程登录了

SQL> conn sys/change_on_install@mgt:1541/test as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> conn sys/change_on_install@mgt:1541/test as sysoper
ERROR:
ORA-01031: insufficient privileges


SQL>

可以操作系统认证登录

SQL> conn / as sysdba
Connected.
SQL>

2.用orapwd的nosysdba选项禁止sysdba登录

orapwd重新创建密码文件, 密码不变, 加上nosysdba=y选项

[oracle@MGT ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install entries=5 force=y nosysdba=y
[oracle@MGT ~]$

禁止sysdba远程登录, 但允许本地sysdba登录(操作系统认证)和远程sysoper登录

SQL> conn sys/manager@mgt:1541/test as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn / as sysdba
Connected.
SQL> conn sys/manager@mgt:1541/test as sysoper
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

3.设置remote_login_passwordfile禁止sysdba登录

重建密码文件

[oracle@MGT ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install entries=5 force=y
[oracle@MGT ~]$

可以连接sysdba

SQL> conn sys/change_on_install@mgt:1541/test as sysdba
Connected.
SQL>

设置remote_login_passwordfile=none, 重启数据库

alter system set remote_login_passwordfile=none scope=spfile;

SQL> conn / as sysdba
Connected.
SQL> alter system set remote_login_passwordfile=none scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2084392 bytes
Variable Size             251658712 bytes
Database Buffers          159383552 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.
SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      NONE
SQL>

sysdba连接失败

SQL> conn sys/change_on_install@mgt:1541/test as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn sys/change_on_install@mgt:1541/test as sysoper
ERROR:
ORA-01017: invalid username/password; logon denied


SQL>

恢复设置

SQL> conn / as sysdba
Connected.
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL>

需要重启数据库


4. 修改密码散列值
alter user sys identified by values ... 将sys用户密码散列修改为一个不存在的值

SQL> conn sys/change_on_install@test as sysdba
Connected.
SQL> select username,password from dba_users where username='SYS';

USERNAME   PASSWORD
---------- ------------------------------
SYS        D4C5016086B2DC6A

SQL> alter user sys identified by values '1234567890123456';

User altered.

SQL> conn sys/change_on_install@test as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter user sys identified by values 'D4C5016086B2DC6A';

User altered.

SQL>

外部链接:
Database Administrator Security and Privileges
Disable Remote SYSDBA Connections
ORA-1031 When Connecting Remotely AS SYSDBA
Why Can I Login AS SYSDBA With any Username and Password?
How to Set up the Oracle Password File
SYSDBA and SYSOPER Privileges in Oracle
Problem - REMOTE_LOGIN_PASSWORDFILE Policy Violation when remote_login_password_file Parameter is set to 'EXCLUSIVE'
Remote login as Sysdba to Oracle database server




-fin-