Default tables of New Database in Oracle and its Super Admin Login Account?-CodePudding

I come from SQL Server background. Please bear with me if it is too basic question for Oracle experts.

I installed Oracle 21 Developer edition. I expected that on creating new database only my tables should be visible. However I see lot of system tables, is this by default?

Has this anything to do with: because I have logged-in as SYS and SYSDBA role, as following? What account and database privilege I should use so that I could see only custom tables created by me?

CodePudding user response：

Those Schemas/Users you are using to log into Oracle...SYS, SYSDBA etc... without going much into detail those are akin to SQL Server's System Tables and the master table. However they are also admin level users by default (similar to the sa user in SQL Server) and allow you to log in using those users to begin setting up your database. The first thing you want to do is to setup your own personal Oracle admin type user/schema.

I worked in an Oracle shop for about 8 years. I've now been working in a SQL Server shop for 3.

When I spin up my own instances of Oracle I run a very basic create user script. After I have it in place I can alter it as needed but the main grants I apply to my user is of course the ability to CREATE TABLES and SEQUENCES. In the latest version of Oracle you might not need to use SEQUENCES to apply to your PRIMARY KEY fields but instead create them similar to SQL Server and use IDENTITY fields for your Primary Keys.

CREATE USER SCRIPT BASIC TEMPLATE

CREATE USER AlmostAdmin
 IDENTIFIED BY SuperSecretPassword
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 QUOTA UNLIMITED ON USERS
 PROFILE DEFAULT;


/* Any user that logs in must have create session */
GRANT CREATE SESSION TO AlmostAdmin;

/* Tables */
GRANT SELECT ANY TABLE TO AlmostAdmin WITH ADMIN OPTION;
GRANT CREATE ANY TABLE TO AlmostAdmin WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO AlmostAdmin WITH ADMIN OPTION;
GRANT DELETE ANY TABLE TO AlmostAdmin WITH ADMIN OPTION;
GRANT DROP ANY TABLE TO AlmostAdmin WITH ADMIN OPTION;
GRANT INSERT ANY TABLE TO AlmostAdmin WITH ADMIN OPTION;
GRANT UPDATE ANY TABLE TO AlmostAdmin WITH ADMIN OPTION;

/* Procedures */
GRANT CREATE ANY PROCEDURE TO AlmostAdmin WITH ADMIN OPTION;
GRANT ALTER ANY PROCEDURE TO AlmostAdmin WITH ADMIN OPTION;
GRANT DROP ANY PROCEDURE TO AlmostAdmin WITH ADMIN OPTION;
GRANT EXECUTE ANY PROCEDURE TO AlmostAdmin WITH ADMIN OPTION;

GRANT SELECT ANY DICTIONARY TO AlmostAdmin WITH ADMIN OPTION;

/* Sequences */
GRANT ALTER ANY SEQUENCE TO AlmostAdmin WITH ADMIN OPTION;
GRANT CREATE ANY SEQUENCE TO AlmostAdmin WITH ADMIN OPTION;
GRANT DROP ANY SEQUENCE TO AlmostAdmin WITH ADMIN OPTION;
GRANT SELECT ANY SEQUENCE TO AlmostAdmin WITH ADMIN OPTION;

/* Triggers */
GRANT ALTER ANY TRIGGER TO AlmostAdmin WITH ADMIN OPTION;
GRANT CREATE ANY TRIGGER TO AlmostAdmin WITH ADMIN OPTION;
GRANT DROP ANY TRIGGER TO AlmostAdmin WITH ADMIN OPTION;

CodePudding user response：

Since you are logged in as SYS, SYSDBA, you can see system tables.

To create your own, Logged in as SYSDBA and follow the below steps.

CREATE PERMANENT TABLESPACE

CREATE TABLESPACE tbs_perm_100
  DATAFILE 'tbs_perm_100.dat' 
    SIZE 10M
    REUSE
    AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

CREATE TEMPORARY TABLESPACE

CREATE TEMPORARY TABLESPACE tbs_temp_100
  TEMPFILE 'tbs_temp_100.dbf'
    SIZE 5M
    AUTOEXTEND ON;

CREATE USER

CREATE USER Abhijeet
  IDENTIFIED BY password123
  DEFAULT TABLESPACE tbs_perm_100
  TEMPORARY TABLESPACE tbs_temp_100
  QUOTA 20M on tbs_perm_100;

ASSIGN SYSTEM PRIVILEGES TO NEW USER

GRANT ALL PRIVILEGES TO Abhijeet;

Now you can log in using Abhijeet and password123.

Then you can create tables, views and etc...

CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50)
);