1

So I want to create a user (schema) and a tablespace for a project, and the I found the following examples online. They work, and I get no errors.

As far as I know and if I understand the sources correct, my goal to have a separate user requires the following

  • create a user
  • create a tablespace
  • unlock the user
  • grant the user session privileges

Am I missing a step? Basically I want to create/drop tables, create sequences/triggers/views basically the standard SQL developer stuff.

CREATE USER myuser IDENTIFIED BY mypassword 

DEFAULT TABLESPACE users 
TEMPORARY TABLESPACE temp;

CREATE SCHEMA AUTHORIZATION myuser;

ALTER USER myuser ACCOUNT UNLOCK

GRANT CREATE SESSION TO myuser;

ALTER USER myuser DEFAULT TABLESPACE USERS

create tablespace myspace
  logging
    datafile 'C:\Oracle\oradata\myspace.dbf'
  size 32m 
  autoextend on 
  next 32m maxsize 2048m
  extent management local;

However, once I connect to the Oracle environment, I am able to connect, but I cant create tables at all with that user. I am missing something here. Basically, I just want to set things up to play with it, but I think I am missing some kind of privileges step here.

Any help would be really appreciated.

Shyam
  • 2,357
  • 8
  • 32
  • 44

1 Answers1

2

Obviously it is

GRANT CREATE TABLE TO myuser;
GRANT CREATE TRIGGER TO myuser;
GRANT CREATE SEQUENCE TO myuser;
zerkms
  • 249,484
  • 69
  • 436
  • 539
  • could it be that easy?! I thought it would be more like a role thing. But if a standard user would need a grant for a table, how about sequences and triggers? I work with SQL import and export scripts, which uses triggers/views/sequences among tables :S – Shyam Feb 11 '11 at 02:57
  • I assume for views it is CREATE VIEW TO ? – Shyam Feb 11 '11 at 04:39
  • You also need space quota(s): "alter user myuser quota 100M on users". Replace 100M with another size or "unlimited". – redcayuga Feb 16 '11 at 22:19