Oracle 18c Docker部署后测试表创建权限与Schema问题咨询

Hey there! Let's break down your questions one by one, aligned with Oracle best practices for your Docker-based 18c setup.

1. Which User Should I Use to Create Test Tables? Best Practices #

First off, avoid using SYS, SYSTEM, or even PDBADMIN for day-to-day test/development tasks. These are administrative accounts with broad, system-level privileges—using them for regular work raises the risk of accidental changes to critical database configurations or objects.

The industry standard best practice is to create a dedicated, limited-privilege user for your testing needs. Here's how to set this up in your PDB (ORCLPDB1):

• First, connect to your running container and log in as SYS or SYSTEM (they hold the admin rights needed for user creation):

  sudo docker exec -it oracle18se sqlplus SYS/Oradoc_db1@ORCLPDB1 AS SYSDBA
  

  (Note: Oradoc_db1 is the default password for Oracle's official Docker images—adjust if you set a custom password during setup.)
• Once connected, create your test user and grant essential permissions:

  -- Create user with secure password and assign unlimited quota on default tablespace
  CREATE USER test_dev IDENTIFIED BY your_strong_password 
  DEFAULT TABLESPACE users 
  QUOTA UNLIMITED ON users;
  
  -- Grant basic permissions to connect to the database and create tables
  GRANT CREATE SESSION, CREATE TABLE TO test_dev;
  

• Now you can log in as test_dev and create your test tables safely without risking administrative-level changes.

If you insist on using PDBADMIN (not recommended), you can grant it the missing CREATE TABLE privilege:

GRANT CREATE TABLE TO PDBADMIN;

But dedicated test users remain the cleaner, safer choice for long-term use.

2. What Schema Name Should I Specify? #

In Oracle, a schema is directly tied to a user—each user has exactly one schema with the exact same name as the user.

• If you're logged in as your dedicated test user (e.g., test_dev), you don't need to specify a schema name when creating a table. It will automatically be created in your own schema (test_dev):

  CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
  );
  

• If you want to explicitly name the schema for clarity, use the user's username as the schema name. For example, creating the table in test_dev's schema explicitly:

  CREATE TABLE test_dev.Persons (...);
  

• If you try to create a table in another user's schema (e.g., CREATE TABLE another_user.Persons...), you'll need either the CREATE ANY TABLE privilege or specific permission to create objects in that schema.

As for your ORA-01031 error with PDBADMIN: This occurs because PDBADMIN doesn't have the CREATE TABLE privilege by default. Even as the PDB's admin user, it doesn't automatically get all object-creation rights—you'd need to grant that permission manually. Again, switching to a dedicated test user is the better practice.

内容的提问来源于stack exchange，提问作者Alexey Starinsky