Customizing your Oracle database installation through hiera data

No database is equal. So after a basic installation, you need some way to customize the database setup. Our ora_profile module allows you to do this by just adding some hiera values.

Overview

In our last post, we discussed how to setup an Oracle database using Enterprise Modules’ ora_profile. We talked about which modules you needed to pull in from the Forge and some basic hiera data overrides to get up and running. In this post, we’re going to discuss some of the additional overrides you can do to set up and manage tablespaces, profiles, users, and security rules.

Managing tablespaces, profiles, and users

In this section, we’re going to discuss how to manage the default tablespace. We’re also going to create a couple of new tablespaces and manage them. Then, we’ll play around with managing profiles and users.

Managing tablespaces

In our last post, we created a database called DB01. When we did this, a tablespace called USERS was created. In fact, whenever a template database or a database with the ora_database type is created. this tablespace is always created. To manage this tablespace, we add the following to our hiera data:

ora_profile::database::db_tablespaces::list:
  'USERS@DB01':
    ensure: 'present'
    autoextend: 'on'
    contents: 'permanent'
    extent_management: 'local'
    logging: 'yes'
    max_size: 'unlimited'
    size: '5M'
    num_datafiles: 1

To see a full list of what you can manage, check out the ora_tablespace documentation.

Since the ora_profile code uses create_resources, you can structure your hiera data to take advantage of this and easily create new tablespaces. For example, add the following to your hiera data to create the tablespaces TS_INDEX and TS_TABLE:

ora_profile::database::db_tablespaces::list:
  'TS_INDEX@DB01':
    ensure: 'present'
    autoextend: 'on'
    contents: 'permanent'
    extent_management: 'local'
    logging: 'yes'
    max_size: 'unlimited'
    size: '5M'
    num_datafiles: 1
  'TS_TABLE@DB01':
    ensure: 'present'
    autoextend: 'on'
    contents: 'permanent'
    extent_management: 'local'
    logging: 'yes'
    max_size: 'unlimited'
    size: '5M'
    num_datafiles: 1

So what did we do here? Well, we made use of the ora_profile::database::db_tablespaces::list to create a list of database tablespaces. We named the first one TS_INDEX@DB01 and gave it a bunch of parameters. Same with the second. Super easy, right? Well guess what? It’s the exact same process for managing users and profiles.

If you’d like to know more of the options available, check the ora_profile documentation.

Managing users

Since ora_profile also uses create_resources for users, you can do the same hiera data magic to create and manage all of your users:

ora_profile::database::db_users::list:
  'curly':
    ensure: 'present'
    password: 'ConsiderUsingEyaml'
    create_only: ['password']
  'moe':
    ensure: 'present'
    expired: true
    locked: true

This is all fine and good, but you might not want to manage the password for interactive users because they might be required to change their password and not do so through Puppet. In cases like this, you can use the create_only parameter. The create_only parameter will create the values initially, but then not manage them after that. So, let’s say that we have a user named larry and we want this user to be present and set up an initial password, but that’s it. In this case, we would:

ora_profile::database::db_users::list:
  'larry':
    ensure: 'present'
    password: 'changeme'
    create_only: 'password'

We can also apply create_only to multiple things. In this next example, we’re going to manage a new user, shemp, but only create the expired, locked, and password settings:

ora_profile::database::db_users::list:
  'shemp':
    ensure: 'present'
    expired: true
    locked: true
    password: 'changeme'
    create_only: [ 'expired', 'locked', 'password' ]

To see a full list of the attributes managed by ora_user, check the ora_user documentation.

Managing profiles

Now that we’ve learned how to manage tablespaces and users, it will be quick and easy to learn how to manage profiles. Spoiler alert: it’s done the same way.

Let’s go ahead and manage the default profile:

ora_profile::database::db_profile::list:
  'default@DB01':
    ensure: 'present'
    composite_limit: 'UNLIMITED'
    connect_time: 'UNLIMITED'
    cpu_per_call: 'UNLIMITED'
    cpu_per_session: 'UNLIMITED'
    failed_login_attempts: '10'
    idle_time: 'UNLIMITED'
    logical_reads_per_call: 'UNLIMITED'
    logical_reads_per_session: 'UNLIMITED'
    password_grace_time: '7'
    password_life_time: '180'
    password_lock_time: '1'
    password_reuse_max: 'UNLIMITED'
    password_reuse_time: 'UNLIMITED'
    password_verify_function: 'NULL'
    private_sga: 'UNLIMITED'
    sessions_per_user: 'UNLIMITED'

If you’d like to manage a different profile, just name it and give the database it corresponds to: something like non_default@DB01.

And that’s all there is to it. For more information on what profile settings you can manage, check the ora_profile documentation.

Overriding security rules

Overriding security rules is super easy with the ora_cis module. With this module, you can modify the rules for all of your databases, or you can just modify the rules for specific databases. You can also change the values for each rule.

Let’s say that we’d like to ignore Rule 1.1 and Rule 2.1.1 (ensuring the appropriate version/patch and SECURE_CONTROL_). To override these rules for all of our databases, all we have to do is specify the following in hiera:

ora_cis::ignore:
  - r_1_1
  - r_2_1_1

To only ignore these values for our default database, DB01, we just specify that SID in lowercase:

ora_cis::db01::ignore:
  - r_1_1
  - r_2_1_1

Now, let’s say that we like some of the rules, but not the default values. Rule 1.2 is:

1.2 Ensure All Default Passwords Are Changed (Scored)

The default password_length is 12. Let’s assume our our security team has mandated that passwords must be 16 characters long. We can easily change this with hiera data:

ora_cis::rules::r_1_2::password_length: 16

Each of the rules follow the same format, so feel free to tinker with each rule and customize as you’d like. And, as always, a full list of configurable options can be found in the ora_cis documentation.

What’s next?

As you’re going through these customizations, you’ve probably said to yourself, “Do I really have to include all these classes? We’re already doing a bunch of this in-house.” We hear you loud and clear which is why we’ve included the ability to skip any classes you don’t need. In our next post, we’ll cover how to do this. We’ll also discuss how you go about inserting extra classes before or after other steps. This is useful when your current implementation is okay, but you need to do a few extra things.