OraFAQ Forum: Security » Lock the SYS account

Home » RDBMS Server » Security » Lock the SYS account

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Lock the SYS account [message #622550]

Wed, 27 August 2014 09:54

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Lalit Kumar B wrote on Wed, 27 August 2014 16:07

Lock the sys account? Why? A user not having the necessary privileges would not be able to login as sys. So why the question to lock?

You cannot lock SYS.

Report message to a moderator

Re: Locking the system user id in oracle [message #622552 is a reply to message #622550]

Wed, 27 August 2014 10:02

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Michel, it was a question to OP. Perhaps, "how?" would have been more appropriate than "why?". Sorry for the confusion.

An analogy, I cannot lock my car while I am still inside.

Report message to a moderator

Re: Locking the system user id in oracle [message #622553 is a reply to message #622552]

Wed, 27 August 2014 10:14

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can't? I can, there is a button for this. Smile

And you can lock your own account in Oracle (if you are not SYS):

SQL> show user
USER is "MICHEL"
SQL> alter user michel account lock;

User altered.

SQL> connect michel/michel
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.

Anyway, it is better you can't lock SYS:

SYS> show user
USER is "SYS"
SYS> alter user sys  account lock;

User altered.

SYS> connect sys as sysdba
Enter password: 
Connected.

Even if (for completion):

SQL> select account_status from dba_users where username='SYS';
ACCOUNT_STATUS
--------------------------------
LOCKED

Lalit : Removed superfluous lines. I wonder how these extra lines get appended.

[Updated on: Wed, 27 August 2014 10:24] by Moderator

Report message to a moderator

Re: Locking the system user id in oracle [message #622554 is a reply to message #622553]

Wed, 27 August 2014 10:21

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

LOL! No more experiments now Smile

Report message to a moderator

Re: Locking the system user id in oracle [message #622555 is a reply to message #622552]

Wed, 27 August 2014 10:21

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

Lalit Kumar B wrote on Wed, 27 August 2014 16:02

Michel, it was a question to OP. Perhaps, "how?" would have been more appropriate than "why?". Sorry for the confusion.

An analogy, I cannot lock my car while I am still inside.

It's the wrong question though. OP is asking about system not sys.

Report message to a moderator

Re: Locking the system user id in oracle [message #622557 is a reply to message #622555]

Wed, 27 August 2014 10:54

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

That's honestly my bad CM /forum/fa/1587/0/

Edit : Didn't realize it was CM and not MC.

[Updated on: Wed, 27 August 2014 10:59]

Report message to a moderator

Re: Locking the system user id in oracle [message #622560 is a reply to message #622553]

Wed, 27 August 2014 11:51

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

One can lock the sys account:

12c orclz>
12c orclz>
12c orclz> alter user sys account lock;

User altered.

12c orclz> conn sys/oracle
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
12c >

Anyone know how I did that?

Report message to a moderator

Re: Locking the system user id in oracle [message #622562 is a reply to message #622560]

Wed, 27 August 2014 11:59

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You didn't connect AS SYSDBA.

Report message to a moderator

Re: Locking the system user id in oracle [message #622565 is a reply to message #622560]

Wed, 27 August 2014 12:01

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Account_status in dba_users with username "SYS", will reveal the truth even after failed attempts of locking SYS.

Report message to a moderator

Re: Lock the SYS account [message #622568 is a reply to message #622562]

Wed, 27 August 2014 12:13

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

Michel Cadot wrote on Wed, 27 August 2014 17:59

You didn't connect AS SYSDBA.

Correct. Anyone know how I managed that?

[Updated on: Wed, 27 August 2014 12:13]

Report message to a moderator

Re: Lock the SYS account [message #622569 is a reply to message #622568]

Wed, 27 August 2014 12:16

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Since Oracle still allowed you.

Report message to a moderator

Re: Lock the SYS account [message #622570 is a reply to message #622568]

Wed, 27 August 2014 12:29

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Even if you don't set the parameter you set you have the same message. Wink

(It seems Oracle checks SYS account status before checking you specified "AS SYSDBA".)

[Updated on: Wed, 27 August 2014 12:31]

Report message to a moderator

Re: Lock the SYS account [message #622571 is a reply to message #622570]

Wed, 27 August 2014 12:34

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

You have the answer, Michel. I thought you probably would. I'll post the answer tomorrow, in case anyone wants to try and work it out. Hint: think back to release 7.

Report message to a moderator

Re: Lock the SYS account [message #622572 is a reply to message #622571]

Wed, 27 August 2014 12:38

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Another hint: you can then connect if the account is unlocked.

Report message to a moderator

Re: Lock the SYS account [message #622577 is a reply to message #622572]

Wed, 27 August 2014 14:01

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

In a quite old AskTom thread, Thomas said :

Quote:

If an user is in the dba group, then that user can connect "/ as sysdba", and they can connect "anything/anything as sysdba"

Report message to a moderator

Re: Lock the SYS account [message #622580 is a reply to message #622577]

Wed, 27 August 2014 14:08

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is not that, read what I said:

Michel Cadot wrote on Wed, 27 August 2014 18:59

You didn't connect AS SYSDBA.

[Updated on: Wed, 27 August 2014 14:09]

Report message to a moderator

Re: Lock the SYS account [message #622596 is a reply to message #622580]

Wed, 27 August 2014 23:28

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Ok, understood. Did some research and got the answer. It's the parameter O7_DICTIONARY_ACCESSIBILITY set to TRUE after logging as sysdba. Then we can simply connect without sysdba, but of course we can't perform any operations of sysdba.

Report message to a moderator

Re: Lock the SYS account [message #622598 is a reply to message #622596]

Thu, 28 August 2014 00:49

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But this is not the only consequence of this parameter, it is a BIG security hole.

And as I said, you didn't have to set this parameter to get the message as "SYS account is locked".

Report message to a moderator

Re: Lock the SYS account [message #622602 is a reply to message #622598]

Thu, 28 August 2014 01:21

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Reading about the parameter and understanding in words was fine, but when I did it practically I failed to replicate :

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 28 10:59:18 2014

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

SQL> conn sys@pdborcl as sysdba
Enter password:
Connected.
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

System altered.

SQL> shutdown immediate;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.
SQL> conn sys@pdborcl
Enter password:
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

SQL> conn sys@pdborcl as sysdba
Enter password:
Connected.
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE

It is set to TRUE, however, I am unable to connect without sysdba. Am I doing anything wrong here? Or is it the wrong container I am experimenting with? I tried with CDB, the show parameter doesn't even return anything Sad

Report message to a moderator

Re: Lock the SYS account [message #622606 is a reply to message #622602]

Thu, 28 August 2014 01:32

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SYS> @v

Version Oracle : 10.2.0.4.0

SYS> show parameter O7
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
SYS> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

System altered.

SYS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup
ORACLE instance started.
Total System Global Area  209715200 bytes
Fixed Size                  1295832 bytes
Variable Size             121637416 bytes
Database Buffers           75497472 bytes
Redo Buffers               11284480 bytes
Database mounted.
Database opened.
SYS> connect sys
Enter password: 
Connected.

SYS> @v

Version Oracle : 11.2.0.1.0

SYS> show parameter O7
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
SYS> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

System altered.

SYS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup
ORACLE instance started.
Total System Global Area  188321792 bytes
Fixed Size                  1373432 bytes
Variable Size             138414856 bytes
Database Buffers           46137344 bytes
Redo Buffers                2396160 bytes
Database mounted.
Database opened.
SYS> conn sys
Enter password:
Connected.

Either they remove this from 12c either you are not started with the spfile.
Post result of

show parameter spfile
show parameter ifile

Report message to a moderator

Re: Lock the SYS account [message #622619 is a reply to message #622606]

Thu, 28 August 2014 02:46

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Michel Cadot wrote on Thu, 28 August 2014 12:02

Post result of

show parameter spfile
show parameter ifile

Here you go,

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 28 13:10:48 2014

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

Enter password:

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

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\APP\LALIT\PRODUCT\12.1.0.1\
                                                 DBHOME_1\BIN\DATABASE\SPFILEOR
                                                 CL.ORA
SQL> show parameter ipfile
SQL>

No results for ifile parameter.

Report message to a moderator

Re: Lock the SYS account [message #622623 is a reply to message #622619]

Thu, 28 August 2014 03:00

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So maybe 12c no more allows it (thanks to Oracle).
I have no 12c so we will have to wait for John to confirm.

Report message to a moderator

Re: Lock the SYS account [message #622626 is a reply to message #622623]

Thu, 28 August 2014 03:12

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

May be something changed with 12c, but it can't be disallowed because John did that in 12c :

John Watson wrote on Wed, 27 August 2014 22:21

12c orclz> conn sys/oracle
ERROR:
ORA-28000: the account is locked

John, where are you? Smile

Report message to a moderator

Re: Lock the SYS account [message #622649 is a reply to message #622626]

Thu, 28 August 2014 04:50

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No this does not prove anything as I said and repeated.
Try it with O7 set to FALSE and account locked and you will see.

Report message to a moderator

Re: Lock the SYS account [message #622656 is a reply to message #622649]

Thu, 28 August 2014 05:45

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Woo-hoo! I did it! I did it! /forum/fa/451/0/

With O7 parameter set to false(by default) :

SQL> show parameter O7;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
SQL> alter user sys account lock;

User altered.

SQL> conn sys@pdborcl
Enter password:
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn sys@pdborcl as sysdba
Enter password:
Connected.
SQL> select account_status from dba_users where username='SYS';

ACCOUNT_STATUS
--------------------------------
LOCKED

With O7 parameter set to true :

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

System altered.

SQL> shutdown immediate;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.
SQL> show user
USER is "SYS"
SQL> show parameter O7

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE
SQL> conn sys@pdborcl
Enter password:
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.

You were absolutely right! I learnt something new today Smile

Thanks Michel

Report message to a moderator

Previous Topic:	Key Points on Database security best practices
Next Topic:	Audit Client Utility Information

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 22 19:11:07 CST 2025