Here are some sample DDL commands to create:

  • A database user (schema)
  • who is member or a role
  • and this role has permissions as an owner of this schema (can create tables etc...)
-- Create the user
CREATE USER MY_SCHEMA 
  IDENTIFIED BY password
  DEFAULT TABLESPACE "DATA_FED01"
  TEMPORARY TABLESPACE "TEMP"
  ACCOUNT UNLOCK ;

-- Make sure that the user can access the tablespaces needed
alter user MY_SCHEMA quota unlimited on DATA_TS;
alter user MY_SCHEMA quota unlimited on INDEX_TS;

-- Create a role for the user
create role SCHEMA_ROLE not identified;
grant alter session to SCHEMA_ROLE;
grant create session to SCHEMA_ROLE;
grant create materialized view to SCHEMA_ROLE;
grant create procedure to SCHEMA_ROLE;
grant create sequence to SCHEMA_ROLE;
grant create synonym to SCHEMA_ROLE;
grant create table to SCHEMA_ROLE;
grant create trigger to SCHEMA_ROLE;
grant create type to SCHEMA_ROLE;
grant create view to SCHEMA_ROLE;
grant query rewrite to SCHEMA_ROLE;

-- Grant this role to the user
grant SCHEMA_ROLE to MY_SCHEMA;

-- Grnat some permissions directlty to the user
grant create table to MY_SCHEMA;
grant create materialized view to MY_SCHEMA;

-- Ready to create tables and other stuff
ALTER SESSION SET CURRENT_SCHEMA=MY_SCHEMA;

CREATE TABLE ....