Home » RDBMS Server » Security » Lock the SYS account
Lock the SYS account [message #622550] Wed, 27 August 2014 09:54 Go to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Locking the system user id in oracle [message #622552 is a reply to message #622550] Wed, 27 August 2014 10:02 Go to previous messageGo to next message
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.
Re: Locking the system user id in oracle [message #622553 is a reply to message #622552] Wed, 27 August 2014 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
LOL! No more experiments now Smile
Re: Locking the system user id in oracle [message #622555 is a reply to message #622552] Wed, 27 August 2014 10:21 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Locking the system user id in oracle [message #622557 is a reply to message #622555] Wed, 27 August 2014 10:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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?
Re: Locking the system user id in oracle [message #622562 is a reply to message #622560] Wed, 27 August 2014 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't connect AS SYSDBA.

Re: Locking the system user id in oracle [message #622565 is a reply to message #622560] Wed, 27 August 2014 12:01 Go to previous messageGo to next message
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.
Re: Lock the SYS account [message #622568 is a reply to message #622562] Wed, 27 August 2014 12:13 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Since Oracle still allowed you.
Re: Lock the SYS account [message #622570 is a reply to message #622568] Wed, 27 August 2014 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: Lock the SYS account [message #622572 is a reply to message #622571] Wed, 27 August 2014 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Lock the SYS account [message #622577 is a reply to message #622572] Wed, 27 August 2014 14:01 Go to previous messageGo to next message
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"

Re: Lock the SYS account [message #622580 is a reply to message #622577] Wed, 27 August 2014 14:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.
Re: Lock the SYS account [message #622598 is a reply to message #622596] Thu, 28 August 2014 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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".

Re: Lock the SYS account [message #622602 is a reply to message #622598] Thu, 28 August 2014 01:21 Go to previous messageGo to next message
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
Re: Lock the SYS account [message #622606 is a reply to message #622602] Thu, 28 August 2014 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Lock the SYS account [message #622619 is a reply to message #622606] Thu, 28 August 2014 02:46 Go to previous messageGo to next message
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.
Re: Lock the SYS account [message #622623 is a reply to message #622619] Thu, 28 August 2014 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Lock the SYS account [message #622626 is a reply to message #622623] Thu, 28 August 2014 03:12 Go to previous messageGo to next message
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

Re: Lock the SYS account [message #622649 is a reply to message #622626] Thu, 28 August 2014 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Lock the SYS account [message #622656 is a reply to message #622649] Thu, 28 August 2014 05:45 Go to previous message
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
Previous Topic: Key Points on Database security best practices
Next Topic: Audit Client Utility Information
Goto Forum:
  


Current Time: Thu Mar 28 15:29:49 CDT 2024