Home » SQL & PL/SQL » SQL & PL/SQL » Grant previlege to role (Oracle 10.2.0.4, HP-UX)
Grant previlege to role [message #459150] Thu, 03 June 2010 10:36 Go to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Hello everyone,

I have a stored procedure which when executed creates a user and grants some roles to the user and also makes certain roles DEFAULT using "alter user"

The issue comes when i execute the procudure.

I have a User who has a role and this role has the "alter user" privilege, for example lets say that the user1 has the role ABC, and role ABC has the "alter user" privilege.
SQL> grant alter user to ABC;
Grant succeeded.
SQL> grant ABC to user1;
Grant succeeded.


Now, when i run the stored procedure as the user1, it gives me an error on a line saying "insufficient privileges", when i check the line, its this line:
alter user user1 DEFAULT ROLE "ROLE1", "ROLE2"

But as far as i know the user1 has the "alter user" privilege

I want to make those two roles ROLE1, ROLE2 DEFAULT because i dont want the other roles ROLE3,ROLE4 to be default, as you know if there are many roles and if we alter user with default for certain roles other roles become DEFAULT=NO.

So i get the error at "alter user" statement though the user has the "alter user" privilege, what do you think might be wrong?

OR is there anyway to grant roles to the user with default=NO option?

Please help me resolve this.

Thanks.
Munna
Re: Grant previlege to role [message #459153 is a reply to message #459150] Thu, 03 June 2010 10:43 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Roles aren't enabled by default in stored procedures.
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551289900368934430

[Updated on: Thu, 03 June 2010 10:43]

Report message to a moderator

Re: Grant previlege to role [message #459154 is a reply to message #459150] Thu, 03 June 2010 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
when i run the stored procedure as the user1, it gives me an error on a line saying "insufficient privileges"

Privileges acquired through are not enabled in procedure.
Privileges acquired through are not enabled in procedure.
Privileges acquired through are not enabled in procedure.
Privileges acquired through are not enabled in procedure.
Privileges acquired through are not enabled in procedure.
Privileges acquired through are not enabled in procedure.
Privileges acquired through are not enabled in procedure.
Privileges acquired through are not enabled in procedure.

Regards
Michel
Re: Grant previlege to role [message #459155 is a reply to message #459150] Thu, 03 June 2010 10:58 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you cookiemonster and Michel.

According to the source link posted by cookiemonster, i granted the privilege directly to the user and the procedure works well.

Thanks much guys.

Regrds,
Munna
Re: Grant previlege to role [message #459168 is a reply to message #459155] Thu, 03 June 2010 11:59 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel, could you, please, rewrite it once again (10 times at least), but now try to make a complete sentence.

Michel
Privileges acquired through are not enabled in procedure.

Through ... what?

a) strawberies
b) potatos
c) submarines
d) airplanes
e) rolexes
f) roles
g) singers
h) actors
i) avatars

[Updated on: Thu, 03 June 2010 12:22]

Report message to a moderator

Re: Grant previlege to role [message #459170 is a reply to message #459168] Thu, 03 June 2010 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
e)
e)
e)
e)
e)
e)
e)
e)

Too bad "Spell-check" does not detect missing words. Wink

Regards
Michel

[Updated on: Thu, 03 June 2010 12:06]

Report message to a moderator

Re: Grant previlege to role [message #459171 is a reply to message #459170] Thu, 03 June 2010 12:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Thu, 03 June 2010 13:05
Too bad "Spell-check" does not detect missing words. Wink


Like definer rights? Wink
Re: Grant previlege to role [message #459175 is a reply to message #459171] Thu, 03 June 2010 12:23 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is "e" your final answer? /forum/fa/1600/0/
Re: Grant previlege to role [message #459177 is a reply to message #459175] Thu, 03 June 2010 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Er... no g) Laughing

Regards
Michel
Re: Grant previlege to role [message #459182 is a reply to message #459177] Thu, 03 June 2010 13:25 Go to previous message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
/forum/fa/2115/0/
Previous Topic: Insert Statement
Next Topic: trigger error
Goto Forum:
  


Current Time: Thu Aug 21 16:01:26 CDT 2025