Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Granted privilege not present in session_privs when connecting to user in SQL Developer

Danno_AECCMar 18 2025

MSSQL version: SQL Server 2019
Oracle version: 19c

SQL Developer version: 24.3.1.347

Hello!

I have an odd problem that I can't explain and hopefully someone has run across.

I'm migrating a MSSQL DB to Oracle using SQL Developer Migration. The migration script fails because the target Oracle user cannot create a ROLE or USER.

Log snippet: 

SQL> CREATE ROLE ROLE_TP__AvantisPROD_Gold_test ;
Error starting at line : 18 File @ C:\Temp\SQL Developer Output\TP__AvantisPROD_Gold_test\generated\2025-03-15_10-18-44\master.sql
In command -
CREATE ROLE ROLE_TP__AvantisPROD_Gold_test
Error report -
ORA-01031: insufficient privileges

The user permissions were created per the SQL Developer documentation and clearly show that it has those permissions via the DDL and DBA GUI. But when I connect to the user via SQL Developer and check session_privs they are missing:

And I predictably get an ORA-01031 (insufficient privileges) when I manually try to create a ROLE or USER.

However, I can connect to the user via SQL*Plus and display the session_privs and they include ROLE and USER creation. And I can create a role or user with no error.

SQL*Plus : 

[oracle@ ~]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 18 08:46:39 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle.  All rights reserved.
Enter user-name: AVANTIS_GOLD
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL> set lin 1000;
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
SET CONTAINER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE TYPE
CREATE MATERIALIZED VIEW
CREATE TRIGGER
CREATE PROCEDURE
DROP ANY ROLE
CREATE ROLE
CREATE SEQUENCE
CREATE VIEW
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE TABLE
DROP USER
CREATE USER
CREATE SESSION
16 rows selected.
SQL> CREATE ROLE ROLE_TP__AvantisPROD_Gold_test;
Role created.
SQL>

I want to use the Online option, but this user issue is killing the migration.

Comments

Post Details

Added on Mar 18 2025
6 comments
113 views
1 mention