This forum is currently locked. You can't register or post questions at this time. (read more)

DML Restriction on Schema level for particular users..

All posts relating to Oracle database administration.

Moderator: Tim...

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

DML Restriction on Schema level for particular users..

Postby mail.madhankumar » Wed Nov 21, 2012 9:21 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: DML Restriction on Schema level for particular users..

Postby Tim... » Wed Nov 21, 2012 10:45 am

Hi.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: DML Restriction on Schema level for particular users..

Postby mail.madhankumar » Wed Nov 21, 2012 11:21 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: DML Restriction on Schema level for particular users..

Postby Tim... » Wed Nov 21, 2012 2:49 pm

Hi.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: DML Restriction on Schema level for particular users..

Postby mail.madhankumar » Fri Nov 23, 2012 3:29 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: DML Restriction on Schema level for particular users..

Postby Tim... » Sat Nov 24, 2012 9:00 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests