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:
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.
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.
14 Comments
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
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
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
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
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!
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
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
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
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
yes, put your login.sql someplace sqlplus will never see it…like in the directory with the exe/sh
sqlplus has/had the Product_user_profile Table https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch9.htm
Is there something similar in SQLDeveloper, it keeps the natives under control and out of Production.
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.
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.
Could be…but why not just have a versioned file that folks can periodically pull down as updates are made available?