Oracle Active Dataguard and User passwords

dataguardoracle

I support a database that uses an active dataguard physical standby. The standby database is configured to let users access the read only standby for read only queries and reporting. We do this allow querying of production data, without risking user queries creating problems with locking/blocking.

The one problem with this, is users are unable to reset their own passwords, because they are going given the connection details for the read only database. I am trying to think of a way that would allow the users to reset their own passwords from the read only standby, but I am coming up late. I'm wondering if anyone has dealt with a similar issue has found a work around.

Right now, every time one of the users who only has access to the standby database, has their password expire, they reach out to a member of the dba team and have their password reset manually.

I am trying to find a way to allow users to be, logged into the standby database, but reset their password in the primary copy, without having to give the users the production connection details.

Best Answer

You can use the same "ugly" mechanism Oracle uses to generate AWR reports for standby hosts. Create a database link that actually points back to primary and execute PL/SQL procedure on primary, through DB link.

Related Solutions

Oracle Security – Why OS Authentication is Considered Poor

Consider the following scenario:

There is a Unix user named gaius on the Oracle server with external authentication, so in Oracle there is a corresponding user called ops$gaius. When logged into a shell, I can also log straight into my Oracle schema, and my cron jobs don't need a password embedded in script either.
Remote OS authentication is permitted, on the assumption that the LAN is 100% secure and the clients can be trusted (same as rlogin/rsh used to be normally allowed)
An attacker gets his or her laptop onto the LAN by whatever means, knows that I work there, and creates a local user on their laptop called gaius and runs SQL*Plus as that user
Oracle sees (i.e. OSUSER in V$SESSION) is gaius and logs that remote user in as ops$gaius

That's not only laughably easy to spoof, but putting on my cynic's hat, Oracle can't make any more money selling you their fancy single sign-on product... Which by the way does fulfill all the points you raise as advantages of OS-level auth. Two passwords better than one is entirely spurious; most people will set them to be the same anyway (there's no mechanism in Oracle to prevent this).

The general principle is that it is extremely difficult to defend in software when an attacker has physical access. And never trust the client.

Oracle password has expired, however I have no way of changing it

If the account is locked, as far as I know, the user can't change it. The Oracle output in your question shows the account is not locked, but expired.

Log in to your database as a user who has privilege to alter other users (for example, SYS) and issue the following command:

ALTER USER tmpdbo IDENTIFIED BY new_password;

You can change the expiration behaviour. There already was a question about that on Stack Overflow:

Make Oracle Password Never Expire

Related Question