Skip to Main Content
  • Questions

  • Difference in DB Behaviour in Restricted Mode

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Amit.

Asked: January 09, 2023 - 7:02 am UTC

Last updated: January 13, 2023 - 4:52 am UTC

Version: Oracle Database 19c Enterprise Edition 19.16.0.0.0

Viewed 1000+ times

You Asked

Background:
• As part of our system daily process we change Database to Restricted Mode so that other operation user (who doesn’t have restricted environment privilege) cannot access the environment. The environment can be accessed by DBA Users, IT users during the restricted hours for monitoring and batch job etc. process. This is currently working well in 12C environment.
• We tried to simulate this scenario during our UAT for oracle 19C upgrade
• However, After we set the 19C database to restricted access mode, even DBA, IT users were unable to access the environment.
• When we check in v$instance >> logins column shows as RESTRICTED.

Upon further checking it was identified that a new setup for Oracle connection “UR=A” is required in TNSNames.ora.

Concern: We are concern about doing these changes, as this parameter “UR=A” is not a new parameter introduced in 19C. This is an existing setup suggested by oracle in previous environment itself. But in our current 12C environment we are not using this parameter. Even then the restricted mode is working perfectly.
Question:
1. Why do we need to have this setting to be done?
2. Any other error that can cause this to behave differently?

Thanks for your help.

and Connor said...

This also depends on the version of the oracle client that is being used to connect to the database, and the use (or not) of static SID registration versus Service registration with the listener.

Since this parameter was introduced back in (I think0 10g, my hypothesis is that as part of changing from 12c to 19c, there's also been a change in one or more of the above elements as well.





Rating

  (1 rating)

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

Comments

Amit Chaudhary, January 11, 2023 - 11:31 am UTC

In that case may i know if there's any other way to handle this scenario.

How can we restrict other user entering the application during the offline users, while DBA users and IT users can still access to the system?
Connor McDonald
January 13, 2023 - 4:52 am UTC

LOGON trigger seems an option here.
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

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database