Блог

Я нахожусь на этапе в рамках одного из моих проектов Oracle APEX, где мне нужно реализовать различные уровни безопасности для конкретных пользователей для конкретных приложений.

Для начала я создал декартову таблицу, содержащую информацию из user таблицы, app table и role table.

Это выглядит следующим образом:

 SELECT
A.user_id, B.app_id, C.role_id
FROM user A, app B, role C
ORDER BY A.user_id ASC, B.app_id ASC, C.role_id ASC
  

Это позволяет мне возвращать ЛЮБУЮ комбинацию user , app и role . без использования предложения where она возвращает более 303 тысяч строк. в настоящее время почти 500 пользователей, 6 ролей и более 100 приложений.

когда я выбираю из этого представления для конкретного пользователя, оно возвращается примерно через 10 мс, что приемлемо.

Теперь у меня также есть vw, в котором хранится назначение приложений / ролей каждого пользователя. Я присоединил эту таблицу к декартовой следующим образом.

 SELECT
A.*,
DECODE(B.app_right_id, null, 0, 1) AS user_access 
FROM
            vw_user_app_role A -- My cartesian view
LEFT JOIN   vw_tbl_user_app_role B 
        ON  A.user_id = B.user_id
        AND A.app_id = B.app_id
        AND A.role_id = B.role_id
  

Это возвращает очень удобный набор данных, который напоминает

 user_id app_id role_id user_access
50      5      1       0  
50      10     2       1
50      15     3       1
75      5      1       1
75      10     2       0
75      15     3       0
  

Я обдумываю, каким должен быть мой следующий шаг, если я должен создать свод данных, где app_id будет строкой, role_id будет столбцами, а user_access будет «данными». «Данные» в конечном итоге будут отображаться в виде флажка на веб-сайте с соответствующими заголовками строк / столбцов.

Я также рассматриваю возможность использования чистого решения ajax / json, где я создам строку json с использованием pl sql и верну всю строку клиенту для обработки с помощью jquery.

Меня беспокоит сложность первого варианта (я очень новичок в pl sql, и я не уверен в том, как сгенерировать сводную таблицу для использования в этой версии oracle (v 10)), и я обеспокоен расходами на создание целой строки json, которая будет содержать так много данных.

Любые предложения будут с благодарностью приняты.

Редактировать

Я добился желаемой сводной таблицы с помощью следующего sql:

 SELECT
B.application_nm,
A.user_id,
MAX(DECODE(b.role_name, 'role 1', A.USER_ACCESS, NULL)) "role 1",
MAX(DECODE(b.role_name, 'role 2', A.USER_ACCESS, NULL)) "role 2",
MAX(DECODE(b.role_name, 'role 3', A.USER_ACCESS, NULL)) "role 3",
MAX(DECODE(b.role_name, 'role 4', A.USER_ACCESS, NULL)) "role 4",
MAX(DECODE(b.role_name, 'role 5', A.USER_ACCESS, NULL)) "role 5",
MAX(DECODE(b.role_name, 'role 6', A.USER_ACCESS, NULL)) "role 6"
FROM 
vw_user_app_access A LEFT JOIN vw_tbl_app B ON A.app_id = B.app_id
LEFT JOIN vw_tbl_roles C ON A.role_id = C.role_id
GROUP BY B.application_name, A.user_id
ORDER BY A.user_id DESC
  

Единственная проблема заключается в том, когда в будущем нам придется добавить «роль 7». Затем я должен вернуться к этому запросу и добавить строку MAX(DECODE(b.role_name, 'role 7', A.USER_ACCESS, NULL)) "role 7"

Забегая вперед, это может быть неудобством, но, учитывая платформу APEX, мне пришлось бы любым способом перейти к отчету, чтобы обновить количество столбцов вручную, я полагаю.

Я думаю, что это может быть «лучшим» решением на данный момент, если у кого-нибудь нет других предложений…

Для области отчета Apex, основанной на динамическом SQL-запросе, возможно возвращать другое количество столбцов по мере изменения запроса. Я настроил простую демонстрацию на apex.oracle.com . Введите новое имя столбца в табличную форму Columns и нажмите «Добавить строку», и отчет Matrix будет повторно отрисован с дополнительным столбцом с таким именем.

Вы должны:

1. Основывайте отчет на функции, которая возвращает SQL для запуска в виде строки
2. Выберите атрибут region «Использовать общие имена столбцов (анализировать запрос только во время выполнения)».
3. Задайте для типа заголовков отчета значение PL / SQL, а затем используйте функцию для динамического возврата требуемых заголовков столбцов в виде списка, разделенного двоеточием. Обратите внимание, что это может отличаться от имени столбца, хотя в моем примере используется один и тот же текст для обоих.

Если мой пример недостаточно понятен, я добавлю больше информации позже — сейчас у меня нет времени.