Home » SQL & PL/SQL » SQL & PL/SQL » Rights query (Oracle 10.2.0.3)
| Rights query [message #620443] |
Fri, 01 August 2014 08:20  |
pointers
Messages: 451
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 #620449 is a reply to message #620446] |
Fri, 01 August 2014 08:43  |
pointers
Messages: 451
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 |
 |
sss111ind
Messages: 634
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 #620476 is a reply to message #620455] |
Fri, 01 August 2014 23:55 |
pointers
Messages: 451
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 #620478 is a reply to message #620477] |
Sat, 02 August 2014 00:46 |
pointers
Messages: 451
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
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 03:48:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
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. 
