{"id":24630,"date":"2023-04-18T12:30:30","date_gmt":"2023-04-18T10:30:30","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=24630"},"modified":"2024-01-30T00:14:01","modified_gmt":"2024-01-29T23:14:01","slug":"oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/","title":{"rendered":"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User?"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\" id=\"h-problem-description\">Problem description<\/h3>\n\n\n\n<p>In a recent consulting assignment I got the following task: <\/p>\n\n\n\n<p>A server with a specific IP address (Application server or Oracle Client) should be able to connect to a specific DB-user only.<\/p>\n\n\n\n<p>E.g. a single application server responsible for reports should be able to <em>only<\/em> 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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-ideas-to-solve-the-issue\">Ideas to solve the issue<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>Using the Oracle Connection Manager, which acts as a proxy server, it&#8217;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. <\/p>\n\n\n\n<p>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:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>  (rule_list=\n    (rule=\n       (src=19cClient)(dst=19cServer)(srv=pdb1)(act=accept)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n    (rule=\n       (src=19cClient)(dst=19cServer)(srv=ncdb1)(act=reject)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n  )<\/code><\/pre>\n\n\n\n<p>However the most important part (access to a specific DB-User) is not fulfilled with above setup.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-connection-manager-in-traffic-director-mode-tdm\">Connection Manager in Traffic Director Mode (TDM)<\/h3>\n\n\n\n<p>After reading through this <a href=\"https:\/\/download.oracle.com\/ocomdocs\/global\/CMAN_TDM_Oracle_DB_Connection_Proxy_for_scalable_apps.pdf\">whitepaper<\/a>, I thought the CMAN in Traffic Director Mode (TDM) could be an option here. So I defined a test environment as follows:<\/p>\n\n\n\n<p>Client1: 192.168.56.13 (19cClient) with Oracle 19c (19.18.)<br>Client2: 192.168.56.33 (12cr2Client) with Oracle 12cR2<br>CMAN-Server: 192.168.56.14 (cman) with Oracle Client 21c (21.9.) and CMAN installed<br>DB-Server: 192.168.10.92 (19c-dg1) with DB version 19c (19.16.). 2 DBs: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>cdb1 (CDB with 1 PDB: pdb1. Service is also pdb1)<\/li>\n\n\n\n<li>ncdb1 (Non-CDB, Service is ncdb1)<\/li>\n<\/ul>\n\n\n\n<p>REMARK: There are 2 users cbleile1 and cbleile2 on both DBs (pdb1, ncdb1).<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-test-1\">Test 1<\/h3>\n\n\n\n<p>Rules:<\/p>\n\n\n\n<p>1 &#8211; The Client1 19cClient should be allowed to connect to DB cdb1, PDB pdb1, User cbleile1<\/p>\n\n\n\n<p>2 &#8211; The Client1 19cClient should NOT be allowed to connect to DB cdb1, PDB pdb1, User cbleile2<\/p>\n\n\n\n<p>3 &#8211; The Client1 19cClient should NOT be allowed to connect to DB ncdb1<\/p>\n\n\n\n<p>CMAN-setup:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>cman = (configuration=\n  (address=(protocol=tcp)(host=cman.localdomain)(port=1999))\n  (parameter_list =\n    (log_level=ADMIN)\n    (max_connections=1024)\n    (idle_timeout=0)\n    (registration_invited_nodes = *)\n    (inbound_connect_timeout=0)\n    (session_timeout=0)\n    (outbound_connect_timeout=0)\n    (max_gateway_processes=16)\n    (min_gateway_processes=2)\n    (remote_admin=on)\n    (trace_level=off)\n    (max_cmctl_sessions=4)\n    (event_group=init_and_term,memory_ops)\n  )\n  (rule_list=\n    (rule=\n       (src=cman)(dst=127.0.0.1)(srv=cmon)(act=accept)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    ) \n    (rule=\n       (src=19cClient)(dst=19c-dg1)(srv=pdb1)(act=accept)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n    (rule=\n       (src=19cClient)(dst=19c-dg1)(srv=ncdb1)(act=reject)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n  )\n)<\/code><\/pre>\n\n\n\n<p>Client tnsnames.ora:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>pdb1_cman=\n (DESCRIPTION =\n     (SOURCE_ROUTE = YES)\n     (ADDRESS =\n        (PROTOCOL = TCP)(HOST = cman)(PORT = 1999)\n     )\n     (ADDRESS =\n        (PROTOCOL = TCP)(HOST = 19c-dg1)(PORT = 1521)\n     )\n        (CONNECT_DATA = (SERVICE_NAME=pdb1)\n     )\n )\n\nncdb1_cman=\n (DESCRIPTION =\n     (SOURCE_ROUTE = YES)\n     (ADDRESS =\n        (PROTOCOL = TCP)(HOST = cman)(PORT = 1999)\n     )\n     (ADDRESS =\n        (PROTOCOL = TCP)(HOST = 19c-dg1)(PORT = 1521)\n     )\n        (CONNECT_DATA = (SERVICE_NAME=ncdb1)\n     )\n )<\/code><\/pre>\n\n\n\n<p>REMARK: I didn&#8217;t setup remote listener registration of my DB to the cman-listener on purpose to make the tests easier.<\/p>\n\n\n\n<p><strong>Results after the initial setup:<\/strong><\/p>\n\n\n\n<p>sqlplus cbleile1\/cbleile1@pdb1_cman<br>-&gt; works.<\/p>\n\n\n\n<p>sqlplus cbleile2\/cbleile2@pdb1_cman<br>-&gt; works, but shouldn&#8217;t.<\/p>\n\n\n\n<p>sqlplus cbleile1\/cbleile1@ncdb1_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<\/p>\n\n\n\n<p>sqlplus cbleile2\/cbleile2@ncdb1_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<\/p>\n\n\n\n<p>I also created a tns-alias with a non-existing service:<br>sqlplus cbleile2\/cbleile2@non_existing_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<\/p>\n\n\n\n<p>I.e. with above rule-set we can ensure that<\/p>\n\n\n\n<p>The Client 19cClient can only connect to service pdb1. That satisfies rules 1 and 3, but not rule 2.<\/p>\n\n\n\n<p>How to achieve that Client 19cClient can NOT connect to user cbleile2 on PDB pdb1?<\/p>\n\n\n\n<p>Setup CMAN in TDM and a proxy user:<\/p>\n\n\n\n<p>On DB cdb1, PDB pdb1:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER TDM1 IDENTIFIED BY tdm1pwd;\nGRANT CREATE SESSION TO TDM1;\nALTER USER cbleile1 GRANT CONNECT THROUGH TDM1;<\/code><\/pre>\n\n\n\n<p>Create a wallet on the cman-server with an entry for proxy user tdm1:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#091;oracle@cman ~]$ cd $TNS_ADMIN\n&#091;oracle@cman admin]$ pwd\n\/u01\/app\/oracle\/network\/admin\n&#091;oracle@cman admin]$ mkdir wallet\n&#091;oracle@cman admin]$ mkstore -wrl \/u01\/app\/oracle\/network\/admin\/wallet -create\nOracle Secret Store Tool Release 21.0.0.0.0 - Production\nVersion 21.0.0.0.0\nCopyright (c) 2004, 2022, Oracle and\/or its affiliates. All rights reserved.\n\nEnter password:   \nEnter password again:   \n&#091;oracle@cman admin]$ mkstore -wrl \/u01\/app\/oracle\/network\/admin\/wallet createCredential pdb1 tdm1 tdm1pwd<\/code><\/pre>\n\n\n\n<p>Adjusted the cman.ora:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>cman = (configuration=\n  (address=(protocol=tcp)(host=cman.localdomain)(port=1999))\n  (parameter_list =\n<strong>    (tdm=true)\n    (tdm_threading_mode=dedicated)\n    (tdm_shared_threads_min=5)\n    (tdm_shared_threads_max=20)\n<\/strong>    (log_level=ADMIN)\n    (max_connections=1024)\n    (idle_timeout=0)\n    (registration_invited_nodes = *)\n    (inbound_connect_timeout=0)\n    (session_timeout=0)\n    (outbound_connect_timeout=0)\n    (max_gateway_processes=16)\n    (min_gateway_processes=2)\n    (remote_admin=on)\n    (trace_level=off)\n    (max_cmctl_sessions=4)\n    (event_group=init_and_term,memory_ops)\n  )\n  (rule_list=\n    (rule=\n       (src=cman)(dst=127.0.0.1)(srv=cmon)(act=accept)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    ) \n    (rule=\n       (src=19cClient)(dst=19c-dg1)(srv=pdb1)(act=accept)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n    (rule=\n       (src=19cClient)(dst=19c-dg1)(srv=ncdb1)(act=reject)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n  )\n)\n<strong>wallet_location = (source = (method = file) (method_data = (directory=\"\/u01\/app\/oracle\/network\/admin\/wallet\")))\nsqlnet.wallet_override = true<\/strong><\/code><\/pre>\n\n\n\n<p>Check if I still can connect. Actually the connect did hang:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>oracle@19c:\/home\/oracle\/tools\/netlat\/ &#091;rdbms19ee] sqlplus cbleile1\/cbleile1@pdb1_cman\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 14 03:53:01 2023\nVersion 19.18.0.0.0\n\nCopyright (c) 1982, 2022, Oracle.  All rights reserved.\n\n<\/code><\/pre>\n\n\n\n<p>In the \/u01\/app\/oracle\/diag\/netcman\/cman\/cman\/trace\/cman.log I do see this repeatedly:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>2023-04-14T04:00:08.297518+02:00\n14-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\nTNS-12514: TNS:listener does not currently know of service requested in connect descriptor<\/code><\/pre>\n\n\n\n<p>I had to add an alias pdb1 to the cman-tnsnames.ora:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#091;oracle@cman admin]$ cat $TNS_ADMIN\/tnsnames.ora \npdb1=\n (DESCRIPTION =\n     (ADDRESS =\n        (PROTOCOL = TCP)(HOST = 19c-dg1)(PORT = 1521)\n     )\n        (CONNECT_DATA = (SERVICE_NAME=pdb1)\n     )\n )<\/code><\/pre>\n\n\n\n<p>Then the connection as cbleile1 worked:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>oracle@19c:\/home\/oracle\/ &#091;rdbms19ee] sqlplus cbleile1\/cbleile1@pdb1_cman\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 14 04:15:37 2023\nVersion 19.18.0.0.0\n\nCopyright (c) 1982, 2022, Oracle.  All rights reserved.\n\nLast Successful login time: Fri Apr 14 2023 13:43:41 +02:00\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.16.0.0.0\n<strong>connected via Oracle Connection Manager in Traffic Director mode 21.9.0.0.0<\/strong>\n\nSQL&gt; \n<\/code><\/pre>\n\n\n\n<p>Then I tried it as cbleile2:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>oracle@19c:\/home\/oracle\/tools\/netlat\/ &#091;rdbms19ee] sqlplus cbleile2\/cbleile2@pdb1_cman\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 14 04:14:21 2023\nVersion 19.18.0.0.0\n\nCopyright (c) 1982, 2022, Oracle.  All rights reserved.\n\n<strong>ERROR:\nORA-28725: Message 28725 not found;  product=RDBMS; facility=ORA\n\n\nError accessing package DBMS_APPLICATION_INFO<\/strong>\nSQL&gt; <\/code><\/pre>\n\n\n\n<p>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:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#091;oracle@cman trace]$ oerr ora 28725\n28725, 00000, \"invalid CMAN in Traffic Director mode configuration\\n\"\n\/\/ *Cause:  An attempt was made to connect to Connection Manager (CMAN) in\n\/\/          Traffic Director mode with an invalid configuration.\n\/\/ *Action: Correct the configuration of CMAN in Traffice Director mode before\n\/\/          starting it. Check and correct the wallet credentials and proxy\n\/\/          authorization. Check with CMAN in Traffic Director mode\n\/\/          administrator to get more information from traces and sqlnet.log.\n&#091;oracle@cman trace]$ <\/code><\/pre>\n\n\n\n<p>So that&#8217;s expected, because we do not have the proxy user rights granted for cbleile2.<\/p>\n\n\n\n<p>The test results now were as follows:<\/p>\n\n\n\n<p>sqlplus cbleile1\/cbleile1@pdb1_cman<br>-&gt; works.<\/p>\n\n\n\n<p>sqlplus cbleile2\/cbleile2@pdb1_cman<br>-&gt; ORA-28725<\/p>\n\n\n\n<p>sqlplus cbleile1\/cbleile1@ncdb1_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<\/p>\n\n\n\n<p>sqlplus cbleile2\/cbleile2@ncdb1_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<\/p>\n\n\n\n<p>tns-alias with a non-existing service:<br>sqlplus cbleile2\/cbleile2@non_existing_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<\/p>\n\n\n\n<p>wrong password:<br>sqlplus cbleile1\/cbleile2@pdb1_cman<br>ORA-01017: invalid username\/password; logon denied<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-test-2\">Test 2<\/h3>\n\n\n\n<p>Rules:<\/p>\n\n\n\n<p>1 &#8211; The Client1 19cClient should be allowed to connect to DB cdb1, PDB pdb1, User cbleile1<\/p>\n\n\n\n<p>2 &#8211; The Client1 19cClient should not be allowed to connect to DB cdb1, PDB pdb1, User cbleile2<\/p>\n\n\n\n<p>3 &#8211; The Client1 19cClient should NOT be allowed to connect to DB ncdb1<\/p>\n\n\n\n<p>4 &#8211; The Client2 12cR2Client should be allowed to connect to DB cdb1, PDB pdb1, User cbleile2<\/p>\n\n\n\n<p>5 &#8211; The Client2 12cR2Client should NOT be allowed to connect to DB cdb1, PDB pdb1, User cbleile1<\/p>\n\n\n\n<p>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. <\/p>\n\n\n\n<p>First I&#8217;m creating a new TDM2-User on PDB pdb1 with proxy-rights to become cbleile2:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER TDM2 IDENTIFIED BY tdm2pwd;\nGRANT CREATE SESSION TO TDM2;\nALTER USER cbleile2 GRANT CONNECT THROUGH TDM2;<\/code><\/pre>\n\n\n\n<p>Then I add the alias pdb1_2 to the wallet on the cman server:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#091;oracle@cman admin]$ mkstore -wrl \/u01\/app\/oracle\/network\/admin\/wallet -createCredential pdb1_2 tdm2 tdm2pwd<\/code><\/pre>\n\n\n\n<p>And add an entry in the tnsnames.ora on the cman server:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>pdb1_2=\n (DESCRIPTION =\n     (ADDRESS =\n        (PROTOCOL = TCP)(HOST = 19c-dg1)(PORT = 1521)\n     )\n        (CONNECT_DATA = (SERVICE_NAME=pdb1)\n     )\n )<\/code><\/pre>\n\n\n\n<p>In addition add the rules to the $TNS_ADMIN\/cman.ora:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>cman = (configuration=\n  (address=(protocol=tcp)(host=cman.localdomain)(port=1999))\n  (parameter_list =\n    (tdm=true)\n    (tdm_threading_mode=dedicated)\n    (tdm_shared_threads_min=5)\n    (tdm_shared_threads_max=20)\n    (log_level=ADMIN)\n    (max_connections=1024)\n    (idle_timeout=0)\n    (registration_invited_nodes = *)\n    (inbound_connect_timeout=0)\n    (session_timeout=0)\n    (outbound_connect_timeout=0)\n    (max_gateway_processes=16)\n    (min_gateway_processes=2)\n    (remote_admin=on)\n    (trace_level=off)\n    (max_cmctl_sessions=4)\n    (event_group=init_and_term,memory_ops)\n  )\n  (rule_list=\n    (rule=\n       (src=cman)(dst=127.0.0.1)(srv=cmon)(act=accept)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    ) \n    (rule=\n       (src=19cClient)(dst=19c-dg1)(srv=pdb1)(act=accept)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n    (rule=\n       (src=19cClient)(dst=19c-dg1)(srv=ncdb1)(act=reject)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n<strong>    (rule=\n       (src=12cr2Client)(dst=19c-dg1)(srv=pdb1_2)(act=accept)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n    (rule=\n       (src=12cr2Client)(dst=19c-dg1)(srv=ncdb1)(act=reject)\n       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))\n    )\n<\/strong>  )\n)\nwallet_location = (source = (method = file) (method_data = (directory=\"\/u01\/app\/oracle\/network\/admin\/wallet\")))\nsqlnet.wallet_override = true<\/code><\/pre>\n\n\n\n<p>On the client I do add a tnsnames-entry with service pdb1_2:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>pdb1_cman=\n (DESCRIPTION =\n     (SOURCE_ROUTE = YES)\n     (ADDRESS =\n        (PROTOCOL = TCP)(HOST = cman)(PORT = 1999)\n     )\n     (ADDRESS =\n        (PROTOCOL = TCP)(HOST = 19c-dg1)(PORT = 1521)\n     )\n        (CONNECT_DATA = (SERVICE_NAME=pdb1_2)\n     )\n )<\/code><\/pre>\n\n\n\n<p>Test a connection to cbleile1, which should not be allowed:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>oracle@12cr2:\/home\/oracle\/ &#091;prem122] sqlplus cbleile1\/cbleile1@pdb1_cman\n\nSQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 14 14:28:38 2023\n\nCopyright (c) 1982, 2016, Oracle.  All rights reserved.\n\nERROR:\nORA-28725: Message 28725 not found;  product=RDBMS; facility=ORA\n\n\n\nERROR:\nORA-28725: Message 28725 not found;  product=RDBMS; facility=ORA\n\n\n\nError accessing PRODUCT_USER_PROFILE\nWarning:  Product user profile information not loaded!\nYou may need to run PUPBLD.SQL as SYSTEM\nERROR:\nORA-28725: Message 28725 not found;  product=RDBMS; facility=ORA\n\n\n\nError accessing package DBMS_APPLICATION_INFO\nSP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.\nSQL&gt; \n<\/code><\/pre>\n\n\n\n<p>Again, the error is ORA-28725 is not available on release 12cR2, that&#8217;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&#8217;s what I wanted. Can I connect to cbleile2?<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>oracle@12cr2:\/home\/oracle\/ &#091;prem122] sqlplus cbleile2\/cbleile2@pdb1_cman\n\nSQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 14 14:29:19 2023\n\nCopyright (c) 1982, 2016, Oracle.  All rights reserved.\n\nLast Successful login time: Fri Apr 14 2023 14:21:45 +02:00\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\n<strong>connected via Oracle Connection Manager in Traffic Director mode 21.9.0.0.0<\/strong>\n\nSQL&gt; <\/code><\/pre>\n\n\n\n<p>Yes, that worked &#8211; as intended.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-summary-of-the-tests\">Summary of the tests<\/h3>\n\n\n\n<p><strong>From 19cClient (Client1):<\/strong><\/p>\n\n\n\n<p>sqlplus cbleile1\/cbleile1@pdb1_cman<br>-&gt; works. OK.<\/p>\n\n\n\n<p>sqlplus cbleile2\/cbleile2@pdb1_cman<br>-&gt; ORA-28725. OK.<\/p>\n\n\n\n<p>sqlplus cbleile1\/cbleile1@ncdb1_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<br>-&gt; OK.<\/p>\n\n\n\n<p>sqlplus cbleile2\/cbleile2@ncdb1_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<br>-&gt; OK.<\/p>\n\n\n\n<p>tns-alias with a non-existing service:<br>sqlplus cbleile2\/cbleile2@non_existing_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<br>-&gt; OK.<\/p>\n\n\n\n<p>wrong password:<br>sqlplus cbleile1\/cbleile2@pdb1_cman<br>ORA-01017: invalid username\/password; logon denied<br>&#8211;&gt; OK.<\/p>\n\n\n\n<p><strong>From 12cr2Client (Client2):<\/strong><\/p>\n\n\n\n<p>sqlplus cbleile1\/cbleile1@pdb1_cman<br>-&gt; ORA-28725. OK.<\/p>\n\n\n\n<p>sqlplus cbleile2\/cbleile2@pdb1_cman<br>-&gt; works. OK.<\/p>\n\n\n\n<p>sqlplus cbleile1\/cbleile1@ncdb1_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<br>-&gt; OK.<\/p>\n\n\n\n<p>sqlplus cbleile2\/cbleile2@ncdb1_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<br>-&gt; OK.<\/p>\n\n\n\n<p>tns-alias with a non-existing service:<br>sqlplus cbleile2\/cbleile2@non_existing_cman<br>-&gt; rejected: ORA-12529: TNS:connect request rejected based on current filtering rules<br>-&gt; OK.<\/p>\n\n\n\n<p>wrong password:<br>sqlplus cbleile2\/cbleile1@pdb1_cman<br>ORA-01017: invalid username\/password; logon denied<br>&#8211;&gt; OK.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-summary\">Summary<\/h2>\n\n\n\n<p>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).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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. [&hellip;]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[2925,2929,2363,96,2924,2927,2928],"type_dbi":[],"class_list":["post-24630","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-cman","tag-filter","tag-firewall","tag-oracle","tag-oracle-connection-manager","tag-tdm","tag-traffic-director-mode"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User?\" \/>\n<meta property=\"og:description\" content=\"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. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-18T10:30:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-29T23:14:01+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User?\",\"datePublished\":\"2023-04-18T10:30:30+00:00\",\"dateModified\":\"2024-01-29T23:14:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/\"},\"wordCount\":1334,\"commentCount\":10,\"keywords\":[\"CMAN\",\"filter\",\"FireWall\",\"Oracle\",\"Oracle Connection Manager\",\"TDM\",\"Traffic Director Mode\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/\",\"name\":\"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2023-04-18T10:30:30+00:00\",\"dateModified\":\"2024-01-29T23:14:01+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\/\/www.dbi-services.com\",\"https:\/\/x.com\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/","og_locale":"en_US","og_type":"article","og_title":"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User?","og_description":"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. [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/","og_site_name":"dbi Blog","article_published_time":"2023-04-18T10:30:30+00:00","article_modified_time":"2024-01-29T23:14:01+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User?","datePublished":"2023-04-18T10:30:30+00:00","dateModified":"2024-01-29T23:14:01+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/"},"wordCount":1334,"commentCount":10,"keywords":["CMAN","filter","FireWall","Oracle","Oracle Connection Manager","TDM","Traffic Director Mode"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/","name":"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-04-18T10:30:30+00:00","dateModified":"2024-01-29T23:14:01+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-is-it-possible-to-setup-a-system-that-a-client-specific-ip-address-can-only-connect-to-a-specific-db-user\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle: Is it possible to setup a system that a client (specific IP address) can only connect to a specific DB-User?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/24630","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=24630"}],"version-history":[{"count":9,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/24630\/revisions"}],"predecessor-version":[{"id":30589,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/24630\/revisions\/30589"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=24630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=24630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=24630"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=24630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}