Как правильно составить запрос sql?

Question

[Kvert007]

Здравствуйте, нужно составить sql запрос, который выбирает всех пользователей из бд, которые имеют ТОЛЬКО следующие Роли.

Вот запрос, но он не работает.

select * from dba_role_privs where (GRANTED_ROLE='CONNECT' AND GRANTED_ROLE='PRESS');

Помогите, пожалуйста!

Comments

[Kvert007]

Нужно чтоб вернулись записи у которых присутствует ТОЛЬКО две эти роли.

Answer 1

[okashirin]

select * from dba_role_privs where (GRANTED_ROLE='CONNECT' OR GRANTED_ROLE='PRESS')

Comments

[Kvert007]

К сожалению этот вариант не подходит так как вернуться все записи которые содержат роль CONNECT,роль PRESS и обе роли, а мне нужно чтоб вернулись только те записи которые содержат обе роли.

[okashirin]

каким образом 'CONNECT' и 'PRESS' могут быть в одном поле?

[okashirin]

не может быть записи, где GRANTED_ROLE равен и 'CONNECT', и 'PRESS'

[okashirin]

если они в строке, то попробуй select * from dba_role_privs where (GRANTED_ROLE LIKE '%CONNECT%' AND GRANTED_ROLE LIKE '%PRESS%')

[Kvert007]

В одном поле они не могут быть

[okashirin]

тогда первый ответ верный

[Kvert007]

Но почему то мне выводятся записи пользователей которые просто имеют роль коннект, а мне нужно чтоб было у пользователя одновременно 2е этих роли.

[okashirin]

попробуй структуру таблицы показать

[okashirin]

я кажется понял

[Kvert007]

Это стандартная служебная таблица Oracle

[okashirin]

select * from dba_role_privs where GRANTED_ROLE='CONNECT' AND ИД_ПОЛЬЗОВАТЕЛЯ IN (select ИД_ПОЛЬЗОВАТЕЛЯ from dba_role_privs where GRANTED_ROLE='PRESS')

[Kvert007]

почти но не совсем

[Kvert007]

так отбираются все пользователи у которых одновременно есть и коннект и пресс + еще что то

[Kvert007]

А нужно ТОЛЬКО коннект и пресс

[okashirin]

теперь точно дошло :-)

[okashirin]

можно попробовать NOT IN и перечислить все кроме PRESS и CONNECT

[Kvert007]

Я тоже так думал, но к сожалению не известно что там есть...так что данный вариант отпадает =(

[okashirin]

можно выбрать все, кто там есть select distinct GRANTED_ROLE from dba_role_privs

Answer 2

[okashirin]

еще попробуй

select * from dba_role_privs t1 join dba_role_privs t2 on t1.GRANTED_ROLE='CONNECT' and t2.GRANTED_ROLE='PRESS'

Comments

[Kvert007]

Не, этот вариант много лишнего дает

Answer 3

[Олег]

Грязно, можно оптимизировать:

select t.grantee
  from (select grantee, count(grantee) as counts
          from dba_role_privs
         where grantee in
               (select grantee
                  from dba_role_privs
                 where granted_role = 'PRESS'
                   and grantee in
                       (select grantee
                          from dba_role_privs
                         where granted_role = 'CONNECT'))
         group by grantee) t
 where t.counts = 2

Comments

[Kvert007]

Ругается что отсутствует выражение

[Олег]

опечатался, удалите " and ", вот так

select t.grantee from (select g.grantee as grantee, count(g.grantee) as counts from dba_role_privs g where g.granted_role in ('CONNECT','PRESS') group by g.grantee) t where t.counts=2

[Олег]

И вот вам правильный вариант, первый был некорректный, сорь)):

select t.grantee
  from (select grantee, count(grantee) as counts
          from dba_role_privs
         where grantee in
               (select grantee
                  from dba_role_privs
                 where granted_role = 'PRESS'
                   and grantee in
                       (select grantee
                          from dba_role_privs
                         where granted_role = 'CONNECT'))
         group by grantee) t
 where t.counts = 2

[Олег]

Необходима оптимизация, не бросайте камнями)

[Олег]

Этот запрос, вернет пользователей, котроые состоят в ролях ТОЛЬКО 'CONNECT' и 'PRESS', если пользователь состоит еще в какойто роли, запрос его не покажет и если пользователь состоит в одной из этих ролей, запрос его тоже не покажет.

[Kvert007]

Спасибо большое =)

[Олег]

Рад был помочь.

Answer 4

[fessvmk]

select distinct grantee
  from (select p.grantee, 
               sum(decode(p.granted_role, 'PRESS', 1, 'CONNECT', 1, 0)) over (partition by p.grantee) cnt,
               count(*) over (partition by p.grantee) cnt_all
          from dba_role_privs p)
 where cnt = 2
   and cnt_all = 2

Answer 5

[xtender]

just for lulz:

select grantee
from dba_role_privs
group by grantee
having count(*)=2
   and cast(collect(GRANTED_ROLE) as sys.ku$_vcnt) = sys.ku$_vcnt('CONNECT','RESOURCE')

чуть поскучнее:

select grantee
from dba_role_privs
group by grantee
having count(*)=2
   and min(GRANTED_ROLE) ='CONNECT'
   and max(GRANTED_ROLE) ='RESOURCE'