Locking it down: Steps to Oracle database security
A DBA guide to securing your intellectual assets
Workshop How secure is your Oracle database? One of the DBA's roles is to ensure that the database is reliable and available, and to maintain the integrity of its data. Adequate system security is a big part of that process, and the more that you can do to lock down your database, the happier your compliance department and IT director will be.
Here are a few steps that you can take to secure your database from attack.
Secure your configuration during setup
When Oracle is installed, there are various security measures in the configuration that a DBA should be aware of. Most of these are changed by altering initialization parameters, which incidentally, you can do in templates to create standard secure builds. These configuration parameters let you do things including:
Securing the data dictionary The data dictionary is the set of tables that provides information about the whole database, including user names, privileges and roles, in addition to definitions for every object in the database. As such, it could be considered the crown jewels of the Oracle operating system, and should be locked down. You can do this using O7_DICTIONARY_ACCESSIBILITYsettings described here.
Restricting operating system access The operating system running the database needs a certain level of access to interact with it. But don't give it too much. You can remove all permissions to modify default file and directory permissions for the installation directory, and ensure that path variables to all database files aren't modifiable by untrusted users.
Finally, you can set limits on the number of operating system users who can connect to the local database (think administrative user accounts), and you can limit their privileges, too.
This privilege management is an important part of any Oracle security policy, and extends beyond just the host operating system. Users should be granted only those privileges that they need to carry out their responsibilities. The problem is that there will be many different users in an organization, and keeping track of all those privilege settings can be difficult. This is where roles come in useful.
A role has its own GRANT settings, configured by the DBA, and these can be used as templates for entire groups of users. This makes it possible to keep track of user privileges far more easily, and also to avoid GRANTing a user inappropriate access privileges by mistake. It's far easier to grant a department of 30 users the ability to update a catalogue this way, than without.
Toad for Oracle has a way of easily managing this. Browsing the database enables DBAs to bring up separate lists of users and roles. Each role contains detailed information about the privileges that it grants to manipulate specific database objects.
As a side note, be careful when granting access to application schemas, as applications can be a good ingress point for intruders.
Restricting privileges apply to DBAs, too. There may be some things that even the DBA shouldn't do, for compliance reasons. You can use the database vault feature in the database to secure certain things, such as groups of schemas, objects, and roles.
You can also create rules for database access restrictions in PL/SQL, and store these in the same groups, which are known as realms. The DBA can then give access to these realms to specific users. Perhaps only Rod on the security team should be allowed to approve patches, for example.
Manage your passwords
General password management is a general security concept that should be applied everywhere in IT, and your Oracle database is no exception. The database contains default passwords for all user accounts. Default passwords are a bad, bad thing. Find them using the DBA_USERS_WITH_DEF_PWD database view, and then ruthlessly delete them. Instead, create strong, distinct passwords. An alphanumeric password at least ten characters in length, containing mixed case and symbols, is a good idea.
During normal database operation, it’s a good idea to choose a system to manage your passwords. Enforce a policy that tracks failed log ins, and expires passwords after a set time (Oracle 11g has an option that configures default profiles to enforce the expiry and reuse of passwords).
Restrict network access
It's harder for people to hack your Oracle database if they can't connect to it. There are various methods that you can use to restrict access.
Firewalls are a common way to protect access to the server, as they can be configured to allow access via certain network routes only, and via certain ports. Another way to lock down network access is to protect the TNS Listener. This is a software process that handles connection requests from local or remote clients, and doles out server processes to them, enabling them to connect to the database.
Oracle says that it's better not to set a dedicated password for the listener. From Oracle 10g onwards, the listener authenticates to the operating system password if administered locally. Instead, it advises restricting privileges for the listener, so that it can't read or write files in the database or the Oracle address space. That'll stop any agents spawned by the listener from reading or writing to the database.
The other trick you can use to stop unauthorised connections across the network is to restrict them based on IP address. You can configure the listener to monitor specific IP addresses only, which can stop an unauthorised client from making a connection using an address outside that space.
Finally, use encrypted traffic across secure Oracle connections. You can do this by modifying the sqlnet.ora file on both the clients and the server to support any of several different encryption standards.
Secure data access
This encryption extends beyond data in flight. You can also encrypt data at rest, using transparent data encryption to encipher database table columns and tablespaces. This requires storing a master key in an Oracle wallet, and Oracle advises DBAs to use a separate wallet for this purpose, rather than sharing wallets used for other Oracle database components.
The performance impact of transparent encryption in Oracle might not be as bad as you'd think. Performance is only hit when you read or insert data from an encrypted column in a database. You may wish to restrict encryption to sensitive data only.
You can further restrict client access to sensitive data using Oracle Virtual Private Database, or Oracle Label Security. These two features serve broadly similar purposes, by masking data that should be kept from certain users. But they do it differently. For example, OLS (which must be licensed separately from the standard installation) lets you embed labels detail the sensitivity level of certain data types, but it won't let you mask entire columns, like VPD does. VPD comes standard with the database.
Conduct regular audits
In spite of all these efforts, you may still run into instances where users do things in the database that they shouldn't. If someone is deleting stock from the inventory and selling it on the side for personal profit, for example, that's something that the DBA should be able to bring to the company's attention.
We talk about the role of auditing in configuration and change management in depth in another article in this workshop series, but there are several ways to audit changes made to a database. One of them is the standard auditing feature in Oracle, which writes database changes to an audit log.
This feature can be configured to audit specific things, such as statements that require a specific privilege to run ('SELECT ANY TABLE', for example).
As any good security expert will tell you, nothing is ever 100 per cent foolproof. But you can make your database difficult enough to deter all but the most determined attackers. These days, all good security strategies focus on defense in depth, in which intellectual assets are secured at multiple levels. The steps above, combined with good user education, are a good start along the road to database protection. ®
The Register is running a series of Oracle DBA workshop articles in association with Dell Software.