Help - Search - Members - Calendar
Full Version: Setting roles in stored procedures
Oracle DBA Forums > Oracle > Oracle Forum
Krzysztof Kielan
Hello,

Recently I've encountered a problem, when I try to set user ROLEs by encapsulating the 'set role' code in a stored procedure, I received an error message:

Error::: ORA-06565: cannot execute SET ROLE from within stored procedure.

I've tried some solutions given on the Internet:

1. dbms_session.set_role('rolenames') - the same error
2. dbms_utility.exec_ddl_statement('SET ROLE rolename');
3. encapsulation in an anonymus block:

begin
dbms_session.set_role('rolenames') - the same error
end;

All of the above haven't solved my problem.

Can any of You help me, mayby by providing sample code, or navigate me to some document. Mayby what I'm trying to do is impossible, however this is certainly not an option in my situation. Thank you in advance.

Krzysztof Kielan
dave
sorry, but you cant set role in stored procs - it doesnt make sense to either
Krzysztof Kielan
QUOTE (dave @ Jun 30 2008, 11:35 AM) *
sorry, but you cant set role in stored procs - it doesnt make sense to either


Well, the problem is that setting roles in the stored procedure would solve many problems which I have to tackle.

I'll try to introduce You to project I'm working on.

I have a client and a service (C#, WCF, .NET). The client connects to the service in order to authenticate himself. The service when he receives authentication data from the client executes a stored procedure located on oracle database. This procedure authenticates the current user and returns (IN/OUT parameters) some data. User roles are one of the parameters returned by the stored procedure.

The problem is that I have the roles, which the user has, but they are not activated (it is the default setting in the database I'm working with). That is the reason why I need to use the set role statement. Another aspect is that roles are secured by passowrds. I wouldn't like to send these passowrds through the communication channel from the database to the service. This is the reason, why I think that setting roles in my authentication stored procedure would be natural. To go deeper into teh problem, I dont't have direct access to the roles passowrds, they are written down in a package and I don't have the privilages even to see them (although the storage of the passwords is not the proble I'm in need to discuss).

So once again I would like to ask for help in my problem. Mayby there is some solution, which would enable me to set roles in the body of a stored procedure.

Cheers,

Krzysztof Kielan
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.