EZConnect is simple when we know the host and port. However, wallets
are great mechanism for establishing password less connections to servers.
However, the combination of these two were not easy before Oracle 19c.
We can add a wallet entry for an EZCONNECT connection string like
‘//localhost/pdb1’ but in the wallet, you would need a different connecting
string for each user because it associates a user and password to a service
name. and have multiple users connecting to a service.
demo@PDB19>
show con_name
CON_NAME
------------------------------
PDB19
demo@PDB19>
grant create session to app_user identified by app_user;
Grant
succeeded.
demo@PDB19>
I created a wallet,
[oracle@en701c104vm01
~]$ mkdir -p /home/oracle/wallet
[oracle@en701c104vm01
~]$ mkstore -wrl /home/oracle/wallet -create
Oracle Secret
Store Tool Release 23.0.0.0.0 - Production
Version
23.0.0.0.0
Copyright (c)
2004, 2021, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password
again:
Added an entry for the service “app_user_demo” connecting to PDB19 with
the service account name as “app_user”
[oracle@en701c104vm01
~]$ mkstore -wrl /home/oracle/wallet -createCredential app_user_demo app_user
Oracle
Secret Store Tool Release 23.0.0.0.0 - Production
Version
23.0.0.0.0
Copyright
(c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.
Your
secret/Password is missing in the command line
Enter
your secret/Password:
Re-enter
your secret/Password:
Enter
wallet password:
[oracle@en701c104vm01
~]$
“Then defined the sqlnet.ora with the wallet_location parameter and the
tnsnames.ora for this “app_user_demo” entry,
[oracle@en701c104vm01
~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ENCRYPTION_SERVER
= required
SQLNET.CRYPTO_CHECKSUM_SERVER
= required
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER
= (SHA1)
ENCRYPTION_WALLET_LOCATION
=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/var/opt/oracle/dbaas_acfs/XEN701C1/tde_wallet)))
SQLNET.ENCRYPTION_TYPES_SERVER
= (AES256, AES192, AES128)
SQLNET.EXPIRE_TIME
= 10
SQLNET.WALLET_OVERRIDE
= FALSE
WALLET_LOCATION
= (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/wallet)))
SQLNET.WALLET_OVERRIDE=TRUE
SSL_VERSION
= 1.2
[oracle@en701c104vm01
~]$ tnsping app_user_demo
TNS
Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-SEP-2021 10:16:10
Copyright
(c) 1997, 2021, Oracle. All rights
reserved.
Used
parameter files:
/u02/app/oracle/product/19.0.0.0/dbhome_2/network/admin/XEN701C1/sqlnet.ora
Used
TNSNAMES adapter to resolve the alias
Attempting
to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT =
1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB19)
(FAILOVER_MODE = (TYPE = select) (METHOD = basic))))
OK
(0 msec)
Now we can connect password less when using sqlplus
[oracle@en701c104vm01
~]$ sqlplus
/@app_user_demo
SQL*Plus:
Release 19.0.0.0.0 - Production on Fri Sep 17 10:26:55 2021
Version
19.12.0.0.0
Copyright
(c) 1982, 2021, Oracle. All rights
reserved.
Last
Successful login time: Fri Sep 17 2021 10:25:29 -04:00
Connected
to:
Oracle
Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version
19.12.0.0.0
SQL>
show user
USER
is "APP_USER"
SQL>
show con_name
CON_NAME
------------------------------
PDB19
SQL>
Now for easy connect we can add a new entry for EZCONNECT string
[oracle@en701c104vm01
~]$ mkstore -wrl /home/oracle/wallet -createCredential //10.41.41.8/PDB19
app_user
Oracle Secret
Store Tool Release 23.0.0.0.0 - Production
Version
23.0.0.0.0
Copyright (c)
2004, 2021, Oracle and/or its affiliates. All rights reserved.
Your
secret/Password is missing in the command line
Enter your
secret/Password:
Re-enter your
secret/Password:
Enter wallet
password:
We can now connect using EZCONNECT wallet entry like this
[oracle@en701c104vm01
~]$ sqlplus /@//10.41.41.8/PDB19
SQL*Plus:
Release 19.0.0.0.0 - Production on Fri Sep 17 10:41:00 2021
Version 19.12.0.0.0
Copyright (c)
1982, 2021, Oracle. All rights reserved.
Last Successful
login time: Fri Sep 17 2021 10:26:57 -04:00
Connected to:
Oracle Database
19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version
19.12.0.0.0
SQL> show
user
USER is
"APP_USER"
SQL> show
con_name
CON_NAME
------------------------------
PDB19
SQL> exit
Disconnected
from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version
19.12.0.0.0
But what you do when you need to connect with different users? With a
tnsnames.ora we can have multiple entries for each one like this
scott_demo,app_user_demo
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB19)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
)
)
)
And then define a credential for each one, but that is not possible
with EZCONNECT, associating two different (service user) accounts to same
EZCONNECT endpoints will return error like this
[oracle@en701c104vm01
~]$ mkstore -wrl /home/oracle/wallet -createCredential //10.41.41.8/PDB19
app_user
Oracle Secret
Store Tool Release 23.0.0.0.0 - Production
Version
23.0.0.0.0
Copyright (c)
2004, 2021, Oracle and/or its affiliates. All rights reserved.
Your
secret/Password is missing in the command line
Enter your
secret/Password:
Re-enter your
secret/Password:
Enter wallet
password:
[oracle@en701c104vm01
~]$ mkstore -wrl /home/oracle/wallet -createCredential //10.41.41.8/PDB19 scott
Oracle Secret
Store Tool Release 23.0.0.0.0 - Production
Version
23.0.0.0.0
Copyright (c)
2004, 2021, Oracle and/or its affiliates. All rights reserved.
Your
secret/Password is missing in the command line
Enter your
secret/Password:
Re-enter your
secret/Password:
Enter wallet
password:
Secret
Store error occurred: oracle.security.pki.OracleSecretStoreException:
Credential already exists
[oracle@en701c104vm01
~]$
Oracle 19c extends the EZCONNECT syntax – namely EZCONNECT
PLUS – with that it is still possible to add dummy parameters and using
this option we can resolve the above error (differentiate multiple entries
connecting to same database but with different user accounts)
Here is an example of it.
[oracle@en701c104vm01
~]$ mkstore -wrl /home/oracle/wallet -createCredential //10.41.41.8/PDB19?My_Tag=app_user app_user
Oracle Secret
Store Tool Release 23.0.0.0.0 - Production
Version
23.0.0.0.0
Copyright (c)
2004, 2021, Oracle and/or its affiliates. All rights reserved.
Your
secret/Password is missing in the command line
Enter your
secret/Password:
Re-enter your
secret/Password:
Enter wallet
password:
oracle@en701c104vm01
~]$
This just add a parameter that will be ignored, but still helps to
differentiate multiple entries.
[oracle@en701c104vm01
~]$ tnsping //10.41.41.8/PDB19?My_Tag=app_user
TNS Ping
Utility for Linux: Version 19.0.0.0.0 - Production on 17-SEP-2021 10:48:45
Copyright (c)
1997, 2021, Oracle. All rights reserved.
Used parameter
files:
/u02/app/oracle/product/19.0.0.0/dbhome_2/network/admin/XEN701C1/sqlnet.ora
Used HOSTNAME
adapter to resolve the alias
Attempting to
contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB19))(My_Tag=app_user)(ADDRESS=(PROTOCOL=tcp)(HOST=10.41.41.8)(PORT=1521)))
OK (0 msec)
Here is the connection the app_user service account using the
credentials from Wallet.
[oracle@en701c104vm01
~]$ sqlplus /@//10.41.41.8/PDB19?My_Tag=app_user
SQL*Plus:
Release 19.0.0.0.0 - Production on Fri Sep 17 10:49:02 2021
Version
19.12.0.0.0
Copyright (c)
1982, 2021, Oracle. All rights reserved.
Last Successful
login time: Fri Sep 17 2021 10:41:01 -04:00
Connected to:
Oracle Database
19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version
19.12.0.0.0
SQL>
show user
USER
is "APP_USER"
SQL> show
con_name
CON_NAME
------------------------------
PDB19
Similarly, for the scott user account we do something like this
[oracle@en701c104vm01
~]$ mkstore -wrl /home/oracle/wallet -createCredential //10.41.41.8/PDB19?My_Tag=scott scott
Oracle Secret Store
Tool Release 23.0.0.0.0 - Production
Version
23.0.0.0.0
Copyright (c)
2004, 2021, Oracle and/or its affiliates. All rights reserved.
Your
secret/Password is missing in the command line
Enter your
secret/Password:
Re-enter your
secret/Password:
Enter wallet
password:
[oracle@en701c104vm01
~]$ tnsping //10.41.41.8/PDB19?My_Tag=scott
TNS Ping
Utility for Linux: Version 19.0.0.0.0 - Production on 17-SEP-2021 10:50:21
Copyright (c)
1997, 2021, Oracle. All rights reserved.
Used parameter
files:
/u02/app/oracle/product/19.0.0.0/dbhome_2/network/admin/XEN701C1/sqlnet.ora
Used HOSTNAME
adapter to resolve the alias
Attempting to
contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB19))(My_Tag=scott)(ADDRESS=(PROTOCOL=tcp)(HOST=10.41.41.8)(PORT=1521)))
OK (0 msec)
[oracle@en701c104vm01
~]$ sqlplus /@//10.41.41.8/PDB19?My_Tag=scott
SQL*Plus:
Release 19.0.0.0.0 - Production on Fri Sep 17 10:50:44 2021
Version
19.12.0.0.0
Copyright (c)
1982, 2021, Oracle. All rights reserved.
Last Successful
login time: Fri Sep 17 2021 10:46:50 -04:00
Connected to:
Oracle Database
19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version
19.12.0.0.0
SQL>
show user
USER
is "SCOTT"
SQL> show
con_name
CON_NAME
------------------------------
PDB19
SQL> exit
Disconnected
from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version
19.12.0.0.0
[oracle@en701c104vm01
~]$
So using EZCONNECTPLUS all we need is a sqlnet.ora and a wallet, but no tnsnames.ora
Here is all the entries I have
[oracle@en701c104vm01
~]$ mkstore -wrl /home/oracle/wallet -listCredential
Oracle Secret
Store Tool Release 23.0.0.0.0 - Production
Version
23.0.0.0.0
Copyright (c)
2004, 2021, Oracle and/or its affiliates. All rights reserved.
Enter wallet
password:
List credential
(index: connect_string username)
5:
//10.41.41.8/PDB19?My_Tag=scott scott
4:
//10.41.41.8/PDB19?My_Tag=app_user app_user
3: scott_demo
scott
2:
//10.41.41.8/PDB19 app_user
1:
app_user_demo app_user
[oracle@en701c104vm01
~]$
No comments:
Post a Comment