Setting up a role and linking it to an user is not working as expected in SQL

Question

I created a role R_CLIENTE:

CREATE ROLE R_CLIENTE IDENTIFIED BY RolCliente;

Then I granted some privileges on it:

GRANT SELECT ON alquiler.CLIENTE TO R_CLIENTE;

(Schema alquiler and table CLIENTE already exist). Then I created an user U_Cliente1:

CREATE USER U_Cliente1 IDENTIFIED BY Cliente1 DEFAULT TABLESPACE table_def TEMPORARY TABLESPACE table_temp QUOTA 2M ON table_def PASSWORD EXPIRE;

(Both tablespaces already exist). I granted U_Cliente1 to R_CLIENTE privileges:

GRANT R_CLIENTE TO U_Cliente1;

When I login as U_Cliente1 I am not able to select any data from the table alquiler.CLIENTE:

SQL> desc alquiler.CLIENTE;
ERROR:
ORA-04043: object alquiler.CLIENTE does not exist

However, if I grant directly the privilege to the user U_Cliente1:

GRANT SELECT ON alquiler.CLIENTE TO U_Cliente1;

Now I am able to select the table alquiler.CLIENTE:

SQL> desc alquiler.CLIENTE;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 K_CODCLIENTE                  NOT NULL NUMBER(5)
 N_NOMBRE1                 NOT NULL VARCHAR2(15)
 N_NOMBRE2                      VARCHAR2(15)
 N_APELLIDO1                   NOT NULL VARCHAR2(15)
 N_APELLIDO2                        VARCHAR2(15)
 N_DIRECCION                   NOT NULL VARCHAR2(50)
 Q_TELEFONO                NOT NULL NUMBER(10)
 K_CODREF                       NUMBER(5)
 I_TIPOID                  NOT NULL VARCHAR2(2)
 Q_IDENTIFICACION              NOT NULL VARCHAR2(10)

How can I properly link a role and user(s) so they can all share the same privileges?

Answer 1

The reason that the direct grant worked the way it did is because direct grants are always active. Roles can be activated and deactivated within a session. In your example you didn't configure the role as a default role, so it must be explicitly activated after you login, like this:

set role r_cliente;

alternatively, after granting the role set it as a default for the user:

alter user U_Cliente1 default role r_cliente;

or

alter user U_Cliente1 default role all;

Then the role will be active automatically when you login.