Re: Trigger on create user

ronnieyours_at_yahoo.com wrote:
> Hi,
>
> We are using a third part application (Sun IDM) to streamline our
> database ID creation where when a new employee joins the company a
> request is put in the system to create his/her username/password. Once
> approved the application creates a database user automatically for the
> new employee.
>
> This application uses a database user (lets call it USRCREATOR) with
> create user priveleges to create new users. The problem is that Sun
> IDM creates the user with system as default tablespace and then alters
> it to the specified default tablespace which means that we need to give
> USRCREATOR alter user privelege which is a very powerfull one. To work
> around the limitation we are thinking of creating a trigger which will
> get fired after the user is created and will change its tablespace to
> something other than system.
>
> Any suggestions on if this is possible and how do we go about doing
> this.
>
> Thanks
> Ron

If you have a currently supported version of Oracle wagen123's suggestion is a good one but my instinct would be to throw this back at Sun and tell them to fix their tool. What you describe is a gross violation of good practice and there is no excuse for it.

Another alternative might be to use a DDL EVENT TRIGGER. Not sure if it would work.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org