Wait, SQLcl has ‘connections?’ Yes, yes it does!

SQLcl Docs: managing connections

It means you can store connection definitions locally (or in the Oracle Cloud), complete with usernames and passwords (optional), and reference those by name when connecting.

Example –

SQL> connmgr list
ADMIN 19c
FUN DEMO Data
HR
HR_TEXT
SYSTEM
Windows Spaces Are Legal
SQL>
SQL> connect -name HR
Connected.

The passwords are SECURELY stored via Java Wallets.

You can import these from what you’ve already defined in SQL Developer. And, coming extremely, very soon, these same connections will be SHARED with our new VS Code extension.

But we all know what happens with your most frequently used sets of credentials – your passwords expire, frequently. We all love security, but we all hate that moment when we’re asked to update our passwords, again.

If you want to update your connection via SQLcl’s command line, then here’s how you do it.

Changing the database password

SQL> alter user hr identified by "8asawlCU&h?makota48o";

User HR altered.

No drama here, just me changing the password for the HR user.

In order to update the password stored for my ‘HR’ connection, I need to manually connect in SQLcl, using the new password.

Changing the connection’s password

Version 23.4 is coming out, SOON, but this will work in 23.3 (the current release as of today) –

❯ sql /nolog


SQLcl: Release 23.4 Production on Fri Jan 12 08:27:18 2024

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

SQL> connect -save HR -replace -savepwd hr/oracle1@localhost:1521/freepdb1
Name: HR
Connect String: localhost:1521/freepdb1
User: hr
Password: ******
Connected.
SQL>

So we’re making a new connection, but on the connect command, we’re using the -save, -replace, and -savepwd flags.

  • -save will use the conn details to create a new conn in our store, using name ‘HR’
  • -replace will allow us to overwrite the existing ‘HR’ connection defintion
  • -savepwd tells SQLcl in include the password, by default it’s not stored

And that’s it, we can see in the output, what’s stored with the connection, ‘HR’, the connection string, username, and that a password have been recorded.

Using the connection name from the base shell

I don’t have to use /nolog, I can reference the connection directly from invoking SQLcl.

23.4\sqlcl\bin
❯ sql -name HR


SQLcl: Release 23.4 Production on Fri Jan 12 09:37:47 2024

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

Last Successful login time: Fri Jan 12 2024 09:37:49 -05:00

Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL>
1:0 ¦ HR ¦ jdbc:oracle:thin:@localhost:1521/freepdb1 ¦ viins ¦ None ¦ No time

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

2 Comments

  1. Adam vonNieda Reply

    Hi Jeff, great write up!

    Let’s say that I want to manage passwords (change regularly) for accounts that are used by power users for DB management etc. They may connect via SQL-Developer and/or SQLcl . Is it possible to have a centralized credential store / wallet that these different users can all access? So that when I change the passwords for these accounts, it’s transparent to them?

    Thanks!

    Adam vonNieda

    • You read my mind…. yeah, were working on it. We’ve built our own connection store in the Oracle Cloud, we’re going to integrate support for that in sqlcl and our vs code extension.

Write A Comment