RE: Oracle 9i and connect as sys

The security is not as bad as it sounds. The patch is to release major software and the user gets prompted to enter the password. It is not stored any place (just UNIX variables during the running of the release). The
software upgrades are released once every six months to year.

Not all patches require SYS access. Only in earlier releases that granted access to SYS. However, when I build a new environment, I needed to run through the entire set of patches.

Even if I use SYSTEM to grant access to SYS objects, I would need to go back and change the scripts to refer to SYS objects instead of currently connected users. And we are not supposed to go back and change scripts that have been released already :-(

But we are migrating all of the application to the mainframe and none of the original release scripts will work soon any way :-(

At least as part of the move, we have been going through getting rid of the public synonyms and public grants the previous DBA put in place :-)

I will have to try that GRANT ANY OBJECT PRIVILEGE and let you know how it goes.

• BAbette

-----Original Message-----
Tim Gorman
Sent: Sunday, August 17, 2003 11:39 PM
To: Multiple recipients of list ORACLE-L

Babette,

This is how database security unravels. Pretty soon, the password to SYS is embedded everywhere, used everywhere, and everyone knows it. Thus, the DBA ends up with the pager and responsibility for fixing stuff, but everyone else can cause that pager to go off with a stupid goof at 3:00am where they shouldn't have been able to goof up.

It sounds like the patching utility only needs a couple privileges, but instead all of the goddess-like privileges of SYS are provided. Pretty soon, it seems normal for people and programs to connect as SYS on a regular basis. And so it goes...

A couple alternatives:

• use 9i "GRANT ANY OBJECT PRIVILEGE" to let another account have an incredible amount of authority, which is OK if you don't know exactly what permissions will be needed ahead of time...
• grant specific permissions WITH GRANT OPTION to another user, a more focused approach than the shotgun "GRANT ANY OBJECT PRIVILEGE" approach, provided you know what permissions will be needed ahead of time. This has been around forever...
• encapsulate such actions within a stored procedure owed by SYS, which may seem cumbersome but allows all kinds of control. Not just "who can do what" (which is basically what permissions and roles provide), but also "during what time", "from where", "from what program", "from what location", etc...

Just this Friday, I was wrapping up an installation engagement and one of the last things we did was change all the passwords. Standard practice. Immediately, one of the development managers comes boiling out of his office screaming "Who changed the passwords to SYS and SYSTEM?". I 'fessed up and asked him why he thought he needed it. He turned red and snarled that he just needed it and never you mind, turned on his heel and went in the CIO's office, then came boiling back with approval. We turned it over, and within 5 minutes I logged back onto the system and saw SQL*Plus running with the SYS/SYSTEM password visible to anyone and everyone who can run the UNIX "ps" command. I looked at the scripts he was running, noticed that all he wanted SYS/SYSTEM for was to create PUBLIC SYNONYMs. I left to catch my plane...

Hope this helps...

-Tim

on 8/17/03 6:09 PM, Babette Turner-Underwood at babette_at_rogers.com wrote:

> Tim / Peter / Michael
>
> Thanks for the information. I was afraid of that.
> We have a patching mechanism and need to logon as
> sys to grant access to sys objects for part of
> the process. (to grant select on sys.dba_free_space
> and execute on sys.dbms_util).
>
> However, the patching mechanism only does a regular
> connect and not "as sysdba"--- DARN! - Will have to
> change automation scripts if we upgrade ... and I was
> hoping this would be easy to slide in :-(
>
> - Babette
>
> -----Original Message-----
> Tim Gorman
> Sent: Sunday, August 17, 2003 1:09 AM
> To: Multiple recipients of list ORACLE-L
>
>
> It's a 9i thing, across all platforms.
>
>
>
> on 8/16/03 9:29 PM, Babette Turner-Underwood at babette_at_rogers.com wrote:
>
>>
>> I have created my first 9i database on OS/390 v2.10.
>>
>> On my Oracle 8i instance, I can connect to the database
>> using:
>>
>> sys/sys_password_at_the_instance
>>
>> HOWEVER, In Oracle 9i, I cannot do this. I am FORCED
>>
>> to connect using:
>> sys/password_from_orapwd_at_the_instance as sysdba
>>
>> I was wondering if this was a new 9i "feature"
>> or if it was configurable? Or just a weird thing
>> because of the mainframe environment.
>>
>> Comments please.
>>
>> Thanks in Advance
>> - Babette

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Babette Turner-Underwood
  INET: babette_at_rogers.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).