Re: connecting automatically as sys

jonathan wrote:

>
> well, it can't be done as regular DBA because regular dba does not
> have permissions to create a function owned by sys. And it has to be
> automatable - command line driven and executable via a script, hence
> it would be very nice that the command was stored in the file system,
> not the database. And it has to be cross-version.
>
> Those are my only three conditions; sqlplus is a common denominator so
> hence I thought I would start there. I'm not familiar with UTL_FILE or
> EXTPROC or external tables or contexts or FGAC; perhaps you could
> elaborate.
 

1. Pull yourself up a chair by the fire and start reading about Oracle's true capabilities. The stuff I mentioned is discussed in the Concepts and the Supplied Packages manuals available at http://docs.oracle.com There's simply to much capability to give quick summaries.

If you don't know about these things, I'd guess you are still living with Oracle v7 capabilities and limitations in mind. Things dun changed, son.

2) As far as I can tell, your cross-version requirement simply will not be fulfilled. There have been a significant number of changes down deep between Oracle v7 and Oracle8. Those changes are coming even faster, although not as deep, between Oracle8i , Oracle9i and Oracle10g. There will need to be a lot of custom version-dependant code.

For most? versions, there is a document titled "Oracle Reference" that gives addiitonal details around this area. Suggest you check that out as well.

_Fair Warning_ :: The rest is "soap-box" stuff. Feel free to stop here and ignore unsolicited advice....

3) Unless you are prepared to void your (your customer's?) warranty, you might want to rethink the 'owned by SYS' concept. Other than AUDIT, Oracle is pretty prickly about virtually anything else happening around the SYS userid.

Also, Oracle seems to be rapidly moving away from 'SYS' and a visible schema owned by SYS. A fair chunk is now made visible through V$xxx views, and these do not necessarily relate to any tables stored in any database files.

4) I get the distinct impression you are attempting to use SYS roughly like a SYBASE or SQL Server 'Master database'. There are enough differences that this would lead me to worry about your baseline design concept, as I get the impression you are not familiar enough with Oracle to take advantage of it's native capabilities AND avoid the pitfalls.

5) I can think of no valid reason, at least effective Oracle8i, to attempt ANY coding against SYS. Playing at that level is roughly equivalent to coding against the kernel data structures of a proprietary (closed source) OS.

I consider it ethically wrong to support or provide advice that [I percieve] involves a high risk of disabling an entire database instance. Quite the contrary, I consider it a requirement to ensure you have as much information about why NOT to proceed in this direction ... this being based on my understanding of the requirement (which, of course, you can not elaborate for 'security reasons').

Perhaps you can get others to do help. I stop here. /Hans Received on Tue Jul 20 2004 - 22:25:44 CDT