Oracle会话有效期利用全局限定性（oracle会话有效期）

Oracle会话有效期：利用全局限定性控制会话时间

在Oracle数据库中，有时候需要限制一个用户或一个应用程序的登录时间，也即是控制一个会话的有效期。这个需求的场景很多，比如：

– 保证数据库安全性。禁止非授权用户或应用程序通过长时间保持会话（sleep状态）来运行非法查询或执行非法事务。

– 节省数据库资源。释放那些已经超时但仍然占用资源的会话，提高数据库性能。

Oracle数据库提供了不少方法来实现对话限制，其中最常见的就是对话控制（Session Control）语句：`ALTER SYSTEM KILL SESSION`。但这个方法是粒度较高的，不能直接限制单个用户或应用程序，也不能控制会话的仅限制登录时间而不是运行时间。

本文介绍一种全局限定性（Global Conditional）方法用于控制会话时间。这种方法通过在会话的初始阶段创建和检查一个全局共享的条件（Condition），用于检查会话是否已经超时，从而超时的会话可以自动注销并释放资源。

注意：本文使用Oracle 11g Release 2作为实例数据库以提供代码示例，但该方法适用于所有版本的Oracle数据库。

实现流程

1. 创建全局条件

创建一个全局条件来表示会话是否已经超过有效期。对于每个会话，我们会在会话创建时自动创建一个对应的条件，该条件定义为一个布尔变量：`g_session_timeout`。该变量的默认值为false，也即表示新的会话是有效的。

CREATE OR REPLACE PACKAGE pkg_session_timeout AS  
    g_session_timeout BOOLEAN DEFAULT FALSE;  
END pkg_session_timeout;

2. 会话创建时检查全局条件

在会话创建时，向会话的上下文中添加一个触发器来检查全局条件。如果当前的会话已经超时，可以通过以下语句强制结束会话：

ALTER SYSTEM KILL SESSION '[sid],[serial#]' IMMEDIATE;

CREATE OR REPLACE TRIGGER trg_session_timeout  
AFTER LOGON ON DATABASE  
DECLARE  
    v_session_id NUMBER := SYS_CONTEXT('userenv', 'sessionid');  
BEGIN  
    IF pkg_session_timeout.g_session_timeout THEN  
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || v_session_id || ',' || SYS_CONTEXT('userenv', 'serial#') || ''' IMMEDIATE';  
    END IF;  
END trg_session_timeout;  
/

3. 定期检查会话是否超时

在一定的时间间隔内，以较低的频率检查全局条件是否已被设置为超时（`g_session_timeout=true`）。如果被设置，标记并释放掉那些已经超时的会话。

DECLARE  
    v_session_id NUMBER;  
    v_serial#    NUMBER;  
BEGIN  
    FOR c IN (SELECT sid, serial# FROM v$session WHERE status = 'INACTIVE' AND seconds_in_wt > 600) LOOP  
        DBMS_OUTPUT.PUT_LINE('Session ' || c.sid || ', serial# ' || c.serial# || ' is inactive for too long, kills it.');  
        v_session_id := c.sid;  
        v_serial#    := c.serial#;  
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || v_session_id || ',' || v_serial# || ''' IMMEDIATE';  
    END LOOP;  
END;

4. 编写crontab定时任务

以root用户打开crontab服务，实现定时任务的自动运行。

su  
crontab -e  
*/5 * * * * /oracle/bin/run_session_timeout.sh > /dev/null 2>&1

解释：每5分钟自动运行一次`run_session_timeout.sh`。

5. 编写run_session_timeout.sh脚本

该脚本运行上文中的检查语句，因此每5分钟，会立即发现已经超时的会话并将其杀死。

#!/bin/bash  
source /oracle/home/user/.bash_profile # 脚本所需的变量  
export ORACLE_SID=database  
export PATH=$ORACLE_HOME/bin:$PATH  
sqlplus -S 
user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521))(CONNECT_DATA=(SID=database)))  
@/oracle/sql/session_timeout.sql  
EOF

实现测试

在实现之前，先做出以下准备：

– 将sys.aud$表放入一个专用的表空间。

– 修改参数auditing_globally，auditing_on和audit_sys_operations设置为TRUE。

接下来模拟延迟12分钟的账号钟漏，为了达到模拟的效果。

### 创建测试用户

CREATE USER test_user IDENTIFIED BY test_user PASSWORD EXPIRE ACCOUNT LOCK;  

### 创建标商模式（Auditing）

CREATE TABLESPACE audts DATAFILE '/u01/app/oracle/oradata/orcl/auddata01.dbf' SIZE 128M  
AUTOEXTEND ON NEXT 128M MAXSIZE 512M;  

CREATE TABLE sys.readlog (username VARCHAR2(30), read_date DATE)  
TABLESPACE audts;  
BEGIN  
    DBMS_AUDIT_MGMT.create_policy(  
        audit_condition  => 'audit_sys_operations = TRUE  
                            AND (event_timestamp BETWEEN TO_DATE(''2021-08-18 01:00:00'', ''YYYY-MM-DD HH24:MI:SS'')  
                            AND TO_DATE(''2021-08-18 02:00:00'', ''YYYY-MM-DD HH24:MI:SS''))',  
        audit_trl      => DBMS_AUDIT_MGMT.AUDIT_TRL_AUD_STD,  
        audit_trl_name => 'my_audit');  
END;  
/

### 设置系统时间

su - root
date -s "20210818 005600"

### 连接到数据库

# 客户机 
ssh user@oracle_client
# oracle_client
. oraenv
# 输入SID并回车
sqlplus "/ as sysdba" 

### 激活测试用户

ALTER USER test_user PASSWORD EXPIRE ACCOUNT UNLOCK;  
conn test_user/test_user

运行以下脚本，创建并编译上文中的全局共享包裹：

CREATE OR REPLACE PACKAGE pkg_session_timeout AS  
    g_session_timeout BOOLEAN DEFAULT FALSE;  
END pkg_session_timeout;  
CREATE OR REPLACE TRIGGER trg_session_timeout  
AFTER LOGON ON DATABASE  
DECLARE  
    v_session_id NUMBER := SYS_CONTEXT('userenv', 'sessionid');  
BEGIN  
    IF pkg_session_timeout.g_session_timeout THEN  
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || v_session_id || ',' || SYS_CONTEXT('userenv', 'serial#') || ''' IMMEDIATE';  
    END IF;  
END trg_session_timeout;  
/
DECLARE  
    v_session_id NUMBER;  
    v_serial#    NUMBER;  
BEGIN  
    FOR c IN (SELECT sid, serial# FROM v$session WHERE status = 'INACTIVE' AND seconds_in_wt > 600) LOOP  
        DBMS_OUTPUT.PUT_LINE('Session ' || c.sid || ', serial# ' || c.serial# || ' is inactive for too long, kills it.');  
        v_session_id := c.sid;  
        v_serial#    := c.serial#;  
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || v_session_id || ',' || v_serial# || ''' IMMEDIATE';  
    END LOOP;  
END; 

完成全局条件和检查语句的编写后，执行以下表达式，将全局共享包裹的检查条件设置为true：

exec pkg_session_timeout.g_session_timeout := true;

通过设置此条件，全局检查程序会立即发现我们的当前会话已经过时并且将其杀死。在以下命令执行完之后，您将无法再运行任何请求：

SELECT * FROM my_audit;

尝试向测试用户授予DELETE权限。在以下命令执行完之后，您将无法获得删除的权限，同时也确保库中只有一个并发的测试用户：

GRANT DELETE ON oracle.test TO test_user;

注意：如果要保存表格“AUD$”上的审计资料，请确保在另一个表格空间中执行Audit Trl。在上面的代码片段中，我将sys.aud$