Subversion Repositories DevTools

Rev

Rev 5512 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
6265 dpurdie 1 
/* Get a list of all user rols with flags to indicate if they can be delegated or if they have been granted */
2 
SELECT role_id,
3 
  role_name,
4 
  comments,
5 
  can_delegate,
6 
  granted
7 
FROM
8 
  (SELECT ro.*,
9 
    (SELECT COUNT(*)
10 
    FROM ROLES ra,
11 
      USER_ROLES ur
12 
    WHERE ra.APP_ID = :APP_ID
13 
    AND ur.USER_ID  = :USER_ID_USER
14 
    AND ur.ROLE_ID  = ra.ROLE_ID
15 
    AND ra.role_id  = ro.role_id
16 
    ) AS can_Delegate,
17 
    (SELECT COUNT(*)
18 
    FROM ROLES ra,
19 
      USER_ROLES ur
20 
    WHERE ra.APP_ID = :APP_ID
21 
    AND ur.USER_ID  = :USER_ID
22 
    AND ur.ROLE_ID  = ra.ROLE_ID
23 
    AND ra.role_id  = ro.role_id
24 
    ) AS granted
25 
  FROM ROLES ro
26 
  WHERE ro.APP_ID = :APP_ID
27 
  )
28 
WHERE IS_ROLE_VARIATION IS NULL
29 
AND app_id               = :APP_ID
30 
ORDER BY UPPER(role_name)