Home » RDBMS Server » Server Administration » Logging in as SYS (11.2.0.3 RHEL5.9)
Logging in as SYS [message #596642] Wed, 25 September 2013 06:16 Go to next message
gazzag
Messages: 388
Registered: November 2010
Location: Bristol, UK
Senior Member
Hi,

I've been an Oracle DBA for almost twenty years. In that time I have worked in various organisations - sometimes as a "permanent" employee (although nothing is permanent these days Smile) and other times as a contractor. I have been in my current role for the last six months since being made redundant from my last position.

In this organisation we support a lot of Oracle databases on many different servers spanning various networks. There are two DBAs who have been here for several years and for whom I was recruited to help with the workload.

However, they are both in the habit of logging in as SYSDBA for everything. They find it amusing that I refuse to do so for daily administration as I know that this is bad practice. Import and exports are even taken as SYS. Whenever I mention that it's bad practice they shrug it off saying that they've been doing it for years with no problems.

So I have been scouring Tahiti and Metalink looking for a definitive document from Oracle that states just why this is such bad practice. I would imagine that an import as SYS could have catastrophic consequences for the data dictionary if you're not careful, but I can't think of much else.

We've all seen Michel's default "just don't do it" message whenever a poster mentions that they have done something as SYS. But I was wondering if anyone has found anything official other than the documentation recommending that it should not be done.

I look forward to your replies.

Regards,
-g

[Edit: Typo]

[Updated on: Wed, 25 September 2013 07:05]

Report message to a moderator

Re: Logging in as SYS [message #596644 is a reply to message #596642] Wed, 25 September 2013 07:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10630
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Metalink Doc 277237.1
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2659418700346202574

[Updated on: Wed, 25 September 2013 07:14]

Report message to a moderator

Re: Logging in as SYS [message #596648 is a reply to message #596644] Wed, 25 September 2013 07:26 Go to previous messageGo to next message
gazzag
Messages: 388
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:

SYS is like root for us. It holds the data dictionary, it is special (it physically works differently from other accounts - no flashback query for it, no read only transactions, no triggers, etc)


Hi Mahesh. That's exactly the sort if thing I was after. Michel has hinted at "code paths changing" but that's more specific. Thanks.
icon4.gif  Re: Logging in as SYS [message #596650 is a reply to message #596648] Wed, 25 September 2013 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 60006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Michel has hinted at "code paths changing" but that's more specific.


I will update my usual maxim to be more specific. Wink
The most important one is that SYS may work out of the transactional rules, for instance no consistency for it.

Re: Logging in as SYS [message #596705 is a reply to message #596650] Wed, 25 September 2013 13:26 Go to previous messageGo to next message
Littlefoot
Messages: 19890
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's a discussion we've had some time ago.
Re: Logging in as SYS [message #596765 is a reply to message #596705] Thu, 26 September 2013 04:36 Go to previous messageGo to next message
gazzag
Messages: 388
Registered: November 2010
Location: Bristol, UK
Senior Member
Thank you, Littlefoot.
Re: Logging in as SYS [message #596816 is a reply to message #596642] Thu, 26 September 2013 08:09 Go to previous messageGo to next message
EdStevens
Messages: 349
Registered: September 2013
Senior Member
how about this quote, directly from the 11.2 Utilites manual, in the opening paragraphs of Data Pump Export:

Note:
Do not invoke Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users.


I believe you will find the same statement for importdp, imp, and exp.
Re: Logging in as SYS [message #596822 is a reply to message #596816] Thu, 26 September 2013 08:18 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
But I was wondering if anyone has found anything official other than the documentation recommending that it should not be done.

[Updated on: Thu, 26 September 2013 08:18]

Report message to a moderator

Re: Logging in as SYS [message #596909 is a reply to message #596822] Fri, 27 September 2013 07:21 Go to previous messageGo to next message
EdStevens
Messages: 349
Registered: September 2013
Senior Member
ramoradba wrote on Thu, 26 September 2013 08:18
Quote:
But I was wondering if anyone has found anything official other than the documentation recommending that it should not be done.


I missed that detail. Probably because it is an oxymoron.

I wonder what the OP would consider "official" that is "other than the documentation"?

Re: Logging in as SYS [message #596910 is a reply to message #596909] Fri, 27 September 2013 07:40 Go to previous messageGo to next message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
I think the OP was looking for a reason or a firm do NOT as opposed to "advising against".
Re: Logging in as SYS [message #597191 is a reply to message #596910] Tue, 01 October 2013 08:44 Go to previous message
gazzag
Messages: 388
Registered: November 2010
Location: Bristol, UK
Senior Member
Thanks for all of your replies. So, the upshot of this little discussion is that
I've managed to make a case for changing our Datapump regime. The solution that
I've proposed is to use an O/S authenticated account with the EXP_FULL_DATABASE
privilege (you also need to grant CREATE SESSION and CREATE TABLE in
10.2.0.4 as they are not included in the EXP_FULL_DATABASE role). This has the
advantage of not requiring hard-coded passwords in the relevant scripts.

Incidentally, we DataPump our environments as well as RMAN-ing them. This allows
us to more quickly restore any objects in our Dev environment that may have been
accidentally dropped or trashed by a developer.

Ed, apologies for the oxymoron; I should have more accurately said "has anyone
found anything official other than the documentation's vague recommendation
of not doing it" as Roachcoach correctly inferred.

Once again, thanks all.
Previous Topic: ORA-30967: operation directly on the Path Table (during CSSCAN process)
Next Topic: Oracle Scheduler Agent licensing
Goto Forum:
  


Current Time: Sat Dec 20 13:41:09 CST 2014

Total time taken to generate the page: 0.12205 seconds