As a DBA you often come across a situation when you have to grant the access of the Oracle Enterprise Manager to a non-administrative manager. So, today in this tutorial you will learn how to do that.
Disclaimer –
Granting access to your Oracle Enterprise Manager to the users of your database could lead to catastrophic events. Don’t ever do that unless it’s absolutely necessary.
Database Configuration –
Let me tell you the database configuration used for the demonstration –
I am using Oracle Database 19c Enterprise Manager which is configured with 1 root container (CDB$ROOT) and 1 pluggable database container (orclpdb). Additionally, you can Click here to learn how to properly install Oracle 19c.
The non-administrative user that I will be using for this tutorial will be HR. HR is a sample schema which comes preinstalled with the Oracle Database. Though it’s preinstalled but it comes in a locked state. Click here to learn how to unlock HR user.
Also, we will be using Oracle Enterprise Manager Database Express Edition. Click here to learn how to open Oracle EM Express (The DBA Version).
Though I am using Oracle 19c and HR user for the demonstration, you can follow the steps for Oracle Database 12c, 18c or any other version as long as it is based on multi-tenant architecture.
Is this tutorial only for Sample users?
However, before you start asking whether this tutorial is only for sample users like HR which comes with the Oracle Database, let me make one thing clear.
You can follow steps shown in this tutorial to grant access of your Oracle Database Express to any user. And by any I mean a newly created administrative user or any non-administrative user.
Why The Error –
Usually when we try to log into the Oracle Database Express using non administrative user we get “Invalid Database Credentials” Error.
This happens because the user through which you are trying to sign-in to the Oracle Database Express does not have sufficient privileges.
Therefore, in order to log into the Oracle Enterprise manager all non-administrative users need to have either EM_EXPRESS_BASIC or EM_EXPRESS_ALL role.
EM_EXPRESS_BASIC and EM_EXPRESS_ALL role –
So let’s talk about these roles in detail.
EM_EXPRESS_BASIC –
EM_EXPRESS_BASIC role, as the name suggests gives basic privileges to the user over Oracle EM. It enables the user to log into the Oracle EM. The user of this role gets read-only privileges of the Oracle Enterprise Manager.
The EM_EXPRESS_BASIC roles also include the SELECT_CATALOG_ROLE which means the user to which you have granted em_express_basic role also gets all the privileges of select_catalog_role.
SELECT_CATALOG_ROLE gives SELECT privileges over all the data dictionary views to the user.
EM_EXPRESS_ALL –
EM_EXPRESS_ALL role not only lets the user log into the Oracle Enterprise Manager but also gives access to all the functionalities provided by EM Express. Moreover the user to which you have granted this role gets read/write access to all Oracle EM Express features.
How to Grant EM_EXPRESS_BASIC / EM_EXPRESS_ALL role –
There is process of granting these roles to a user which you have to follow. That process is –
Step 1: Connect with the Oracle Database
As we know that in Oracle Database a role can only be granted either by an administrative user or by an authorised user. In addition, here for the demonstration I will be using the SYS user to grant these roles to the HR user.
SQLPLUS / as SYSDBA
This statement will connect you with your Oracle Database using the SYS user.
Suggested Reading: How to create an External User.
Step 2: Switch the container
If you have followed my previous tutorial, then you must know that, by default, Oracle Engine connects us to the database through the root container.
So, if the user to which you are granting these privileges is placed inside the root container, then you are good to go. There is no need to switch anything. Skip this step.
However if this is not the case then you have to switch the container to the one which has your user. For example, we know that the HR user is placed inside the pluggable container whose name is ORCLPDB.
Therefore, we will have to switch the container from root to pluggable orclpdb.
alter session set container = orclpdb;
Step 3: Open the database.
In order to perform any further action, we need to make sure that the pluggable database to which we are connected right now is open for read write operations.
Let’s first check if the database is already open or mounted.
COLUMN name FROMAT a10; SELECT name, open_mode FROM v$pdbs;
Furthermore, if your pluggable database is already open then you are free to jump on to the next step.
However if not then you have to open your database. And, to do that we have to execute the alter DDL like this –
alter pluggable database open;
This statement will open that database to which you are currently connected.
Step 4: Grant the EM_EXPRESS_BASIC role
Granting a role is very easy. We simply have to execute a grant statement.
Grant em_express_basic
The statement for granting the em_express_basic role will be –
grant EM_EXPRESS_BASIC to HR;
On execution this statement will grant the EM_EXPRESS_BASIC role to the HR user.
Grant em_express_all
The statement for granting the em_express_all role will be –
grant EM_EXPRESS_ALL to HR;
Consequently, the execution of this statement will grant the EM_EXPRESS_ALL role to the HR user.
In the above statement I have granted the said roles to the HR user. But, If you want to grant the EM_EXPRESS_BASIC or EM_EXPRESS_ALL role to any other user, you simply have to change the name HR with whatever the name of your user is.
Suggested Reading: How To Create Roles in Oracle Database
Log into the Oracle Enterprise Manager
Once you have successfully granted the required roles to the user then you are all set to log into your Oracle Database.
Since we have used HR user for the demonstration thus, we will use its login credentials to log into Oracle Database express. In my case these login credentials will be –
Username – HR Password – HR (This could be different in your case) Container Name – orclpdb <This field will be obsolete now>
Moreover, if you want to unlock the HR user to practice the Oracle db, the I have done a tutorial on it which you can check out here.
So, that’s it for this tutorial. However, if you still have any doubts, then feel free to send a message on my Facebook.
In addition to that if you want to check out the video tutorial then here you go.
Thanks and have a great day!