A System for Oracle Users and Privileges with Automatic Expiry Dates

Tired of tracking down all the users in the database to deactivate them when they cease to exist, or change roles, or fulfill their temporary need to the database? Or, tracking down privileges you granted to existing users at the end of their requested period? The solution is to think out of the box - developing a system that allows you to create a database user account with an expiration date. This fire-and-forget method allows you to create users with the assurance that they will be expired (locked or dropped) at the expiration date automatically, without your intervention. Interested? Read on how I developed such a system--along with source code for you to try.

Last Successful Login Time in SQL*Plus in Oracle 12c

If you have been working with Oracle 12c, you may have missed a little something that appeared without mush fanfare but has some powerful implications. Let's see it with a small example--connecting with SQL*Plus.

C:\> sqlplus arup/arup

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 19 14:17:45 2013

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

Last Successful login time: Mon Aug 19 2013 14:13:33 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Did you note the line in red above?

Last Successful login time: Mon Aug 19 2013 14:13:33 -04:00

That line shows you when you last logged in successfully. The purpose of that little output is to alert you about the last time you (the user ARUP) logged in, very similar to the message you get after logging in to a unix session. If you didn't login earlier, this message will alert you for possible compromise of your account.

Suppression

What if you don't want to show this timestamp?

C:\> sqlplus -nologintime arup/arup

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 19 14:23:25 2013

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

The login time has been suppressed, going back to the old behavior.

Collaborate 2012 Sessions and Select Article

Thank you all who came to my sessions at #IOUG Collaborate 2012 #C12LV on April 22-24 in Las Vegas. I had four full sessions, two panels and one bootcamp. Quite a busy schedule, as you can see. I also worked on some urgent performance issues at work during the week.

You can download the the slides and scripts here. They are available from the IOUG site but I thought I would put them for download here as well.

• Rise of the Machines slides
• Secure Your Database in a Single Day slides scripts
• Beginning Oracle Performance Tuning slides scripts
• How Oracle Locking Works slides

My article Cache Fusion Demystified on SELECT Journal won the Editor's Choice award for 2011. This article is available for free download here.

I hope you like them. As always, feedback - good, bad and something in between - will be appreciated.

Update as of April 29th, 2012

I usually add more stuff to my slides after the session is over. This may result from a direct attendee feedback that some content was not clear, inadequate, misleading or even incorrect. This time is no exception. I added some more content to make the concepts clearer. Also many thanks to Charles Hooper for pointing out an Oracle documentation bug which makes two scripts in my session incorrect. I have since corrected the slides and re-uploaded. Please redownload the slides for How Oracle Locking Works and specifically look at the slides 14, 18, 24, 25 and 26. Also please download the scripts for Beginning Oracle Performance Tuning. More specifically, the script lock1.sql has changed.

Difference between Select Any Dictionary and Select_Catalog_Role

When you want to give a user the privilege to select from data dictionary and dynamic performance views such as V$DATAFILE, you have two options:

grant select any dictionary to ;
grant select_catalog_role to ;

Did you ever wonder why there are two options for accomplishing the same objective? Is one of them redundant? Won't it make sense for Oracle to have just one privilege? And, most important, do these two privileges produce the same result?

The short answer to the last question is -- no; these two do not produce the same result. Since they are fundamentally different, there is a place of each of these. One is not a replacement for the other. In this blog I will explain the subtle but important differences between the two seemingly similar privileges and how to use them properly.

Create the Test Case

First let me demonstrate the effects by a small example. Create two users called SCR and SAD:

SQL> create user scr identified by scr;
SQL> create user sad identified by sad;

Grant the necessary privileges to these users, taking care to grant a different one to each user.

SQL> grant create session, select any dictionary to sad;


Grant succeeded.

SQL> grant create session, select_catalog_role to scr;

Grant succeeded.

Let's test to make sure these privileges work as expected:

SQL> connect sad/sad

Connected.

SQL> select * from v$session;

 ... a bunch of rows come here ...

SQL> connect scr/scr

Connected.

SQL> select * from v$datafile;

 ... a bunch of rows come here ...

Both users have the privilege to select from the dictionary views as we expected. So, what is the difference between these two privileges? To understand that, let's create a procedure on the dictionary tables/views on each schema. Since we will create the same procedure twice, let's first create a script which we will call p.sql. Here is the script:

create or replace procedure p as
    l_num number;
begin
    select count(1)
    into l_num
    from v$session;
end;
/

The procedure is very simple; it merely counts the number of connected sessions by querying V$SESSION. When you connect as SAD and create the procedure by executing p.sql:

SQL> @p.sql


Procedure created.

The procedure was created properly; but when you connect as SCR and execute the script:

SQL> @p.sql


Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P:


LINE/COL ERROR
-------- ------------------------------------------------
4/2      PL/SQL: SQL Statement ignored
6/7      PL/SQL: ORA-00942: table or view does not exist

That must be perplexing. We just saw that the user has the privilege to select from the V$SESSION view. You can double check that by selecting from the view one more time. So, why did it report ORA-942: table does not exist?

Not All Privileges have been Created Equal

The answer lies in the way Oracle performs compilations. To compile a code with a named object, the user must have been granted privileges by direct grants; not through the roles. Selecting or performing DML statements do not care how the privileges were received. The SQL will work as long as the privileges are there. The privilege SELECT ANY DICTIONARY is a system privilege, similar to create session or unlimited tablespace. This is why the user SAD, which had the system privilege, could successfully compile the   procedure P.

The user SCR had the role SELECT_CATALOG_ROLE, which allowed it to SELECT from V$SESSION but not to create the procedure. Remember, to create another object on the base object, the user must have the direct grant on the base object; not through a role. Since SCR had the role not the direct grant on V$DATAFILE, it can't compile the procedure.

So while both the privileges allow the users to select from v$datafile, the role does not allow the users to create objects; the system privilege does.

Why the Role?

Now that you know how the privileges are different, you maybe wondering why the role is even there. It seems that the system grant can do everything and there is no need for a role. Not quite.The role has a very different purpose. Roles provide privileges; but only when they are enabled. To see what roles are enabled in a session, use this query:


SQL> connect scr/oracle
Connected.
SQL> select * from session_roles
  2  /


ROLE
------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE


2 rows selected.

We see that two roles - SELECT_CATALOG_ROLE and HS_ADMIN_SELECT_ROLE - have been enabled in the session. The first one was granted to the user. The other one is granted to the first one; so that was also enabled.

Just because a role was granted to the user does not necessarily mean that the role would be enabled. The roles which are marked DEFAULT by the user will be enabled; the others will not be. Let's see that with an example. As SYS user, execute the following:

SQL> alter user scr default role none;

User altered.

Now connect as SCR user and see which roles have been enabled:

SQL> connect scr/oracle

SQL> select * from session_roles;

no rows selected

None of the roles have been enabled. Why? That's  because none of the roles are default for the user (effected by the alter user statement by SYS). At this point when you select from a dynamic performance view:

SQL> select * from v$datafile;

select * from v$datafile

              *

ERROR at line 1:

ORA-00942: table or view does not exist

You will get this error because the role is not enabled, or active. Without the role the user does not have any privilege to select from the data dictionary or dynamic performance view. To enable the role, the user has to execute the SET ROLE command:

SQL> set role SELECT_CATALOG_ROLE;

Role set.

Checking the enabled roles:

SQL> select * from session_roles;

ROLE

------------------------------

SELECT_CATALOG_ROLE

HS_ADMIN_SELECT_ROLE

2 rows selected.

Now the roles have been enabled. Since the roles are not default, the user must explicitly enable them using the SET ROLE command. This is a very important characteristic of the roles. We can control how the user will get the privilege. Merely granting a role to a user will not enable the role; the user's action is required and that can be done programmatically. In security conscious environments, you may want to take advantage of that property. A user does not always have the to have to privilege; but when needed it will be able to do so.

The SET ROLE command is an SQL*Plus command. To call it from SQL, use this:

begin

   dbms_session.set_role ('SELECT_CATALOG_ROLE');

end;

You can also set a password for the role. So it will be set only when the correct password is given;


SQL> alter role SELECT_CATALOG_ROLE identified by l       
  2  /


Role altered.

To set the role, you have to give the correct password:

SQL> set role SELECT_CATALOG_ROLE identified by l;

Role set.

If you give the wrong password:

SQL> set role SELECT_CATALOG_ROLE identified by fl

  2  /

set role SELECT_CATALOG_ROLE identified by fl

*

ERROR at line 1:

ORA-01979: missing or invalid password for role 'SELECT_CATALOG_ROLE'

You can also revoke the execute privilege on dbms_session from public. After that the user will not be able to use it to set the role. You can construct another wrapper procedure to call it. Inside the wrapper, you can have all sort of checks and balances to make sure the call is acceptable.

We will close this discussion with a tip. How do you know which roles are default? Simply use the following query:

SQL> select GRANTED_ROLE, DEFAULT_ROLE
  2  from dba_role_privs
  3  where GRANTEE = 'SCR';


GRANTED_ROLE                   DEF
------------------------------ ---
SELECT_CATALOG_ROLE            NO

Update


Thanks to Randolph Geist (http://www.blogger.com/profile/13463198440639982695) and Pavel Ruzicka (http://www.blogger.com/profile/04746480312675833301) for pointing out yet another important difference. SELECT ANY DICTIONARY allows select from all SYS owner tables such as TAB$, USER$, etc. This is not possible in the SELECT_CATALOG_ROLE. This difference may seem trivial; but is actually quite important in some cases. For instance, latest versions of Oracle do not show the password column from DBA_USERS; but the hashed password is visible in USER$ table. It's not possible to reverse engineer the password from the hash value; but it is possible to match it to a similar entry and guess the password. A user with the system privilege will be able to do that; but a user with the role will not be.



Conclusion

In this blog entry I started with a simple question - what is the difference between two seemingly similar privileges - SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE. The former is a system privilege, which remains active throughout the sessions and allows the user to create stored objects on objects on which it has privileges as a result of the grant. The latter is not a system grant; it's a role which does not allow the grantee to build stored objects on the granted objects. The role can also be non-default which means the grantee must execute a set role or equivalent command to enable it. The role can also be password protected, if desired.

The core message you should get from this is that roles are different from privileges. Privileges allow you to build stored objects such as procedures on the objects on which the privilege is based. Roles do not.

Build a Simple Firewall for Databases Using SQL Net

This article was initially published in 2003 in DBAZine.com, which has since been folded.

So, you want to set up a secured database infrastructure?

You are not alone. With the proliferation of threats from all sources — identity thefts to corporate espionage cases — and with increased legislative pressures designed to protect and serve consumer privacy, security has a taken on a new meaning and purpose. Part of the security infrastructure of an organization falls right into your lap as a DBA, since it’s your responsibility to secure the database servers from malicious entities and curious insiders.

What are your options? Firewalls are first to come to mind. Using a firewall to protect a server, and not just a database server, is not a new concept and has been around for a while. However, a firewall may be overkill in some cases. Even if a firewall is desirable, it may still have to be configured and deployed properly. The complexity in administering a firewall, not to mention the cost to acquire one, may be prohibitive. If the threat level can be reduced by proper positioning of existing firewalls, the functionality of additional ones can be created by a tool available free with Oracle Net, Node Validation. In this article, you will learn how to build a rudimentary, but effective, firewall-like setup with just Oracle Net, and nothing else.

Background

Let’s see a typical setup. Acme, Inc. has several departments — two of which are Payroll and Benefits. Each department’s database resides on a separate server. Similarly, each department’s applications run on separate servers. There are several application servers and database servers for each department. To protect the servers from unauthorized access, each database server is placed behind a firewall with ports open to communicate SQL*Net traffic only. This can be depicted in figure 1 as follows:

Figure 1: Protecting departmental database and application servers using multiple firewalls.

This scheme works. But notice how many firewalls are required and the complexity that having this number adds to the administration process. What can we do to simplify the setup? How about removing all the firewalls and having one master firewall around the servers, as in Figure 2?

Figure 2: One master firewall around all the servers.

This method protects the servers from outside attacks; however, it still leaves inside doors open. For instance, the application server PAYROLL1 can easily connect to the database server of the Benefits Department BENEFITDB1, which is certainly inappropriate. In some organizations, there could be legal requirements to prevent this type of access.

Rather than creating a maze of firewalls as in the case we noted previously, we can take advantage of the SQL*Net Node Validation to create our own firewall. We will do this using only Oracle Net, which is already installed as a part of the database install. The rest of this article will explain in detail how to accomplish this.

Objective

Our objective is to design a setup as shown in figure 3. In this network, the application servers benefits1 and benefits2 access the database on server benefitsdb1. Similarly, application servers payroll1 and payroll2 access the database on server payrolldb1. Clients should be given free access to the intended machines. Client machines shouldn’t be allowed to access the database on other departments (e.g., benefits1 and benefits2 shouldn’t be able to access the database on payrolldb1). Likewise, application servers payroll1 and payroll2 should not be allowed to access benefitsdb1.

Figure 3: One master firewall and restricting access from non-departmental clients.

Note the key difference in requirements here — we are not interested in disallowing any type of access from client machines to servers of another department. Rather, it’s enough to disable access at the Oracle level only. This type of restriction is enforced by the listener. A listener can check the IP address of the client machine and, based on certain rules, decide to allow or deny the request. This can be enabled by a facility called Valid Node Checking, available as a part of Oracle Net installation. Let’s see how this can be done.

To set up valid node checking, simply place a set of lines on a specific file on the server. In our example, the following lines are placed in the parameter file on the server payrolldb1, allowing access to servers payroll1 and payroll2.

tcp.validnode_checking = yes

tcp.invited_nodes = (payroll1, payroll2)

Where this parameter file is located depends on the Oracle version. In Oracle 8i, it’s a file named protocol.ora; in Oracle 9i, it’s called sqlnet.ora. Both these files are located in the directory specified by the environmental variable TNS_ADMIN, which defaults to $ORACLE_HOME/network/admin in UNIX or %ORACLE_HOME%\network\admin in Windows.

These parameters are self-explanatory. The first line, tcp.validnode_checking = yes, specifies that the nodes are to be validated before accepting the connection.

The second line specifies that only the clients payroll1 and payroll2 are allowed to connect to the listener. The clients are indicated by either IP address (e.g., 192.168.1.1) or the node name as shown above. The list of node names is specified by a single line separated by commas. It is important to have only one line — you shouldn’t break it up.

The values take effect only during the startup of the listener. After making the change in protocol.ora (in Oracle 8i) or sqlnet.ora (in Oracle 9i and later), stop and restart the listener. After you’ve done so, if a user, regardless of the authentication in the database or authority level, attempts to connect to the database on benefits1 from the node payroll1, he receives the error as shown below.

$ sqlplus scott/tiger@payrolldb1

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 2o 9:03:33 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR: ORA-12537: TNS:connection closed

Enter user-name:

The error message is not very informative; it does not explicitly state the nature of the error. This error occured, however, because the connection request came from a client that is not listed as accepted. In this case, the listener simply rejected the connection originating from the node benefits1, regardless of the user. Yet the same user trying to connect from node payroll1 would succeed.

Excluded Nodes

In the previous example, we saw how to allow only a certain set of clients, and disallow all others. Similarly, you can specify the other type of rule — exclude some clients and allow all others. Say the lines in the parameter file are as follows:

tcp.validnode_checking = yes

tcp.excluded_nodes = (payroll3)

All clients but those connecting from payroll3 would be able to connect to all nodes. So, in this case, clients benefits1 and benefits2 would be able to connect to payrolldb1 in addition to clients payroll1 and payroll2. Isn’t that counter to what we wanted to achieve? Where can this exclusion be used?

In real life cases, networks are subdivided into subnetworks, and they offer adequate protection. In a particular subnet, there may be a greater number of clients needing access than the number being restricted. In such a case, it might be easier to specifically refuse access from a set of named clients, conveniently named in the tcp.excluded_nodes parameter. You can also use of this parameter to refuse access from certain machines that had been used to launch attacks in the past.

You can also mix excluded and included nodes, in which case, the invited nodes are given precedence over excluded ones. But there are three very big drawbacks to this approach.
1. There is no way to specify a wild card character in the node list. You must specify a node explicitly by its name or its IP address.
2. All excluded or invited nodes are to be specified in only one line, severely limiting your ability to specify a large number of nodes.
3. Since the validation is based on IP address or client names only and it’s relatively easy to spoof these two key pieces of identification, the system is not inherently secure.

For these reasons, mixing excluded and included nodes is not quite suitable for excluding a large list of servers from a network or subnetwork. This method can be used when the list of machines accessing the network is relatively small and the machines are in a subnetwork, behind a firewall. In such a configuration, the risk of external attacks is very slight, and the risk of unauthorized access by spoofing key identification is negligible.

Oracle Net also provides another means to develop a rudimentary firewall using a lesser known and even lesser used tool called Connection Manager. This tool is far more flexible in the setup; you can specify wildcards n-node names without restrictions such as the need to have only a single line for naming the nodes. A detailed discussion of Connection Manager with real-life examples can be found in the book Oracle Privacy Security Auditing.

Troubleshooting

Of course, things may not always proceed as smoothly as in the examples we’ve cited so far. One of the common problems you can encounter is that the exclusion may not work even though the files may be present and the parameters seem to be defined properly.

To diagnose a node checking issue you may encounter, you need to turn on tracing during the connection process. Tracing the process can be done in several levels of detail, and in this case, you should enable it for the level called support, or "16." Place the following line in the file sqlnet.ora:

trace_level_server = support

Doing this causes the connection process to write detailed information in a trace file under the directory $ORACLE_HOME/network/trace. The directory can be specified to a different value by a parameter in the file sqlnet.ora, as
trace_directory_server = /tmp

By doing this, the trace information to be written to the directory /tmp instead of the default. After setting the parameters as shown above, you should attempt the connection again. There is no need to bounce the listener. The connection attempt will create trace files named similar to svr_0.trc to be written in the proper directory. You should open this file in an editor (parts of the file are shown below).

[20-JAN-2004 12:00:01:234] Attempted load of system pfile
source /u02/oracle/product/9.2/network/admin/sqlnet.ora

[20-JAN-2004 12:00:01:234] Parameter source loaded successfully

[20-JAN-2004 12:00:01:234]

[20-JAN-2004 12:00:01:234] -> PARAMETER TABLE LOAD RESULTS FOLLOW <-

[20-JAN-2004 12:00:01:234] Successful parameter table load

[20-JAN-2004 12:00:01:234] -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-

[20-JAN-2004 12:00:01:234] tcp.validnode_checking = yes

[20-JAN-2004 12:00:01:234] trace_level_server = support

[20-JAN-2004 12:00:01:234] tcp.invited_nodes = (192.168.1.1, 192.168.1.2)

[20-JAN-2004 18:27:04:484] NAMES.DIRECTORY_PATH = (TNSNAMES)

[20-JAN-2004 18:27:04:484] tcp.excluded_nodes = (192.168.1.3)

[20-JAN-2004 18:27:04:484] --- PARAMETER SOURCE INFORMATION ENDS ---

These lines indicate that

1. The parameter file /u02/oracle/product/9.2/network/admin/sqlnet.ora was read by the listener.

2. The parameters were loaded successfully.

3. The contents of the parameter were read as they were mentioned.

4. The names of the excluded and invited nodes are displayed.

If the information is not as shown here, the problem be caused by the way the parameter file is written; most likely a typographical error such as a missing parenthesis. This type of error should be fixed before proceeding further along the trace file.

If the parameters are indeed loaded properly, you should next check the section of the file in which the node validity checking is done. This section looks like this:

[20-JAN-2004 12:30:45:321] ntvllt: Found tcp.invited_nodes. Now loading...

[20-JAN-2004 12:30:45:321] ntvllhs: entry

[20-JAN-2004 12:30:45:321] ntvllhs: Adding Node 192.168.1.1

[20-JAN-2004 12:30:45:321] ntvllhs: Adding Node 192.168.1.2

[20-JAN-2004 12:30:45:321] ntvllhs: exit

[20-JAN-2004 12:30:45:321] ntvllt: exit

[20-JAN-2004 12:30:45:321] ntvlin: exit

[20-JAN-2004 12:30:45:321] nttcnp: Validnode Table IN use; err 0x0

The first line indicates that the parameter tcp.invited_nodes was found. Next, the entries in that list are read and displayed one after the other. This is the most important clue. If the addresses were written incorrectly, or the syntax were wrong, the trace files would have indicated this by not specifying the node names checked. The last line in this section shows that the ValidNode table was read and used with error code of 0x0 (in hexadecimal, equating to zero) — the table has no errors. If there were a problem in the way the valid node parameters were written in the parameter file, the trace file would have shown something different. For instance, say the parameters were written as

tcp.excluded_nodes = (192.168.1.3

Note how a parenthesis is left out, indicating a syntax problem. However, this does not affect the connection; the listener simply ignores the error and allows the connection without doing a valid node checking. Upon investigation, we would find the root of the problem in the trace file. The trace file shows the following information.

--- PARAMETER SOURCE INFORMATION FOLLOWS ---

[20-JAN-2004 12:45:03:214] Attempted load of system pfile

source /u201/oracle/product/9.2/network/admin/sqlnet.ora

[20-JAN-2004 12:45:03:214] Load contained errors 14] Error stack follows: NL-00422: premature end of file NL-00427: bad list

[20-JAN-2004 12:45:03:214]

[20-JAN-2004 12:45:03:214] -> PARAMETER TABLE LOAD RESULTS FOLLOW <-

[20-JAN-2004 12:45:03:214] Some parameters may not have been loaded

[20-JAN-2004 12:45:03:214]

See dump for parameters which loaded OK This clearly shows that the parameter file had errors that prevented the parameters from loading. Because of this, the valid node checking is turned on and in use, but there is nothing in the list of the excluded nodes as shown in the following line from the trace file:

[20-JAN-2004 12:45:03:214] nttcnp: Validnode Table IN use; err 0x0

Since the error is 0x0, no error is reported by the validity checking routine. The subsequent lines on the trace file show other valuable information. For instance this line,

[20-JAN-2004 12:45:13:211] nttbnd2addr: using host IP address: 192.168.1.1

shows that the IP address of the server to which the listener was supposed to route the connection was 192.168.1.1. If all goes well, the listener allows the client to open a connection. This is confirmed by the following line:

[20-JAN-2004 12:45:14:320] nttcon: NT layer TCP/IP connection has been established.

As the line says, the TCP/IP connection has been established. If any other problems exist, the trace file will show enough helpful information for a diagnosis.

Summary

To summarize:

1. Node Validation can be used to instruct listeners to accept or reject a connection from a specific client.
2. The parameter file is sqlnet.ora in Oracle 9i and protocol.ora in Oracle8i.
3. The nodes must be explicitly specified by name or by IP Address; no wildcards are supported.

Mining Listener Logs

When is the last time you looked at the listener logs? Perhaps never. Not a very good idea. Listener logs contain a wealth of information on security events - it shows you the profile of hosts connecting to the database, the programs they are using and attempting to communicate but failed, among other things. But reading the listener logs is not exactly a pleasant experience. A while ago I wrote a series of articles on an online eZine called DBAZine.com on how to create an external table to read the listener logs using simple SQL; but unfortunately DBAZine.com has folded.

I have placed the articles on my website for your reference. As always, I would love to hear from you how you felt about these, stories of your own use and everything in between.

Mining Listener Logs Part 1
Mining Listener Logs Part 2
Mining Listener Logs Part 3

IOUG Webcast on Security

Many thanks to those who attended my webcast "Secure Your Database in a Single Day" for IOUG's wecast series. I hope you found it useful. I would highly appreciate if you take a moment to let me know how you felt - good, bad and ugly. Please write to me at arup@proligence.com.



You can find the scripts referenced in the webcast here.

Put External Procedures on a Different Listener

As a best practice, I recommend placing external procedures, if you use them, on a different listener.ora file. Why so?

External Procedures are actually O/S level programs executed by the listener, as the user "oracle" (or whatever the username for Oracle software is).

Let me repeat that: external procs allow database sessions to execute O/S level programs with the privilege level of the Oracle user! Do you see a problem here? Since the programs run as "oracle", they can do anything the user can do at the command line: "ls -l", "rm listener.log", or even "rm datafiles". Do you want that? Of course not.

Unfortunately there are several vulnerabilities that exist which exploit this particular feature. the CPU patches address some of them; but the bad guys respond by exposing even more holes. In this cat and mouse game, the best thing, in my opinion, is to reduce or even eliminate the possibility if feasible. If you don't use external procs (most people don't), why put them on the listener.ora file? Unfortunately the default config puts the external procs; so you should remove them and you can do that easily.

If you must use external procs, then I suggest using a different listener for that. Doing so allows you to shutdown that functionality while allowing normal database connectivity. If you perceive an imminent threat, you can take evasive action by shutting down the ext proc listener. You can't do that if the external jobs are on the same listener.

Again, consider this: what is the harm is doing that? Nothing. and what is the benefit? I just showed you, however small that may be. So, if you lost nothing and potentially gain something, why not do it?

Why should you set the ADMIN_RESTRICTIONS_LISTENER to ON

Recently someone probably went through the slides of my session on "Real Life DBA Best Practices" and had a question on OTN forum why I was recommending setting the parameter to ON, as a best practice. I responded on the forum; but I feel it's important enough to put it here as well.

As a best practice, I recommend setting this parameter to ON (the default is OFF). But as I profess, a best practice is not one without a clear explanation. Here is the explanation.

Over the period of time, the Oracle Database has encountered several security vulnerabilities, some of them on the listener. Some are related to buffer overflow. others involve unauthorized access into the listener process itself. Some of the listener access exploits come from external listener manipulations. Did you know that you do not need to even log into a server to connect to the listener? As long as the port the listener is listeneing on is open (and it will be, for obvious reasons) you can connect to the listener from a remote server.

In 10g, Oracle provided a default mechanism that does not require password from the oracle user manipulating the listener via online commands. Having said that, there have been bugs and there will be. Those vulnerabilities usually get fixed later; but most often the fix does not get to the software quickly enough.

So, what should you do to protect against these vulnerabilities? I consider a simple thing to do is to remove the possibilty altogether; and that's where the admin restrictions come into picture. After setting this parameter, you can't dynamically change the parameter. So, even though a connection is made somehow from an outside server - bug or not - eliminating the possibilty altogether mitigates the risk. And, that's why recommend it.

Let's ponder on the problem a little bit more. Is that a problem is setting the parameter? Absolutely not. When you need to change a parameter, you simply log on to the server, update the listener.ora and issue "lsnrctl reload". This reloads the parameter file dynamically. Since you never stopped the listener, you will not see unsuccessful conection requests from clients. So, it is dynamic. If you are the oracle user, then you can log on to the server; so there is no issue there.

I advocate this policy rather than dyanamic parameter changes, for these simple reasons:

(1) It plugs a potential hole dues to remote listener vulnerability attacks, regardless of the probabilty of that happening.
(2) It forces you to make changes to listener.ora file, which shows the timestamp.
(3) I ask my DBAs to put extensive comments on the parameter files, including the listener.ora file, to explain the change. I also ask them to comment a previous line and create a new line with the new value, rather than updating a value directly. This sort of documentation is a gem during debugging. Changing in the parameter file allows that, while dynamic change does not.

So, I don't see a single functionality I lose by this practice; and I just showed you some powerful reasons to adopt this practice. No loss, and some gain, however small you consider that to be - and that's why I suggest it.

As I mentioned earlier, a best practice is not one without a clear explanation. I hope this explanation makes it clear.