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 220.127.116.11.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 18.104.22.168.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';
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.