Skip to Main Content
  • Questions
  • How do I grant limited "alter system" privileges?


Question and Answer

Tom Kyte

Thanks for the question, Peter.

Asked: October 25, 2004 - 8:36 am UTC

Last updated: December 14, 2005 - 12:57 pm UTC


Viewed 10K+ times! This question is

You Asked

Hi Tom,

I would like to grant a user the ability to execute "alter system set user_dump_dest" only.

I don't want to give them the ability to alter system on every init.ora variables - only user_dump_dest.

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 directory.

Is there a way to do this easily?


and Tom said...

This is exactly what stored procedures are awesome for!

A stored procedure by default runs with the base privileges of the DEFINER of the routine. Therefore, as a user with the ability to alter system - all you need to do is:

ops$tkyte@ORA9IR2> create or replace procedure set_udump( p_udump in varchar2 )
2 as
3 begin
4 execute immediate 'alter system set user_dump_dest = ''' || p_udump
|| ''' scope=memory';
5 end;
6 /

Procedure created.

ops$tkyte@ORA9IR2> exec set_udump( '/tmp' );
BEGIN set_udump( '/tmp' ); END;

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "OPS$TKYTE.SET_UDUMP", line 4
ORA-06512: at line 1

the owner needs ALTER SYSTEM directly granted -- NOT via a role

ops$tkyte@ORA9IR2> grant alter system to ops$tkyte;

Grant succeeded.

ops$tkyte@ORA9IR2> exec set_udump( '/tmp' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> show parameter user_dump

------------------------------------ ----------- ------------------------------
user_dump_dest string /tmp
ops$tkyte@ORA9IR2> grant execute on set_udump to scott;

Grant succeeded.

ops$tkyte@ORA9IR2> @connect scott/tiger
ops$tkyte@ORA9IR2> set termout off
scott@ORA9IR2> set termout on
scott@ORA9IR2> exec ops$tkyte.set_udump( '/home' );

PL/SQL procedure successfully completed.

scott@ORA9IR2> @connect /
scott@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> show parameter user_dump

------------------------------------ ----------- ------------------------------
user_dump_dest string /home


  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment



Peter Tran, October 25, 2004 - 11:21 am UTC

Much obliged!!!


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

Tom Kyte
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

Tom Kyte
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

Hi Dave,

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?


Tom Kyte
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'.

Tom Kyte
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;
User dropped.
ops$tkyte@ORA9IR2> grant create session to a identified by a;
Grant succeeded.
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!

Tom Kyte
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' );
end if;

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

Hi Tom,

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?


Tom Kyte
September 06, 2005 - 9:26 pm UTC

THanks, Tom!

Bipin, September 07, 2005 - 5:56 pm UTC

Thanks for a quick response.

sql injection

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 --')


Tom Kyte
December 14, 2005 - 9:58 am UTC

Indeed, new with 10gr2.

there are others now that I look at the new syntax for 10gr2.

we should add a check for a quote - dbms_assert would be useful:

</code> <code>


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...)

Tom Kyte
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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library