Manage your oracle users with Puppet
Since version 2.1.0 of ora_config, we can manage almost all properties you’d want to manage for an Oracle user. Versions before 2.1.0, where already able to manage settings like default tablespaces and quota’s. But since version 2.1.0 we can also manage grants, profiles, roles and passwords and other security properties. In this blog post, we are going to show you the possibilities.
The basics
The basics we need to manage a user is, of course, the ensure property. Most of the time, we also want to set a password. Here is a simple example manifest.
ora_user { 'scott':
ensure => 'present',
password => 'verysecret',
}
Like before, this makes sure the user scott exists in the database. If it is already there, Puppet will do nothing. If the user doesn’t exist, Puppet will make it and give it the specified password.
Here is the output from a database where the user didn’t exist:
$ puppet apply scott.pp
Notice: Compiled catalog for dbasmnfs.example.com in environment production in 0.12 seconds
http://www.enterprisemodules.com/ for details.
Notice: /Stage[main]/Main/Ora_user[scott]/ensure: created
Notice: Applied catalog in 2.14 seconds
What is new in version 2.1.0, is that the password has become a manageable property. This means you can use Puppet to change it. Let change the password in the manifest:
ora_user { 'scott':
ensure => 'present',
password => 'exposed',
}
and re-apply it:
$ puppet apply scott.pp
Notice: Compiled catalog for dbasmnfs.example.com in environment production in 0.12 seconds
http://www.enterprisemodules.com/ for details.
Notice: /Stage[main]/Main/Ora_user[scott]/password: changed to new value
Notice: Applied catalog in 2.14 seconds
As you can see in the output, Puppet recognized that the password was different, and changed it. Because it is a sensitive property, the output shows that the password is changed, but unlike changing other attributes, it doesn’t show the old and the new values.
Some more security Settings
Besides the password an Oracle user account also contains the security properties expired and the locked. Managing these with Puppet is as strait forward as can be. Let’s make sure the scott user is locked and expired.
ora_user { 'scott':
...
locked => true,
expired => true,
...
}
Let’s apply the manifest:
$ puppet apply scott.pp
Notice: Compiled catalog for dbasmnfs.example.com in environment production in 0.19 seconds
Notice: /Stage[main]/Main/Ora_user[scott]/expired: expired changed 'false' to 'true'
Notice: /Stage[main]/Main/Ora_user[scott]/locked: locked changed 'false' to 'true'
Notice: Applied catalog in 2.51 seconds
Et voila. The user is locked and expired.
But I don’t want to change the password on every run…
For applications users it is probably ok, to ensure these security attributes every twenty minutes on every puppet run. For interactive users, this, however, is not acceptable. We don’t want puppet to set the password back to the specified value after the user has changed it. We also don’t want to reset the expired property in every puppet run.
Fortunately the ora_user custom type has support for that. We can use the parameter create_only, to specify which properties are used only when creating the user and not used to update the user. Here is an example:
ora_user {'scott':
...
password => 'secret',
locked => true,
expired => true,
create_only => ['locked', 'expired', 'password']
...
}
This manifest will only manage the properties locked, expired and password when it needs to create the user, but will not use these properties on subsequent runs and leave them as they are.
The user profile
Since version 2.1.0 we are also able to manage the user profile. It has been added as a property for ora_user. Here is a simple example:
ora_user {'scott':
...
profile => 'default',
...
}
We have also added the ora_profile custom type, so you can manage the default profile and of course make your own named profiles. Here is an example on ensuring a non default profile:
ora_profile { 'non_default':
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'
}
As you can see all the attributes available in a profile can be managed. See the documentation for ora_profile for more information about this Puppet type.
Grants
You can also user the ora_user custom type to manage user grants. Let’s say the next two roles already exist: role_1 and role_2 and we want to grant them to scott. But only the role_1 needs to be granted by default. Here is the extended manifest to make that work:
ora_user { 'scott':
...
grants => ['role_1','role_2'],
default_roles => ['role_1'],
...
}
Let apply the manifest:
$ puppet apply scott.pp
Notice: Compiled catalog for dbasmnfs.example.com in environment production in 0.19 seconds
Notice: /Stage[main]/Main/Ora_user[scott]/grants: granted the ROLE_1,ROLE_2 right(s)
Notice: /Stage[main]/Main/Ora_user[scott]/default_roles: adding ROLE_1
Notice: Applied catalog in 2.80 seconds
As you can see, the roles are granted, and the default roles contain ROLE_1.
Start on your own database
This concludes our post on the new possibilities for ora_user in version 2.1.0. We hope this article has shown you how easy it has become to setup and manage your Oracle users using Puppet and the ora_config module. If you want to know more, please check out the documentation of ora_config for more information about this module and all the possibilities. If you are just starting, check out our FREE trial license to get going.
