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.

Looks to me as if possibly you might be able to put together a DDL EVENT trigger? One way to proceed might be to update an existing question over in asktom.oracle.com and see if Tom would put together some more detail.

On the CREATE you could possibly populate a table row then have some asynchronous process look at some duraction for new rows in that table.  If you find any new rows then look for any recent users created with the wrong tablespace.

My thoughts sound a little kludgy. Maybe enough has changed recently in oracle since the DDL EVENT triggers became available ( 8? I forget ) that you can get all the detail you need immediately in the trigger.

Of course you could always just have a process that runs periodically look for any bad user tablespace defaults without using a trigger.

Work with sun on this one. See if they have any good ideas or alternatives on how to proceed. Received on Fri Dec 01 2006 - 13:44:53 CST