Methods for Creating Users and Granting Roles in Oracle Autonomous Database

AB.

Sure, I can explain the methods to create users and grant roles in Autonomous Database services.

Autonomous Database services are fully managed databases provided by Oracle Cloud that automatically perform many of the traditional database administration tasks. To create users and grant roles in Autonomous Database services, you can use the following two methods:

1. Through SQLPlus: SQLPlus is a command-line tool used to access and manipulate Oracle databases. You can use SQLPlus to connect to the Autonomous Database and run SQL commands to create users and grant roles. To use SQLPlus, you need to have the Oracle Instant Client and SQLPlus installed on your local machine. Once you have installed SQLPlus, you can connect to the Autonomous Database using the connection string provided by the service console. For example:

javaCopy code
sqlplus admin_user@db_service_name_high 

Once you have connected to the database, you can run SQL commands to create users and grant roles, such as:

sqlCopy code
CREATE USER new_user IDENTIFIED BY password;  GRANT CONNECT, RESOURCE TO new_user; 

2. Through the Oracle Cloud Infrastructure service console: The Oracle Cloud Infrastructure (OCI) service console is a web-based interface that allows you to manage your Autonomous Database services. You can use the OCI service console to create users and grant roles by following these steps:

a. Log in to the OCI service console.

b. Navigate to the Autonomous Database service that you want to manage.

c. Click on the "DB Connection" button to get the connection string.

d. Connect to the database using a SQL client such as SQL Developer.

e. Run SQL commands to create users and grant roles, such as:

sqlCopy code
CREATE USER new_user IDENTIFIED BY password;  GRANT CONNECT, RESOURCE TO new_user; 

3. Using DBMS_CLOUD_ADMIN Package: You can also use the DBMS_CLOUD_ADMIN package to create users and grant roles in Autonomous Database services. The DBMS_CLOUD_ADMIN package is a PL/SQL package that provides procedures to manage Autonomous Database services. You can use the DBMS_CLOUD_ADMIN package to create users and grant roles by following these steps:

a. Connect to the Autonomous Database using a SQL client such as SQL Developer.

b. Run the following command to create a credential for the DBMS_CLOUD_ADMIN package:

javascriptCopy code
BEGIN     DBMS_CLOUD.CREATE_CREDENTIAL(         credential_name => 'ADMIN_CRED',         username => 'ADMIN_USER',         password => 'ADMIN_PASSWORD'     ); END; 

c. Run the following command to create a user:

javascriptCopy code
BEGIN     DBMS_CLOUD_ADMIN.CREATE_USER(         username => 'NEW_USER',         password => 'NEW_PASSWORD'     ); END; 

d. Run the following command to grant roles to the user:

javascriptCopy code
BEGIN     DBMS_CLOUD_ADMIN.GRANT_ROLE(         role => 'CONNECT',         grantee => 'NEW_USER'     ); END; 

In summary, the two methods to create users and grant roles in Autonomous Database services are through SQLPlus and the OCI service console. Additionally, you can also use the DBMS_CLOUD_ADMIN package to perform these tasks.