Search code examples
oracle-databaseoracle12cprivilegesaccount

Privileges needed to create schema (Oracle)

I want to import schema to my new host. First I had created new user account:

CREATE USER test IDENTIFIED BY test;

What kind of privileges I need to grant to have super role?

(create schema, tables, packages, triggers...etc)

It's one privilege to grant me access to all of them?

Solution

  • You should grant only those privileges that are required for a newly created user to work. One by one.

    CREATE SESSION is the first one; without it, user can't even connect to the database. CREATE TABLE is most probably also required, if user TEST is going to create his own tables. That's enough to get it started. Once it appears that user needs to create a procedure, you'll grant CREATE PROCEDURE. And so forth.

    There are/were roles named CONNECT and RESOURCE which contained the "most frequent" privileges one needed, but their use is - as far as I can tell & in my opinion - discouraged.