Since Oracle 12c, The user can create the database called, pluggable database. The idea is to allow user to create several database for specific purposes. For example, we can have the database for HR department and another for Sale department. After finishing creating PDB, pluggable database, we can create the user. In this article, we will create user to local PDB.
1. Login as SYS user
2. Switch to pluggable data as
sql > alter session set container = pdb01;
Check current database we are in
sql > select name, pdb from all_services;
sql > create user tommy01 identified by xxxxx container=current;
Then, we need to grant necessary privileges for this new user. In this case, this user shall have enough privileges to perform tasks, such as creating table, creating view and other common tasks. Run below grant statements
sql > grant create session to tommy01 container=current;
sql > grant connect to tommy01 container=current;
sql > grant CREATE ANY TABLE to tommy01 container=current;
sql > grant INSERT ANY TABLE to tommy01 container=current;
sql > grant resource to tommy01 container=current;
sql > grant create tablespace to tommy01 container=current;
sql > grant create datafile to tommy01 container=current;
this allow user tommy01 to use data file in the current
sql > grant unlimited tablespace to tommy01 ;
sql > alter user tommy01 default tablespace data_01 container=current;
sql > grant CREATE ANY VIEW to tommy01 container=current;
sql > grant create PROCEDURE to tommy01 container=current;
sql > grant EXECUTE ANY PROCEDURE to tommy01 container=current;
Check all privileges, granted to user TOMMY01.
SELECT * FROM DBA_SYS_PRIVS where grantee in ('TOMMY01') order by 1, 2;
If any SQL command run with error, we need to grant more privileges to the user. Your comment is welcome.
