PaxPax.me

External Password Store for Oracle Databases

Sometimes we have the need to arrange a set of instructions or scripts on automated processes, E.g when we automate a backup process, or when we prepare a batch process to load data in a database or even practical maintenance processes, like purging an audit table.

In the case of Oracle scripts, it's very common to use an externally authenticated user, this is, a user which is authenticated using an external identity, like the OS or a Kerberos service or an LDAP, however, besides the OS authentication, those are complex setups that includes more licensing costs and infrastructure.

If we're not running those process on the database box, a common practice is to use a designed user for those processes, however, those credentials are usually enclosed on the job managers or parent scripts, even worse, the execution line for those scripts are recorded on log entries (which they might also include the user credentials)

Oracle has a feature implemented on the OCI client named Secure External Password Store, it allows us to store credentials on an encrypted wallet and those can be accessed only by the client itself during the login operation.

Basic Syntax

We can create a new EPS wallet by using the "mkstore" command

mkstore -wrl <wallet_location> -create

Create Entry

Once created, we can manage its entries using the same command with the -createCredential parameter, just like this

mkstore -wrl <wallet_location> -createCredential <db_connect_string> <username> <password>

This way, each credential will be recorded using the connection string as its identifier, we'll be using this identifier for maintaining the credential information and for connecting into the database

Modify Entry

In order to modify an existing record, we can use the ´-modifyCredential´ command

mkstore -wrl <wallet_location> -createCredential <db_connect_string> <new_username> <new_password>

SQLNet Profile setup

After creating and setting up our wallet, we need to setup our sqlnet.ora file with the following entries

• WALLET_LOCATION
• SQLNET.WALLET_OVERRIDE

WALLET_LOCATION = 
  ( SOURCE = 
    (METHOD = FILE)
	(METHOD_DATA = 
	  (DIRECTORY = /path/to/eps)
	)
  )
SQLNET.WALLET_OVERRIDE = TRUE

Once everything is set, we can use the following connection string /@<db_connect_string> instead of <user>/<password>@<db_connect_string>

Troubeshooting

TOAD Always asks for username/password when using an EPS Wallet or external authentication

when we setup an EPS connection using TOAD from Quest we may find that the tool is always asking us for a username and a password, even if we setup the SQLNet profile properly as described above.

It's not intuitive, but if we input ´EXTERNAL´ as username, it will not request us a password and will try to use the external authentication defined on the SQLNet Profile, please note that we need to use the Oracle Client instead of a JDBC connection in order to make it work.

Update Gentoo Linux

Updating Gentoo is an easy process but it requires manual intervention, as with this distribution, it's very important to not skip any error and always inform yourself about what's going to be updated

Update dependency tree

eix-sync

Retrieve and compile all packages including their dependencies

emerge -avuDN --with-bdeps y --keep-going @world

Please note that we're using the --keep-going flag, in case any package won't be merged at first, we need to make a note of it and try again after all other dependencies got merged, in some cases the dependency link got broken or some packages needs to be updated beforehand, playing with the USE flags can also raise a lot of errors, mostly because some libraries were compiled with different profiles, will depend on each case what we should do in order to fix it.

Update configuration files

dispatch-conf

Never forget to check which configuration options are being updated

Delete obsolete packages

emerge -av --depclean

Cleanup using gentoolkit

revdep-rebuild

Cleanup

eclean -d distfiles

Troubleshooting

Masked packages

Some new package version are being masked, either because more testing is needed or because it hasn't been patched for a specific architecture or configuration set, the most common mask is ~amd64, if we want to go and install it we can add it on our packages.accept_keyword path in portage

tux ~ # cat /etc/portage/package.accept_keywords/libudev-compat
sys-libs/libudev-compat ~amd64

tux ~ # cat /etc/portage/package.mask/steam-launcher
>=games-util/steam-launcher-1.0.0.78-r1

!!! The following installed packages are masked:
- games-util/steam-launcher-1.0.0.78-r1::steam-overlay (masked by: package.mask)
For more information, see the MASKED PACKAGES section in the emerge
man page or refer to the Gentoo Handbook.

Batch processing in Oracle using a single PL/SQL Anonymous Block

As an Oracle consultant a very common activity is to perform commands in batch, for example: rebuild invalid indexes on a given schema, unlock all locked users on the database, recompile invalid stored procedures, etc.

One of the most common techniques I learnt and seen on field is to dynamically build the commands needed to be executed, put them on a script and after that execute it, for example, on a SQL Developer window or directly on SQL*Plus, while this approach works fine, it can't be easily automated, this is relevant specially when we have to automate this batch processing, like when we have to disable all indexes from a staging schema before loading data.

My personal favorite option for doing batch processing is to use dynamic sql execution inside a looped cursor, a technique I found on an old Oracle forum. below you may find an example of how to rebuild indexes from a specific schema

DECLARE
  c_schema_name CONSTANT VARCHAR2(30) := 'SCOTT';
BEGIN
  FOR c_r IN ( SELECT owner, index_name
                 FROM dba_indexes
                WHERE status != 'VALID'
                  AND owner = c_schema_name
             ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX "' || c_r.owner || '"."' || c_r.index_name || '" REBUILD';
  END LOOP;
END;
/

Above script can be enhanced by using parallel processing with APEX_PLSQL_JOB.SUBMIT_PROCESS, of course if the APEX subsystem is not installed on the database, the same feature can be implemented using DBMS_SCHEDULER or DBMS_JOB, however the later is deprecated and might be removed on future Oracle releases.

Generate a sequence of numbers using a SQL query

Sometimes we need to generate a sequence of numbers, maybe for cartesian products, or we want to work directly with number series using SQL expressions, for those cases I present a simple option using query recursiveness

Oracle

Using CONNECT BY operator

select level
  from dual
connect by level <= 10;

Using Query Recursive Subfactoring

with c_numbers (lvl) as
( select 1 as lvl
  from dual
 union all
  select lvl + 1
   from c_numbers
   where lvl < 10
)
select lvl
  from c_numbers
order by lvl;

MySQL / MariaDB

(borrowed directly from the MySQL manual) https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

Hello World!

Be welcome to this site! I am Nicko, The Drako PaxPax and I'll be your host during your stay here. mostly you'll find information about the projects I've been working for, and some handy stuff I've learnt across time.

And last but not least, I'll be using this space here as a personal journal were I'll be sharing some thoughts and experiences I had, I really hope you find this place handy and entertaining as well.