Jump to content
richchen65

alter user sys password question in 12cR2 database

Recommended Posts

Dear Oracle experts,

The password changed time will be recorded in the column (Ptime ) in sys.user$  prior to 11gR2 .

I found out the Ptime value won't be updated in 12cR2 after the sys password was changed.

Any idea ?

Rich

 

Share this post


Link to post
Share on other sites

Hi Rich,

Sorry, I cannot find any changed behaviors in 12c r2 for the prime column in SYS.user$.

Could this be a PDB vs CDB issue?

This query has always worked for me:

select name, 
   ctime, 
   ptime 
FROM 
   sys.user$ 
WHERE 
    name = 'SYS';

Please see if you can document this new behavior in a script, and share it with us!

Good luck!

Share this post


Link to post
Share on other sites

Hi Don,

I think I didn't describe the question clearly.

This question is from the internal auditors who will check the password changing policy is enforced or not.

They rely on the timestamp in  PTIME column of sys.user$ to determine if the password was changed periodically.

Prior to 11gR2 , the timestamp in  PTIME of SYS will be updated soon after the password was changed but not in 12cR2.

In 11gR2:

SQL> alter user sys identified by xxxx;   --- Done without error

SQL> select ctime, ptime From sys.user$ where name = 'SYS';  --- Ptime will be the password changed time

In 12cR2:

 SQL> alter user sys identified by xxxx;  --- Done without error

SQL> select ctime, ptime From sys.user$ where name = 'SYS';  --- Ptime will NOT be changed ..in my case , same as ctime

 

Rich

Share this post


Link to post
Share on other sites

Hi Rich,

Yeah, I understand your issue, and your real-world observation demonstrates that Oracle has changed the mechanism for tracking password changing for the SYS user.

Again, I have not seen this, but I believe you!  I researched this on the web and I found nothing, sorry.

Can you use the MOSC bug database?  It could be a bug, but I recommend that you report your observation to MOSC via a service request.

Again, sorry that I could not help, but i’m Sure that Oracle support can help:

http://support.oracle.com 

Let us know what you find.

Good luck!

 

Share this post


Link to post
Share on other sites

Hi Don,

This problem will happen after applying 2018 JULY PSU  (Bug 28538439) ..

The solution:

1. Apply 2018 OCT PSU patch (Patch No# 28662603)

2. Apply Bug 28538439 hotfix

3. Setting hidden parameter _enable_ptime_update_for_sys=TRUE and restart the database.

The solution works for me.

Thank you for running this forum for all Oracle experts around the world.

Rich

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×