READ-ONLY PDB users in Oracle 23ai - Secure Multitenancy

Oracle 23ai has introduced a new feature, READ-ONLY PDB user to improve security, developer productivity and performance. This helps multi-tenant environment where data access is critical.

The READ-ONLY user cannot perform any DDL or DML activities.

Create a Read-Only PDB User: To create a Read-Only PDB use the new READ ONLY clause in the CREATE USER statement.

Connect to PDB user
SQL> ALTER SESSION SET CONTAINER = my_pdb;

Create readonly “hr_user”
SQL> CREATE USER hr_user IDENTIFIED BY passwordxxx READ ONLY;

Grant create session to hr_user
SQL> GRANT CREATE SESSION TO hr_user;

Note that the “hr_user” cannot be able to perform below tasks

• User cannot run INSERT, DELETE, UPDATE or MERGE.
• User Cannot create or modify tables, indexs, views or procedures
• User cannot change roles or privileges
• User cannot modify session-level settings

When you try any of the above user will receive “ORA-28194: Can perform read operations only " error.

Run below view to see the user is read-only or not
SQL> SELECT username, read_only from dba_users where username='HR_USER';
USERNAME      READ_ONLY
--------------------  -----------------
HR_USER           YES

SQL> Connect hr_user/paswordxxx;
Connected.

SQL> CREATE TABLE employee_test (emp_id number, emp_name varchar2(50));
*
ERROR at line 1:
ORA-28194: Can perform read operations only

SQL> DELETE FROM employee;
*
ERROR at line 1:
ORA-28194: Can perform read operations only

Note that READ-ONLY users can execute PL/SQL if it doesn’t have any DDL or DML.
The below procedure rev_salary has update statement and cannot perform the operation.

 
SQL> exec REV_SALARY;
ERROR at line 1:
ORA-28194: Can perform read operations only
ORA-06512: at "HR_USER.REV_SALARY", line 3
ORA-06512: at line 1

The READ-ONLY user can run a SELECT query without any issues.

SQL> SELECT emp_id, emp_name from employee;
EMP_ID EMP_NAME
-------------- ----------------------------
1 test_user1
2 test_user2
3 test_user3
SQL>

The Read-Only PDB Users provide a clean way to enforce non-modifiability of users at the database level. This helps with read intensive applications, as these users restricted to only SELECT and users cannot perform any DDL or DML activities.

Thanks & Regards,

https://oracleracexpert.com  

Webinar: Strengthen Your Oracle Database 23c Security

Join us for an informative session focused on the latest security enhancements in Oracle Database 23c. We’ll explore key features such as improved authentication methods, encryption, and SQL Firewall, and demonstrate how these innovations help protect your data and strengthen your defenses against evolving threats.

Date & Time : 

Sept 19th  , 2025 8:00 AM – 9:00 AM Pacific Time (GMT-07:00 | San Francisco)

 
This session is ideal for:
Database Administrators (DBAs)
IT Security Professionals
Oracle Architects and Developers
Who are looking to enhance database security practices using Oracle 23c's new capabilities.

Topics covered in this webinar include:

• SQL Firewall
• Database Auditing Enhancements
• Authentication & Authorization Updates
• Data Encryption
• Autonomous Database Security Features
• And other key innovations in Oracle 23c

How to Register
Please send an email to: SatishbabuGunukula@gmail.com to register and receive webinar access details.

Join the Webinar

Click here to join the Meeting 
Click here to view the Presentation 

RMAN-03009 - RMAN Backup Failure After Applying DB RU Patch: A Resolution Guide

After applying a Database Release Update (DB RU) patch, we encountered an RMAN backup failure. The error messages provided were as follows:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 08/09/2025 14:54:45
ORA-01403: no data found

Issue: Failure While Registering Database
When attempting to register the database with RMAN, the following output was observed:

RMAN> register database;
database registered in recovery catalog
Creating and using snapshot control file for resync
starting full resync of recovery catalog
Control file used records for BACKUP REDOLOG = 6880
Control file used records for DELETED OBJECT = 6544
Control file used records for BACKUP SET = 2720
Control file used records for ARCHIVED LOG = 2384
Control file used records for LOG HISTORY = 2336
Control file used records for BACKUP DATAFILE = 2288
Control file used records for RMAN STATUS = 2256
Control file used records for BACKUP PIECE = 2096
Control file used records for BACKUP SPFILE = 524
Resync in progress: 11000 RMAN OUTPUTrecords resynced
RMAN Command Id : 2025-08-11T14:58:25
RMAN Command Id : 2025-08-11T14:58:25
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMANCAT.RLH_F1) violated - parent key
not found
RMAN Client Diagnostic Trace file :
/oracle/diag/clients/user_oracle/RMAN_3026693161_110/trace/ora_
rman_37312_1.trc
RMAN Server Diagnostic Trace file :
/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_37319.trc

Root Cause: Recovery Catalog Not Current
Upon attempting to connect to the RMAN catalog, we received the following message:

$ rman catalog rmancatusr@CATDB

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 09 01:38:51 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMANCAT.DBMS_RCVCAT version 19.10.00.00. in RCVCAT database is not current
PL/SQL package RMANCAT.DBMS_RCVMAN version 19.10.00.00 in RCVCAT database is not current

This indicates that the RMAN catalog was not upgraded and was out of sync with the new database release.

Solution: Upgrade the RMAN Catalog
To resolve the issue, we needed to upgrade the RMAN catalog. The solution was to run the following command:

$ rman catalog <catalog user/passwd> @catdb
RMAN> upgrade catalog ;

The issue has been RESOLVED after upgrading the database. The user able to register database and able to run backups without any issues.

Conclusion

This issue was caused by the RMAN catalog being out of sync after applying a DB RU patch. Upgrading the catalog resolved the error, allowing successful database registration and backup operations.

Unlocking the Power of GraphQL in Oracle Database 23ai

Oracle 23ai introduces native support for GraphQL, it’s a modern API query language and helps developers by enabling efficient and flexible data access from the database. Unlike REST API’s, the GraphQL allows clients to extract the data they need by reducing over-fetching, undirecting of data with flexible data access.

By using GraphQL now developers can expose database schemas as GraphQL API’s without any custom code and access real time data securely with seamless integration using modern frontend tools.

GraphQL helps in the following areas

• Modern Web and Mobile Apps rapid development
• Data federation by coming relation data with other sources such as JSON Data
• Microservices architecture in a clean, versioned manner.

In order to use GraphQL, you must install or upgrade to Oracle 23ai and you should use Oracle Rest Data services (ORDS) 23.2 version or later. Note that SQL Developer web gives us a GraphQL editor screen and by using that user can write the queries

requests

For example 1: - Simple query

SQL> CREATE TABLE emp (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
salary number(10),
is_active BOOLEAN);


SQL> CREATE TABLE dept (
dept_id NUMBER PRIMARY KEY,
name VARCHAR2(100);


GraphQL Query:
query {
    emp {
        emp_id
        name
        salary
        is_active
    }
}

Oracle SQL equivalent query:

SQL> Select emp_id, name, salary, is_active from emp;

For example, 2: - Query filtering with arguments


GraphQL Query:

query {
    emp (emp-id:12345) {
        name
        salary
        is_active
    }
}

Oracle SQL equivalent query:

SQL> Select name, salary, is_active from emp where emp_id=12345;

Example 3: Role based access control

If you have HR user and other users, you want only HR users to see the salary info but not others. By using oracle built in security your GraphQL Schema you can have these access controls.

HR User:
query {
    emp {
        name
        salary
    }
}

Other User:
query {
    emp {
        name
    }
}

You can restrict which fields are visible depending upon the users’ roles without writing any custom logic.

Users can use the tools below to test GraphQL

1. postman with GraphQL support
2. GraphQL playground based or local
3. Apollo Studio
4. ORDS GraphQL endpoint tester

When using GraphQL, Oracle translates GraphQL into an optimized SQL Json and returns only the requested fields. The above examples demonstrate how easy it is to use GraphQL to integrate directly into the oracle stack. The Oracle 23ai enables you to build faster, less code, and deliver richer APIs directly from your database.

Exploring the New BOOLEAN Data Type in Oracle Database 23ai

Oracle Database 23ai has introduced many features, and one of the nice additional in SQL is the support for the BOOLEAN data type. The BOOLEN data types are available for many years in Pl/SQL and now it is supported as native data type. This will help developers simplify application logic. Earlier developers often had to rely on CHAR(1) or NUMBER(1) fields to simulate boolean logic in SQL

The BOOLEAN data type represents logical values such as TRUE, FALSE, and NULL.

Advantages of native BOOLEAN support in SQL:

• Improved readability: No more cryptic 'Y', 'N', or 1, 0 values. Developers can use TRUE and FALSE make code more intuitive.
• Better integration: Direct support in SQL helps use BOOLEAN values easier to use in views, constraints, triggers, and queries.
• Less error-prone: removes confusion caused by using characters or numbers to represent boolean values
• Modernization: Aligns Oracle SQL more closely with other RDBMSs such as PostgreSQL and MySQL.

How to use BOOLEAN in Oracle 23ai

1. Creating a Table with a BOOLEAN Column

SQL> CREATE TABLE emp (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
is_active BOOLEAN);

2. Inserting BOOLEAN Values

INSERT INTO emp (employee_id, name, is_active)
VALUES (101, 'Samantha', TRUE);

INSERT INTO emp (employee_id, name, is_active)
VALUES (102, 'Rex', FALSE);

3. Querying BOOLEAN Values

SELECT name FROM emp
WHERE is_active = TRUE;

Or

SELECT name FROM emp WHERE is_active;

4. Using BOOLEAN in CASE Statements

SELECT name,
CASE
WHEN is_active THEN 'Active'
WHEN NOT is_active THEN 'Inactive'
ELSE 'Unknown'
END AS status FROM emp;

Key Considerations: -

• Note that BOOLEAN values can still be NULL, but in conditions where NULL might affect the logic.
• Not all Oracle tools and connectors fully support BOOLEAN yet and it’s user responsibility to check compatibility with client libraries
• Existing old code using CHAR(1) or NUMBER(1) won’t auto-convert for BOOLEAN, user must migrate manually

The BOOLEAN data type in Oracle Database 23ai is a game changer for developers and DBAs. It improves data modeling, simplifies SQL logic, helps developer experience like other RDBMS platforms and consider adopting BOOLEAN fields wherever required.

Thanks & Regards,

https://oracleracexpert.com