Oracle database user privileges

Unless specifically stated for a given task, the Sterling Order Management System Software user does not require database administrator (DBA) privileges.

Administrator privileges

The following list includes some of the basic privileges that should be granted to the Sterling Order Management System Software administrative user who creates or modifies the Oracle database:

  • Alter any sequence
  • Alter session
  • Create any sequence
  • Create procedure
  • Create sequence
  • Create session
  • Create synonym
  • Create table
  • Create trigger
  • Create type
  • Create view
  • Delete any table
  • Execute any procedure
  • Execute any type
  • Connect
  • Insert any table
  • Select any dictionary
  • Select any sequence
  • Select any table
  • Select catalog role
  • Update any table

Assign the user responsible for creating and modifying the Oracle database a specified quota (extent) in the tablespace, even if that user was assigned an unlimited tablespace when created. Otherwise, the installer will generate a "ORA-01950: no privileges on tablespace name" error.

Application user privileges

The following list contains basic privileges that should be granted to users who will only run the application.

The Privileges/Grant is only for the tables owned by the user. The following is an example script to create table update grants:
select 'grant update on '||table_name||' to [user];' from user_tables

You can write additional scripts to create similar permissions for the following privileges:

  • Alter session
  • Create session
  • Delete table
  • Execute procedure
  • Insert table
  • Select sequence
  • Select table
  • Update table
  • If you are using text indexes, you must also have privileges for CTXAPP or CTXCAT, depending on the type of text indexes you are using.