In EBS, we often assume that a user named APPSRO is exactly what it sounds like—a read-only account. Recently, I came across an interesting privilege investigation that proved once again why assumptions and Oracle databases don't always agree.
What started as a simple privilege check turned into a deeper lesson on roles, effective privileges, and one subtle Oracle dictionary view that many DBAs overlook.
The Setup
The user was created with what appeared to be only read-related privileges.
CREATE USER APPSRO
DEFAULT TABLESPACE xxxx
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO APPSRO;
GRANT RESOURCE TO APPSRO;
GRANT ADJ_SYS_VIEW_SELECT TO APPSRO;
GRANT ADJ_FULL_TAB_SELECT TO APPSRO;
GRANT UNLIMITED TABLESPACE TO APPSRO;
GRANT SELECT ANY TABLE TO APPSRO;
GRANT SELECT ANY SEQUENCE TO APPSRO;
GRANT SELECT ANY DICTIONARY TO APPSRO;
GRANT CREATE ANY SYNONYM TO APPSRO;
GRANT DEBUG CONNECT SESSION TO APPSRO;
Looking at these grants, the account appeared to be intended for read access.
Naturally, I expected the following statement to fail.
CREATE TABLE testread AS
SELECT *
FROM apps.some_table;
Instead...
Table created.
Wait...
How did a supposedly read-only user create a table?
First Suspect: Direct System Privileges
My first assumption was simple.
There must be a direct
CREATE TABLEprivilege.
So I checked:
SELECT privilege
FROM dba_sys_privs
WHERE grantee = 'APPSRO';
The output showed:
CREATE ANY SYNONYM
DEBUG CONNECT SESSION
SELECT ANY DICTIONARY
SELECT ANY SEQUENCE
SELECT ANY TABLE
UNLIMITED TABLESPACE
No CREATE TABLE.
To be absolutely certain:
SELECT *
FROM dba_sys_privs
WHERE grantee='APPSRO'
AND privilege='CREATE TABLE';
Result:
no rows selected
So the privilege wasn't granted directly.
Second Suspect: Roles
Maybe the privilege was inherited through a role.
Checking the assigned roles:
SELECT granted_role
FROM dba_role_privs
WHERE grantee='APPSRO';
Result:
RESOURCE
CONNECT
ADJ_SYS_VIEW_SELECT
ADJ_FULL_TAB_SELECT
The custom roles looked harmless.
That left one interesting candidate:
RESOURCE
The Plot Twist
To inspect what the RESOURCE role contained, I queried:
SELECT role,
privilege
FROM role_sys_privs
WHERE role='RESOURCE';
The output immediately solved the mystery.
RESOURCE CREATE CLUSTER
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE
There it was.
CREATE TABLE.
The privilege wasn't granted directly to the user.
It was inherited through the RESOURCE role.
Session Privileges Never Lie
To confirm what Oracle actually allowed during the session:
SELECT privilege
FROM session_privs
ORDER BY privilege;
The output included:
CREATE TABLE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
Even though DBA_SYS_PRIVS showed no direct grant, Oracle clearly allowed table creation because SESSION_PRIVS reflects the effective privileges available in the current session.
This is one of my favorite Oracle views during privilege investigations.
When someone asks,
"What can this user actually do?"
SESSION_PRIVS usually provides the quickest answer.
Why the CTAS Worked
When Oracle executed:
CREATE TABLE testread AS
SELECT *
FROM apps.some_table;
it validated three requirements.
✔ CREATE TABLE
(via the RESOURCE role)
✔ SELECT ANY TABLE
(to read the source table)
✔ UNLIMITED TABLESPACE
(to allocate storage)
Since all three conditions were satisfied, Oracle created the table successfully.
Bonus Discovery: ROLE_SYS_PRIVS Doesn't Show Every Role
While testing the solution, I revoked the RESOURCE role.
REVOKE RESOURCE FROM APPSRO;
Then I reran the same query:
SELECT role,
privilege
FROM role_sys_privs
WHERE role='RESOURCE';
To my surprise, Oracle returned:
no rows selected
At first, I wondered whether someone had modified the RESOURCE role.
But then I revisited the Oracle documentation for ROLE_SYS_PRIVS.
It states:
Information is provided only about roles to which the user has access.
That small sentence explains everything.
Before the revoke:
APPSRO had access to the
RESOURCErole.ROLE_SYS_PRIVSdisplayed the role's privileges.
After the revoke:
APPSRO no longer had access to
RESOURCE.Oracle filtered the view.
The role still existed in
DBA_ROLES, butROLE_SYS_PRIVSno longer displayed its privileges.
This is a subtle behavior that's easy to overlook during privilege investigations.
The Bigger Lesson
This investigation wasn't really about CREATE TABLE.
It was about understanding where Oracle gets a user's privileges.
No single dictionary view tells the complete story.
Each answers a different question.
| View | Answers |
|---|---|
SESSION_PRIVS | What can this session actually do? |
DBA_SYS_PRIVS | What system privileges are granted directly to the user? |
DBA_ROLE_PRIVS | Which roles are granted to the user? |
ROLE_SYS_PRIVS | What system privileges are granted to roles that the current user can access? |
Understanding the purpose of each view makes privilege troubleshooting much easier.
A Handy Diagnostic Checklist
Whenever I troubleshoot Oracle privilege issues, I usually start with these queries.
-- Current user
SHOW USER;
-- Effective session privileges
SELECT privilege
FROM session_privs
ORDER BY privilege;
-- Direct system privileges
SELECT *
FROM dba_sys_privs
WHERE grantee='APPSRO';
-- Granted roles
SELECT *
FROM dba_role_privs
WHERE grantee='APPSRO';
-- System privileges contained in accessible roles
SELECT role,
privilege
FROM role_sys_privs
ORDER BY role, privilege;
These four queries answer almost every privilege-related question.
Final Takeaway
This investigation started with a simple question:
"Why can a read-only user create tables?"
The answer wasn't hidden in DBA_SYS_PRIVS.
It wasn't obvious from the user definition.
It was buried inside a role.
Along the way, I also learned something I hadn't paid much attention to before:
ROLE_SYS_PRIVS only shows the system privileges of roles that the current user can access.
That small detail completely explained why the view's output changed immediately after revoking the RESOURCE role.
Sometimes the most valuable lessons come not from solving the original problem, but from discovering how Oracle's privilege model really works.
And that's what makes troubleshooting so rewarding.
