3. Managing SSL Connections

Managing SSL Connections

This section describes how to enhance security using the SSL exclusive connection feature, and monitor or manage SSL connections.

• Limit TCP Connections

• Monitor and Manage SSL Connections

Limit TCP Connections

The main reason to use SSL is to guarantee secure communication between the client and the server. In SSL-enabled Altibase, remote users are, by default, allowed to access the database over SSL, as well as TCP. Tightened security may require stricter access control of each database user.

The SYS user can control each user's access to the database only via SSL connection by disabling TCP connection as follows.

• User's TCP connection limit

  ALTER USER user_name [ENABLE | DISABLE] TCP
  CREATE USER user_name IDENTIFIED BY password [ENABLE | DISABLE] TCP
  

  Permission of TCP connection for database users can be checked by issuing the SELECT query on the DISABLE_TCP column in the SYSTEM_.SYS_USERS_ meta table.

  SELECT * FROM SYSTEM_.SYS_USERS_;
  

Examples

<Query> Create a user who is not allowed to connect via TCP.

iSQL> CREATE USER user1 IDENTIFIED BY user1 DISABLE TCP;
Create success.

<Query> Verify that the user is not allowed to connect via TCP.

iSQL> SELECT user_name, disable_tcp FROM SYSTEM_.SYS_USERS_;
USER_NAME                                 DISABLE_TCP
----------------------------------------------------------
SYSTEM_                                   F
SYS                                       F
USER1                                     T
3 rows selected.

<Query> After changing so that an unauthorized user can connect to TCP, check the meta table to see if the access rights have been changed.

iSQL> ALTER USER user1 ENABLE TCP;
Alter success.
iSQL> SELECT user_name, disable_tcp FROM SYSTEM_.SYS_USERS_;
USER_NAME                                 DISABLE_TCP
----------------------------------------------------------
SYSTEM_                                   F
SYS                                       F
USER1                                     F
3 rows selected.

Monitor and Manage SSL Connections

Monitoring active database connections is important to detect illegal access to the database.

The V$SESSION performance view provides detailed information on database connections such as the connected client, connection properties, and so on. In particular, the COMM_NAME column describes the type of protocol, the client's address, and connected port number. For more detailed information about performance views, please refer to the General Reference.

SELECT ID, DB_USERNAME, COMM_NAME FROM V$SESSION WHERE COMM_NAME LIKE 'SSL%';

ID          DB_USERNAME        COMM_NAME
-------------------------------------------------------------
1           USER1              SSL 127.0.0.1:40328
1 row selected.

Once illegal access to the database is detected, the database administrator may need to forcefully terminate it. This can be done in the following steps.

• Log into the database in iSQL as the SYS user in SYSDBA mode and issue a query to disconnect the target session in iSQL.

  $ isql -s localhost -u user_name -p password -SYSDBA
  

  ALTER DATABASE database_name SESSION CLOSE session_number;
  

• 세션을 종료한 후에는 모니터링하여 대상 세션이 종료되었는지 확인한다.

  SELECT * FROM V$SESSION WHERE ID = session_id;
  

SYSDBA is a special privilege for the SYS user to perform administrative jobs. Logging into the database with the SYS account in SYSDBA mode can be done in iSQL by inputting the -SYSDBA option when starting iSQL or using the CONNECT command with the AS SYSDBA option.

$ isql -s localhost -u sys -p manager -sysdba
-----------------------------------------------------------------
     Altibase Client Query utility.
     Release Version 7.3.0.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-----------------------------------------------------------------
ISQL_CONNECTION = UNIX, SERVER = localhost
iSQL(sysdba)> SELECT ID, DB_USERNAME, COMM_NAME FROM V$SESSION WHERE COMM_NAME LIKE 'SSL%';
ID          DB_USERNAME        COMM_NAME
-------------------------------------------------------------
1           USER1              SSL 127.0.0.1:40328
1 row selected.

iSQL(sysdba)> ALTER DATABASE mydb SESSION CLOSE 1;
Alter success.

iSQL(sysdba)> SELECT ID, DB_USERNAME, COMM_NAME FROM V$SESSION WHERE COMM_NAME LIKE 'SSL%';
ID          DB_USERNAME        COMM_NAME
-------------------------------------------------------------
No rows selected.