Peter Tran, October 25, 2004 - 11:21 am UTC
Dave, October 25, 2004 - 11:21 am UTC
Myabe I am being dumb here,
but even if you change the udump directory the developers won't be able to read them because they will still be owned by the oracle(or whoever user) so will still most likely have no read access for 'other'.
If you want to give them access to trace files, use _trace_file_public or some external directory to read the contents into a table
October 25, 2004 - 11:52 am UTC
they just wanted to be able to change the directory:
I want them to give them the ability to turn on extended tracing, but write out
the trace files to a different directory (e.g. /tmp) rather than to the default
Dave, October 25, 2004 - 2:00 pm UTC
but what use is just changing the directory?
Also if you have loads of people, doing this, you will have trace files all over the place from people changing it.
I know its possile, just failing to see the need or usefulness
October 25, 2004 - 2:05 pm UTC
guess they would have to respond. agreed however.
Why I want to do this...
Peter Tran, October 26, 2004 - 8:27 am UTC
This is a production system. The client has already set _trace_files_publiic=true. However, when I enable tracing it gets written to the default user_dump_dest configured by Oracle which means the directory is also owned by the Oracle account. The login account I use doesn't have the privilege to "clean up" these trace files. Rather then nag the SysAdmin or the DBA to clean up these trace files, I'd figure it would be easier to ask them to set it up so I can change the user_dump_dest directory to /tmp where I can always delete them when I'm done.
Yes, you do bring up some good points, but there's limited access to this production box. I just need to remember change the user_dump_dest back to the original directory when I'm done tracing.
I wouldn't take the same approach with development box. Heck with a development box, I just tell SysAdmin to "chmod 777" the entire user_dump_dest.
Tom - is there a better approach?
October 26, 2004 - 9:15 am UTC
a production system with _trace_file_public = true has certain security attributes associated with it that you don't really want.
I'm going to back up here -- I suggest you turn that off unless you totally trust everyone with alter session.
I would nag the sysadmins, it is sort of their job. to have to trace on a production system should be rare. have the team work together to provide the develop temporary access to the file system. them remove that ability.
Its 'alter session' ...
Connor McDonald, October 26, 2004 - 1:09 pm UTC
You could argue that 'alter session' is the problem here and not _trace_files_public. After all, if people can't alter session then its unlikely they'll be able do anything except write standard sql_trace data into user_dump_dest.
But everyone seems to think 'alter session' is 'harmless' - 'create/alter session' seems to be norm as "base access" to the database.
But as long as setting events can be done with alter session, man, there's some nasty nasty nasty stuff you can do with 'alter session'.
October 26, 2004 - 2:16 pm UTC
i would argue that absolutely, yes.
if they cannot alter session - they cannot even sql_trace :)
ops$tkyte@ORA9IR2> drop user a cascade;
ops$tkyte@ORA9IR2> grant create session to a identified by a;
ops$tkyte@ORA9IR2> @connect a/a
a@ORA9IR2> alter session set sql_trace=true;
ORA-01031: insufficient privileges
<b>so, maybe they revoke alter session, they use the same technique to let them turn on JUST SQL_TRACE as we did with the alter system...
but wait, it is a production system. I stick with "let them work together. it is not a BOTHER to the sysadms, rather it is their JOB</b>
Dave, October 26, 2004 - 2:03 pm UTC
but you cant change user_dump_dest on a session level....
Wouldn't a bind variable be better here?
Amy, January 06, 2005 - 8:11 pm UTC
I was just reading the "Ask Tom" column in Oracle Magazine the other day. It had a long discussion of SQL injection and the importance of using bind variables, and then at the end it had this example. So, the concatenation of the variable p_udump into that SQL really jumped out at me. Surely it would be even better with a bind variable!
January 06, 2005 - 8:25 pm UTC
DDL doesn't support binding -- alter, no binds.
As a matter of fact though, someone did point out that we DO need a:
if (p_udump NOT LIKE '%=%' )
raise_application_error(-20000, 'No' );
in there, before the execute immediate......
It was ironic, an article about sql injection, with a short blurb that suffered from ... well, sql injection...
Alter Session works in SQL*Plus but does not work in SP.
Bipin, September 06, 2005 - 9:09 pm UTC
I have a related question.
'alter session set sql_trace=true' works from sql*plus for Oracle user x. It also works for user x from anonymous PL/SQL block using execute immediate. When i put alter session in PL/SQL SP owned and executed by user x, it complains about insufficient privileges.
The owner of the SP and session creator (SP Caller) are the same Oracle user. I fail to understand this behavior.
Can you please help?
September 06, 2005 - 9:26 pm UTC
Bipin, September 07, 2005 - 5:56 pm UTC
Thanks for a quick response.
Laurent Schneider, December 14, 2005 - 9:29 am UTC
you will surely like this one
execute sys.set_udump('/app/oracle/product/10.2.0/db_1/admin/orcl/udump'' encryption wallet close --')
Laurent Schneider, December 14, 2005 - 10:54 am UTC
this undocumented package seems to be very usefull!
p_udump not like '%''%'
could also prevent using quotes (rarely seen in a directory like user_dump_dest...)
December 14, 2005 - 12:57 pm UTC
yes, I toyed with the '%''%' - but thought dbms_assert would be "cleaner" (and a good change to introduce it)
Laurent Schneider, September 20, 2007 - 9:33 am UTC
by the way it is documented in 11g