EugeneGardner
asked on
Dynamic SQL using DBMS_SQL
If I get to SQL*Plus and type:
SET ROLE ALL EXCEPT R1;
SELECT * FROM SESSION_ROLES;
I see I have all except R1. But if I execute the following:
create or replace procedure ds as
cur integer;
tmp integer;
begin
cur := dbms_sql.open_cursor;
dbms_sql.parse (cur,'set role all except R1',dbms_sql.v7);
tmp := dbms_sql.execute(cur);
dbms_sql.close_cursor(cur)
end;
I get no error messages yet the R1 role is still in my privilege domain.
What did I miss ?
SET ROLE ALL EXCEPT R1;
SELECT * FROM SESSION_ROLES;
I see I have all except R1. But if I execute the following:
create or replace procedure ds as
cur integer;
tmp integer;
begin
cur := dbms_sql.open_cursor;
dbms_sql.parse (cur,'set role all except R1',dbms_sql.v7);
tmp := dbms_sql.execute(cur);
dbms_sql.close_cursor(cur)
end;
I get no error messages yet the R1 role is still in my privilege domain.
What did I miss ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
EugeneGardner :
This is a bug in Oracle 8.0.6 and below and Oracle 8.1.5 and below. I don't think thet have a fix for this out yet.
This procedure should give a ORA-6565 error. The SET ROLE commad is not a valid command from within a procedure.
This is a bug in Oracle 8.0.6 and below and Oracle 8.1.5 and below. I don't think thet have a fix for this out yet.
This procedure should give a ORA-6565 error. The SET ROLE commad is not a valid command from within a procedure.
What is your Oracle version and platform? Are you sure you are not getting any errors when running your stored procedure? Since roles in stored procedures are ignored, statement SET ROLE (same as DBMS_SESSION.SET_ROLE) can not be used in stored procedures. You had to get ORA-06565: cannot execute SET ROLE from within stored procedure.
SQL> exec ds
begin ds; end;
*
ERROR at line 1:
ORA-06565: cannot execute SET ROLE from within stored procedure
ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "SYSTEM.DS", line 7
ORA-06512: at line 1
Bottom line. You can not set roles from within SP. All stored procedure can do is to check if role is enabled (via DBMS_SESSION.IS_ROLE_ENABL
Solomon Yakobson.
SQL> exec ds
begin ds; end;
*
ERROR at line 1:
ORA-06565: cannot execute SET ROLE from within stored procedure
ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "SYSTEM.DS", line 7
ORA-06512: at line 1
Bottom line. You can not set roles from within SP. All stored procedure can do is to check if role is enabled (via DBMS_SESSION.IS_ROLE_ENABL
Solomon Yakobson.
ASKER
Thanks for the replies. I'm running Oracle V7.1 (soon to go to V7.3) on VMS. I definately do not get
ORA-06565: cannot execute SET ROLE from within stored procedure
or any other message.
The problem I have is that I have roles for each application and the Test Team need to hold <aplication>_USER role and no other to perform a valid QA test. They can not just switch roles on and off as some applications are accessed via Forms and there is no trap door for them to switch all other roles off. I guess I'll just have to create <application>_TEST accounts and ask them to use those, but that's not ideal as several testers could be testing the same application at the same time and may tread on each others' toes.
Eugene.
ORA-06565: cannot execute SET ROLE from within stored procedure
or any other message.
The problem I have is that I have roles for each application and the Test Team need to hold <aplication>_USER role and no other to perform a valid QA test. They can not just switch roles on and off as some applications are accessed via Forms and there is no trap door for them to switch all other roles off. I guess I'll just have to create <application>_TEST accounts and ask them to use those, but that's not ideal as several testers could be testing the same application at the same time and may tread on each others' toes.
Eugene.
ASKER
Oracle's Note:69483.1 says it all:
Overview
--------
This article explains why in Oracle 8.0 you cannot use dbms_session.set_role
from within a procedure to change a user's role. It is meant for PL/SQL
developers who are attempting to implement a set of procedures to control user privileges.
Information
-----------
In Oracle 8.0, you cannot set roles in a stored procedure or trigger
using either the dbms_session.set_role, or by issuing the set role command
via the dbms_sql package.
The following error is defined for situations such as this:
ORA-06565 : cannot execute <name> from within stored procedure
With new features of Oracle8i, it is now possible to clarify the reason
for this restriction. When a procedure is executed, it assumes the schema
identity of the user that created the procedure and the "current" user is
switched to the procedure owner. (This switch takes place even when the owner
and invoking user are the same in order to maintain consistency.) Therefore,
a 'set role' issued within the procedure sets the role for the procedure owner, not the invoker.
Hence, if the operation was allowed, it would have no effect since procedures
running under the schema of the owner run with no roles enabled. When the
procedure exits, the "current" user is switched back to the invoking user.
Therefore, the set role would be lost anyway.
An anonymous block is executed with roles enabled. The current user remains as the invoking user - i.e. no switching takes place - so set role is both allowed and remains active after the execution of the block completes.
With Oracle8i, it is possible to specify whether a stored procedure executes with the identity of the procedure owner ("definer") or the identity of the user executing the procedure ("invoker"). If a procedure is run as the definer, you still receive the same error when attempting to set a role.
However, if a procedure is run as the invoker, set role succeeds and the
role is set after the procedure has completed. When a procedure is run
with "invoker's rights", roles are not disabled and role operations are
permitted the same way they are in anonymous blocks.
Overview
--------
This article explains why in Oracle 8.0 you cannot use dbms_session.set_role
from within a procedure to change a user's role. It is meant for PL/SQL
developers who are attempting to implement a set of procedures to control user privileges.
Information
-----------
In Oracle 8.0, you cannot set roles in a stored procedure or trigger
using either the dbms_session.set_role, or by issuing the set role command
via the dbms_sql package.
The following error is defined for situations such as this:
ORA-06565 : cannot execute <name> from within stored procedure
With new features of Oracle8i, it is now possible to clarify the reason
for this restriction. When a procedure is executed, it assumes the schema
identity of the user that created the procedure and the "current" user is
switched to the procedure owner. (This switch takes place even when the owner
and invoking user are the same in order to maintain consistency.) Therefore,
a 'set role' issued within the procedure sets the role for the procedure owner, not the invoker.
Hence, if the operation was allowed, it would have no effect since procedures
running under the schema of the owner run with no roles enabled. When the
procedure exits, the "current" user is switched back to the invoking user.
Therefore, the set role would be lost anyway.
An anonymous block is executed with roles enabled. The current user remains as the invoking user - i.e. no switching takes place - so set role is both allowed and remains active after the execution of the block completes.
With Oracle8i, it is possible to specify whether a stored procedure executes with the identity of the procedure owner ("definer") or the identity of the user executing the procedure ("invoker"). If a procedure is run as the definer, you still receive the same error when attempting to set a role.
However, if a procedure is run as the invoker, set role succeeds and the
role is set after the procedure has completed. When a procedure is run
with "invoker's rights", roles are not disabled and role operations are
permitted the same way they are in anonymous blocks.
When a stored procedure is executed, it is executed under the privileges of the owner of the procedure without enabling any roles. Therefore, a select from the SESSION_ROLES view will return no rows if this query is executed within a procedure. Depending on the goal of the procedure, you may be able to select from DBA_ROLES/USER_ROLES, or possibly use the procedure IS_ROLE_ENABLED within the DBMS_SESSION package.
Regards
rwarsh