Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Row Level Security?
- Thread starter tonedef
- Start date
alaskaguru
Programmer
You can build a view using a where clause. <br>
<br>
For instance, two locations using the same employee table. Site_ID field contains the location name. <br>
View would be <br>
Select * from employee where site_id = user;<br>
<br>
Or even--<br>
Select * from equipment where site_id in (select site_id from employee where emp_code = user);<br>
<br>
Here is the syntax for creating a view in 8.x<br>
<br>
DROP VIEW MYDB.VTASK;<br>
CREATE OR REPLACE VIEW MYDB.VTASK <br>
(<br>
TASK_ID,<br>
DESCRIPTION,<br>
SITE_ID<br>
)<br>
AS<br>
select "TASK_ID","DESCRIPTION","SITE_ID" from TASK<br>
where site_id = user;<br>
<br>
After you create the view then define a synonym for that view for the user and give the user privileges.<br>
<br>
<br>
CREATE SYNONYM USERNAME.TASK<br>
FOR MYDB.VTASK;<br>
<br>
You've got the general idea by now.
<br>
For instance, two locations using the same employee table. Site_ID field contains the location name. <br>
View would be <br>
Select * from employee where site_id = user;<br>
<br>
Or even--<br>
Select * from equipment where site_id in (select site_id from employee where emp_code = user);<br>
<br>
Here is the syntax for creating a view in 8.x<br>
<br>
DROP VIEW MYDB.VTASK;<br>
CREATE OR REPLACE VIEW MYDB.VTASK <br>
(<br>
TASK_ID,<br>
DESCRIPTION,<br>
SITE_ID<br>
)<br>
AS<br>
select "TASK_ID","DESCRIPTION","SITE_ID" from TASK<br>
where site_id = user;<br>
<br>
After you create the view then define a synonym for that view for the user and give the user privileges.<br>
<br>
<br>
CREATE SYNONYM USERNAME.TASK<br>
FOR MYDB.VTASK;<br>
<br>
You've got the general idea by now.
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.<br>
<br>
Mark.
<br>
Mark.
- Thread starter
- #4
alaskaguru,<br>
<br>
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.<br>
<br>
mark06,<br>
<br>
I haven't seen or heard of 'Trusted Oracle' but I will definitely take a look now.<br>
<br>
Thanks both,<br>
<br>
tone
<br>
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.<br>
<br>
mark06,<br>
<br>
I haven't seen or heard of 'Trusted Oracle' but I will definitely take a look now.<br>
<br>
Thanks both,<br>
<br>
tone
I believe that the features in Trusted Oracle have now been incorporated into Oracle 8i.<br>
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.<br>
I'm not sure how easy these policies are to set up though.
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.<br>
I'm not sure how easy these policies are to set up though.
Views: You'd have to give your user read-access on the base table anyway. <p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href= Cargill's Corporate Web Site</a><br>
- Status
Similar threads
- Locked
- Question
- Locked
- Question