Tag Archives: Oracle

Oracle RDBMS: Easily determine what parameters are set to using sqlplus without Oracle Enterprise Manager (OEM)

This is probably unnecessaryOracle for many of the Oracle RDBMS gray beards out there ;-) but if you’re asked to provide the number of client connections. Just fire up sqlplus or whatever tool of choice:

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     784
shared_server_sessions               integer
SQL>  show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     2
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     10
log_archive_max_processes            integer     4
processes                            integer     500

Not sure what the difference or the relationship is between processes and sessions are? It was asked in DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS over at Ask Tom.

Ask Tom

Share Button

HOWTO: List users that perform os authentication to log into the Oracle database instance (external users)

Oracle will Oraclerequire you to configure the Oracle RDBMS to use operating system authentication but if you inherit an Oracle instance, you will want to disable os authentication when possible. When the OS_AUTHENT_PREFIX is set, any os user that is created with “IDENTIFIED EXTERNALLY” will have the prefix. For example, in the below example, the value is “ops$”. The os user johnnybgood will have an internal Oracle user id of “ops$johnnybgood”. It’s a handy way to quickly identify such users but it isn’t full proof.

show parameter OS_AUTHENT_PREFIX
lang-NAME                            TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$

To determine exactly which users are set up for os authentication run the following query:

set pagesize 5000
set linesize 999
set trimspool on

select gn.GLOBAL_NAME as "Instance", username, authentication_type
from dba_users du, global_name gn
where authentication_type = 'EXTERNAL'

Now, there are valid reasons to use os authentication but I would push LDAP long before os authentication.

From the Oracle Security Admin Guide:

Advantages of External Authentication

Following are the advantages of external authentication:

More choices of authentication mechanism are available, such as smart cards, fingerprints, Kerberos, or the operating system.

Many network authentication services, such as Kerberos support single sign-on, enabling users to have fewer passwords to remember.

If you are already using some external mechanism for authentication, such as one of those listed earlier, then there may be less administrative overhead to use that mechanism with the database as well.

Share Button

FW: Hardening the Oracle 11g Database – Initial Steps (Kevin Sheehan)

Kevin Sheehan wrote how to harden the Oracle 11g RDBMS. I highly recommend it:

TheOracle following is a basic set of hardening guidelines for an Oracle 11g database along with some scripts you may find useful. This list is by no means complete. It does not cover file permissions, authentication controls and user profiles, encryption, grants or auditing but it is a good place to start.

Or perhaps takes Slavic’s advice from his comment on this post and start with Oracle’s Checklist for Database Security. It gives a broader, though perhaps less detailed view, and covers some of the topics I have left out (for now). Also check out Slavik’s Musings on Database Security Blog.

Read more on Kevin’s blog.

Share Button

HowTo: List locked, expired and to expire dates for Oracle logins query for sqlplus/scripting


set pagesize 5000
set linesize 999
set trimspool on

column "Expire Date" format a20
column "Locked Date" format a20

    gn.GLOBAL_NAME as "Instance",
    du.expiry_date as "Expire Date",
    du.lock_date as "Locked Date",
    dp.limit as "Profile Password Expiration"
from dba_users du, global_name gn, dba_profiles dp
where (du.account_status IN ( 'OPEN', 'LOCKED' )  OR du.account_status like '%EXPIRE%')
  and du.profile = dp.profile
  and dp.resource_name = 'PASSWORD_LIFE_TIME'
order by du.account_status, du.expiry_date, du.username

Updated to show which login profile is being by a user and what the password expiration interval is per the login profile.

Share Button