DML Restriction on Schema level for particular users..

[mail.madhankumar]

Hi Tim,
i have implemented particular employee can do ddl statement in schema.. based on their machine name using userenv and ora_sysevent. but ora_sysevent not shows anything for DML operation.. how can i find that .. example users execute insert statement , i need insert .. so i can restrict in existing trigger itself.. also i need to differentiate session connections.. it's connected from users application or from application..
i finalized v$session - program can get JDBC thin client.. so i can allo only dml operation for jdbc thin client..

my requirement is only particular users can do ddl,dml operation from backend.. others can do dml from front end application..
i need to find event for insert,update,delete event name in trigger....
Please help for this...

Regards,
Madhan

[Tim...]

Hi.

I guess the point here is you are trying to prevent DML using non-DML triggers.

Cheers

Tim...

[mail.madhankumar]

yes.. now i am trying to modify that trigger.. to restrict dml on schema level... don't want to create trigger for every table...

[Tim...]

Hi.

I'm not sure that is possible as DML is controlled using DML triggers, not system triggers.

Cheers

Tim...

[mail.madhankumar]

Hi Tim,
Thanks for support.. We have changed requirement based on possibilities....
We created new schema with create session and granted select privillage on all objects from QA schema and created synonym..
and granted debug privillege to new schema.. so all users can use read only schema... specific users can accesss with all privillege in Main schema..

[Tim...]

Hi.

My personal preference is no users access the schema owner directly. Instead they access by their won user, or in the case of application users, the connect using a gateway user. You can see the wort of thing I'm thinking about here:

http://www.oracle-base.com/articles/mis ... -users.php

Cheers

Tim...