Home » RDBMS Server » Security » Restrict DML on table (Oralce 10g)
Restrict DML on table [message #517484] Tue, 26 July 2011 05:19 Go to next message
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
Senior Member

Dear All,

I have a table which contains secret data, i want that nobody can query,insert,update or delete that table, we can do by creating a table and giving rights to specific person but problem is that our programmers can query that table while working on that database.


Is there any other event or trigger which check that if person is performing DML on secret table then an error may generate.

Best Regards,
Asif.
Re: Restrict DML on table [message #517488 is a reply to message #517484] Tue, 26 July 2011 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i want that nobody can query,insert,update or delete that table

If no one can make anything on the table why the table is there?

Quote:
problem is that our programmers can query that table while working on that database.

A developer should have NO access to a production database.

Regards
Michel
Re: Restrict DML on table [message #517489 is a reply to message #517488] Tue, 26 July 2011 05:24 Go to previous messageGo to next message
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
Senior Member

but they have, If programmers have then how can we restric them?
Re: Restrict DML on table [message #517492 is a reply to message #517489] Tue, 26 July 2011 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You miss my first point: drop the table, it is useless.

Quote:
If programmers have then how can we restric them?

First fix your organization problem.
Trying to fix an organization problem with a technical workaround is a VERY bad idea and WRONG to do.
Any workaround can be workaround.
Remove their privileges on the table. But you will tell us they have DBA rights.

Regards
Michel

[Updated on: Tue, 26 July 2011 05:30]

Report message to a moderator

Re: Restrict DML on table [message #517493 is a reply to message #517492] Tue, 26 July 2011 05:31 Go to previous messageGo to next message
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
Senior Member

Look i am not here to correct my company procedure, i have a problem and i think there will be any solution. I need that.
Re: Restrict DML on table [message #517495 is a reply to message #517493] Tue, 26 July 2011 05:36 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You missed the point again. If nobody is allowed to access the table (not even select from it), what is its purpose? If nobody is using it, it is useless so you can drop it.

Anyway: would creating a new schema and moving table over there solve the situation? Of course, nobody of your programmers would have its (new schema's) password and, of course, nobody has privileges to change it.
Re: Restrict DML on table [message #517496 is a reply to message #517495] Tue, 26 July 2011 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And nobody (from the programmers) should have been granted DBA or SELECT ANY TABLE or any ANY privilege.

Regards
Michel

[Edit: missing word]

[Updated on: Tue, 19 April 2022 00:12]

Report message to a moderator

Re: Restrict DML on table [message #517499 is a reply to message #517493] Tue, 26 July 2011 05:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Is there any other event or trigger which check that if person is performing DML on secret table then an error may generate.
I would think that you can do something like this with fine grained auditing, if you have an Enterprise Edition licence. And if you can't fix the the problem (as Michel suggests.)
Re: Restrict DML on table [message #517506 is a reply to message #517484] Tue, 26 July 2011 06:32 Go to previous messageGo to next message
khawja_bilalahmed
Messages: 7
Registered: August 2008
Location: KHI
Junior Member
HELLO,

TABLE LEVEL TRIGGER IS CREATED AND TABLE FOR AUDIT LOG TABLE IS CREATED IN SYS OR ANY OTHRE USER
Re: Restrict DML on table [message #517515 is a reply to message #517506] Tue, 26 July 2011 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT post in UPPER case.

Table level triggers does not forbid SELECT statement (for this you have FGA as John said).
And do NOT create it in SYS schema. SYS is ONLY for Oracle (but in some very special cases).

Regards
Michel

[Updated on: Tue, 26 July 2011 07:12]

Report message to a moderator

Re: Restrict DML on table [message #517706 is a reply to message #517515] Wed, 27 July 2011 07:10 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Despite the fact it is an insane problem to have and it should be fixed at source, rather than any other way.....it sounds like virtual private database might be useful - if you have it available.

Much easier just to sort out developer privs though Wink
Previous Topic: active directory authentication for oracle 10g database running on solaris
Next Topic: Schema consolidation and user schema mapping based on service?
Goto Forum:
  


Current Time: Thu Mar 28 12:02:40 CDT 2024