D
D
DunkanMaklaut2022-02-20 09:01:21
Oracle
DunkanMaklaut, 2022-02-20 09:01:21

How to make a trigger fire when a certain role enters?

Good day! I am doing an Oracle database administration task, the teacher said to create several roles (Storeman, Admin and customer)

create role kladovshchik;
create role zakazchik;
create role admin_role;

and give some privileges to these roles. The problematic task for me is this: Give the admin the right to enter the database only on weekdays. I created a trigger for this, with code like this:
CREATE OR REPLACE TRIGGER vhod_tolko_po_budnjam AFTER LOGON ON DATABASE DECLARE
    current_day NUMBER;
    user_role   NUMBER;
BEGIN
    SELECT DISTINCT
        COUNT(*)
    INTO user_role
    FROM
        user_role_privs
    WHERE
        granted_role = 'admin_role'; --Проверяем роль
    SELECT
        to_char(sysdate, 'D')
    INTO current_day
    FROM
        dual; --получаем день недели
    IF
        ( current_day > 5 )
        AND ( user_role >= 1 )
    THEN --если вошел после пятницы и роль админ
        raise_application_error(-20000, 'Приходите в будни!'); 
    END IF;

END;

I created this trigger under the user Sergey (well, under which I do all the labs), and for other users it doesn’t work, but I don’t know how to make the trigger work for a specific role, but as far as I know, it’s impossible to assign it as a procedure .. ..

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question