x

Can We Force a User to Run a Script When Connecting to the Server?

When a user connects to an Oracle database, is there any way to force him to execute a script/stored procedure/function/statement/thing?

more ▼

asked Nov 30, 2009 at 03:50 PM in Default

KillerDBA gravatar image

KillerDBA
1.5k 8 9 10

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

There is a system level AFTER LOGON ON DATABASE trigger that can be incorporated into your database code. The trigger is executed after the user logon is processed. A trigger body can contain PL/SQL code.

Inside the trigger you have access to the username via sys_context so you can conditionally execute code if you desire.

Oracle 10g Documentation here

Here is a basic example:

CREATE OR REPLACE TRIGGER LOGON_TEST AFTER LOGON ON DATABASE BEGIN

-- Your custom code here -- Use SYS_CONTEXT() to get information about the user's session -- Ex: sys_context('USERENV','SESSION_USER')

END; /
more ▼

answered Nov 30, 2009 at 04:48 PM

dmann gravatar image

dmann
437 1 1 4

Thank you.

After I posted the note, I kept digging and found security policies, which might also help us accomplish what we're looking for.
Dec 01, 2009 at 02:21 PM KillerDBA
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x9

asked: Nov 30, 2009 at 03:50 PM

Seen: 708 times

Last Updated: Nov 30, 2009 at 03:50 PM