Before Creating a User, Decide on the Right Attributes in Oracle 23ai

Creating a database user in Oracle 23ai is not merely an administrative formality. It is a design decision that affects security, storage management, auditing, access control, and long-term maintainability. A DBA should decide on user attributes before issuing CREATE USER so that each account aligns with the person’s job function, application purpose, and operational boundaries inside the database.

In a well-managed Oracle environment, a user account should reflect the principle of least privilege. That means the account receives only the privileges, quotas, tablespaces, and authentication settings required for its intended workload. This planning step is especially important in modern environments where Oracle Enterprise Manager, SQL*Plus, automation scripts, Unified Auditing, and role-based administration work together to support governance and compliance.

Before creating a new account, the DBA should answer several questions:

1. Will this account represent a human administrator, an application schema owner, a developer, a reporting user, or a service account?
2. Which default and temporary tablespaces best support the user’s workload?
3. Should the account authenticate with a local password, externally, or through enterprise identity integration?
4. Which profile should enforce password complexity, password lifetime, session controls, and resource limits?
5. Should the account start unlocked, or should it remain locked until deployment or onboarding is complete?
6. What storage quota should be assigned, and on which tablespace?
7. Which roles, system privileges, and object privileges are truly required?
8. How will the account be monitored through Oracle Unified Auditing?

Thinking through these attributes first helps prevent overprivileged accounts, uncontrolled storage growth, and weak operational discipline. It also makes later administration easier because the account was designed correctly from the outset rather than patched after problems appear.

When preparing to create a user in Oracle 23ai, the DBA should evaluate the following attributes carefully:

1. Username: The username should be unique, descriptive, and aligned with naming standards. For example, a schema owner might use an application-oriented name such as HR_APP, while an individual developer account might use a person-based identifier.
2. Authentication method: Password authentication is still common, but the correct method depends on the environment. Some organizations use centrally managed identities or external authentication for stronger governance. The DBA should choose the method that fits enterprise security policy.
3. Initial password: If password authentication is used, the initial password should be strong, temporary when appropriate, and handled securely. The account can be configured so the password must be changed at first login.
4. Default tablespace: This is the tablespace where the user’s objects are created unless another tablespace is specified explicitly. The DBA should avoid casual defaults and instead assign a tablespace appropriate for the account’s schema objects and storage patterns.
5. Temporary tablespace: Sorts, hash joins, and other work operations may spill into temporary space. The DBA should ensure that the user points to the correct temporary tablespace, typically a shared temporary tablespace such as TEMP.
6. Profile: Profiles control password rules and may also limit resources such as sessions, idle time, and CPU usage. In Oracle administration, the profile is one of the most important guardrails for controlling user behavior.
7. Quota: A quota limits how much space a user can consume in a tablespace. This is especially important for developers and schema owners, because without quotas a user may be unable to create objects or, if over-allocated, may consume space irresponsibly.
8. Account status: The user can be created locked or unlocked. In many controlled environments, it is safer to create the account first and unlock it only when onboarding, deployment, or privilege review is complete.
9. Roles and privileges: The DBA must distinguish between roles, system privileges, and object privileges. These should be assigned deliberately, with an emphasis on job responsibility rather than convenience.

Before the CREATE USER statement is executed, the DBA should identify the following prerequisites:

1. The default tablespace for objects the user will create.
2. The temporary tablespace used for sort and work operations.
3. The initial password and whether that password should be expired immediately.
4. The profile assignment that will enforce password policy and, where appropriate, resource limits.
5. The storage quota available to the user on one or more tablespaces.
6. The roles and privileges required for the account’s real duties.
7. The auditing approach needed to track logins, privilege use, and sensitive actions.

These prerequisites reflect an important DBA mindset: user creation is not only about enabling access, but also about constraining, documenting, and supervising that access.

In Oracle 23ai, user management should be viewed as part of a broader security architecture. The account definition itself is only one layer. The profile establishes password and session behavior, roles aggregate privileges into manageable sets, and Unified Auditing records important activity for review and compliance.

For example, a developer may need to connect, create tables, create views, and test PL/SQL code in a non-production environment. That does not mean the same account should automatically receive sweeping system privileges in production. A better approach is to assign narrowly scoped roles appropriate to each environment and audit the usage of sensitive privileges.

This also illustrates why the DBA should distinguish among different account types:

• Individual user accounts for named people should be traceable and auditable.
• Schema owner accounts should own objects but should not necessarily be used interactively by people.
• Application runtime accounts should have only the object privileges needed by the application.
• Administrative accounts should be tightly controlled and audited.

When these distinctions are ignored, organizations often end up with shared accounts, excessive privileges, and weak audit trails. Deciding attributes up front helps prevent those design flaws.

Although Oracle Enterprise Manager provides a graphical interface, every DBA should understand the SQL syntax behind the operation. A simplified example is shown below:

CREATE USER nick
IDENTIFIED BY "Strong_Temporary_Password1"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE default
QUOTA 10M ON users;

After the account exists, the DBA grants the needed privileges:

GRANT CREATE SESSION TO nick;
GRANT CREATE TABLE, CREATE VIEW TO nick;
GRANT role_name TO nick;
GRANT SELECT, INSERT, UPDATE ON hr.employees TO nick;

In practice, many DBAs prefer granting carefully designed roles rather than many direct privileges, because roles simplify administration, standardize entitlements, and reduce configuration drift.

Suppose you want to create a user account for an application developer named Nick. Because Nick is a developer, the account should support object creation and testing in the correct environment, but it should still follow Oracle security standards.

Before creating Nick’s account, the DBA decides the following:

• Nick will authenticate with a password.
• Nick’s objects should be created in the USERS tablespace.
• Nick’s temporary operations should use the TEMP tablespace.
• Nick should inherit password rules from the DEFAULT profile, unless the organization uses a custom developer profile.
• Nick should receive a modest quota, such as 10 MB, sufficient for lab or development exercises.
• Nick should receive only the roles and privileges required for development work.

If you use Oracle Enterprise Manager to create the account, the workflow resembles the following:

1. Navigate to the Users page.
2. Click Create to open the Create User page.

3. Enter NICK in the Name field.
4. Accept or select the appropriate profile, such as DEFAULT or a custom profile defined for developers.
5. Select the authentication method. In this example, use Password.
6. Enter and confirm a strong password.
7. Decide whether the password should be expired immediately so the user must change it at first login.
8. Set the Default Tablespace to USERS, unless the environment uses a different application tablespace strategy.
9. Set the Temporary Tablespace to TEMP.
10. Choose whether the account should start Unlocked or remain Locked until release time.
11. Grant only the needed roles, system privileges, and object privileges.
12. Assign the quota, such as 10M ON USERS.
13. Review the generated SQL before confirming the operation.

Notice that the real design work happens before the final click. The interface only captures the choices the DBA has already made.

Several best practices help modernize user creation in Oracle 23ai:

1. Separate people from schemas: Interactive users should not casually log in as schema owners. Schema ownership and day-to-day usage should be distinct where possible.
2. Prefer roles over scattered privilege grants: Build reusable role models for developers, analysts, support users, and applications.
3. Use quotas deliberately: A quota expresses policy. Unlimited space should be rare and justified.
4. Review authentication choices: Password accounts are common, but enterprise identity integration may be more appropriate for some organizations.
5. Use Unified Auditing: Audit account creation, privilege grants, and sensitive access so security review is practical rather than theoretical.
6. Lock unused accounts: If a user is not yet active, create the account in a locked state until it is needed.
7. Avoid overusing powerful privileges: Privileges such as DBA, CREATE ANY TABLE, or broad object access should be assigned only with strong justification.

These practices support both relational discipline and operational security. A database is not only a storage engine; it is a controlled multi-user system where the quality of account design affects every other layer of administration.

Deciding on user attributes also prepares the DBA for later GRANT and REVOKE operations. If the account is designed correctly, privilege management becomes predictable. If the account is created vaguely, privilege sprawl tends to follow.

For example, a reporting user may only need CREATE SESSION and SELECT on a controlled set of views. A developer may need object creation privileges in a sandbox environment but not in production. An application runtime account may need execute rights on PL/SQL packages but no interactive schema design privileges at all.

In other words, CREATE USER is the foundation, while GRANT and REVOKE refine the access model over time. That foundation should be intentional.

Before creating a user in Oracle 23ai, the DBA should decide far more than a name and password. The account’s profile, authentication method, tablespaces, quota, status, auditing expectations, and privilege model all need to be aligned with the account’s purpose. This planning protects the database, improves maintainability, and supports compliance.

A well-designed user account reflects the user’s real role, consumes only the storage it needs, operates under the correct password and resource controls, and can be monitored through Oracle’s auditing framework. Whether the user is a developer, analyst, application schema, or administrator, careful attribute selection before user creation is one of the simplest and most effective DBA practices in Oracle Database Administration.

Click the Quiz link below to answer a few questions about preparing to create a user.
Preparation For Creating User - Quiz