Home » SQL & PL/SQL » SQL & PL/SQL » Rights query (Oracle 10.2.0.3)
Rights query [message #620443] Fri, 01 August 2014 08:20 Go to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
Hi,

I have a requirement related to rights.
t_p stores user rights, p1 column stores highest level, p2 is next level, p3 is next level of p2 and p4 is next level of p3.

t_c stores some business data and also rights. so we wanted to select all rows from t_c where id matches to t_p table and t_p have minimum rights to the given id.

e.g.

create table t_p (id number, p1 number, p2 number, p3 number, p4 number);

create table t_c (id number, c1 number, c2 number, c3 number, c4 number);

begin
 insert into t_p(id, p1,p2,p3,p4) values (100, 1,2,'','');
 insert into t_p(id, p1,p2,p3,p4) values (200, 1,'','','');
 insert into t_p(id, p1,p2,p3,p4) values (300, 1,3,4,'');
 insert into t_p(id, p1,p2,p3,p4) values (400, 1,4,2,'');
 insert into t_p(id, p1,p2,p3,p4) values (500, 1,2,3,4);
 end;
 /
 
 begin
 insert into t_c(id, c1,c2,c3,c4) values (100, 1,2,3,'');
 insert into t_c(id, c1,c2,c3,c4) values (200, 1,2,3,4);
 insert into t_c(id, c1,c2,c3,c4) values (300, 2,3,4,'');
 insert into t_c(id, c1,c2,c3,c4) values (400, 1,4,3,'');
 insert into t_c(id, c1,c2,c3,c4) values (500, 1,2,'','');
 end;
 /

select * from t_p;
---------------------------------
100	1	2		
200	1			
300	1	3	4	
400	1	4	2	
500	1	2	3	4

select * froom t_c;
---------------------------------
100	1	2	3	
200	1	2	3	4
300	2	3	4	
400	1	4	3	
500	1	2		

final output:
100	1	2	3	
200	1	2	3	4

I have written like below. Which is giving correct result.
I am sure there mights be much better way to do it.

SELECT c.*
  FROM t_p p, t_c c
 WHERE p.id = C.ID
 and (NVL (p1, nvl(c1,0))
       - NVL (c1, 0))
       + (NVL (p2, nvl(c2,0))
       - NVL (c2, 0))
       + (NVL (p3, nvl(c3,0))
       - NVL (c3, 0))
       + (NVL (p4, nvl(c4,0))
       - NVL (c4, 0))=0

It would be great, if you could please show me any other way of doing it or may be a better approach.

Regards,
Pointers
Re: Rights query [message #620444 is a reply to message #620443] Fri, 01 August 2014 08:28 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
>It would be great, if you could please show me any other way of doing it or may be a better approach.

Which metric measures "better"?
fewer characters in SQL statement?
Re: Rights query [message #620445 is a reply to message #620444] Fri, 01 August 2014 08:34 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
Quote:
Which metric measures "better"?
fewer characters in SQL statement?

Shocked I would certainly look for the query which gives me results quicker when the table size grows, my example above only have few rows which was meant for the purpose, but actually the table is huge. I also mentioned like "other way of doing" because I tend to see lots of new ways from Solomon or Micheal etc.. so thought i would put a case here to learn something new.
But you know Mr.Blackswan I kind of expected your reply Razz

Regards,
Pointers
Re: Rights query [message #620446 is a reply to message #620443] Fri, 01 August 2014 08:35 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Quote:
and t_p have minimum rights to the given id.


Please explain it properly.
Re: Rights query [message #620449 is a reply to message #620446] Fri, 01 August 2014 08:43 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
I will try my best to explain it.
let say,
t_p has columns p1, p2, p3 and p4
for the first row in t_p
i.e. 1,2,'',''
1 is for global
2 is for asia

t_c has columns c1, c2, c3 and c4
for the first row in t_c
1,2,3,''
1 is for global
2 is for asia
3 is for india

so the parent table t_p row has right to see data in t_c as 2 is the minimum right which is above india.

I am sure you will get an idea if you looks at my test data and result set.

Regards,
Pointers
Re: Rights query [message #620453 is a reply to message #620449] Fri, 01 August 2014 09:29 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Please check it


select c.*
  FROM t_p p, t_c c
 WHERE p.id = C.ID
 and nvl(nvl(p1,c1),1)=nvl(c1,1)
 and nvl(nvl(p2,c2),1)=nvl(c2,1)
 and nvl(nvl(p3,c3),1)=nvl(c3,1)
 and nvl(nvl(p4,c4),1)=nvl(c4,1);

Re: Rights query [message #620455 is a reply to message #620449] Fri, 01 August 2014 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
so the parent table t_p row has right to see data in t_c as 2 is the minimum right which is above india.


OK now explain "all rows from t_c where id matches to t_p table and t_p have minimum rights to the given id."

In otherr words,
why?
100	1	2	3	
200	1	2	3	4

and not
500	1	2	3	4

Re: Rights query [message #620476 is a reply to message #620455] Fri, 01 August 2014 23:55 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
Hi Michel,

Thank you for your response.

For 500 id in t_p:
t_p has 1 2 3 4 which means 1for globe 2 for europe 3 for france 4 for paris so id 500 has access to only paris in t_ p.
Where as for 500 id in t_c, 1 for globe 2 for europe.

So, the parent row t_p 500 has access to a level of paris but not at global level as for t_c. so 500 row is not picked up from t_c.

where as id 200 in t_p has 1 which is globe so he has acess to globe so he can see anything at tge globe or below level.
t_c has id 200 as 1 2 3 4 which is 1 for globe 2 for europe 3 for france and 4 for paris
So, id 200 in t_p has right at global level so he can pick this record from t_c.


Regards,
Pointers
Re: Rights query [message #620477 is a reply to message #620476] Sat, 02 August 2014 00:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
So, id 200 in t_p has right at global level so he can pick this record from t_c.


Is this not the case for id 500?
Quote:
For 500 id in t_p: t_p has 1 2 3 4 which means 1for globe


Sorry, I really don't understand, maybe someone else...

Re: Rights query [message #620478 is a reply to message #620477] Sat, 02 August 2014 00:46 Go to previous message
pointers
Messages: 350
Registered: May 2008
Senior Member
It is like " upper level in t_p can see the lower level in t_c"
But lower level in t_p can not see upper level record of t_c for a given id.

The level is as below
globe>europe>france>paris
globe is highest level

For 500 in t_p it is mapped to a level of 4 which is paris where as the same record in t_c is mapped to level of 2 i.e. europe. So paris in t_p can not see globe.

For 200 in t_p it is mapped to a level of 1 i.e. globe where as the record is mapped to a level of 4 in t_c i.e. paris
So 200 id in t_p can see t_c record of 200.

Regards,
Pointers
Previous Topic: Exception Handling For %type , %Rowtype variables
Next Topic: case statemnent
Goto Forum:
  


Current Time: Sat Sep 20 09:53:23 CDT 2014

Total time taken to generate the page: 0.09639 seconds