Securing the Oracle Listener

Oracle listener is a server process, which listens incoming connections on the specified port(default is 1521) and redirects them to the database server. It is not limited to database connections, but it also can be used to access executable programs on the database server. As you can guess executing external procedures sometimes is not secure, because listener can be used by hackers to execute malicious programs.

There are several security options:

1.Setting the password for listener

It can be accomplished by the several ways:

1.1. Add the following entry to the listener.ora file.

PASSWORD_LISTENER=YourPassword

–Reload the listener

lsnrctl>reload

Note that password, in this case, is a plain text.

1.2. Using CHANGE_PASSWORD command

lsnrctl> change_password
Old password:
New password:
Reenter new password:
....

Note that Old password option asks you to enter the old password for listener, but if it doesn’t exist(means that listener currently not have the password) press the Enter key(do not enter anything).

You should save the configuration of the listener. Before saving, set its password, because listener is governed by the password right now and for to save the configuration you should enter the password for the listener.

lsnrctl> set password
Password:
lsnrctl> save_config

–To check, open listener.ora file and you will see the entry like this:

#----ADDED BY TNSLSNR 06-FEB-2011 23:40:18---
PASSWORDS_LISTENER = ADD733DA61CD19A5
#--------------------------------------------

Note that the password is in an encrypted format.

2. Controlling the Access

Valid Node Cheching it the functionality by which access can be controlled for some specific hosts.

This functionality is implemented by manually adding some entries to sqlnet.ora file.

Valid entries are:

TCP.VALIDNODE_CHECKING – YES/NO, its value should be YES to enable valid node checking.

TCP.INVITED_NODES – values are ip addresses or hostnames, from where requests can be accepted.

TCP.EXCLUDED_NODES – values are ip addresses or hostnames, from where requests should not be accepted.

Note that just one option TCP.INVITED_NODES or TCP.EXCLUDED_NODES may be specified, not both of them. Because they are mutually exclusive. Also wild card values can not be used, each individual ip/hostname should be specified.

For example:

We need to configure oracle net configuration like that just request from 192.168.11.2 and Reichel can be accepted. To do this, we should add the following entries to sqlnet.ora file:

tcp.validnode_checking=yes
tcp.invited_nodes=(192.168.11.2, Reichel)

–Restart the listener

lsnrctl> stop
lsnrctl> start

3. Using the Listener Logging

Enabling this feature gives you an ability to track down all listener activities. For example, it may contain brute force password attack symptoms. Error TNS-01169 in the log file indicates that someone was trying to enter the password for the listener but failed.

To turn on the logging, the following parameters should be used:

LOG_DIRECTORY – location where log files should be created.

LOG_FILE – name of the log file.

LOG_STATUS – ON/OFF , to enable logging set its value to ON.

lsnrctl> set log_directory C:\oracle\product\10.2.0\oralsnrlogs
lsnrctl> set log_file listener_ora.log
lsnrctl> set log_status on

You should save the configuration. Restarting listener is not necessary.

4. Removing External Procedure Services

This service is defined in listener.ora file.

–listener.ora file.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = ORCL)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Mariami-PC)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

Underlined rows need to be removed. After removing reload the listener.

5. Creating a Separate Listener for External Procedures

I think that this choice is better than explicitly removing external procedure entries from listener.ora file. You can run another lister, which listens just external procedures, under limited operating user or limit the libraries from which procedures can be executed.

5.1 Executing listener under limited OS user

When external procedure is called it runs the extproc agent process, which inherits privileges from OS user under which the listener process was started.

This user should not have any permission to access files owned by oracle user account.No permission to read data files. For this user, listener.ora file must be readonly.

5.2 Limiting libraries

As I said you can  limit libraries from where external procedures can be executed. You can do it by the following way:

–In listener.ora file

(ENVS="EXTPROC_DLLS=ONLY:/usr/libjava.so:/usr/lib/libz.so,PATH=$PATH")

ENVS -is used to define any environment variable.
EXTPROC_DLLS -list of libraries that can be accessed,list is separated by colon.
ONLY – only specified libraries list.

6. ADMIN_RESTRICTIONS Parameter

If the value of this parameter is ON in listener.ora file, than listener file can only be modified manually. It disallows all SET commands.

Above post is extracted and modified from Sybex.OCA.Oracle.10g.Administration.II.Study.Guide.1Z0-042

Oracle Database Resource Management (DRM)

Oracle Database Resource Manager is a pretty useful utility for DBAs. It simplifies distribution of resources(CPU time, idle time…) between users.

Three main elements are included in DRM:

1. Resource Consumer Groups
Resource Consumer Groups are groups to which user sessions can be assigned.
2. Resource Plans
It consists of Resource Plan Directives which decide how resources can be allocated for Resource Consumer Groups(same as user sessions).
3. Resource Plan Directives
As I said previously, it decides how resources should be distributed between users.

Resources are:
CPU usage, idle time, process execution time, number of sessions, etc.

After a brief summary about what DRM is, let’s use it in the example.

0. Privilege Needed for to use DRM

You must have ADMINISTER_RESOURCE_MANAGER system privilege to use it. This privilege is the default for DBA role.

You know how to grant privileges to users, by the following way:

grant privilege to user;

But this privilege(ADMINISTER_RESOURCE_MANAGER) can’t be granted by this way, you must use the following syntax:

begin
 dbms_resource_manager_privs.grant_system_privilege(
    grantee_name   => 'hr',
    privilege_name => 'administer_resource_manager',
    admin_option   => false);
end;

1. Creating Pending Area

First step is to create a Pending Area. This is like a room in which resource management objects are created and validated before their activation.

begin
 dbms_resource_manager.create_pending_area();
end;

2. Creating Resource Consumer Groups

For the first point you must define unique group name and the description.

begin
 dbms_resource_manager.create_consumer_group(
    consumer_group => 'group1',
    comment => 'This is the first resource consumer group');
end;

begin
 dbms_resource_manager.create_consumer_group(
    consumer_group => 'group2',
    comment => 'This is the second resource consumer group');
end;

–To see what resource consumer groups are in your database, query the following:

select *
from dba_rsrc_consumer_groups

The result may surprise you, because there will be some additional groups. By default there are four resource consumer groups:

DEFAULT_CONSUMER_GROUP – Users who are not assigned to some initial group, this will be default.

OTHER_GROUPS – Users who are assigned to groups, that are not part of the currently active plan, will become a member of this group. You can not assign users to this group manually.

SYS_GROUP – It is used by Oracle-provided SYSTEM_PLAN.

LOW_GROUP – It is used by Oracle-provided SYSTEM_PLAN.

3. Creating Resource Plans

begin
 dbms_resource_manager.create_plan(
    plan=> 'plan1',
    comment => 'This is the first resource plan');
end;

begin
 dbms_resource_manager.create_plan(
    plan=> 'plan2',
    comment => 'This is the second resource plan',

    cpu_mth=> 'ratio');
end;

Note:
cpu_mth is a CPU allocation method, the valid values are:

EMPASIS -Allocated amount is considered as a percentage(the default).

RATIO     -Allocated amount is considered as a ration of total CPU.

4. Creating Resource Plans Directives

–Creating Resource Plan Directives for plan1

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan1',
    group_or_subplan => 'sys_group',
    comment => 'Alocating CPU resource for sys_group',
    cpu_p1 =>90 );
end;

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan1',
    group_or_subplan => 'group1',
    comment => 'Alocating CPU resource for group1',
    cpu_p1 =>10 );
end;

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan1',
    group_or_subplan => 'other_groups',
    comment => 'Alocating CPU resource for other_groups',
    cpu_p2 =>100 );
end;

Note: 90%  of CPU resources is allocated on level 1 for sys_group, and 10% for group1.
100% of CPU resources is allocated on level 2 for other_group.

Groups at level 2 will share CPU resources not used by level 1 groups. And groups at level 3 will receive CPU time after level 1 and level 2.

–Creating Resource Plan Directives for plan2

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan2',
    group_or_subplan => 'sys_group',
    comment => 'Alocating CPU resource for sys_group',
    cpu_p1 =>4);
end;

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan2',
    group_or_subplan => 'group2',
    comment => 'Alocating CPU resource for group2',
    cpu_p1 =>4 );
end;

begin
 dbms_resource_manager.create_plan_directive(
    plan=> 'plan2',
    group_or_subplan => 'other_groups',
    comment => 'Alocating CPU resource for other_groups',
    cpu_p2 =>1 );
end;

Note: CPU allocation ratio for plan2 will be 4:4:1.

5. Validating the Pending Area

Validation ensures that:
1.Objects defined in a pending area exist,
2.All plan directives refer to either plans or resource groups(Plan directive can refer to plan(this plan is considered as a sub-plan) or consumer group).
3.Allocation percentage for a single leven can not exceed to 100%.
4. No deletion is allowed for top plans being used by an active instance.
5.An active plan schema can contain no more than 32 resource consumer groups.
6. Plan names cannot conflict with group names.
7.There must be a plan directive for OTHER_GROUPS.
8.No plan schema can contain loop.

If any of them are violated, error will occur and validating procedure fails.

begin
 dbms_resource_manager.validate_pending_area;
end;

6. Submitting the Pending Area

Submitting the pending area does the following steps: validates,submits and clears pending area. We validated it before submitting, because it is the preferable choice to check that everything is OK, before submit it(Move it to the data dictionary).
After submitting pending area, created plans are not activated! It just prepares plans for activation.

begin
 dbms_resource_manager.submit_pending_area;
end;

7. Activating the Resource Plans

There are two ways, to activate plan:

1. ALTER SYSTEM
2. RESOURCE_MANAGER_PLAN

The first method:

alter system set resource_manager_plan='plan1' [scope=both];

Note: [scope=both] is optional, you can choose it if you want to make changes permanent. The option both means memory and spfile(database must be started by spfile).

The second method:

In the init.ora file add the following:

RESOURCE_MANAGER_PLAN=plan1

8. Switching between Resource Plans

alter system set resource_manager_plan='plan2' [scope=both];

System altered. 

alter system set resource_manager_plan='plan1' [scope=both]; 

System alered.