1

I have created a user in AWS oracle RDS and have my tables,functions and all other code. As I needed the same replica of this DB structure, I exported it to an sql file using SQL Developer. Created another user, and now want to import the sql file to this new user, but it is throwing insufficient privilege error.

ORA-01031: insufficient privileges

  1. 00000 - "insufficient privileges"

*Cause: An attempt was made to perform a database operation without the necessary privileges.

*Action: Ask your database administrator or designated security administrator to grant you the necessary privileges

I have granted the privileges to the user. Is it because of the user1 which is present in the create table statement?

CREATE TABLE "user1"."MBOMCOMPONENTS" ( "COMPONENTNO" VARCHAR2(14 BYTE), "PLANT" VARCHAR2(50 BYTE) )

Could you please help. Thank you, Manju

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
XSL Query
  • 11
  • 4
  • Seems like your new user is missing one or more privileges - CREATE TABLE, CREATE PROCEDURE, etc. Only you know what objects are created in that SQL file so only you can figure out what privilege is lacking. – APC Jan 03 '20 at 10:13
  • I have granted the privilges. is it because of the user1 which is present in the create table statement. – XSL Query Jan 03 '20 at 10:18
  • CREATE TABLE "user1"."MBOMCOMPONENTS" ( "COMPONENTNO" VARCHAR2(14 BYTE), "PLANT" VARCHAR2(50 BYTE) ) – XSL Query Jan 03 '20 at 10:20
  • then how can we have separate users with same schema and different data? – XSL Query Jan 03 '20 at 10:23

1 Answers1

2

CREATE TABLE"user1"."MBOMCOMPONENTS" ( "COMPONENTNO" VARCHAR2(14 BYTE), "PLANT" VARCHAR2(50 BYTE) )`

This statement builds a table in the "user1" schema. The command will fail if the current schema is not "user1" and the current user lacks the CREATE ANY TABLE privilege.

how can we have separate users with same schema and different data?

A schema is a set of objects owned by a user. A schema has the same name as the user which owns it. To achieve your aim all you need to do is remove "user1". from all the SQL statements, then run the script as USER2 (or whoever).

For future reference, the SQL Developer Export DDL tool has a checkbox for including the schema name; by default it is ticked (i.e. include) but we can unset it if we will want to run the scripts as different users.

APC
  • 144,005
  • 19
  • 170
  • 281