×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Row Level Security?

Row Level Security?

Row Level Security?

(OP)
Is there a way to implement a row level security. I know table and column security can be implemented using roles, but I would like to limit which records in a table a user can see.

RE: Row Level Security?

You can build a view using a where clause.

For instance, two locations using the same employee table. Site_ID field contains the location name.
View would be
Select * from employee where site_id = user;

Or even--
Select * from equipment where site_id in (select site_id from employee where emp_code = user);

Here is the syntax for creating a view in 8.x

DROP VIEW MYDB.VTASK;
CREATE OR REPLACE VIEW MYDB.VTASK
(
TASK_ID,
DESCRIPTION,
SITE_ID
)
AS
select "TASK_ID","DESCRIPTION","SITE_ID" from TASK
where site_id = user;

After you create the view then define a synonym for that view for the user and give the user privileges.


CREATE SYNONYM USERNAME.TASK
FOR MYDB.VTASK;

You've got the general idea by now.

RE: Row Level Security?

Oracle used to licence 'Trusted Oracle' separately which I seem to recall added extensions to the RDBMS to provide row level security. May be a lower long term overhead in terms of maintenance if this is still an active product.

Mark.

RE: Row Level Security?

(OP)
alaskaguru,

That is a way I was going to go. However I am using WebDB as my user interface. When I build a screen it wants a table or view as the basis of the screen. It needs to have the shema and the table/view names to create the screen. What I was hoping to do was NOT build a different screen for every group or user but implement a row level security directly on the table itself. If you have any more ideas they would be appreciated.

mark06,

I haven't seen or heard of 'Trusted Oracle' but I will definitely take a look now.

Thanks both,

tone

RE: Row Level Security?

I believe that the features in Trusted Oracle have now been incorporated into Oracle 8i.
Any table or view can now be assigned a security policy, a function that determines which rows in the table or view a given user can access. Whenever a query is issued on this table, the security policy is applied. For example, a simple security policy would be to allow members of each sales region to view only information about their specific region, while allowing corporate users to view all sales information. The policies are implemented by dynamically rewriting the incoming queries to append new predicates (in the WHERE clause) to the users' SQL statements.
I'm not sure how easy these policies are to set up though.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close