Now that we understand everything about the Oracle database and we’ve installed it, we need to allow users start using the database. As a DBA, one of the most challenging tasks is managing users especially when it comes to ensuring their security and that of the database. You need to ensure whoever connects to the database has got a valid user account as well as ensuring they have enough rights to do their jobs. In this section, we are going to focus on how to ensure user security i.e. creating user accounts and user authorization.

Default User Accounts

Before we learn how to create user accounts, there are some accounts that come with Oracle when it is installed. These are many but we are going to look at only three that are used for administration.

1. SYS. This is the most powerful DBA user account. This account owns data dictionary and can do anything with the database that is administrative like creating databases, shutting down and starting the database, performing database backup and recovery, etc.
2. SYSTEM. This is the next powerful DBA account in Oracle. It can be used to perform everything the SYS account can do except shutting and starting up the database, database backup and recovery as well as database upgrade.
3. SYSMAN. This is the least privileged user account used majorly in Oracle’s Enterprise Manager tool.

Creating User Accounts

To create a user account, we need to connect to the database as DBAs. It is only the DBA that can create user accounts in Oracle. The statement below is used in creation of user accounts.

From the above code snippet, we connected to the database using the DBA account. We use the CREATE USER keyword to create user account for karitus. We are going to use this snippet to understand different components of a user account in Oracle database. We should pay attention to the following:

Components of a User Account.

Username

Every user account must have a username. A valid username must not exceed 30 characters and should not include special characters except underscore ( _ ) and dollar ( $ )sign. It should also not be Oracle’s reserved word or keyword. You can take a look at all reserved words in Oracle here. In the code snippet above, karitus is the username.

Authentication Method

This is another parameter in authenticating users. We have three authentication methods in Oracle i.e. password authentication, external authentication and global authentication method. We are going to look at one by one.

1. Password Authentication Method. With this method, we use a password as a form of authenticating users. This password together with the username are stored in the database and it is the database to authenticate users (confirm the username and password issued by the user). The password should not be only made up of numbers. In the code snippet above, hr has been used as the password for karitus’s account.

2. External Authentication Method. In this method, we don’t use password as an authentication method. With external authentication, the operating system performs the authentication. The database only verifies if a valid username has been supplied by the user. Externally authenticated user accounts start with ops$username. To create such accounts, we use the following code:

3. Global Authentication Method. This is the last method of authentication in Oracle. With this method, the database only verifies only the username just like with external authentication. Authentication is done by advanced security options like digital certificates, biometrics, etc. Oracle doesn’t

Note: We can still create user accounts with only a username and authentication method without other components. It is highly recommended to configure them as well. In our case, we shall look at all of them.

Default Tablespace

Tablespaces store data files as we saw when looking at Oracle’s storage structures. Users whom we’re creating accounts for will generate their own data and it will be kept on data files. We need to specify the tablespace in which the user’s data files will be kept and this is exactly what this component of the user account does. For our case, we chose users but you can decide to choose another tablespace of your choice. In case you don’t specify the default tablespace for a user account during account creation, you can configure this using the statement below:

Temporary Tablespace

A temporary tablespace is used by the database to store temporary segments created during sorting operations like ORDER BY, GROUP BY, JOIN , etc. For our case, we used temp as the temporary tablespace for Karitus. In case you don’t specify the temporary tablespace for a user account during account creation, you can configure this using the statement below:

Quota Space

The user has been successfully assigned a tablespace where their work will be stored. However, they haven’t been assigned a working space on the tablespace. Quota space limit amount of disk space a user can consume on a tablespace. It is this space that the user will use to create objects and without it, they can’t create any objects in the database. This quota is assigned on a default tablespace that was chosen. To assign quota to an existing account, you can use the following command:

Profile

Further to the tablespaces, the user is also assigned a profile. A profile is used to limit the resource usage of some resources e.g. CPU usage, memory usage, session timeout, etc. as well as enforcing password management rules e.g. password expiration duration, password complexity, etc. We can create profiles in Oracle with different settings we want and we assign that profile to a user account. However, we shall maintain the default profile in Oracle which is profile. If you want users to change the password on the first attempt of login, you set PASSWORD EXPIRE option. In the figure below, karitus’s password has expired and is prompted to choose a new password.

Account Status

This is also an optional component of the CREATE USER account statement. The user account can be open, locked or expired. By default, Oracle accounts are locked on creation and need to be unlocked. We can unlock them during the time of creation or afterwards. If we don’t unlock them during creation, we can use the command below to unlock them:

With the above , the user account is created successfully with everything set. If you have succeeded with creating a user account, Congratulations 🥂 . In case you want to delete any user account, we use the statement below:

Lets try connecting to the database using the newly created user account (karitus). We have been prompted to change the password.

Oops! 😔, we get into another error ORA-01045: user lacks CREATE SESSION privilege; logon denied. What does it mean 🤷‍♂️? Lets see what it means briefly.

When a user account is created, it doesn’t have a right of connecting to the database nor doing anything with the database until given something called privileges. In this case, karitus doesn’t have the privilege of connecting to the database. Privileges lead us to a sub-topic called User Authorization.

User Authorization

Lets get a picture of a university database. It has various users like students, lecturers, and administrators. These do different tasks on the database for instance, a student can only view what was uploaded by the lecturer yet the lecturer can upload new content, view the uploaded content, edit content, etc. The administrator can add and delete users from the database. These are called database access levels and it is privileges (rights) that define these access levels.

Privileges are rights granted to a user account to access objects belonging to other users or perform database level operations. Privileges are assigned to a user account using GRANT keyword and withdrawn from a user account using REVOKE keyword.

Types of Privileges

Object Privileges

Object privileges are rights given to a user account to access and work with objects belonging to another user account. Such objects include tables, views, procedures, etc. The privileges that can be granted include SELECT, UPDATE, DELETE, ALTER, etc.

For example, we need our account we create (Karitus) to access and work with the regions table (only selecting and updating data) belonging hr account. This is what we are going to do:

From the above figure, we first connect as a DBA since only a DBA can grant privileges. After that, we use the GRANT keyword to assign the CONNECT privilege (to be looked at later in details). This is to allow karitus to connect to the database. After that, we assign the SELECT and UPDATE privileges on regions table belonging to hr to karitus. Now lets connect using karitus’s account and we see if we can select and update data in the regions table.

From the above figure, we can see that now karitus can read information from the regions table belonging to the hr account. Note that we append hr. before the table name. This is because the table doesn’t belong to karitus but to hr. Karitus has been just granted a right to access it but not to own it. Karitus can only SELECT and UPDATE data in the hr’s regions table. She can’t do anything extra. To confirm this, let us try insert anything in the regions table.

With object privileges, we can give a right to extend the privileges granted to a grantee to another user account. For example, the DBA can allow karitus to extend the same rights to another account. This is done using WITH GRANT OPTION keyword when granting the privilege to the grantee. Let us give this right to karitus.

WITH GRANT OPTION keyword allows Karitus to extend the same rights to another user account which she didn’t have before. Lets try it out (To do this, let us create another dummy user whom we shall extend the rights.)

In case of WITH GRANT OPTION enabled, the grantor revokes privileges from the grantee, all those who got the same privilege from the grantee lose the privilege automatically. They can only maintain the privilege if they received the same privilege from multiple grantees. Let us revoke the select privilege from karitus on hr.regions.

Dummy automatically loses the SELECT privilege on hr.regions since Karitus lost the same privilege. Remember, this privilege was granted to the dummy user account by karitus.

System Privileges

Apart object privileges, we also have system privileges. System privileges are rights granted to a user to enable them perform database level operations. Such privileges are administrative in nature and include ALTER SYSTEM, ALTER DATABASE, CREATE USER, ALTER USER, CREATE TABLESPACE, ALTER TABLESPACE, CREATE PROFILE, etc. Just like the object privileges, system privileges are also granted by the DBA using GRANT keyword. For example, lets grant the CREATE USER privilege to karitus to enable her create users.

To enable the grantee extend the same privilege to other users, we use WITH ADMIN OPTION keyword. However, in case of a revoke of system privilege from the grantee, all other users who got the same privilege from the grantee are not affected by the revoke. Therefore, we need to take caution as DBAs before granting privileges accompanied with WITH ADMIN OPTION.

Role Privileges

A role is a set of privileges (they can be object or system privileges). Take a role as a job position in an organization with a list of tasks to perform for example human_resource_manager. This manager can view, update, insert and delete employee data. All these rights are going to be given to that job position (the hr.employees table will be used). The job position that has been granted the privileges is assigned to anyone in the organisation who has been appointed the human resource manager.

From the above, the job position is the role. In database administration, we create a role called human_resource_manager and then grant it the privileges (SELECT, UPDATE, INSERT, DELETE) on hr.employees table. After this, we assign the role to a user account for someone who is the human resouce manager. Lets see this:

Roles are inherited for example, Karitus is now the human resource manager according to the above code snippet but anytime she may not be the one. In case it happens, we revoke the role from her to another user who has taken up the position. Roles help so much in managing privileges.

Default Roles

Apart from the roles we create, we also have default roles in Oracle. These come by default with the installation of Oracle database. These are so many but lets see just a few of them:

1. CONNECT. You remember when Karitus couldn’t connect to the database, it was because she didn’t have the CREATE SESSION privilege. This privilege is contained in the CONNECT role. When CONNECT role is assigned to a user account, the user account can now successfully connect to the database.
2. SYSDBA. This is one of the most powerful roles in the database and shouldn’t be granted to any account except those to be in charge of database administration. The role contains all administrative privileges.
3. RESOURCE. This role has privileges that enable a user account to create and work with objects in the database for example, tables, views, etc.

There are other default roles you can look at. If you’re still interested, you can check out this. Roles can be deleted using the DROP ROLE keyword and revoked from a user account using the REVOKE keyword.

Principle of Least Privilege

The principle states that every user must be given only the minimal privileges needed to perform their jobs. The principle follows the initially-closed philosophy where all access is initially closed and granted on a need-to-know basis. That is why accounts are initially closed and can’t even connect. Accounts are only granted connect privilege if they indeed have to connect to the database. In brief, give users only necessary privileges they need to do their tasks.

PUBLIC User Account.

In case we need to grant a privilege to all user accounts in the database and those to be created in future, we grant the privilege to a special user account called PUBLIC. For example, we need every account created to be able to create objects in the database. We can grant the RESOURCE role to the PUBLIC account. This will be available to all user accounts and those to be created in future.

Congratulations for making it this far ✨. You’ve so far learnt a lot about database administration. Now that you have users using your database, you need to keep a keen eye on their actions as they’re using the database. The next topic is designed purposely for this. Lets dive in with database auditing.

Summary

1. We use CREATE USER keyword to create user accounts in Oracle.

2. A user account to be created, it must have a username and an authentication method.

3. The optional components of a user account include: default & temporary tablespaces, quota, profile.

4. Privileges determine the access level of a user account on the database.

5. We have object privileges, system privileges and role privileges.

6. DBAs should always look at the principle of least privileges.