Starting and Leaving SQLcl

2.1 Starting and Leaving SQLcl

Note:

Startup and Login Scripts

When you start SQLcl, it looks for a startup.sql script. This script is run only once during the program's runtime.

For every connection, SQLcl looks for a login.sql file. This file is run for every connection created.

The Show Login command displays the location of the login.sql file and verifies whether the file exists or not.

Logging In and Logging Out

Use the following commands to log in to and out of SQLcl.

SQL [[option] [logon | /NOLOG] [start]]

where option has the following syntax:

-H[ELP] | -V[ERSION] | [ [-C[OMPATIBILITY] x.y[.z]]] [-L[OGON]] [-NOLOGINTIME] [-R[ESTRICT] {0|1|2|3|4}] [-S[ILENT]]

where logon has the following syntax:

{username[/password] [@connect_identifier] | /} [AS {SYSASM |SYSBACKUP |SYSDBA |SYSDG |SYSOPER |SYSRAC |SYSKM}] [edition=value]

and where start has the following syntax:

@{url | file_name[.ext]} [arg ...]

{EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]

Commits or rolls back all pending changes, logs out of Oracle, terminates SQLcl and returns control to the operating system.

{QUIT | EXIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]

Commits or rolls back all pending changes, logs out of Oracle, terminates SQLcl and returns control to the operating system.

Setting JVM Options

To set an Oracle Java Virtual Machine (JVM) option in SQLcl, use the following environment variable:

JAVA_TOOL_OPTIONS

The following example shows in Windows, how to change the user interface language in SQLcl to Spanish (es):

c:\SQLDev\sqlcl\20.2\sqlcl\bin>SET JAVA_TOOL_OPTIONS=-Duser.language=es
c:\SQLDev\sqlcl\20.2\sqlcl\bin>sql hr/oracle
Picked up JAVA_TOOL_OPTIONS: -Duser.language=es
SQLcl: Versi≤n 20.2 Production en mar. ago. 25 15:37:58 2020
Copyright (c) 1982, 2020, Oracle. Todos los derechos reservados.
Last Successful login time: Mar Ago 25 2020 15:38:01 -04:00
Conectado a:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Se ha encontrado login.sql en el CWD. El acceso a la DB estß restringido para login.sql.
Ajuste SQLPATH para incluir la ruta y activar la funcionalidad completa.
SQL>

The following example shows how to change the user interface language in SQLcl to French (fr), and also increase the maximum memory available to SQLcl (-Xmx800m):

c:\SQLDev\sqlcl\20.2\sqlcl\bin>SET JAVA_TOOL_OPTIONS=-Duser.language=fr -Xmx800m
c:\SQLDev\sqlcl\20.2\sqlcl\bin>sql hr/oracle
Picked up JAVA_TOOL_OPTIONS: -Duser.language=fr -Xmx800m
SQLcl : version 20.2 Production sur mar. ao√t 25 15:57:21 2020
Copyright (c) 1982, 2020, Oracle. Tous droits rΘservΘs....

2.1.1 About Restrict Levels in SQLcl

For enhanced security, Oracle SQLcl provides a restrict option that allows you to disable certain commands that interact with the operating system.

This option is useful for preventing potentially unsafe operations, such as running host commands or scripts from untrusted sources.

Explore the available restrict levels in SQLcl, commands restricted at each level, and best practices for applying these restrictions.

2.1.1.1 The `RESTRICT` Option

SQLcl defines five restrict levels, ranging from most restrictive (4) to unrestricted (0). Each level permits or denies specific categories of commands.

For information on commands restricted at each level, see Commands Restricted by Level.

Syntax

SQL -R[ESTRICT] {0|1|2|3|4}

Options

Restrict Level	Access	Description
4	Most restrictive	Blocks all commands from level 1 to 4. This includes host commands, scripting, and configuration changes. For the full list, see Restricted Commands at Level 4.
3	Highly restrictive	Disables running external script files in SQLcl. Blocks level 1 to 3 commands (such as `@@`, `save`, `edit`) while allowing level 4 commands.
2	Moderately restrictive	Prevents saving generated SQLcl content to external files. Blocks level 1 and level 2 commands (such as `save`, `edit`, and so on) while allowing level 4 and level 3 commands.
1	Minimally restrictive	Prevents execution of host (OS-level) commands. Blocks level 1 commands (such as `edit` and `host`) while allowing level 2 to 4 commands.
0	Unrestricted	Allows all commands, including host and script execution. This is the default restrict level.

2.1.1.2 Commands Restricted by Level

Review the commands SQLcl disables at each restrict level.

Restricted Commands at Level 4

accept
alias
append
archive
attribute
break
bridge
btitle
cd
change
clear
cloudstorage
column
compute
connmgr
copy
datapump
del
drivers
exit
find
format
history
info
input
list
load
loadqueryfile
migrateadvisor
net
oci
oradebug
oerr
password
pause
ping
pwd
quit
recover
repeat
rollback
run
script
secret
set \_12.1\_longident
set \_12.1\_pdb
set \_prelim
set arraysize
set autocommit
set autodblink
set autoprint
set classicmode
set clear
set cloudconfig
set closecursor
set cmdsep
set colsep
set compatibility
set concat
set datapump
set desc
set document
set editfile
set editor
set embedded
set encoding
set exitcommit
set flagger
set flush
set heads
set heat
set hidden
set highlighting
set history
set linenumbers
set linesize
set load
set loadformat
set loglevel
set long
set longc
set longchunksize
set maxdata
set maxrows
set maxspoolrowstruncate
set net
set newpage
set noverwrite
set null
set num
set numf
set parameterpolicy
set pause
set printtheme
set property
set secureliteral
set securedcol
set socksproxy
set space
set sqlco
set sqlformat
set sqlpluscompatibility
set sqlprefix
set sqlt
set statementcache
set statusbar
set suffix
set tab
set termout
set tnsadmin
set trimout
set trims
set truncate
set wallet
set wrap
set xquery
set xmlformat
set xmloptimizationcheck
show array
show autocommit
show autoprint
show autorecovery
show btitle
show buffer
show colsep
show commandline
show con\_name
show connection
show encodings
show encoding
show endbuft
show endbuftoken
show flagger
show heading
show headsep
show instance
show internalerrors
show java
show jdbc
show linesize
show lno
show locale
show login
show loboffset
show long
show longchunksize
show newpage
show nls
show null
show numformat
show numwidth
show pagesize
show pause
show pno
show prelim
show recsep
show recsepchar
show registry
show release
show repfooter
show repheader
show securedcol
show spool
show spparameter
show sqldev
show sqldev2
show sqlprompt
show suffix
show systemout
show termout
show toplevel
show trimout
show trimspool
show ufi
show urls
show wrap
show xquery
show xmloptimization
show xmloptimizationcheck
unload
vault
version
whenever
which
xquery

Restricted Commands at Level 3

@@
@
get
start

Restricted Commands at Level 2

save
spool
store

Restricted Commands at Level 1

edit
host (!, $)

2.1.1.3 Best Practices for Applying Restrictions

For optimal security and control in your SQLcl environment, adhere to the following best practices. These recommendations ensure you apply the appropriate restrictions, balancing system protection with operational flexibility.

Use the highest restrict level for enhanced security
For most production or secure environments, you should use the highest restrict level (4) to minimize security risks.
Lower restrictions for specific needs
Only lower the restrict level when you have a specific need to run commands that are otherwise blocked. For instance, if you need to run installation scripts, you could temporarily lower the restrict level to one (1) and revert it to a higher level after the task is complete.
Use level zero (0) with caution
The restrict level zero (0) provides full administrative freedom and can be useful for development or in isolated, trusted environments. However, you should use it with caution due to the potential security implications of allowing host command execution.

2.1 Starting and Leaving SQLcl

2.1.1 About Restrict Levels in SQLcl

2.1.1.1 The RESTRICT Option

2.1.1.2 Commands Restricted by Level

2.1.1.3 Best Practices for Applying Restrictions

2.1.1.1 The `RESTRICT` Option