Oracle User Management Best Practices
This article is based on the author's own research and testing and does not guarantee the accuracy or completeness of the information provided.
If you use the information in this article, you do so at your own risk. The author assumes no responsibility for any issues or damages resulting from its use.
Oracle User Management Best Practices
My personal best-practice series lol.
I will not cover things used individually, such as development environments, here. This is specifically about best practices for production environments (and staging environments shared by multiple people).
* I am not an Oracle master, so this may contain incorrect information. Please keep that in mind.
* I am writing down some best-practice-like guidelines on how access users should be managed for databases in production environments.
* These ideas may also apply to permission management for applications and operating systems, not just databases.
* This topic was written in the heat of the moment when someone messed something up...
* This is a rewrite of an old article, so some parts may differ from the current Oracle specifications or UI.
Terminology
Let me define a few terms first.
- DBA
Short for Database Administrator. A database administrator. A person/account that can do anything to the database (has all privileges). - Default user
[第21回] Users created by default from the beginning - Production environment
The production/operational environment. - Staging environment
An environment used for verification, etc. It is used for pre-production verification and testing. Unlike individual developers' development environments, it is shared by multiple people.
Best Practices
Grant privileges to roles, not users
Oracle provides a mechanism for handling roles (ROLE), so instead of granting privileges to individual users, assign privileges to roles and configure individual users to belong to those roles.
Reference: Checking / Creating / Granting / Changing / Revoking / Deleting Roles
Give users the minimum access privileges they need.
When you want to make management less restrictive, you may be tempted to give users many privileges, but this can introduce security risks.
For important systems, give users only the minimum privileges they need to perform their work.
Perform auditing
Oracle provides mechanisms for auditing login attempts and operations.
If auditing is disabled, enable it so that sufficient auditing can be performed.
Lock default users whenever possible.
[第21回] Users created by default from the beginning are among the easiest accounts for attackers to target, so it is preferable to lock them whenever possible.
For accounts that cannot be locked, set sufficiently strong passwords.
Do not make default users DBAs
It is easy to make default users such as SYS/SYSTEM DBA users, but instead, create individual DBA users for the people who perform DBA operations. There are the following reasons.
- Auditing
When a shared privileged account is used, it is troublesome to audit and track which user performed a DBA operation. Auditing is easier when individual users are provided. - Password management
Even for DBA users, by ensuring that each person does not know anyone else's password, password management becomes easier when someone leaves the company. This is also effective from a security and auditing perspective.
Separate DBA and developer roles
During application development, especially in development environments, the DBA and developer are often the same person.
However, in production environments, DBAs and developers are usually separate.
Create different roles for these two roles (jobs) and assign privileges separately.
Eliminate anonymity for application/DBA users
The user used by an application may inevitably become a shared/representative user.
Application representative users often have powerful privileges, and even if the account information is leaked, it may be difficult to change the password.
However, even after an operations staff member leaves the company, they may still be able to access the system using a password they used in the past.
For applications that directly access the database and for personnel responsible for database administration and operations, it is preferable to create individual accounts in order to eliminate anonymity and enforce strict access control.
Nest roles
Oracle roles can be nested.
I have never configured this personally, but I have seen setups such as the following:
DEVELOPERrole for developersMAINTAINERrole for database maintenance personnelMANAGERrole with bothDEVELOPERandMAINTAINERprivileges
When the privileges of DEVELOPER are updated, the MANAGER role is also updated.
When there are users whose jobs involve multiple tasks, nesting roles may make management more flexible and reduce management costs.
Do not use the CONNECT/RESOURCE roles.
Some roles such as CONNECT are built into Oracle, but these roles should not be used.
Since 10g R2, the CONNECT and RESOURCE roles have been deprecated based on the principle of least privilege.
Overview of Oracle CONNECT and RESOURCE Roles
※ The privileges included in these roles seem to differ depending on the version, so using them without understanding them can be dangerous.
Be careful with PUBLIC
When privileges are granted to PUBLIC, anyone can use those privileges.
Determine roles based on use cases
Consider the following based on actual operations.
As much as possible, create and visualize an access matrix or something similar to organize the privileges.
- Users
Who are the users that access the database? What types of users are there? - Privileges
What privileges are required for each type of user (actor)?
Summary
When a system is still in development, it may be difficult to decide everything, but define users and roles as much as possible.
Review them at appropriate intervals, such as when adding a new application or a new feature.
When a problem occurs, it is also a good opportunity to review them.