Home » SQL & PL/SQL » SQL & PL/SQL » View
View [message #239109] Mon, 21 May 2007 09:01 Go to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
Hello

I have created a view that represents the table "games" with the columns "id" and "name".

Every user, (they can only be logged on one at a time). Have a relation the the game name.
Lets say that User1 has game1 and 2 and user2 has game 3 and 4.

I want the view to check witch user is logged on and then only show the games that the logged on user has.

Thanks.
Re: View [message #239110 is a reply to message #239109] Mon, 21 May 2007 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to be more precise in your question.
Do you want each user to only see his games?
How can you know which user owns which games?

Regards
Michel
Re: View [message #239120 is a reply to message #239109] Mon, 21 May 2007 09:15 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Try the following

Your view would have to the the oracle name of the user associated with the games that it can access, Once you have that then

select id,name
from my_view
where game_user = user;

User is a restricted oracle verb that always returns the name of the currently logged on user.
Re: View [message #239129 is a reply to message #239120] Mon, 21 May 2007 09:36 Go to previous messageGo to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
Michel Cadot, I will typ to precise my question.

Table1
Tablename = Games
Columns = id, game_name, user_name

1, name1, user1
2, name2, user1
3, name3, user2

Table2
Table name = Users
Columns = id, user_name

The two tables have a relation.

I want a view, showing id and game_name from "games"
But I only want the current user logged on, to see his own games (game_name and id).

Bill B wrote on Mon, 21 May 2007 16:15
Try the following

Your view would have to the the oracle name of the user associated with the games that it can access, Once you have that then

select id,name
from my_view
where game_user = user;

User is a restricted oracle verb that always returns the name of the currently logged on user.



Thanks again Bill B. But would that not result in, that I have to do a select statement every time?

Does it not work todo something like this..
CREATE OR REPLACE VIEW view_1 AS
Select id, game_name From games
where games.user_name = user;

[Updated on: Mon, 21 May 2007 09:38]

Report message to a moderator

Re: View [message #239152 is a reply to message #239129] Mon, 21 May 2007 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pgorama wrote on Mon, 21 May 2007 16:36
Does it not work todo something like this..
CREATE OR REPLACE VIEW view_1 AS
Select id, game_name From games
where games.user_name = user;


Yes, this is the best solution.
Just add the constraint "with read only" if you want your users to only read the view and never update (through) it.

Regards
Michel

Re: View [message #239153 is a reply to message #239129] Mon, 21 May 2007 10:32 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Hi,
Do you mean user 1 can see only his associated game not the other games...if yes than you have to use VPD for that.

http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/
Re: View [message #239161 is a reply to message #239153] Mon, 21 May 2007 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, he does not HAVE.
He already found the best solution: view.

Regards
Michel
Re: View [message #239174 is a reply to message #239109] Mon, 21 May 2007 12:07 Go to previous message
pgorama
Messages: 13
Registered: May 2007
Junior Member
Thanks everyone!
Previous Topic: Index..
Next Topic: Algorithm for date events comparision (merged topics)
Goto Forum:
  


Current Time: Sat Dec 03 01:30:43 CST 2016

Total time taken to generate the page: 0.06735 seconds