Re: Why are people so afraid of underscore parameters ?

In article <b3cb12d6.0208251521.18cbe86a_at_posting.google.com>, yong321_at_yahoo.com says...
>
>Thomas Kyte <tkyte_at_oracle.com> wrote in message
>news:<ak60ht02k5d_at_drn.newsguy.com>...
>>(in fact, I can show you a truly big problem with _trace_files_public, security
>> and another undocumented but seemingly innocent event that can be set at the
>>session level -- just need _trace_files_public to be set and ALTER SESSION privs
>> and I can get some pretty neat information)
>
>Hi, Tom,
>
>Out of curiosity, what event is that, suppose the user has alter
>session privilege?

curiosity kills cats. I like cats.

>
>By the way, I don't always grant alter session to all users. But I

good for you but... how many sites do you think grant CONNECT blindly??

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from dba_sys_privs where grantee = 'CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE DATABASE LINK                     NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        CREATE VIEW                              NO

8 rows selected.

so, that "innocent" thing that should be set on all production databases (following from original discussion -- my boss ....) -- in the wrong hands -- could be a terrible thing.

>always grant select_catalog_role to whoever asks. Without alter
>session but with _trace_files_public set to true, all trace files are
>world-readable. Is that a problem? I would say, ideally, developers

maybe -- you see -- i haven't thought through ALL OF THE POSSIBLE outcomes -- and neither have you.

>can use a "read only" account on the production box to make their
>development easier.

sure, if you read my book "expert one on one oracle" -- i even discuss setting this. However, the question goes back to setting these things on a PRODUCTION instance.

> That account only has create session privilege and
>select_catalog_role, plus some select on XXX table privileges. With
>_trace_files_public being true, they can also see what errors the
>database generates (as well as trace files DBAs manually create). I'm
>willing to open rather than close the database as much as possible,
>just as UNIX opens /var/adm/messages and most files under /etc
>world-readable. If security is really a concern, don't even allow SQL
>connection to the database, just as you don't allow UNIX shell access
>to a production UNIX box.
>
>Yong Huang

But back to the original question:

Question: "My boss does not allow DBAs to use any underscore parameters. He seems to be unreasonably freaked out upon hearing one.

Some also advise that you should never use it without being instructed by Oracle Support."

is that wrong?

My answer would be: No, sounds fairly reasonable to me. I would need TONS of supporting evidence to the contrary. I have found in my experience (to counter things like "A lot of good tuning parameters in 8i have gone undergound in 9i.") that 99% or more of tuning is done AT THE APPLICATION and setting some undocumented (and very changeable from release to release) parameter to "fix it" -- is worse then applying a band-aid. It is a false sense that "ok, we've gotten over that hurdle..."   

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp