ORACLE: create user of all recorded members and to give all of them them privileges by their department

Viewed 31

I want to create user of all recorded staff member and want to give all of them them privileges by their department.

I have first created procedure which will create user in the start. I am thinking it can have run time time error which I can't able to solve.

cursor will fetch department and then I've applied switch case. which will perform rant operation accordingly.

this code is giving me constantly errors I have tried and searched for long time and can't able to solve. below is my error: declare * ERROR at line 1: ORA-01920: user name 'NAME' conflicts with another user or role name ORA-06512: at "SYS.CRTUSER", line 6 ORA-06512: at line 15

create table staff(ID number primary key, Department varchar2(30), Name varchar2(30), Age number, St_Mob number, Salary number);

insert into staff values(76435 , 'Management', 'SUJAL_LUHAR', 27, 8401994158,  90000);

insert into staff values(76436 , 'Strategy', 'SHUBH_PATEL', 26, 7567667847,  60000);

insert into staff values(76437 , 'Helpline', 'JAY_LIMBACHIYA', 23, 9876520132,  45000);

insert into staff values(76438 , 'Management', 'HARIKRUSHNA', 25, 9973420132,  85000);

insert into staff values(76439 , 'Strategy', 'KAJAL_CHAVDA', 28, 9946701210,  65000);

insert into staff values(76440 , 'Helpline', 'BHAVYA_OZA', 26, 9874890132,  50000);

insert into staff values(76444 , 'Management', 'AVNI_PATEL', 30, 3214567820,  92000);

create or replace procedure crtuser
is
begin
    for R in (select Name,ID from staff)
    loop
        EXECUTE IMMEDIATE 'create user Name identified by ID';
    end loop;
end;
/

set serveroutput on;

declare
S_ID staff.ID%type;
S_DEPRT staff.Department%type;
S_NAME staff.Name%type;

cursor createuser is
    select staff.ID, staff.Department, staff.Name from staff;

begin 

    open createuser;
        
        loop
            fetch createuser into S_ID, S_DEPRT, S_NAME;
                    crtuser;
            case S_DEPRT
                when 'Management' then
                    EXECUTE IMMEDIATE 'grant select on property to S_NAME';
                    EXECUTE IMMEDIATE 'grant update on property to S_NAME';
                    EXECUTE IMMEDIATE 'grant insert on property to S_NAME';
                    EXECUTE IMMEDIATE 'grant delete on property to S_NAME';

                when 'Strategy' then
                    EXECUTE IMMEDIATE 'grant select on client to S_NAME';
                    EXECUTE IMMEDIATE 'grant select on property to S_NAME';

                when 'Helpline' then
                    EXECUTE IMMEDIATE 'grant select on client to S_NAME';
                    EXECUTE IMMEDIATE 'grant update on client to S_NAME';
            end case;

            exit when createuser%notfound;
        end loop;
    
    close createuser;

end;
/
0 Answers
Related