Clemens' Blogs

Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User?

Problem description

In a recent consulting assignment I got the following task:

A server with a specific IP address (Application server or Oracle Client) should be able to connect to a specific DB-user only.

E.g. a single application server responsible for reports should be able to only connect to a specific read-only-user in the database. Other application servers should be allowed to connect to specific other DB-users in the same database. This blog describes the steps on my way to achieve that.

Ideas to solve the issue

It was clear to me that the issue may be resolved using the Oracle Connection Manager. The product Audit Vault/DB Firewall was not an option due to the license costs associated with it. The Oracle Connection Manager can be used without additional license costs if the target database is an Enterprise Edition DB.

Using the Oracle Connection Manager, which acts as a proxy server, it’s possible to define rules that specific servers (ip addresses) are allowed to connect to specific DB-services only. I.e. this would resolve part of my problem that specific application servers get access to specific DBs only. A rule-list to allow access from 19cClient to 19cServer, service pdb1 (in this case pluggable DB pdb1) in the corresponding cman.ora may look as follows.

REMARK: I actually added a second rule that the 19cClient is not allowed to connect to my Non-Container-DB ncdb1 on the same DB-server:

  (rule_list=
    (rule=
       (src=19cClient)(dst=19cServer)(srv=pdb1)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
    (rule=
       (src=19cClient)(dst=19cServer)(srv=ncdb1)(act=reject)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
  )

However the most important part (access to a specific DB-User) is not fulfilled with above setup.

Connection Manager in Traffic Director Mode (TDM)

After reading through this whitepaper, I thought the CMAN in Traffic Director Mode (TDM) could be an option here. So I defined a test environment as follows:

Client1: 192.168.56.13 (19cClient) with Oracle 19c (19.18.)
Client2: 192.168.56.33 (12cr2Client) with Oracle 12cR2
CMAN-Server: 192.168.56.14 (cman) with Oracle Client 21c (21.9.) and CMAN installed
DB-Server: 192.168.10.92 (19c-dg1) with DB version 19c (19.16.). 2 DBs:

  • cdb1 (CDB with 1 PDB: pdb1. Service is also pdb1)
  • ncdb1 (Non-CDB, Service is ncdb1)

REMARK: There are 2 users cbleile1 and cbleile2 on both DBs (pdb1, ncdb1).

Test 1

Rules:

1 – The Client1 19cClient should be allowed to connect to DB cdb1, PDB pdb1, User cbleile1

2 – The Client1 19cClient should NOT be allowed to connect to DB cdb1, PDB pdb1, User cbleile2

3 – The Client1 19cClient should NOT be allowed to connect to DB ncdb1

CMAN-setup:

cman = (configuration=
  (address=(protocol=tcp)(host=cman.localdomain)(port=1999))
  (parameter_list =
    (log_level=ADMIN)
    (max_connections=1024)
    (idle_timeout=0)
    (registration_invited_nodes = *)
    (inbound_connect_timeout=0)
    (session_timeout=0)
    (outbound_connect_timeout=0)
    (max_gateway_processes=16)
    (min_gateway_processes=2)
    (remote_admin=on)
    (trace_level=off)
    (max_cmctl_sessions=4)
    (event_group=init_and_term,memory_ops)
  )
  (rule_list=
    (rule=
       (src=cman)(dst=127.0.0.1)(srv=cmon)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    ) 
    (rule=
       (src=19cClient)(dst=19c-dg1)(srv=pdb1)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
    (rule=
       (src=19cClient)(dst=19c-dg1)(srv=ncdb1)(act=reject)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
  )
)

Client tnsnames.ora:

pdb1_cman=
 (DESCRIPTION =
     (SOURCE_ROUTE = YES)
     (ADDRESS =
        (PROTOCOL = TCP)(HOST = cman)(PORT = 1999)
     )
     (ADDRESS =
        (PROTOCOL = TCP)(HOST = 19c-dg1)(PORT = 1521)
     )
        (CONNECT_DATA = (SERVICE_NAME=pdb1)
     )
 )

ncdb1_cman=
 (DESCRIPTION =
     (SOURCE_ROUTE = YES)
     (ADDRESS =
        (PROTOCOL = TCP)(HOST = cman)(PORT = 1999)
     )
     (ADDRESS =
        (PROTOCOL = TCP)(HOST = 19c-dg1)(PORT = 1521)
     )
        (CONNECT_DATA = (SERVICE_NAME=pdb1)
     )
 )

REMARK: I didn’t setup remote listener registration of my DB to the cman-listener on purpose to make the tests easier.

Results after the initial setup:

sqlplus cbleile1/cbleile1@pdb1_cman
-> works.

sqlplus cbleile2/cbleile2@pdb1_cman
-> works, but shouldn’t.

sqlplus cbleile1/cbleile1@ncdb1_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules

sqlplus cbleile2/cbleile2@ncdb1_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules

I also created a tns-alias with a non-existing service:
sqlplus cbleile2/cbleile2@non_existing_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules

I.e. with above rule-set we can ensure that

The Client 19cClient can only connect to service pdb1. That satisfies rules 1 and 3, but not rule 2.

How to achieve that Client 19cClient can NOT connect to user cbleile2 on PDB pdb1?

Setup CMAN in TDM and a proxy user:

On DB cdb1, PDB pdb1:

CREATE USER TDM1 IDENTIFIED BY tdm1pwd;
GRANT CREATE SESSION TO TDM1;
ALTER USER cbleile1 GRANT CONNECT THROUGH TDM1;

Create a wallet on the cman-server with an entry for proxy user tdm1:

[oracle@cman ~]$ cd $TNS_ADMIN
[oracle@cman admin]$ pwd
/u01/app/oracle/network/admin
[oracle@cman admin]$ mkdir wallet
[oracle@cman admin]$ mkstore -wrl /u01/app/oracle/network/admin/wallet -create
Oracle Secret Store Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved.

Enter password:   
Enter password again:   
[oracle@cman admin]$ mkstore -wrl /u01/app/oracle/network/admin/wallet createCredential pdb1 tdm1 tdm1pwd

Adjusted the cman.ora:

cman = (configuration=
  (address=(protocol=tcp)(host=cman.localdomain)(port=1999))
  (parameter_list =
    (tdm=true)
    (tdm_threading_mode=dedicated)
    (tdm_shared_threads_min=5)
    (tdm_shared_threads_max=20)
    (log_level=ADMIN)
    (max_connections=1024)
    (idle_timeout=0)
    (registration_invited_nodes = *)
    (inbound_connect_timeout=0)
    (session_timeout=0)
    (outbound_connect_timeout=0)
    (max_gateway_processes=16)
    (min_gateway_processes=2)
    (remote_admin=on)
    (trace_level=off)
    (max_cmctl_sessions=4)
    (event_group=init_and_term,memory_ops)
  )
  (rule_list=
    (rule=
       (src=cman)(dst=127.0.0.1)(srv=cmon)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    ) 
    (rule=
       (src=19cClient)(dst=19c-dg1)(srv=pdb1)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
    (rule=
       (src=19cClient)(dst=19c-dg1)(srv=ncdb1)(act=reject)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
  )
)
wallet_location = (source = (method = file) (method_data = (directory="/u01/app/oracle/network/admin/wallet")))
sqlnet.wallet_override = true

Check if I still can connect. Actually the connect did hang:

oracle@19c:/home/oracle/tools/netlat/ [rdbms19ee] sqlplus cbleile1/cbleile1@pdb1_cman

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 14 03:53:01 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

In the /u01/app/oracle/diag/netcman/cman/cman/trace/cman.log I do see this repeatedly:

2023-04-14T04:00:08.297518+02:00
14-APR-2023 04:00:08 * (CONNECT_DATA=(USE_DBROUTER=YES)(SERVICE_NAME=pdb1)(SERVER=DEDICATED)(CID=(PROGRAM=cmop)(HOST=cman.localdomain)(USER=oracle))(CONNECTION_ID=+UMllRd5GXTgUw44qMCj0w==)) * (ADDRESS=(PROTOCOL=tcp)(HOST=19c-dg1)(PORT=60108)) * establish * pdb1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

I had to add an alias pdb1 to the cman-tnsnames.ora:

[oracle@cman admin]$ cat $TNS_ADMIN/tnsnames.ora 
pdb1=
 (DESCRIPTION =
     (ADDRESS =
        (PROTOCOL = TCP)(HOST = 19c-dg1)(PORT = 1521)
     )
        (CONNECT_DATA = (SERVICE_NAME=pdb1)
     )
 )

Then the connection as cbleile1 worked:

oracle@19c:/home/oracle/ [rdbms19ee] sqlplus cbleile1/cbleile1@pdb1_cman

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 14 04:15:37 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Fri Apr 14 2023 13:43:41 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
connected via Oracle Connection Manager in Traffic Director mode 21.9.0.0.0

SQL>

Then I tried it as cbleile2:

oracle@19c:/home/oracle/tools/netlat/ [rdbms19ee] sqlplus cbleile2/cbleile2@pdb1_cman

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 14 04:14:21 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

ERROR:
ORA-28725: Message 28725 not found;  product=RDBMS; facility=ORA


Error accessing package DBMS_APPLICATION_INFO
SQL>

ORA-28725 is an error message, which has been added to Oracle 21c, but is not available on the 19c client. Here the full message on the 21c cman server:

[oracle@cman trace]$ oerr ora 28725
28725, 00000, "invalid CMAN in Traffic Director mode configuration\n"
// *Cause:  An attempt was made to connect to Connection Manager (CMAN) in
//          Traffic Director mode with an invalid configuration.
// *Action: Correct the configuration of CMAN in Traffice Director mode before
//          starting it. Check and correct the wallet credentials and proxy
//          authorization. Check with CMAN in Traffic Director mode
//          administrator to get more information from traces and sqlnet.log.
[oracle@cman trace]$

So that’s expected, because we do not have the proxy user rights granted for cbleile2.

The test results now were as follows:

sqlplus cbleile1/cbleile1@pdb1_cman
-> works.

sqlplus cbleile2/cbleile2@pdb1_cman
-> ORA-28725

sqlplus cbleile1/cbleile1@ncdb1_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules

sqlplus cbleile2/cbleile2@ncdb1_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules

tns-alias with a non-existing service:
sqlplus cbleile2/cbleile2@non_existing_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules

wrong password:
sqlplus cbleile1/cbleile2@pdb1_cman
ORA-01017: invalid username/password; logon denied

So everything works as expected from one server. The important step is to only alter user cbleile1 to grant connect through TDM1, but not cbleile2.

Test 2

Rules:

1 – The Client1 19cClient should be allowed to connect to DB cdb1, PDB pdb1, User cbleile1

2 – The Client1 19cClient should not be allowed to connect to DB cdb1, PDB pdb1, User cbleile2

3 – The Client1 19cClient should NOT be allowed to connect to DB ncdb1

4 – The Client2 12cR2Client should be allowed to connect to DB cdb1, PDB pdb1, User cbleile2

5 – The Client2 12cR2Client should NOT be allowed to connect to DB cdb1, PDB pdb1, User cbleile1

I.e. the first 3 rules are the same as in Test 1, but I added the rules 4 and 5 to have another client to be allowed to connect to user cbleile2 only (and not to cbleile1). I.e. the 19cClient should only be allowed to connect to cbleile1 and the 12cR2Client should only be allowed to connect to cbleile2.

First I’m creating a new TDM2-User on PDB pdb1 with proxy-rights to become cbleile2:

CREATE USER TDM2 IDENTIFIED BY tdm2pwd;
GRANT CREATE SESSION TO TDM2;
ALTER USER cbleile2 GRANT CONNECT THROUGH TDM2;

Then I add the alias pdb1_2 to the wallet on the cman server:

[oracle@cman admin]$ mkstore -wrl /u01/app/oracle/network/admin/wallet -createCredential pdb1_2 tdm2 tdm2pwd

And add an entry in the tnsnames.ora on the cman server:

pdb1_2=
 (DESCRIPTION =
     (ADDRESS =
        (PROTOCOL = TCP)(HOST = DB-dg1)(PORT = 1521)
     )
        (CONNECT_DATA = (SERVICE_NAME=pdb1)
     )
 )

In addition add the rules to the $TNS_ADMIN/cman.ora:

cman = (configuration=
  (address=(protocol=tcp)(host=cman.localdomain)(port=1999))
  (parameter_list =
    (tdm=true)
    (tdm_threading_mode=dedicated)
    (tdm_shared_threads_min=5)
    (tdm_shared_threads_max=20)
    (log_level=ADMIN)
    (max_connections=1024)
    (idle_timeout=0)
    (registration_invited_nodes = *)
    (inbound_connect_timeout=0)
    (session_timeout=0)
    (outbound_connect_timeout=0)
    (max_gateway_processes=16)
    (min_gateway_processes=2)
    (remote_admin=on)
    (trace_level=off)
    (max_cmctl_sessions=4)
    (event_group=init_and_term,memory_ops)
  )
  (rule_list=
    (rule=
       (src=cman)(dst=127.0.0.1)(srv=cmon)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    ) 
    (rule=
       (src=19cClient)(dst=19c-dg1)(srv=pdb1)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
    (rule=
       (src=19cClient)(dst=19c-dg1)(srv=ncdb1)(act=reject)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
    (rule=
       (src=12cr2Client)(dst=19c-dg1)(srv=pdb1_2)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
    (rule=
       (src=12cr2Client)(dst=19c-dg1)(srv=ncdb1)(act=reject)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
  )
)
wallet_location = (source = (method = file) (method_data = (directory="/u01/app/oracle/network/admin/wallet")))
sqlnet.wallet_override = true

On the client I do add a tnsnames-entry with service pdb1_2:

pdb1_cman=
 (DESCRIPTION =
     (SOURCE_ROUTE = YES)
     (ADDRESS =
        (PROTOCOL = TCP)(HOST = cman)(PORT = 1999)
     )
     (ADDRESS =
        (PROTOCOL = TCP)(HOST = 19c-dg1)(PORT = 1521)
     )
        (CONNECT_DATA = (SERVICE_NAME=pdb1_2)
     )
 )

Test a connection to cbleile1, which should not be allowed:

oracle@12cr2:/home/oracle/ [prem122] sqlplus cbleile1/cbleile1@pdb1_cman

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 14 14:28:38 2023

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-28725: Message 28725 not found;  product=RDBMS; facility=ORA



ERROR:
ORA-28725: Message 28725 not found;  product=RDBMS; facility=ORA



Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
ERROR:
ORA-28725: Message 28725 not found;  product=RDBMS; facility=ORA



Error accessing package DBMS_APPLICATION_INFO
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
SQL>

Again, the error is ORA-28725 is not available on release 12cR2, that’s why no messages were found for the error. Anyway, the result is good, because we cannot connect from the second client to our DB user cbleile1. I.e. that’s what I wanted. Can I connect to cbleile2?

oracle@12cr2:/home/oracle/ [prem122] sqlplus cbleile2/cbleile2@pdb1_cman

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 14 14:29:19 2023

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Fri Apr 14 2023 14:21:45 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
connected via Oracle Connection Manager in Traffic Director mode 21.9.0.0.0

SQL>

Yes, that worked – as intended.

Summary of the tests

From 19cClient (Client1):

sqlplus cbleile1/cbleile1@pdb1_cman
-> works. OK.

sqlplus cbleile2/cbleile2@pdb1_cman
-> ORA-28725. OK.

sqlplus cbleile1/cbleile1@ncdb1_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules
-> OK.

sqlplus cbleile2/cbleile2@ncdb1_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules
-> OK.

tns-alias with a non-existing service:
sqlplus cbleile2/cbleile2@non_existing_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules
-> OK.

wrong password:
sqlplus cbleile1/cbleile2@pdb1_cman
ORA-01017: invalid username/password; logon denied
–> OK.

From 12cr2Client (Client2):

sqlplus cbleile1/cbleile1@pdb1_cman
-> ORA-28725. OK.

sqlplus cbleile2/cbleile2@pdb1_cman
-> works. OK.

sqlplus cbleile1/cbleile1@ncdb1_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules
-> OK.

sqlplus cbleile2/cbleile2@ncdb1_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules
-> OK.

tns-alias with a non-existing service:
sqlplus cbleile2/cbleile2@non_existing_cman
-> rejected: ORA-12529: TNS:connect request rejected based on current filtering rules
-> OK.

wrong password:
sqlplus cbleile2/cbleile1@pdb1_cman
ORA-01017: invalid username/password; logon denied
–> OK.

Summary

The tests showed that setting up rules to allow specific clients (IP addresses) to connect only to specific DBs and specific users inside that DB is possible. The technology used is the Oracle Connection Manager in Traffic Director Mode (TDM). TDM is available from Oracle 18c onwards, but I do recommend to install the latest version 21c (as of the time when writing this blog).

web24522998

, , , ,

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert