Before going into the main content, we will learn through:
What is Multitenant Architecture in Oracle Database?
To make it easier to imagine I will compare the architecture of Oracle 11g and 12c at some point, from version 11g and earlier, each instance only serves 1 database so n databases I need to create n instances. With such an architecture in the situation that if there are many databases running on 1 server, there will be many redundant resources, which are not optimized.
In version 12c, Oracle introduced the concept of Multitenant, we will have a parent database Container Database (CDB), which is responsible for storing and managing common control information. In addition, we will have child databases attached to the parent database, called Pluggable databases (PDBs). This is where the real user data is stored. In a nutshell, PDBs live on CDB. CDB provides processes and memory for PDB to work
Note: CDB can also store data, but it is recommended by Oracle not to store data in CDB to avoid conflicts affecting database performance.
There is a special PDB that is generated when initializing the CDB, called the seed PDB (PDB$SEED) and it can only be read, not modified.
1 2 3 4 5 6 7 | SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO |
The management of users and permissions is a bit different from the oracle 11g environment.
In a Multitenant environment there are two types of users:
- Common User: User used in all containers (root and all PDBs)
- Local User: A user that is only used in a specific PDB. The same username can be created in different PDBs
Likewise there are two types of roles:
- Common Role: This role is shared by all containers (Root and PDBs).
- Local Role: This role is used for specific PDBs. The role name may be the same in different PDBs.
- Check Container name, switch Pluggable
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO SQL> alter session set container=ORCLPDB1; Session altered. SQL> show con_name CON_NAME ------------------------------ ORCLPDB1 |
- Create User, Role
- Create User
- Create common user on Container Database (CDB), when creating prefix C##
1 2 3 4 5 | CONN / AS SYSDBA CREATE USER c##user1 IDENTIFIED BY password1 CONTAINER=ALL; GRANT CREATE SESSION TO c##user1 CONTAINER=ALL; |
Note: If you don’t want to use the c## character, use the following tip
1 2 3 | CONN / AS SYSDBA alter session set "_ORACLE_SCRIPT"=true; |
config system use the command: alter system set _common_user_prefix = ” scope=spfile;
- Create local use
1 2 3 4 5 6 7 8 9 10 | CONN / AS SYSDBA ALTER SESSION SET CONTAINER = ORCLPDB1; CREATE USER user2 IDENTIFIED BY password2 CONTAINER=CURRENT; GRANT CREATE SESSION TO user2 CONTAINER=CURRENT; Hoặc CONN system/password@ORCLPDB1 CREATE USER user3 IDENTIFIED BY password3; GRANT CREATE SESSION TO user3; |
- Create role
- Create common roles
1 2 3 4 5 6 7 8 | CONN / AS SYSDBA CREATE ROLE c##role1; GRANT CREATE SESSION TO c##role1; GRANT c##role1 TO c##user1 CONTAINER=ALL; ALTER SESSION SET CONTAINER = ORCLPDB1; GRANT c##role1 TO user2; |
- Create local roles
1 2 3 4 5 6 7 8 | CONN / AS SYSDBA ALTER SESSION SET CONTAINER = ORCLPDB1; -- CONN system/password@ORCLPDB1 CREATE ROLE role1; GRANT CREATE SESSION TO role1; GRANT role1 TO c##user1; |
- Assign role permissions to common and local users
1 2 3 4 5 6 7 8 9 10 11 12 | -- Common grants. CONN / AS SYSDBA GRANT CREATE SESSION TO c##user1 CONTAINER=ALL; GRANT CREATE SESSION TO c##role1 CONTAINER=ALL; GRANT c##role1 TO c##user1 CONTAINER=ALL; -- Local grants. CONN system/password@ORCLPDB1 GRANT CREATE SESSION TO user1; GRANT CREATE SESSION TO role1; GRANT role1 TO user2; |
- Create tablespace To deploy a new project, we need to create a new tablesapce and create an account assigning permissions on that new tablesapce for the project team to use. The purpose is to create tablespaces to easily manage the development capacity of a project.
1 2 3 4 5 6 7 8 9 10 11 12 | CONN / AS SYSDBA -- tạo tabespace CREATE TABLESPACE PROJECT_NAME DATAFILE '/opt/oracle/oradata/ORCLCDB/PROJECT_NAME01.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 8G; -- tạo user gán quyền trên tablespace PROJECT_NAME CREATE USER USER_NAME IDENTIFIED BY <password> DEFAULT TABLESPACE PROJECT_NAME TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- Gán quyền truy cập và kết nối cho User đã tạo GRANT CONNECT TO USER_NAME; GRANT RESOURCE TO USER_NAME; |
Hope the article is helpful for those of you who are learning about Oracle database