Home » RDBMS Server » Security » Howto: Privilegs based on foreign key of a column??
Howto: Privilegs based on foreign key of a column?? [message #390139] Thu, 05 March 2009 02:32 Go to next message
roithi
Messages: 8
Registered: March 2006
Junior Member
Hi guys!

I have the following situation

Two tables, created in the schema "student_admin"

COURSE
courseID
teacherID
....

TEACHER
teacherID
name
....


Is it now possible to set restriction, that a teacher can only edit and see course data he is assigned to? - and how can i do this?

I know how i can grant privilegs to table of a different schema, but i have no clou how i can grant privilegs to a table based on the foreign key


thanks for your help,
Re: Howto: Privilegs based on foreign key of a column?? [message #390148 is a reply to message #390139] Thu, 05 March 2009 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is meaningless.
Data IN tables are no relation with ACCESS to data.

I may have access to teacher and course without being a teacher. Replace "teacher" and "course" with "car" and "wheel", do you ask that a car can only see its wheels?

Regards
Michel
Re: Howto: Privilegs based on foreign key of a column?? [message #390186 is a reply to message #390148] Thu, 05 March 2009 06:37 Go to previous messageGo to next message
roithi
Messages: 8
Registered: March 2006
Junior Member
I know that there is no relation between Data in Tables and the Access to them.

I'm trying to find out how such restrictions could be applied, and if it's possible to implement something in oracle

i try to describe it better now:

e.g: a table that contains all Student's data

table: students (id, name, adress, orauser)

id name address orauser
1 test1 somewhere scott_1
2 test2 somewhere2 scott_2
3 test3 somewhere3 scott_3


If the student "test1" with his oracle user "scott_1" logs in to the database, and perform select * from students, he should only see all rows with his ora_user


And i don't know if something like this is even possible... actually i should be somehow possible with a view, isn't it?
Re: Howto: Privilegs based on foreign key of a column?? [message #390193 is a reply to message #390186] Thu, 05 March 2009 06:52 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If the student "test1" with his oracle user "scott_1" logs in to the database, and perform select * from students, he should only see all rows with his ora_user

Now this has sense.
You can use a view upon your table that restricts access to allowed data and don't allow to directly access to the table.

Regards
Michel

[Updated on: Thu, 05 March 2009 06:53]

Report message to a moderator

Previous Topic: REVOKE INSERT,UPDATE,DELETE rights from owner himself
Next Topic: how can i do this
Goto Forum:
  


Current Time: Thu Mar 28 18:05:46 CDT 2024