OraFAQ Forum: SQL & PL/SQL » how can i secure some row from my table

Home » SQL & PL/SQL » SQL & PL/SQL » how can i secure some row from my table (oracle database 10g, windows 7)

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

how can i secure some row from my table [message #624615]

Wed, 24 September 2014 02:36

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

i want to ask you if i have one table table name EMP in this EMP have 50 rows now i want to permenently secure some raws from this table i want another user not to update and delete some raws from this emp table who have all rights on my emp table

exaple if

user "A" has one table EMP user "A" want to give grant select,insert,update,delete on EMP table to user "B" but from this emp table user"B" only access some raws only on some raws he cannot update and delete from this emp table...

Report message to a moderator

Re: how can i secure some row from my table [message #624616 is a reply to message #624615]

Wed, 24 September 2014 02:41

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Write a trigger to check the user and reject it.

Report message to a moderator

Re: how can i secure some row from my table [message #624617 is a reply to message #624616]

Wed, 24 September 2014 02:49

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

thanks for your reply michel but michel can you give me some example of this triger.. only "B" user not to do update and delete some raws from this "A" user EMP table

Report message to a moderator

Re: how can i secure some row from my table [message #624619 is a reply to message #624617]

Wed, 24 September 2014 03:20

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Database Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Report message to a moderator

Re: how can i secure some row from my table [message #624620 is a reply to message #624615]

Wed, 24 September 2014 03:25

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

x-oracle wrote on Wed, 24 September 2014 13:06

but from this emp table user"B" only access some raws only on some raws he cannot update and delete from this emp table

What do you mean by some rows?

Report message to a moderator

Re: how can i secure some row from my table [message #624621 is a reply to message #624620]

Wed, 24 September 2014 03:33

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

thanks for your reply Lalit Kumar B some raw means if in emp table i have total 20 raws but i want user "B" not update and delete 5 raws from this emp table but he can do update and delete on remaning 15 raws from this emp table.

Report message to a moderator

Re: how can i secure some row from my table [message #624624 is a reply to message #624621]

Wed, 24 September 2014 04:00

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do as I said.

Report message to a moderator

Re: how can i secure some row from my table [message #624625 is a reply to message #624615]

Wed, 24 September 2014 04:03

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

If you have EE licenses, this is what Virtual Private Database is for. Also known as Fine Grained Access Control and Row Level Security.
It can be a bit complicated to setup, but it certainly works.

Report message to a moderator

Re: how can i secure some row from my table [message #624627 is a reply to message #624625]

Wed, 24 September 2014 04:25

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

thanks for your reply yes we have 10g EE license.

Report message to a moderator

Re: how can i secure some row from my table [message #624628 is a reply to message #624627]

Wed, 24 September 2014 05:05

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I didn't mention FGAC because it is far harder to implement it than a simple trigger.

Report message to a moderator

Re: how can i secure some row from my table [message #625350 is a reply to message #624628]

Mon, 06 October 2014 06:54

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

If you don't wish to use Trigger and 'Virtual private database' you can use views.

It's the prefered way of MySQL, because they don't support row level security.

Quote:

Mysql doesn't natively support row level security on tables. However, you can sort of implement it with views. So, just create a view on your table that exposes only the rows you want a given client to see. Then, only provide that client access to those views, and not the underlying tables.

http://stackoverflow.com/questions/5527129/mysql-how-to-do-row-level-security-like-oracles-virtual-private-database

Report message to a moderator

Re: how can i secure some row from my table [message #625352 is a reply to message #625350]

Mon, 06 October 2014 07:19

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

First of all, this it ORACLE, not MySQL forum. Secondly, solution you are referring to is about restricting viewing some table rows, not about restricting modifying some table rows. Creating a view and granting select privilege on it instead of table will prevent grantee from modifying all rows, not some rows as OP requested.

SY.

Report message to a moderator

Re: how can i secure some row from my table [message #625358 is a reply to message #625352]

Mon, 06 October 2014 09:09

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I liked Michel's suggestion about FGAC, although it is a bit complex and leaves the data open through SQL*Plus, but is a good option via application.

Tim demonstrated it here http://www.oracle-base.com/articles/8i/virtual-private-databases.php

Report message to a moderator

Re: how can i secure some row from my table [message #625359 is a reply to message #625358]

Mon, 06 October 2014 09:12

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

In what way does FGAC leave data open through SQL*Plus? It is totally impossible to bypass. That's the whole point.

Report message to a moderator

Re: how can i secure some row from my table [message #625360 is a reply to message #625359]

Mon, 06 October 2014 09:38

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

John, those are not my words. I was just quoting Tim's words as mentioned in the posted link :

Quote:

access via other methods (SQL*Plus) would leave the data open to abuse.

Edit : I have not tested the demo, now that you have pushed me to think, I will try to do that or leave a message to Tim to clarify his statement. Either way, it should bring more clarification.

[Updated on: Mon, 06 October 2014 09:40]

Report message to a moderator

Re: how can i secure some row from my table [message #625362 is a reply to message #625360]

Mon, 06 October 2014 11:09

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Lalit -

I think you have to read that statement in its full context. It was the very opening statement, introducing the setup for FGAC.

"Although this type of access can be controlled by the application, access via other methods (SQL*Plus) would leave the data open to abuse. VPD uses Fine-Grained Access Control to limit which data is visible to specific users. Setting up a VPD involves the following steps."

He's saying that an application can provide the controls, but sqlplus would be inherently bypassing any controls being enforced by the application.

Agreed, that entire paragraph could probably stand to be re-written, to make that distinction more clear.

Report message to a moderator

Re: how can i secure some row from my table [message #625363 is a reply to message #625362]

Mon, 06 October 2014 11:46

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I didn't think much and wrote simply, it is better via an application. Thanks Ed, for explaining it further.

Report message to a moderator

Previous Topic:	Please tell me use of DBMS_SCHEDULER
Next Topic:	Outer Join on Values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 06:14:47 CDT 2024