Home » RDBMS Server » Security » ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE (Oracle Database Enterprise 11gR2, 11.2.0.4.0, Windows Server 2012 r2)
ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663389] Thu, 01 June 2017 14:42 Go to next message
licio.matos
Messages: 4
Registered: June 2017
Junior Member
Hi,

I'm get the error below when issuing the command above:


ALTER USER XXXX GRANT CONNECT THROUGH YYYY WITH ROLE ROLE_APP;

ORA-01924 - "role '%s' not granted or does not exist"

This work on version 11.2.0.1.0 and 11.2.0.3.0, the role exist.

Anyone knows if this is a bug?

Tks All.
Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663390 is a reply to message #663389] Thu, 01 June 2017 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is the role granted?
Post a complete test case showing the error so we can reproduce it at our place and in different versions.

Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663391 is a reply to message #663389] Thu, 01 June 2017 14:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
THis works for me in release 12.1.0.2:
rclz> create role role_app;

Role created.

orclz> grant role_app to scott;

Grant succeeded.

orclz> alter user scott grant connect through system with role role_app;

User altered.

orclz>
so you must have done something different. What did you do?
Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663392 is a reply to message #663391] Thu, 01 June 2017 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This also works for me in 11.2.0.4 (this is why I asked for a reproducible test case):
SQL>  create role role_app;

Role created.

SQL> grant role_app to scott;

Grant succeeded.

SQL> alter user scott grant connect through system with role role_app;

User altered.

SQL> @v

Oracle version: 11.2.0.4.0
Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663393 is a reply to message #663389] Thu, 01 June 2017 15:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It looks like the role was not granted, which is what Michel asked about. Please see the demonstration below.

-- test environment:
SCOTT@orcl_12.1.0.2.0> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

-- reproduction of error:
SCOTT@orcl_12.1.0.2.0> create user xxxx identified by xxxx
  2  /

User created.

SCOTT@orcl_12.1.0.2.0> create role role_app
  2  /

Role created.

SCOTT@orcl_12.1.0.2.0> create user yyyy identified by yyyy
  2  /

User created.

SCOTT@orcl_12.1.0.2.0> alter user xxxx grant connect through yyyy with role role_app
  2  /
alter user xxxx grant connect through yyyy with role role_app
*
ERROR at line 1:
ORA-01924: role 'ROLE_APP' not granted or does not exist

-- solution:
SCOTT@orcl_12.1.0.2.0> grant role_app to xxxx
  2  /

Grant succeeded.

SCOTT@orcl_12.1.0.2.0> alter user xxxx grant connect through yyyy with role role_app
  2  /

User altered.
Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663411 is a reply to message #663393] Fri, 02 June 2017 05:25 Go to previous messageGo to next message
licio.matos
Messages: 4
Registered: June 2017
Junior Member
So, This is whats happens:

In version 11.2.0.1.0
There is no need to grant the ROLE to the user, i think this is why you specified the "with role role_app".

Here is my test on both oracle versions.

-- TEST ENVIRONMENT ERROR

select banner from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE	11.2.0.4.0	Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

CREATE USER XXXX IDENTIFIED BY XXXX;

User XXXX criado.

CREATE USER YYYY IDENTIFIED BY YYYY;

User YYYY criado.

CREATE ROLE ROLE_APP;

Role ROLE_APP criado.

GRANT SELECT ANY TABLE TO ROLE_APP;

Grant bem-sucedido.

/* NO GRANT TO USER EXPLICT FOR ROLE_APP TO XXXX */

ALTER USER XXXX GRANT CONNECT THROUGH YYYY WITH ROLE ROLE_APP;

Erro a partir da linha : 10 no comando -
ALTER USER XXXX GRANT CONNECT THROUGH YYYY WITH ROLE ROLE_APP
Relatório de erros -
Erro de SQL: ORA-01924: a atribuição 'ROLE_APP' não foi concedida ou não existe
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.


-- ENVIRONMENT TESTE THAT WORKS

select banner from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE	11.2.0.1.0	Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

CREATE USER XXXX IDENTIFIED BY XXXX;

User XXXX criado.

CREATE USER YYYY IDENTIFIED BY YYYY;

User YYYY criado.

CREATE ROLE ROLE_APP;

Role ROLE_APP criado.

GRANT SELECT ANY TABLE TO ROLE_APP;

Grant bem-sucedido.

ALTER USER XXXX GRANT CONNECT THROUGH YYYY WITH ROLE ROLE_APP;

User XXXX alterado.

As you can see, i can give the role directly to the user trough the command "alter user connect troutgh with role role app"
There is no needed to give the role explicity to the user directly before the alter user command.

So, any ideas of why this happens in lates version of oracle?


Tks
Licio Matos

Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663412 is a reply to message #663411] Fri, 02 June 2017 05:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The one you think is working, isn't - that's a bug.
The error message and the documentation on alter user are clear that the role needs to already to be granted to the user.
The whole point of that clause is to limit the roles the user can have enabled when doing the connect through.
Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663413 is a reply to message #663412] Fri, 02 June 2017 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and the bug is fixed in next versions so you can no more do it, something you shouldn't have been able to do if there had been no bug.

Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663414 is a reply to message #663412] Fri, 02 June 2017 05:57 Go to previous messageGo to next message
licio.matos
Messages: 4
Registered: June 2017
Junior Member
Tks a lot cookie monster. That is what i thinks to, a bug. I Appreciate the fast answer to the problem. The admin can close this thread. Tks guys!
Smile
Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663415 is a reply to message #663414] Fri, 02 June 2017 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Take care, it was a bug in the previous versions not in the current one.
The correct behavior is the one you experiment now: you can't do what you previously was able to do.

Re: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663416 is a reply to message #663415] Fri, 02 June 2017 06:11 Go to previous message
licio.matos
Messages: 4
Registered: June 2017
Junior Member
I understand.
The bug was on previous versions 11.2.0.4, and was fixed.
i will fix the code of the installer to grant the role previous to the user.
Tks guys.
Previous Topic: Audit specific statment on specific table by specific user
Next Topic: Begin to manage and make security
Goto Forum:
  


Current Time: Thu Mar 28 06:31:07 CDT 2024