Uruguay User Group Session: Secure Your Database in 1 Day

Many thanks to those who I had the pleasure and honor of presenting, albeit virtually, on how to secure 60% of an Oracle Database infrastructure in just under a day. I hope you found it useful.

You can download the presentation here and the scripts here.

Role of # in SQL*Plus

The # character is for commenting in SQL*Plus, right?

The character # has been mostly used for comments in many languages, such as shell scripts and python. Interestingly # is legal syntax in SQL scripting as well; but is it considered a comment? The answer is no; it's not. The purpose of # in SQL scripts is very different. and you should be very careful using it.

Entering # tells SQL*Plus to temporarily pauses what has been entered before and execute everything after that #sign, as if in a different session. Here is a usecase. Suppose you are writing this query:

SQL> select *
  2  from v$sesstat
  3  where

[Typographical Conventions: User inputs are shown bold. System outputs are not.]

At this time you are stuck. You don't remember the column names of V$SESSTAT. But you need to know that to use it as a predicate. What can you do? You could just press ENTER at this point, which causes the SQL*Plus prompt to be redisplayed. You can describe the view and that will be your answer. However, this means you will have lost all the input you have entered until then. In case of a complicated long query it is hardly desirable.

This is where the # symbol comes to help. Just type # and enter the command you want to be displayed. In this case you want to describe the view. So enter at the 4th line prompt of SQL*Plus:

  4  #desc v$sesstat

When you enter this command and press ENTER, SQL*Plus will halt the evaluation of all the command it is doing now and execute the desc v$sesstat command. Here will be the output:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 SID                                                NUMBER
 STATISTIC#                                         NUMBER
 VALUE                                              NUMBER
 CON_ID                                             NUMBER

After this display, the SQL*Plus prompt will show the 4th line as the prompt "4". Now that you know the column, enter the rest of the SQL statement you were entering

  4  con_id = 1 
  5  /

And that's it. Note how SQL*Plus didn't discard any of the commands already entered. Instead it merely paused the evaluation and started evaluating the command "desc v$sesstat" after the # symbol. This symbol "#" is not a comment character but a temporary new command without discarding the previous commands in play in the session.

Unintended Ramifications

This behavior of # character may result in some scenarios not at all intended. Take for instance the following scenario. We create a table, insert a row, do not commit and then check the number of records.

SQL> create table t (col1 number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> select count(1)
  2  from t
  3  where 1 > 0
  4  /

  COUNT(1)
----------
         1

We got the results as expected. Now let's introduce a small line #roll in between the commands. Perhaps someone assumed it was a comment and left it there.

SQL> select count(1)
  2  #roll
Rollback complete.
  2  from t
  3  where 1 > 0
  4  /

  COUNT(1)
----------
         0

Note how the counts came back as 0. Why? It's because the rollback occurred independently of the select command. By the time the select statement was completed, the rollback statement had rolled back the insert and hence there were no rows in the table.

In some cases this behavior might prove deadly. Here is another example I have seen, from a script that shuts down the database, takes a storage snapshot and restarts it.

shutdown abort
startup

Later it was decided that shutdown abort is not desirable. It should be shutdown transactional. So, the DBA changed the script to the following. Assuming the # character to be comment, she thought she was commenting the line instead of removing it:

#shutdown abort
shutdown transactional
startup

Now let's run the script:

SQL> @b
ORACLE instance shut down.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0
ORACLE instance started.

Total System Global Area 5117050880 bytes
Fixed Size                  8757424 bytes
Variable Size            1056968528 bytes
Database Buffers         4043309056 bytes
Redo Buffers                8015872 bytes
Database mounted.
Database opened.                                                                                                                    

What happened? The shutdown failed with "ORACLE not available". Why?

The answer is simple. The shutdown abort executed. The database was already down. Therefore the shutdown transactional failed.

Don't Like #?

Now that you see how some people may confuse # as a comment and make mistakes, you may not like to have this special property assigned to #. Instead, you may want another character, say "*". You can easily change it. The special setting in SQL*Plus called SQLPREFIX allows you to do that:

SQL> set sqlprefix *

Now, let's repeat the same example we used earlier but with * instead of #:

SQL> select count(1)
  2  * desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 COL1                                               NUMBER

  2  from t;

  COUNT(1)
----------
         0

What if you use # character in this case?

SQL> select count(1)
  2  # desc t
  3  from t;
# desc t
*
ERROR at line 2:
ORA-00911: invalid character

You will not go anywhere. You will get an error. It's probably better than an assumption of # character as a comment.

Takeaways

1. The # character is not a comment in SQL scripts.
2. When SQL*Plus encounters a # character at the first position, it temporarily pauses the evaluation of the statements being entered and immediately executes the command after # and resumes evaluation of the previously entered commands afterwards.
3. You can assign another character to exhibit this behavior by setting the SQLPREFIX to that character.

Enough with AFIEDT.BUF

You are in SQL*Plus. You entered a command and urgh, there was a typo. No worries, you bring up the command in an editor by typing:

SQL> ed

This opens up an editor, such as notepad.exe in Windows or vi in Unix, etc. And it puts the last SQL you entered in a file, oddly named, afiedt.buf. You don't like it and you want a name easier to type. Is it possible? Of course.

History of Afiedt.Buf

First a little bit of background information on the odd name. SQL*Plus as a tool evolved from another tool Oracle provided a long, long time ago, called--rather uncreatively--User Friendly Interface, or UFI. When the editor wanted to bring up a file for editing, the file had to be given a name unique enough so as not to conflict with anything else. Therefore the file was named ufiedt.buf, which roughly indicated UFI Editor Buffer.

Later as UFI was added new features, it was called Advanced UFI (well, "advanced" is relative). To keep pace, the generated file was called Advanced UFI Editor Buffer, or aufiedt.buf. However an operating system that Oracle supported couldn't handle a 7 character file name before the period before suffix. Therefore the name was shortened to afiedt.buf to keep it distinct from ufiedt.buf (which presumably was not as "advanced"). As the advancements in technology came, Oracle didn't bother to change it and the name stuck. Long live afiedt.buf!

Why Change It?

If ain't broken, don't fix it. Shouldn't that be the case? The name doesn't break anything; so why change it? There are three reasons to at least consider.

1. The name may difficult to type.
2. he extension of ".buf" is not automatically recognized by SQL*Plus as a SQL script.
3. The file is created in the present directory. Suppose you are in a directory you don't have permission on, e.g. "/etc" in Unix or \Windows in Windows, you can't just bring up the editor as you won't have the permissions to create the file there. You will get this error:

SP2-0110: Cannot create save file "afiedt.buf"

• What do you do? Exit from SQL*Plus, change to a proper directory and re-execute this command? Then you would lose all the settings you may have done in that session. So you would want to create the file in a location of your choosing.

So, while you mean no disrespect to Oracle's legacy, you would want to relegate afiedt.buf to where it belongs--to the footnotes of history.

How can I Change It?

It's ridiculously simple to change the file. A setting in SQL*Plus controls it.

SQL> set editfile arup.sql

After this when you type the editor command:

SQL> ed
Wrote file arup.sql

Look how the file created was named "arup.sql"; not afiedt.buf. Of course you can use any name as you find appropriate.

Changing Directory

Let's handle the second problem. You are in a directory where you don't have write permission and you want to edit the command. Well, you can use a full path to the file as well.

SQL> set editfile c:\temp\arup.sql
SQL> ed
Wrote file c:\temp\arup.sql

The file is created in the C:\temp directory instead of the default option of the current directory.

Changing the Editor

While on the subject, do you know that you can change the default editor as well? For instance, if you are on Windows, you don't have to have Notepad as the editor for SQL scripts. It can be notepad++, or, as I prefer, "vim", which is enhanced vi. In Unix, the default is not vi; it's "ed" and you may want to change it. How to do that?

Very simple; another setting _EDITOR allows us to do that. It's a variable; so you have to use DEFINE command. Use the following:

SQL> define _editor = vi

That's it; now when you enter "ed" command, it will bring up vi editor. Change to whatever you want to set.

Tips for Practical Uses

If you want to change the default editor and the default file for editing, put the commands in the glogin.sql file, which ensures their automatic execution when someone invokes SQL*Plus.

Afiedt.Buf is dead. Long live Afedit.Buf. Or, was it Afiedt.Buf? Oh, who cares?

Setting APPINFO in SQL*Plus

Ever used the MODULE column of V$SESSION view? If you haven't, you are missing out on a very important piece of instruemntation code built right into the Oracle database session management. This allows you to assign a completely arbitary name, as you would see will properly describe for your application. Later when you want to identify that session, you can check the MODULE column in V$SESSION. Even though the userid is the same for all these sessions; the module column will help you identify the session.

Let's see how it works by a little example. Here is how you check the module information.

select module
from v$session
where username = 'SYS'

MODULE
-----------------------------
sqlplus.exe
sqlplus.exe

In both cases the MODULE column shows the same value. You can set he module to a more descriptive name by issuing this command:

SQL> exec dbms_application_info.set_module('MyModule','MyAction')

PL/SQL procedure successfully completed.

Now if you check the module:

MODULE
---------------
MyModule
sqlplus.exe

As you can see the session where executed the packaged procedure has the descriptive module name. If you want to enable tracing, check the sessions stats, debug what's going on, etc., the ability to identify the session uniquely and accurately could be lifesaver.

But we had to execute the package dbms_application_info. The principles of least privileges says that we don't want to grant more privileges than absolutely necessary. While no one will argue against this being convenient, it will probably be hard to justify as "absolutely" necessary. So, what could you do to identify the session via the module? The generic module name "sqlplus.exe" is pretty much useless.

There is a much simpler solution. The SQL*Plus parameter appinfo comes to rescue. By default the setting is off. You can confirm that by issuing the following:

SQL> show appinfo
appinfo is OFF and set to "SQL*Plus"

To set to a value you want, use the follwoing:

SQL> set appinfo MyApp

Now if you check the sessions from another sessions:

SQL> select module
2 from v$session
3 where username = 'SYS';

MODULE
-------------------------------
MyApp
sqlplus.exe

See how the module is set to MyApp, which allows you to locate the session from many from the same user. And you could do that without calling the package.

What are the practical implications? The best usecase, I think is using this in the automatic login scripts such as glogin.sql. Put the following line in gloin.sql in $ORACLE_HOME/sqlplus/admin directory.

set appinfo "AcmeModule"

Now any session using that Oracle Home will have the Module column set to AcmeModule.

However you can also create local files called login.sql in individual directories and set appinfo apprpriately. For instance, suppose you have three major directories where you generally run the SQL scripts from. Create login.sql files on each directory. Directory "C:\App1" has a login.sql file with the following content:

set appinfo "App1"

Create login.sql files in the other directories with the appropriate contents. Now when someone connects to the database from SQL*Plus from those directories, the module will be set appropriately.
You will be able to know what directory the user has been in calling the scripts.

A caveat on the above, though. In Oracle 12.2, login.sql file is not executed, if a glogin.sql file is present. It is silently ignored. To make sure the local login.sql file is read, you have to explcitly set the variable ORACLE_PATH or SQLPATH to that directory.

Hope you find use of this little known setting in SQL*Plus.

E4 Session Developing Agnostic Data Services

Thank you for all attending my virtual session Developing Location and Technology Agnostic Data Services at Accenture Enkitec's E4 2017 Conference.

Here is the presentation deck for your reference. http://www.proligence.com/pres/e4_2017/e4_17_data.pdf

As always, I will appreciate your feedback--the good, the bad and the ugly.

NYOUG Session How Oracle Buffer Cache Works

Thank you all for coming to my session How Oracle Buffer Cache Works at New York Oracle User Group Summer General Meeting in New York City.

You can download the session and scripts used for the demo here.

Presentation : http://www.proligence.com/pres/nyoug17/nyoug17_buffercache.pdf
Scripts : http://www.proligence.com/pres/nyoug17/nyoug17_buffercache_scripts.zip

As I mentioned during my talk, you may find these blogposts helpful to understand this more:

How Oracle Locking Works http://arup.blogspot.com/2011/01/how-oracle-locking-works.html
Cache Buffer Chains Demystified http://arup.blogspot.com/2014/11/cache-buffer-chains-demystified.html
100 Things You Didn't Know About Oracle http://arup.blogspot.com/2010/12/100-things-you-probably-didnt-know.html
More on Buffer Clones http://arup.blogspot.com/2011/04/can-i-fit-80mb-database-completely-in.html

Pesentations at Great Lakes Oracle Conference 2017

For those who came to my sessions at GLOC 2017 in Cleveland, thank you for giving me your time. I hope you found them useful. As always, I love to hear your feedback. You can add a comment here, email me at arup@proligence.com, tweet at @ArupNanda or at www.Facebook.com/ArupKNanda.

All about Deadlocks The Presentation The Tracefile Used as an Example
How Buffer Cache Works The Presentation The Scripts
Big Data for Oracle DBAs The Presentation

Again, appreciate your coming to my session and thanks in advance for your feedback.

Passing Single Quotes in DBMS Assert Package

Today, while describing the usefulness of DBMS_ASSERT package to prevent SQL and PL/SQL Injection attacks someone asked me how to pass a string with single quotes successfully to this package.

First, if you don't know what DBMS_Assert is or why you should know about it, check out the presentation on this blogpost. In summary, the ENQUOTE_LITERAL() function strips off all the single quotes from around the string and replace with just a pair of single quotes, which makes it a clean, uninjected string. Here is an example of a string called Joe Pizza.

SQL> select dbms_assert.enquote_literal('Joe Pizza')  from dual;

DBMS_ASSERT.ENQUOTE_LITERAL('JOEPIZZA')
---------------------------------------
'Joe Pizza'

As you can see, the string is presented back with a pair of single quotes. Now let's see what happens if we put another pair of single quotes. To be syntactically correct, we will need to escape the single quote with another single quote.

SQL> select dbms_assert.enquote_literal('''Joe Pizza''')  from dual;

DBMS_ASSERT.ENQUOTE_LITERAL('''JOEPIZZA''')
-------------------------------------------
'Joe Pizza'

What happened? Well, the Assert package stripped off all extraneous single quotes and replaced them with just one pair of single quotes.

But what happens when we need to put a single single quote as a legitimate character, e.g. Joe's Pizza? This is where the escape sequence in SQL comes in. You can write this as follows:

SQL> select dbms_assert.enquote_literal(q'[Joe''s Pizza]')  from dual;

DBMS_ASSERT.ENQUOTE_LITERAL(Q'[JOE''SPIZZA]')
---------------------------------------------
'Joe''s Pizza'

That's it; "Joe's Pizza" is now perfectly passed.

Wait a minute. It's not Joe's Pizza; it's Joe''s Pizza. There are two single quotes; not one. That's not what we intended, did we? So it's wrong, right?

No; it's perfectly fine. You see, when you pass "Joe's Pizza" inside single quotes, you must escape the single apostrophe inside. The escape character is the single quote. Since the Assert package puts single quotes around the string, the single quote inside the string must be escaped to be syntactically correct. So, it makes sense to have two single quotes; not just one.

Joe's Pizza--if there is something in real world in that name--should probably pay me for the publicity.

Preventing SQL and PL/SQL Attacks at New York Meetup

Thank you all for attending my session at New York Meetup and New York Oracle User Group Spring Meeting in New York City on April 27th. I am truly honored by your presence, and especially for the questions.

You can download the presentation here And the scripts I used for the demos here. As always, I will appreciate your feedback either via comments here, or on social media or emails.

Twitter @ArupNanda
Facebook.com/ArupKNanda

Python for PL/SQL Developers Series

How hard it is to learn a new language? It depends on the learner, of course; but everyone will agree that it is easier to get your feet wet if you learn the corresponding structures of a language you are already proficient in. That's the principle behind my new article series on Oracle Technology Network: Python for PL/SQL Developers. If you are at least an intermediate level in PL/SQL, you will jumpstart your learning Python by learning the corresponding syntax elements, which is almost always a guaranteed way to learn the meat of the language fairly quickly. I learned most of the languages that way, and this is an experiment to do the same here as well.




Designed as a five part series, it starts off with the basics such as variables and assignments. then it gradually progresses to conditions, loops, functions, modules, etc. Finally, it explains how to use publicly available packages for data manipulation, including the ones to connect to the Oracle database. The idea is to have you up and running in Python by committing about an hour every day for a week.




Too busy to read? I have developed a video for each part. Watch it and go at it. Each part also has a quiz to test your knowledge gained from that part.




Here it is: http://bit.ly/python4plsql




As always, I will be successful only if you (and only you) like it. I will appreciate your honest feedback on this series. How you liked it (or, not); what are the strengths; what areas could have been improved and so on. Post as comments here, tweet @ArupNanda, post at www.FaceBook.com/ArupKNanda or just email me at arup@proligence.com.