How to deal with MongoDB shell output in your shell scripts – Part 2/2: parsing JSON with jq

If you read my last post, you now know how to get a valid JSON output from your MongoDB shell.

But raw JSON is often useless in your scripts because you have to manipulate it to extract only the information you need.

For this purpose, I personalty use a free tool called “jq“. It is very powerful and has many advanced features. I will only show you some basic concepts that will allow you to simply deal with JSON data in your scripts.

Continue reading “How to deal with MongoDB shell output in your shell scripts – Part 2/2: parsing JSON with jq”

How to deal with MongoDB shell output in your shell scripts – Part 1/2: produce valid JSON output

Today, I will show you how to easily deal with MongoDB shell output in your shell (bash, ksh, …) scripts.

For readybility purpose, this topic will be divided into 2 posts. This part will focus on how to produce valid JSON output from your MongoDB shell using the “–eval” option. Second part will focus on how to use “jq” to manipulate JSON data.

Continue reading “How to deal with MongoDB shell output in your shell scripts – Part 1/2: produce valid JSON output”

Get rid of clear passwords from your shell scripts with Oracle Secure External Password Store

As a DBA (or developer), you often need to make connection to your Oracle database from shell scripts.

That’s not a big deal if you connect locally to the database through the Linux/Unix account that owns the instance with “/ a sysdba”. But what if you need to connect to a remote database or a local database with a specific user?

Here are the solutions I have most often seen:

  • Clear password in the shell script
  • Clear password in a parameter file loaded from the shell script
  • Environnement variable with clear password
  • Function which decrypt a encrypted password, stored in a parameter file loaded from the shell script (more secure but easily breakable)

As you might have guessed, none of these solutions is secure!

Fortunately, Oracle provides us a free feature that allows you to get rid of clear passwords from your shell scripts:Secure external password store (also known as “SEPS” or “Oracle Wallets”).

The only prerequisite is to use Oracle 10gR2 or onward.

Continue reading “Get rid of clear passwords from your shell scripts with Oracle Secure External Password Store”

Get chunk distribution for all databases in a MongoDB cluster

One of the most important tasks for maintaining good performance in a sharded MongoDB cluster is to ensure optimal chunk distribution across each cluster member.

I’m not going to talk about picking a good shard key for your collections because the MongoDB documentation on the subject is pretty good. But I will share with you a script that I use to get the chunk distribution for all databases on a MongoDB sharded cluster.

Continue reading “Get chunk distribution for all databases in a MongoDB cluster”

COUNT and “GROUP BY” with awk

I want to share with you a small shell script that I often use for reporting purposes, which allows you to simulate the famous “count and group by” well known from DBAs. No database is required. It only use “awk” which is installed with each Linux / Unix distribution (I think?), so you can use it “out of the box”.

In other words: the script allows you to count the number of time a word appears in a text file and display a formatted result.

Continue reading “COUNT and “GROUP BY” with awk”

Hide table content to users with “SELECT ANY TABLE” privilege

Today, we will see how to hide sensitive data in your Oracle database from other users with the “SELECT ANY TABLE” privilege using Oracle Virtual Private Database (VPD).

First of all: in a perfect world, this situation should never happen!
If you need to store sensitive data in your database, you should place it in a dedicated location, without any other schema and with strict security rules.

Anyway, if you choose to tread this post, you probably have no other choice.

Continue reading “Hide table content to users with “SELECT ANY TABLE” privilege”