Dear Readers,
In this article, we will see the following Difference b/w Common user and Local user in Oracle 12c.
Oracle has Introduced Multitenant architecture in the version 12c and also new terminologies for users in container database called LOCAL USER and COMMON USER.
Common users
Users who exist in all containers current and future and can navigate across them.
Local users
Users who exist only within a single pluggable database (PDB) – i.e. the one where they are created.
Difference b/w Common user and Local user in Oracle 12c.
Note :
Once created, the common user will be visible across all containers. However, such a user needs explicit grants to be able to connect to and do things a pdb. And his grants can differ from pdb to pdb.
The common user can switch containers – if granted the required privileges.
|
1 2 3 4 5 6 7 |
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG4 READ WRITE NO 4 PLUG2 READ WRITE NO 5 PLUG5 READ WRITE NO |
How to create a common user.
We can create a common user using CREATE and GRANT command.
By default create command has container=ALL clause, In case of grant mention Container=ALL.
|
1 2 3 4 5 6 7 8 9 10 |
SQL> create user C##ATG IDENTIFIED BY ATG1; User created. SQL> grant connect,resource,unlimited tablespace to C##ATG; Grant succeeded. SQL> GRANT connect,resource,unlimited tablespace TO C##OTG identified by OTG ; GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO C##OTG identified by OTG ERROR at line 1: ORA-65094: invalid local user or role name SQL> grant connect,resource,unlimited tablespace TO C##OTG identified by OTG container=ALL; Grant succeeded. |
Connect to C##OTG user and perform some transaction by creating objects.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
C##OTG>> create table acc_cust (sno number,sname varchar2(10),address varchar2(30)); Table created. C##OTG>> insert into acc_cust(sno,sname,address) values(101,'VINOD','19/45.....'); 1 row created. C##OTG>> insert into acc_cust(sno,sname,address) values(102,'AJAY','14/85...'); 1 row created. C##OTG>> insert into acc_cust(sno,sname,address) values(103,'SAI','16/58.....'); 1 row created. C##OTG>> COMMIT; Commit complete. C##OTG>> SELECT * FROM acc_cust; SNO SNAME ADDRESS ---------- ---------- ------------------------------ 101 VINOD 19/45..... 102 AJAY 14/85... 103 SAI 16/58..... |
Connect to one of pluggable database and check objects created by common user in root container.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
SYS>> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG4 READ WRITE NO 4 PLUG2 READ WRITE NO 5 PLUG5 READ WRITE NO SYS>> alter session set container=plug5; Session altered. SYS>> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 5 PLUG5 READ WRITE NO SYS>> select username from dba_users; USERNAME ---------------------------------------- OUTLN SYSTEM SYS ANONYMOUS DBSNMP XDB APPQOSSYS GSMADMIN_INTERNAL GSMCATUSER SYSBACKUP C##OTG DIP SYSDG ORACLE_OCM SYSKM XS$NULL GSMUSER AUDSYS PLUSER C##ATG 20 rows selected. SYS>> conn c##OTG/OTG Connected. C##OTG>>show user USER is "C##OTG" C##OTG>>select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------ ---------- ACC_CUST TABLE SQL> select * from acc_cust ; SNO SNAME ADDRESS ---------- ---------- ------------------------------ 101 VINOD 19/45..... 102 AJAY 14/85... 103 SAI 16/58..... |
How to create and connect to a local user.
|
1 2 3 4 5 6 7 8 9 10 |
SQL> grant connect,resource,unlimited tablespace to loc_u1 identified by u1 container=all; grant connect,resource,unlimited tablespace to loc_u1 identified by u1 container=all ERROR at line 1: ORA-65065: A local user or role can only be altered within the current container SQL> grant connect,resource,unlimited tablespace to loc_u1 identified by u1; Grant succeeded. SQL>> SQL> conn loc_u1/u1 ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. |
We can’t to a local without Oracle Net Services.
configure listener file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
[oracle@dba20 ~]$ vi listener.ora plug5 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.120)(PORT = 8989)) ) ) ) SID_LIST_plug5 = (SID_LIST = (SID_DESC = (SID_NAME = microcdb) (ORACLE_HOME = /oraeng/app/oracle/product/12.1.0) ) ) [oracle@dba20 ~]$ export TNS_ADMIN=$HOME [oracle@dba20 ~]$ lsnrctl start plug5 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JUN-2018 21:33:41 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /oraeng/app/oracle/product/12.1.0/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /home/oracle/listener.ora Log messages written to /oraeng/app/oracle/diag/tnslsnr/dba20/plug5/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.120)(PORT=8989))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.120)(PORT=8989))) STATUS of the LISTENER ------------------------ Alias plug5 Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 18-JUN-2018 21:33:41 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /home/oracle/listener.ora Listener Log File /oraeng/app/oracle/diag/tnslsnr/dba20/plug5/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.120)(PORT=8989))) Services Summary... Service "microcdb" has 1 instance(s). Instance "microcdb", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully. |
configure tnsnames file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[oracle@dba20 ~]$ vi tnsnames.ora to_plug5= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.120)(PORT = 8989)) ) (CONNECT_DATA = (SERVICE_NAME=plug5) ) ) [oracle@dba20 ~]$ tnsping to_plug5 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 18-JUN-2018 21:35:45 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.120)(PORT = 8989))) (CONNECT_DATA = (SERVICE_NAME=plug5))) OK (10 msec) |
Connect and SQL*PROMPT and set LOCAL_LISTENER parameter.
|
1 2 3 4 5 |
[oracle@dba20 ~]$sqlplus / as sysdba SYS>> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.120)(PORT = 8989))) (CONNECT_DATA = (SERVICE_NAME=plug5)))' scope=spfile; System altered. SYS>> startup force |
Alter session to pluggable database and connect to LOCAL USER.
|
1 2 3 4 5 6 7 8 9 10 11 |
SYS>>alter session set container=plug5; Session altered. SYS>> conn loc_u1/u1@to_plug5 ERROR: ORA-00942: table or view does not exist Error accessing PRODUCT_USER_PROFILE Warning: Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM Connected. LOC_U1>>create table BAN_CUST(SNO NUMBER,SNAME VARCHAR2(10)); Table created. |
How to convert PDB$SEED into READ WRITE mode.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
SYS>> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG4 READ WRITE NO 4 PLUG2 READ WRITE NO 5 PLUG5 READ WRITE NO SYS>> show con_id CON_ID ------------------------------ 1 SYS>> show con_name CON_NAME ------------------------------ CDB$ROOT SYS>> alter session set container=PDB$SEED; Session altered. SYS>> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SYS>> show con_id CON_ID ------------------------------ 2 SYS>> show con_name CON_NAME ----------------------------- PDB$SEED SYS>> alter pluggable database pdb$seed close; ERROR at line 1: ORA-65017: seed pluggable database may not be dropped or altered SYS>> alter session set "_oracle_script"=TRUE; Session altered. SYS>> alter pluggable database pdb$seed close immediate instances=all; Pluggable database altered. SYS>> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED SYS>> alter pluggable database pdb$seed OPEN READ WRITE; Pluggable database altered. SYS>> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ WRITE NO SYS>> alter session set "_oracle_script"=false; Session altered. |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
Vinod
12c: Differences between Common user and Local User
Agenda
Differences b/w common user and local user.
How to create common user and how does it work?
How to create local user and how does it work?
How to convert seed database into READ WRITE mode.
Extra commands in CDB.
Sat, Jun 23, 2018 8:00 AM – 10:00 AM PDT
Please join my meeting from your computer, tablet or smartphone.
https://global.gotomeeting.com/join/423048053
You can also dial in using your phone.
United States: +1 (312) 757-3121
Access Code: 423-048-053
Time Zone Converter
https://www.timeanddate.com/worldclock/converter.html?iso=20180623T140000&p1=176&p2=137&p3=236&p4=136&p5=179
Vinod
Hello Everyone,
Great response to the session on Differences between the Common user and Local User.
Please check Vimeo video link.:
https://vimeo.com/276668325/db37172045
Bharath Kumar
This post is very useful for everyone.Thnak you Ajay and Vinod for sharing 12c sessions and giving us your precious time.
Nama
More Informative article
Ajay Kumar
If you are interested to know more details about future session please join below telegram group :
https://t.me/joinchat/JFVAtAv1TE9DGHLbJ6rZbw