Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

change oracle 19c system password

hi,

I sqlplus to a oracle 19c box and it say SYSTEM  password will be expire in 7 days, what is the safest way to change it?

or simply set it to not expiration at all ?
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do not use EXPIRE when you alter user password, that system password would not expire so quickly.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4003.htm

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

@Peter,
You post doesn't make any sense.  The EXPIRE keyword forces the password to be expired immediately.

Also, the doc link you posted is for 10g.  That version is LONG since Desupported.  If you are going to post a doc link, please try to mkae sure it is at least for a Supported version.
Avatar of marrowyung

ASKER

slightwv ,

what will happen if I do not change password after 7 days? can't login using system anymore ?

You need to know if any applications or scripts might have this password hard-coded.

for me it should be a no. the oracle server is setup by us and no other thing use the system account.
I think you can maintain the password by yourself to reset it periodically and make it not expire.
but how and show the command please.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please DO NOT follow this suggestion! It's very bad practice and bad habit, too :-(

actually for system account I don't hear this before.

you should rather set up a new profile and link this with the SYSTEM user.

seems getting complex! only one method for this?
from SQL developer


User generated image
no where I can set no expire.
by doing this:
select username, account_status, EXPIRY_DATE from dba_users

User generated image
so the account never expire?
I was thinking about something as shown here:
https://www.oracletutorial.com/oracle-administration/oracle-alter-user/

You won't be able to change user's profile with the GUI elements of SQL Developer, you'll need to use good old plain SQL statements ;-)

Another nice resource on this subject:
https://thesmartpanda.com/oracle-database-user-password-expiry/
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
slightwv,

You shouldn't pay attention to Peter Chan's advice.  Pretty sure he's not an Oracle Expert.
.....

Yes.  You are in he GRACE period.
sorry, what is GRACE period is about:

PASSWORD_GRACE_TIME
The number of days after the password has expired during which you are allowed to continue to connect to the database. During the grace period, an error message is returned upon login that warns you to change your password. The grace period begins the first time a user connects after the password has expired. Value is in days.

just a message shown ? nothing else? still function as usual ?

I gave you the command to change the password in the very first post:  #a43319856 
alter user system identified by new_password;   ?

ok

Which brings up Security:  SYS and SYSTEM are very powerful and important accounts.  They will have special security concerns and probably should NOT have passwords that NEVER expire.

as long as there no application hard code the system password, it is very fine to change the system account password ?

it should be just like sa account in SQL server, changing it has no impact at all to the database.


Alex  ,


You won't be able to change user's profile with the GUI elements of SQL Developer, you'll need to use good old plain SQL statements ;-)

Oracle always do not fix old shit....



one thing, when the oracle server in mount mode, I can't do to check the existing policy

http://www.dba-oracle.com/t_make_password_not_expire.htm

Then I must make it in open mode for the command to work ?

Oracle always do not fix old shit.... 
Don't blame Oracle for this ;-) In fact, this has nothing to do with "old shit" or needing a fix -> it's not broken...
Other IDEs can do it via GUI... (whereas under the hood, it's just plain SQL)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Alex  ,,


Other IDEs can do it via GUI... (whereas under the hood, it's just plain SQL)

ok, only oracle SQL developer can't do via UI, ok !

yeah it is all plain text under the hood.

slightwv ,

Are you not connecting while under the grace period?
I am ok now as it is not expired yet, 5 days left.

As with any password on any account in any system/software:  Yes.

tks.

I would encourage you to not do this to the SYS or SYSTEM accounts on any professional database.  Even a small development database!

ok, tks. but just don't want to lock down by this expire thing.

And the data dictionary is stored where????????????? 
so this means as long as the oracle is not accessible as it is in mount mode, we can't alter the password anyway?


SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>so this means as long as the oracle is not accessible as it is in mount mode,

Sorry, I was thinking up or down, not nomount/mount/open

>>ok, tks. but just don't want to lock down by this expire thing.

There really isn't a way to keep the DBA out of the database.  You can always connect as SYS.  Even if the password has expired.
Alex ,

Counter-question: have you tried changing user passwords in mount mode?!?

At this moment I just want to check the expire policy first in mount mode.

it seems not possible as it show error message.

slightwv ,

You can always connect as SYS.

but now I want to check expiration policy in the standby server ,which is in mount mode.

please suggest command.


Checking profile values is a different question.

>>but now I want to check expiration policy in the standby server ,which is in mount mode.

I've not used Dataguard much but the standby is supposed to be the same as the primary.  What makes you think the profiles would be different between the systems?
I've not used Dataguard much but the standby is supposed to be the same as the primary.  

it is not, it is in mount mode.
this is the error message when I check password policy:


User generated image
You mentioned a standby database.  I assumed Dataguard.

You cannot query primary views/tables/??? in mount mode.  There are only a few system level views that are available in that state.  Obviously dba_users isn't.  Please stop trying.

If this isn't a Dataguard standby, what type of standby is it?  

If you absolutely must run that query, then open the database, run the query, then perform a  shutdown/startup mount to get it back to where it is.
There are only a few system level views that are available in that state.  Obviously dba_users isn't.  Please stop trying.

I learn form this page:

http://www.dba-oracle.com/t_make_password_not_expire.htm

If this isn't a Dataguard standby, what type of standby is it?  

it is a DG standby.

If you absolutely must run that query, then open the database, run the query, then perform a  shutdown/startup mount to get it back to where it is.
ok, so I am sorry what is the full command to change to open ?

shutdown should be shutdown abort ?

start up in mount mode should be ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
slightwv ,

is that they should have the same database profile information.  In other words the database are more of less copies of each other.

so in DG the DG standby should have the exactly copy of user profiles too?
but one thing, the DG hasn't been well setup yet, so it can have trouble.


usually this is the command the oracle DBA will use, I copy from internet some where before.

User generated image
User generated image
is it making any sense at all ?
I found this one :
https://docs.oracle.com/cd/B19306_01/server.102/b14231/start.htm

much closer to my need and I do this:
ALTER DATABASE OPEN;

Open in new window


and as according to this:

http://www.dba-oracle.com/t_make_password_not_expire.htm

I do select username, account_status, EXPIRY_DATE from dba_users where username='SYSTEM';

I see this:

SQL> select username, account_status, EXPIRY_DATE from dba_users where username='SYSTEM';


USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS                   EXPIRY_DA
-------------------------------- ---------
SYSTEM
OPEN

Open in new window


what is that mean ?  

OPEN means unlimited?


ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
slightwv ,

 it wouldn't make sense to fail over to the standby and have completely different configurations.  Does it?
in MySQL it is correct has MySQL also sync user account and password.

NEVER, NEVER, EVER shutdown abort unless you absolutely have to.  Well, if you care about your database......

tks.   one thing our DR server do not have any usage yet and the DG is not setup yet.

The docs describe the columns in dba_users:
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_USERS.html#GUID-309FCCB2-2E8D-4371-9FC5-7F3B10E2A8C0 

that link do not show what OPEN means. it said:

  • OPEN
    The account is open

so it is not expired , or it will shows  EXPIRED(GRACE) before it really expired, right?

so by this the system password is not expire yet or going to expire.

I still suggest to not set SYS and SYSTEM to never expire.  It's a huge security issue!!!!!!

and changing ONLY Sys and system password has no harm except application hardcoded.

so once shut down I can do 
STARTUP MOUNT

Open in new window

to make oracle start in mount mode?

I believe the original question has been asked and answered.

As far as all you other outstanding questions asked in this question, the answers are in the docs.
tks both.

account password policy is open means not going to expire soon.