OraFAQ Forum: Security » Restrict DML on table

Home » RDBMS Server » Security » Restrict DML on table (Oralce 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Restrict DML on table [message #517484]

Tue, 26 July 2011 05:19

mamalik
Messages: 270
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.

Report message to a moderator

Re: Restrict DML on table [message #517488 is a reply to message #517484]

Tue, 26 July 2011 05:23

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Restrict DML on table [message #517489 is a reply to message #517488]

Tue, 26 July 2011 05:24

mamalik
Messages: 270
Registered: November 2008
Location: Pakistan

Senior Member

but they have, If programmers have then how can we restric them?

Report message to a moderator

Re: Restrict DML on table [message #517492 is a reply to message #517489]

Tue, 26 July 2011 05:28

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

mamalik
Messages: 270
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.

Report message to a moderator

Re: Restrict DML on table [message #517495 is a reply to message #517493]

Tue, 26 July 2011 05:36

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Restrict DML on table [message #517496 is a reply to message #517495]

Tue, 26 July 2011 05:39

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

John Watson
Messages: 8976
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.)

Report message to a moderator

Re: Restrict DML on table [message #517506 is a reply to message #517484]

Tue, 26 July 2011 06:32

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

Report message to a moderator

Re: Restrict DML on table [message #517515 is a reply to message #517506]

Tue, 26 July 2011 07:12

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 12:27:18 CDT 2025