Skip to Main Content
  • Questions

  • Is external (OS) identification possible for PDB

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ilia.

Asked: April 17, 2016 - 1:47 pm UTC

Last updated: May 03, 2022 - 2:46 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hi,

I need to create user(s) that will be externally (OS) identified and will be defined in a specific container (local to PDB user).

The statement "CREATE USER xxx IDENTIFIED EXTERNAL CONTAINER=yyy" works with no error. I see created user, but I cannot connect as this user using bequath protocol (say "sqlplus /").

I suppose once "CREATE USER ..." works normally there should be a way to connect to the database as this user, but this requires specific environment settings (replacement to $ORACLE_SID?)

I am working in Linux. Is there a way to setup user environment to allow bequath connection with OS authentication?

Thanks in advance,
Ilia

and Connor said...

Yes you can, but from the database's perspective, you are coming in via SQL Net, so remote_os_authent would need to be true,

eg

SQL> alter session set container = orcl;

Session altered.

SQL> create user ops$connor identified externally;

User created.

SQL> grant create session to ops$connor;

Grant succeeded.

SQL> show parameter remote_os

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent       boolean  TRUE
remote_os_roles        boolean  FALSE

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[connor@localhost ~]$ id
uid=1001(connor) gid=1001(connor) groups=1001(connor) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[connor@localhost ~]$ sqlplus /@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 17 18:59:51 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It seems to me I found a better solution

Ilia Vinikovetsky, April 18, 2016 - 5:46 am UTC

Some time after submitting my question I found that with following definitions:
export ORACLE_SID=<my_db_sid>
export TWO_TASK=<my_cont_name>

I solved the problem:
[cust1@my-host ~]$ sqlplus /

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 18 08:40:35 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Sun Apr 17 2016 17:08:01 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL>

This way I don't need to modify our maintenance scripts utilizing OS identification for connecting to Oracle.

Thank you anyway,
Ilia
Connor McDonald
April 18, 2016 - 11:24 am UTC

Setting TWO_TASK=xxx is pretty much equivalent to sqlplus /@xxx

Glad you got it working as you need

Remote Auth Disclaimer

Laurent Schneider, December 14, 2020 - 10:51 am UTC

Hi

Remote OS allows any server to connect to the pluggable, defeating the server security. The parameter is deprecated and should only be used in exceptional cases.

As the OS-authentication works in the container, simply create an OS user C##USER01 and grant SET CONTAINER to that user to set the database. This could even be done in a logon trigger

alter system set os_authent_prefix='C##' scope=spfile;
create user c##user01 identified externally container=all;
grant alter session, create session to c##user01 container=all;
alter session set container=pdb01;
grant set container to c##user01 container=current;


Cheers
Laurent

PS: I apologize for the bad rating. The answer is accurate, but the security issue is huge
Connor McDonald
December 17, 2020 - 4:31 am UTC

Fair point - we should have highlighted the risks

alternative has problems too

Nicole, April 28, 2022 - 6:27 pm UTC

The downside to Laurent's suggested way is that you now end up creating a common user in all of your pluggable databases (and your root container) whether you need one or not. That's a security issue as well. And also this means your username has to start with C## which may be problematic if your unix accounts have naming standards, unless you circumvent that requirement.

Anyway to do this without setting REMOTE_OS_AUTHENT to true and with only a local user?


Connor McDonald
May 03, 2022 - 2:46 am UTC

I still prefer using a wallet to allow this

https://connor-mcdonald.com/2015/09/21/connection-shortcuts-with-a-wallet/

It gives the same "look", ie, not having a password in a script etc
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.