Oracle Base Database Service: Create a User with SYSDBA Privileges
Use case: Have come across a requirement where customer has to give user with SYSDBA Privileges to partner who does implementation and installation of required software's and that interacts with Database. However, DBA does not want to give them the credentials of SYS and want to create a new user with SYSDBA privileges.
Solution: By default when you install/provision an Oracle Database you will be getting SYS and SYSTEM users which have SYSDBA privileges and these are more than users. They are real schemas, they are internal Data Dictionary accounts and shouldn’t be messed up. Hence should never share this user details with anyone. If you have an unavoidable requirement to share user with SYSDBA privileges then create one user and share.
In this blog, I will explain on how to create a user with SYSDBA privileges in Oracle Base Database where my Database is in Private Network.
Follow this blog on how to create a connection to Private DBCS aka Oracle Base Database Service from SQL Developer on your local machine.
Now that you are connected to the Private Oracle Base Database Service instance we will see how to create a user with SYSDBA privileges.
- Before starting with user creation let us check what is the user used for connecting the current session and the connection by running the below commands:
This shows that I am connected to DB using SYS user and the connection is CDB.
2. While creating a connection in SQL Developer we have provided the Service Name of CDB i.e., Container Database and user cannot/should not be created in CDB and is to be created in PDB i.e., Pluggable Database. Hence, the first step is to change the session to connect to PDB by running the below command
Here, we have run the command to alter session to connect to Pluggable database and verified the same by running >show con_name;
3. Now, let us create user in the PDB and then grant them with required permissions to be used as a user with SYSDBA privileges by running the below commands
4. Now, let us create a new connection in SQL Developer to check if the new user created has SYSDBA privileges or not. For creating this connection the procedure remains the same as we did earlier except for a small change where in the Service Name we have to provide the Service Name from Pluggable Database as User is created in PDB.
5. To fetch Service Name of PDB, navigate to the OCI Base Database Service → Under Resources go to Databases → Click on Database Name → Now, Under Resources go to Pluggable Databases → Click on the Pluggable Database Name → Click on PDB Connection Button → Expand the connection String to fetch the Service Name which is as below:
6. Now, let us see the connection created in SQL Developer for the user created in step 3 with SYSDBA Role.
7. Let us test this user connection type.
Here the User is still showing as SYS which means the user has SYSDBA privileges and because you have selected the user role as “SYSDBA” while creating the connection. The same user can also be connected as a normal user and not SYSDBA.
NOTE: Never grant SYSDBA to anyone unless and until you are absolutely sure they have the NEED. If they need this access only for start/stopping database then you can even provide them with access to SYSOPER role rather than SYSDBA role.
