Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ivan.

Asked: November 10, 2022 - 12:07 pm UTC

Last updated: November 21, 2022 - 11:50 am UTC

Version: 12.1.0.2

Viewed 100+ times

You Asked

My problem is the following:

I have created a procedure, in which I assign roles to users. For example:

SET serveroutput ON;

declare
vsqlsql varchar(360);
permiso varchar(360):='PROBA';
BEGIN

            vsqlsql:='GRANT '||UPPER(permiso)||' TO ueueue001';
             EXECUTE IMMEDIATE(vsqlsql);
            
END;
/

--DROP PROCEDURE ROL;

create or replace PROCEDURE          ROL (permiso in VARCHAR2)
AS

vsqlsql varchar(360);
BEGIN

            vsqlsql:='GRANT '||UPPER(permiso)||' TO ueueue001';
             EXECUTE IMMEDIATE(vsqlsql);
            
END;
/

exec ROL('PROBA');

select * from dba_role_privs where grantee='UEUEUE001' AND GRANTED_ROLE='PROBA';




Everything is correct, until I execute the procedure and it tells me:

Procedimiento PL/SQL terminado correctamente.

Procedure ROL compilado


Error que empieza en la línea: 28 del comando :
BEGIN ROL('PROBA'); END;
Informe de error -
ORA-01924: el rol 'PROBA' no se ha otorgado o no existe
ORA-06512: en "IVAN.ROL", línea 8
ORA-06512: en línea 1
01924. 00000 -  "role '%s' not granted or does not exist"
*Cause:    Either the role was not granted to the user, or the role did not exist.
*Action:   Create the role or grant the role to the user and retry
           the operation.

GRANTEE                                                                                                                          GRANT ADM DEL DEF COM
-------------------------------------------------------------------------------------------------------------------------------- ----- --- --- --- ---
UEUEUE001                                                                                                                        PROBA NO  NO  NO  NO 





When I execute the block the role is assigned correctly, but when I call the procedure it tells me that the role does not exist.

Why?

and Connor said...

First, please take a look here

https://asktom.oracle.com/Misc/RolesAndProcedures.html

and see if that is the cause of your problem.

Rating

  (1 rating)

Comments

Resolved

A reader, November 15, 2022 - 11:10 am UTC

Hello, when I read your answer I saw that what I was missing was not the role of creating procedures, but I do need the role
GRANT ANY_ROLE to be able to give any database role to a user.

Thank you very much for the reply
Connor McDonald
November 21, 2022 - 11:50 am UTC

glad we could help

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.