1

I am the first user of Oracle Database.

Now, I want to create a DB schema called ERDB.

I need to create the ERDB user and granting appropriate privileges to the ERDB user on SQL script file.

CREATE USER dmuser IDENTIFIED BY password 
DEFAULT TABLESPACE USERS 
TEMPORARY
TABLESPACE TEMP 
QUOTA UNLIMITED ON USERS;
GRANT CREATE JOB TO dmuser; 
GRANT CREATE MINING MODEL TO dmuser; 
GRANT CREATE PROCEDURE TO dmuser; 
GRANT CREATE SEQUENCE TO dmuser;
GRANT CREATE SESSION TO dmuser; 
GRANT CREATE SYNONYM TO dmuser; 
GRANT CREATE TABLE TO dmuser; 
GRANT CREATE TYPE TO dmuser; 
GRANT CREATE VIEW TO dmuser; 
GRANT EXECUTE ON ctxsys.ctx_ddl TO dmuser;

but the error happens, SQL Error: ORA 01031 insufficient privileges;

please help me.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Miley Noven
  • 11
  • 2

1 Answers1

1

The Oracle documentation clearly states the following:

Prerequisites

You must have the CREATE USER system privilege.

Connect as SYSTEM, no need of SYSDBA and then execute:

CREATE USER user IDENTIFIED BY password

Similarly, execute other command to create the tablespaces and required grants etc.

On a side note, regarding SYSDBA:

  • Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
  • SYS/SYSDBA is special
  • SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
  • SYS/SYSDBA does not act like any other user
  • When you use SYS/SYSDBA Oracle deactivates some code path and activates others
  • Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user. Use SYS/SYSDBA ONLY for something that can't be done by someone else.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124