Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Blacklisting Commands in Your Oracle SQLcl History

1 Min Read

We by default don’t store certain things in your SQLcl HISTORY list – like CONNECTs. For obvious reasons, it’s best not to store certain things, either for convenience or for security.

Here’s how that is controlled.

SET HISTORY BLACKLIST command1, command2, …, commandN

An example:

A few settings here of interest other than the blacklist...
A few settings here of interest other than the blacklist…

So I previously added ALTER to the list, and just now added DESC. And then I ran a few commands that use those blacklisted commands.

Now let’s check our HISTORY.

Only the select on dual is listed.
Only the select on dual is listed.

Note: we changed how the blacklisting is managed from when the Early Adopter was running.

Where is this file stored?

Same place we store your ALIAS list of commands…it’s per OS user, not per install of SQLcl.

On *NIX/OSX they're in your $HOME/.sqlcl folder.
On *NIX/OSX they’re in your $HOME/.sqlcl folder.

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.

Related Posts

14 Comments

  1. Chris Smids Reply

    Hello Jeff,

    I can’t find the option “SET HISTORY BLACKLIST” anymore in SQLcl 20.3. In SQLcl 20.2 I still had it. Is it normal that it can’t be found anymore in SQLcl 20.3 ? It’s still in the documentation though.

    As I noticed the BRIDGE statement is saved within the history with the cleartext password (specified in the JDBC connection string) I wanted to add the command “BRIDGE” to the “HISTORY BLACKLIST”.

    Here’s the output from my SQLcl 20.3 installation :
    SQL> > show version
    Oracle SQLDeveloper Command-Line (SQLcl) version: 20.3.0.0 build: 20.3.0.274.1916

    SQL> help set history
    SET HISTORY
    ———

    set history [FAILS [LIMIT [ n | DEFAULT ] ] | NOFAILS
    |FILTER [DEFAULT ?|? | NONE]
    |LIMIT [n|DEFAULT]]

    FAILS sets history command to show failed statements.
    use limit to limit number of failues
    NOFAILS sets history command to not show failed statements
    FILTER sets a list of sqlcl commands that can be excluded from the history
    LIMIT sets a new max size for the history

    NB. History will not save failed commands at end of session.

    Greetings,

    Chris

    • Chris Smids

      Hello Jeff,

      FYI, I created a SR for this and it turns out the statement “SET HISTORY BLACKLIST” is replaced by “SET HISTORY FILTER” as of SQLcl 20.3. This however is not yet mentioned in the “Oracle SQLcl User’s Guide, Release 20.3”. I asked within my SR to have this changed within the documentation.

      Greetings,

      Chris

    • thatjeffsmith Post Author

      it’s mentioned in the release notes
      Version Updates
      New Features:

      CS – cloudstorage command, work with compartments, buckets, or objects in Oracle Object Store (OSS)
      Modeler – generate DDL or design reports for your SQL Developer Data Modeler designs
      New Commands
      MODELER
      CLOUDSTORAGE
      SET HISTORY BLACKLIST is now SET HISTORY FILTER

      also, it’s shown in the help set history

    • Chris Smids

      Hello Jeff,

      Thank you very much for your feedback. Yes indeed, I overlooked the fact that the “FILTER” option is now mentioned instead of “BLACKLIST” in “HELP SET HISTORY” and indeed, after checking the release notes, I can confirm it is also being mentioned over there.

      However I used the SQLcl documentation section on https://www.oracle.com/database/technologies/appdev/sqlcl.html to get to know more about SQLcl 20.3 by using the SQLcl Users guide and unfortunately in this guide the command “SET HISTORY BLACKLIST” is now still mentioned instead of “SET HISTORY FILTER”.

      I mentioned this also in my SR (3-24557270641) and they told me they were going to make the necessary changes in the SQLcl 20.3 Users guide.

      PS : I am only a proud SQLcl user since one month. Thank you very much for spreading the good word about SQLcl 😉

      Greetings,

      Chris

    • thatjeffsmith Post Author

      Yes, of course it needs to be right in the documentation as well! This change came in late and apparently we missed a spot when it came to updating things. Thanks for taking the time to rise that SR and help make things better for everyone!

      Also, super happy to have you onboard with SQLcl!

    • Chris Smids

      Hello Jeff,

      Thank you very much! FYI, one more thing…

      I also wanted, as you showed over here, to filter “ALTER” from the HISTORY but it seems the “ALTER” statement cannot be added anymore as one of the commands to be filtered from the HISTORY. I also created a SR for that one and Oracle Support is taking care of it. Tested it with SET HISTORY FILTER in SQLcl 20.3 and SET HISTORY BLACKLIST in SQLcl 20.2 and both gave an error. The statements work when I remove the “alter” command from the list.

      SQLcl 20.3
      SQL> show version
      Oracle SQLDeveloper Command-Line (SQLcl) version: 20.3.0.0 build: 20.3.0.274.1916
      SQL> set history filter show,history,connect,clear,bridge,password,alter
      Unknown Command alter
      Unknown command in history filter

      SQLcl 20.2
      SQL> show version
      Oracle SQLDeveloper Command-Line (SQLcl) version: 20.2.0.0 build: 20.2.0.174.1557
      SQL> set history blacklist show,history,connect,clear,bridge,password,alter
      Unknown Command alter
      Unknown command in history blacklist

      Greetings,

      Chris

    • Chris Smids

      Good news ! I got confirmation from Oracle Support that the next version of SQLcl would contain the fix for the bug which currently causes the issue that the “ALTER” statement cannot be added anymore as one of the commands to be filtered from the HISTORY (reproduced in SQLcl 20.2 and 20.3).

      Greetings,

      Chris

    • Chris Smids

      Just to confirm that “Bug 32180833 – CANNOT ADD “ALTER” COMMAND TO LIST OF FILTERED STATEMENTS IN THE HISTORY” is indeed included in SQLcl 20.4 which has just been released.

      The “ALTER” command can now be added again to the list of filtered statements in the history via the “SET HISTORY FILTER” command in SQLcl as of version 20.4.

      Thank you very much SQLcl team for having this one fixed !

      Greetings,

      Chris

  2. Rich Soule Reply

    Jeff,

    Let’s say that I’d like to have the following (for example) as my default history blacklist settings (as a DBA running sqlcl on my database server, I’d like show and connect strings to be in my history… if you are logged in as the oracle OS user, you already have all the keys to the kingdom):

    set history blacklist history clear

    Without putting the above in my glogin.sql or login.sql (which would break SQL/Plus with an ORA error since SQL/Plus would give a “SP2-0268: history option not a valid number”) is there a way for me to make these settings persistent for only SQLcl and not for SQL/Plus?

    Thanks,

    Rich

    • thatjeffsmith Post Author

      yes, put your login.sql someplace sqlplus will never see it…like in the directory with the exe/sh

    • thatjeffsmith Post Author

      No, I’m a firm believer in letting the database handle security, and if the natives misbehave, then they’re asked to not come visit the database anymore.

  4. Jim Campbell Reply

    Practical, thoughtful feature. Well Done. Could it be implemented universally throughout the shop, maybe store the aliases.xml file on a shared protected network drive? Thanks you are a great resource.

    • thatjeffsmith Post Author

      Could be…but why not just have a versioned file that folks can periodically pull down as updates are made available?

Write A Comment