How To Grant Read Only Access To User in Oracle Database?

by

In this article, we will discuss how to grant read only access to user in Oracle database. In the very first we need to create a user in oracle database. Let’s start

New user: EandY
Description: Assign Read only access for user (EandY).

How to create new user (Schema) in oracle database with default tablespace users and temporary tablespace?

  1. Create new user EandY:

SQL> CREATE USER EandY IDENTIFIED BY PassworD DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

User created.

SQL>

image

2. Create Role read only for EandY (user):

SQL> CREATE ROLE readonly_role;

Role created.

SQL>

image 1

3. Grant Role read only for EandY (user):

SQL> GRANT readonly_role to EandY;

Grant succeeded.

SQL>

image 2

4. Grant create session privilege to EandY:

SQL> grant create session to EandY;

Grant succeeded.

SQL>

image 3

5. Give privilege to specific tables:

Abc=schema name

TableName=customer and client.

SQL> GRANT SELECT ON abc.customer TO EANDY;

Grant succeeded.

SQL> GRANT SELECT ON abc.client TO EANDY;

Grant succeeded.

OR Give privilege all tables:

SQL> GRANT SELECT ANY TABLE TO EandY;

Grant succeeded.

SQL>

image 4

6. Connect your newly created user EandY:

SQL> conn EANDY/PassworD

Connected.

SQL>

Cheers….

Leave a Comment