备案控制台
探索云世界
开发者社区 数据库 文章 正文

[20160513]Restrict Session与静态监听.txt

2016-05-17 842
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [20160513]Restrict Session与静态监听.txt --给同事讲解动静态监听时,一次测试,当执行后alter system enable restricted session;发现远程用户依旧可以登录,检查发现是静态 --监听注册搞的鬼,做一个记录.

[20160513]Restrict Session与静态监听.txt

--给同事讲解动静态监听时,一次测试,当执行后alter system enable restricted session;发现远程用户依旧可以登录,检查发现是静态
--监听注册搞的鬼,做一个记录.并且把各种情况做一个总结:

1.环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.检查监听状态:
--没有静态监听注册.
$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

#SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
DIAG_ADR_ENABLED_LISTENER=OFF

3.首先分析启动数据库的各个阶段:
--//关闭数据库.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
--可以发现没有服务.

--启动到nomount阶段:
SYS@book> startup nomount
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "book" has 1 instance(s).
  Instance "book", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

--注意这个时候监听状态BLOCKED.
--这个时候远程client端是无法连接数据库,要想通过连接要加入UR=A的内容如下,并且只能以sys用户登录.

78 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
    )
    (SDU = 32768)
    (CONNECT_DATA =
        (UR = A)
        ~~~~~~~
      (SERVICE_NAME = book)
  )
)

--//启动到mount阶段:
SYS@book> alter database mount ;
Database altered.

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "book" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully

--如果不出现状态READY,可能要等1小会,或者执行alter system register;,这个时候可以实现远程登录使用sys用户.
--另外仅仅存在一个服务.所以仅仅看到一项.
SYS@book> show parameter service
NAME          TYPE    VALUE
------------- ------- ----------
service_names string  book

--//启动到open阶段:
SYS@book> alter database open ;
Database altered.

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=8888))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=7777))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "book" has 1 instance(s).
  Instance "book", status READY, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully

--到open阶段,可以发现还启动了bookXDB服务,以及打开了HTTP以及ftp端口.

4.执行alter system enable restricted session;后.
--//执行alter system enable restricted session后,可以禁止远程用户登录,但不影响本地用户登录.但是实际情况呢?

SYS@book> alter system enable restricted session;
System altered.

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=8888))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=7777))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "book" has 1 instance(s).
  Instance "book", status RESTRICTED, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully

--//可以发现状态变成了RESTRICTED.远程连接出现如下错误不管sys用户还是scott用户.

ORA-12526: TNS:listener: all appropriate instances are in restricted mode

--//但是如果tnsnames.ora配置加入UR=A,完全不受任何影响,照样可以登录.感觉UR=A就像打开了一个后门.
--//取消restricted.

SYS@book> alter system disable restricted session;
System altered.

5.加入静态监听注册呢?
$ lsnrctl stop
..

$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

#SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = book)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
      (SID_NAME = book)
      )
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
DIAG_ADR_ENABLED_LISTENER=OFF

$ lsnrctl start
..

SYS@book> alter system register;
System altered.

$ lsnrctl status
...

Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=8888))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=7777))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Instance "book", status READY, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully

--注意看~,启动状态是UNKNOWN,这个就是静态监听注册,因为这个即使数据库没有启动也存在,因为这个实例是否存在未知,所以oracle标
--识状态为UNKNOWN.

SYS@book> alter system enable restricted session;
System altered.

$ lsnrctl status
....
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=8888))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=7777))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status RESTRICTED, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully

--可以发现动态监听状态RESTRICTED.但是由于静态监听存在,远程用户依旧可以登录,无需加入UR=A.
--远程client使用sqlplus连上后观察:

$ lsnrctl service
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-MAY-2016 08:23:31
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521)))
Services Summary...
Service "book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         LOCAL SERVER
  Instance "book", status RESTRICTED, has 2 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: gxqyydg4, pid: 53314>
         (ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=21060))
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "bookXDB" has 1 instance(s).
  Instance "book", status RESTRICTED, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: gxqyydg4, pid: 53314>
         (ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=21060))
The command completed successfully

--注意看~,可以发现是通过静态监听连上数据库的.

SYS@book> alter system disable restricted session;
System altered.

6.做一个总结:
-- 如果tnsnames.ora,加入(UR=A),就好像打开了一个后门,
-- 配置静态监听注册, alter system enable restricted session;对于远程用户无效.
-- 最后在补充一点,不启动监听实际上也可以远程连接数据库,参考链接:http://blog.itpub.net/267265/viewspace-1816211/

文章标签：
关系型数据库
Oracle
网络协议
数据库
Linux
lfreeali
目录
相关文章
jianz123
|
18天前
.user.ini 作用和配置
.user.ini 作用和配置
jianz123
10 0
jerrywangsap
|
1月前
|
存储 数据库
如何使用代码修改 attachment 实例的 CHANGED_BY 字段
如何使用代码修改 attachment 实例的 CHANGED_BY 字段
jerrywangsap
10 0
qasxfte752dni
|
小程序
小程序--event对象
小程序--event对象
qasxfte752dni
393 0
小程序--event对象
杰克.陈
|
数据库
队列读取器代理 遇到错误 Row handle is invalid
原文:队列读取器代理 遇到错误 Row handle is invalid 今天测试在发布中更改表名称，在发布数据库更改后重新发布这个表。 但是原来的表在订阅没有删除，不小心插入数据到原表中，队列读取器停止并报错。
杰克.陈
1146 0
技术mix呢
SET XACT_ABORT各种用法及显示结果
技术mix呢
1421 0
像教授
quotacheck命令不能创建aquota.user和aquota.group文件解决方法 原
像教授
2296 0
科技探索者
session的创建方式
科技探索者
1072 0
技术小甜
connect 和 create session权限有和区别？
技术小甜
1907 0
技术小甜
|
PHP
php session_set_save_handler _sess_read 无法赋值问题
技术小甜
1207 0
橘子红了呐
|
容器
HttpContext.Current.Session ，出现未将对象引用设置到实例上
橘子红了呐
1548 0

热门文章

最新文章

  • 1
    Linux查看进程的内存占用情况
  • 2
    大咖云集，技术宅开趴倒计时 —— 2017 Kubernetes Meetup | 成都站
  • 3
    ubuntu安装KVM虚拟机管理virt-manager
  • 4
    OpenCV4之C++入门详解 （上）
  • 5
    vue2中computed中无法获取到this
  • 6
    Visual Basic快速入门
  • 7
    HDU 4968 Improving the GPA
  • 8
    这56家公司共同发力智慧城市
  • 9
    书写高质量JavaScript代码的要义（The Essentials of Writing High Quality JavaScript）翻译
  • 10
    linux驱动开发--字符设备：信号量
  • 1
    【软件工程】融通未来的工艺：深度解析统一过程在软件开发中的角色
    14
  • 2
    IBM SPSS Modeler分类决策树C5.0模型分析空气污染物数据
    13
  • 3
    【软件工程】走进瀑布模型：传统软件开发的经典之路
    13
  • 4
    数据分享|R语言用lme4多层次（混合效应）广义线性模型（GLM），逻辑回归分析教育留级调查数据（下）
    11
  • 5
    【软件工程】走近演化过程模型：软件开发的不断进化之路
    86
  • 6
    【Mybatis】深入学习MyBatis：概述、主要特性以及配置与映射
    15
  • 7
    【MySQL】数据库规范化的三大法则 — 一探范式设计原则
    15
  • 8
    r语言中对LASSO回归，Ridge岭回归和弹性网络Elastic Net模型实现（下）
    12
  • 9
    【MySQL】数据库中为什么使用B+树不用B树
    8
  • 10
    【MySQL】SQL优化
    10

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）