OraFAQ Forum: Security » ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE [message #663389]

Thu, 01 June 2017 14:42

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.

Report message to a moderator

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

THis works for me in release 12.1.0.2:

Code: [Select all] [Show/ hide]

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?

Report message to a moderator

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This also works for me in 11.2.0.4 (this is why I asked for a reproducible test case):

Code: [Select all] [Show/ hide]

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

Report message to a moderator

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

Barbara Boehmer
Messages: 9105
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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

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

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

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

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

cookiemonster
Messages: 13967
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.

Report message to a moderator

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

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.

Report message to a moderator

Previous Topic:	Audit specific statment on specific table by specific user
Next Topic:	Begin to manage and make security

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 28 18:10:39 CDT 2025