ALTER SESSION SET CURRENT_SCHEMA Bug?

[CHUMPY]

I am trying to change the current schema of users using the

ALTER SESSION SET CURRENT_SCHEMA =

This works when executed directly through SQL Plus, however when it is either called from a procedure or trigger it doesn't work.... using the code below

execute immediate
'alter session set current_schema =<a schema>';

Is this a bug or am I doing something wrong?

Thanks

Chumpy

 

[sem]

To use non-dml commands from stored procedures you should be granted to do it EXPLICITLY, not via role. Regards, Dima

 

[CHUMPY]

Thanks for the response though I am unsure of what privelages need to be granted? I have set up a test user granted all the system privelages to it and still have the problem?

Any other advice would be much appreciated?

Thanks

Chumpy

 

[sem]

Ok, I repeat it. The user should be granted ALTER SESSION privilege EXPLICITLY, not by granting it DBA or any other ROLE. This is not a bug but rather well-documented feature.
If you're interested in learning why, read about resolving names, early binding and roles. Regards, Dima

 

[CHUMPY]

Thanks for the help..... but still getting the same problem!

I have created a user not assigned any roles to it, granted create session and alter session privelages and the some object privelages.. then done as before and still get the same result calling the procedure which completes but doesn't change the schema but will from the SQL* plus window?

Chumpy

 

[sem]

How do you know that the schema was not changed? All the objects, referenced by stored procedures are resolved during COMPILE TIME, thus can not be influenced by any changes in RUNTIME environment. Regards, Dima

 

[CHUMPY]

The Schema has not been changed, I can reference the packages tables etc by prefixing them with the schema name, then run a stored procedure to execute the alter current schema and it doesn't change the current schema.... however when I issue the command direct from SQL Plus it works....

I have also tried compiling the stored procedure to change the schema with and without invokers rights..... but still can't get it to work worried about the alter session privelage.

I can't find any other references to this problem and am about to test it on some other machines?

Any more ideas would be gratefully appreciated

 

[sem]

I'm sure that the schema WAS CHANGED. Just call your procedure from sql*plus and then try to select from some table, that only one user has access to, to see the results. Changing current schema affects not-fully qualified objects, so if you use schema prefixes, the behaviour remains the same REGARDLESS ON CURRENT SCHEMA. I suppose you do not distinguish current user (the user you was loged on) from current schema (default prefix for unqualified objects).

If you created your procedure with AUTHID DEFINER or omited this clause at all, all the names were RESOLVED ACCORDING TO DEFINERS RIGHTS DURING COMPILE TIME, so they are not re-resolved on invocation and they do not depend on session parameters. Using AUTHID CURRENT_USER will force re-resolving names according to current user (invoker) rights.
Regards, Dima

 

[CHUMPY]

Hi,

I have managed to get it working so this is the soloution I have found for anybody who stumbles across this post later.

I found this paragraph..

&quot;Also, I found that if user b is a dba and he created a logon trigger which uses
&quot;alter session set current_schema=b&quot; <-- his own schema. Then This will fail
all users. Nobody can describe his table.&quot;

So creating the procdure/trigger in the same schema if the user is a DBA was the cause of this problem...... Not very well documented at all really.

Thanks for the help.

Gareth.

 

[sem]

Where did you find it?
In fact it doesn't depend on whether the table belongs to DBA or not, but rather on whether YOU have permissions to access it. Changing schema doesn't change any privs. Regards, Dima