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, IReadOnlyDictionary
2 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, IReadOnlyDictionary
2 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, IReadOnlyDictionary
2 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:
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).
User contributions licensed under CC BY-SA 3.0