How to create database/tablespace/schema and users for quickstart developers on Oracle 12?

Question

Background

I need to create a contained development space for a new application. This requires a user account to connect with all permissions, such as create/drop tables and stored procedures. Following the principle of least privilege, all of the application's database operations will be wrapped in stored procedures, so another application user account is required with only execute permissions to those stored procedures (itemized in production; all in development).

I've got a fresh Oracle 12c installation on Windows Server 2008 R2 Enterprise with available connections to what I think is called a database named 'orcl'. Now I can start sqlplus from cmd.exe

sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 8 09:49:03 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>

---

How do I functionally accomplish the following?

I've provided the MySQL console (mysql -u root -p) equivalent instructions under each of my requirements.

• new database/schema created

  CREATE DATABASE appdb; 
  

• new dbo user created for DDL

  CREATE USER 'appdb_owner'@'localhost' IDENTIFIED BY 'c0mpl3xpw0rd';
  GRANT ALL PRIVILEGES ON appdb.* TO 'appdb_owner'@'localhost';
  

• new test user for application execution

  CREATE USER 'appdb_user'@'localhost' IDENTIFIED BY 'simplerpw0rd';
  GRANT EXECUTE ON appdb.* TO 'appdb_user'@'localhost';
  

---

Failures

I'm very familiar with MySQL, but totally new to Oracle and sqlplus. I think some of my confusion stems from the distinction between a database, tablespace, and a schema from my lesser familiarity with DB2. From what I've seen on the 11.1 create database page, it looks unnecessarily complex (like a new installation) for what I'm trying to achieve, so I think what I am looking for instead is a new schema that will simply provide isolated access to the data. But when looking at the 10.2 documentation for schema creation, it says this:

This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.

But when I try to create a user from the 10.2 documentation

sqlplus / as sysdba
SQL>CREATE USER appdb_owner IDENTIFIED BY c0mpl3xpw0rd;

I get the following error

ORA-65096: invalid common user or role name

Which led me to an answer about distinguishing between common names, which I gather have access to all schemas, which is not what I want. So I'm not sure which comes first, the chicken or the egg?

I found another answer that said I needed to connect as SYSTEM, but sqlplus / as SYSTEM resulted in an error without even prompting for the password.

• a1ex07: It seems you have Oracle12c with pluggable databases, and you're trying to create common user without c## prefix. Workaround is dba-oracle.com/t_ora_65096_create_user_12c_without_c_prefix.htm
• Jeff Puckett: @a1ex07 I have specifically referenced that workaround in my failures section because I don't want to create a common user. I want quarantined users with permissions isolated to the application environment.
• Andrew Brennan: A "database" in Oracle is not the same as in MySQL. In Oracle you create schemas inside users. There are no databases as you would think of them from the MySQL perspective.

Answer 1

• You already have a database, so you don't need the 'create database'.

• Since you are working with a 12.1 database, you need to throw out your 10.2 docs and go to the 12.1 doc set.

• When you create a user, don't use that @localhost construct. Your simple create user foo identified by bar is the correct syntax, but as @a1ex07 pointed out, you need to be attached to the pluggable database, not the container. This is a drastically new architecture for Oracle, beginning with 12c. You need to spend a bit of time with the 12c Concepts manual (see my link above), especially Part VI - Mutlitenant Architecture.

Answer 2

i assume that you have installed oracle database 12c. if that's correct, you should can manage oracle database in website GUI. maybe you can open the url such as https://:5500/em which 5500 is the default port when you installed this oracle database. you can manage the database such as tablespace, users, and etc in there.

i never installed it on windows to be honest, i usually installed it in linux. but it usually use the same way.

but if you want to make database, you can use 'dbca' in command prompt, but the PATH you have to in oracle database installation directory. and run dbca, maybe in windows dbca.exe i don't know. after that, the GUI setup should show up.

C:\Program Files\$ORACLE_HOME\dbca.exe

if you want to make user on your exist database, i usually use this below script when i got error like you.

sqlplus system/password as sysdba 
Sql> alter session set "_ORACLE_SCRIPT"=true; 
Sql> create user <name> identified by <password>; 
Sql> grant dba to <name>; 
Sql> connect <name>/<password>;

correct me if i wrong okay?

• Jeff Puckett: thanks for the tip! However, this requires flash which I'm not at liberty to install on the server, but we could temporarily open a firewall port to allow flash-enabled browser access from another machine. Could you please go into a little more detail for the steps needed for my requirements? also, yes I am running 12c and have updated my question with more specific environment version information.
• Yoga: @JeffPuckettII i have edited my answer, hope it would answer your question