Cannot migrate Oracle database (initially create tables) because of insufficient privileges?

c# database-migration ef-migrations entity-framework-core oracle

Question

I'm new to Oracle DB and really not sure what kind of privileges I should setup for an user so that he can execute basic commands like create,drop,delete, insert, update, select tables.

And actually I've setup all those privileges for my user account. But when running migrate, it failed with the following error printed in console window:

2019-05-27 08:29:07.837809 ThreadID:1 (ERROR) OracleRelationalCommand.Execute() : Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01031: insufficient privileges ORA-06512: at line 2 at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) Failed executing DbCommand (292ms) [Parameters=[], CommandType='Text', CommandTimeout='0'] BEGIN EXECUTE IMMEDIATE 'CREATE TABLE "CUSTOMER" ( "Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL, ... )'; END; 2019-05-27 08:29:07.842817 ThreadID:1 (ERROR) OracleRelationalCommand.Execute() : Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01031: insufficient privileges ORA-06512: at line 2 at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01031: insufficient privileges ORA-06512: at line 2 at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) ORA-01031: insufficient privileges ORA-06512: at line 2

So looks like the involved sql command here is BEGIN EXECUTE IMMEDIATE …. I'm not so sure what kind of privileges I should setup for that to be executed successfully. Could you please take a look at the error info above to give me some suggestion to setup the privileges correctly for my user account? Thanks!

PS: screenshots showing all privileges I've setup:

enter image description here
enter image description here

1
1
5/27/2019 1:42:27 AM

Accepted Answer

Well it looks like that the CREATE TABLE privilege is fine only when the table to be created does not contain any auto-incremented primary key?

It's really not enough in my case and I've tried adding 2 more privileges (for sure but not really know if which one does the trick or both do): CREATE CLUSTER and CREATE SEQUENCE. Looks like when creating my tables, it need to create some sequence first (for identity columns) or create cluster for primary key ...

Actually I just tried looking in the RESOURCE role for any possible privileges that are missing for my specific user and I found the 2 most possible privileges as mentioned above.

It now works fine, all tables can be created fine :) blocking me about 1 day (of course not always focusing on it, but really waiting and thinking about the cause).

0
5/27/2019 8:41:25 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow