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

oraclesqlplus

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.

Best Answer

  • 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.

Related Question