Creating schema and adding roles in an Oracle Autonomous Database

In this article, I’ll point out the difference between schema and users and also explain how to create a user and add roles to a user in an autonomous database instance.

What is a Schema?

In simple terms, the schema is a collection of database objects owned by a user and has the same name as that user. Schema objects are logical structures created by users.

What is a user?

A database user is an account through which you can log in to the database.

In Oracle, USER is the account name, SCHEMA is the set of objects owned by that user. Even though Oracle creates the SCHEMA object as part of the CREATE USER statement but they are not the same thing. If the user has objects then we call it SCHEMA else we call it a USER. A USER may be given access to SCHEMA OBJECTS owned by different USERS. So In Oracle, users and schemas are essentially the same things. We can consider that a user is an account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

How to create a new user?

Use the CREATE USER statement to create and configure a database user. We must have the CREATE USER system privilege in order to create a user. The syntax is as follows:

CREATE USER demo IDENTIFIED BY Password#1234
DEFAULT TABLESPACE "DATA"
TEMPORARY TABLESPACE "TEMP"
QUOTA unlimited ON "DATA";

Explanation:

1. demo is the name of the user to be created and it can contain only characters from your database character set.
2. The IDENTIFIED clause lets you indicate how Oracle Database authenticates the user. The various options are as follows:
   1. BY password – The BY password clause lets you create a local user and indicates that the user must specify password to log on to the database. Passwords must follow the rules described in the section. The password of this user is Password#1234.
   2. EXTERNALLY Clause – Specify EXTERNALLY to create an external user. Such a user must be authenticated by an external service, such as an operating system or a third-party service.
   3. GLOBALLY Clause – The GLOBALLY clause lets you create a global user. Such a user must be authorized by the enterprise directory service (Oracle Internet Directory).

3. The DEFAULT TABLESPACE specifies the default tablespace for objects that the user creates. If you omit this clause, then the user’s objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user’s objects are stored in the SYSTEM tablespace.
4. The TEMPORARY TABLESPACE clause specifies the tablespace or tablespace group for the user’s temporary segments. If you omit this clause, then the user’s temporary segments are stored in the database default temporary tablespace or, if none has been specified, in the SYSTEM tablespace.
5. The QUOTA clause to specify the maximum amount of space the user can allocate in the tablespace.

Once you create a user, we cannot create a connection for that user directly. First, we need to give create session privilege:

GRANT CREATE SESSION TO demo;

Now we can create a connection for the demo user. The username is a demo and the password is Password#1234 and the wallet is the same as shown in the previous article:

This user can now be seen in the DBA_USERS table.

Now, this user does not have the privilege. We can begin adding privileges to the account using the GRANT statement. If no privileges are given to this new user and it tries to query a table from another schema it will get an error. The admin user can give access to view tables created by other users using the below command:

GRANT SELECT ON admin.persons TO demo;
CREATE SYNONYM demo.persons FOR persons;

The first query provides view access to the PERSON table to the demo user but to view the table demo user needs to have a synonym for that table which is achieved using the second statement.

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. We generally use synonyms when you are granting access to an object from another schema and you don’t want the users to have to worry about knowing which schema owns the object. We create a synonym so that users do not have to prefix the table name with the schema name when using the table in a query.

Similar to SELECT privilege on tables we can also give ALTER, CREATE, DELETE and DROP privileges.

Other database objects on which we can provide privileges are Trigger, Index, Sequence, Procedure, View, Dictionary, and Database Link.

Apart from these, the admin can give GRANT EXECUTE privilege for UTL_FILE(read and write operating system (OS) text files), DBMS_CLOUD(work with data in object storage), UTL_RAW(procedures for converting and packing message data that is sent back and forth through the WebSphere MQ queues using the RAW data type and PL/SQL data types), DBMS_SCHEDULER(provides a collection of scheduling functions and procedures that can be called from any PL/SQL program).

We can drop a user using the following command:

DROP user demo CASCADE;

That’s all about this article.

Request you to subscribe to my YouTube channel for regular updates.

Further readings:

What Is an Autonomous Database?

How to create a free tier ATP connection

Creating Oracle Cloud Free Tier account

Oracle Autonomous Transaction Processing