After I issue a set role command i can see the new objects on the left side of the schema browser but cannot select data from the new objects on the right side of the schema browser. Set attached doc file for details
set role issue.docx (354 KB)
it works, but since Toad can execute queries in threads (which means separate sessions), then the session that your SB-Tables-Data grid could be different from your Editor session.
The easiest way to get around this is to go into options -> Oracle -> Transations, and uncheck “execute queries in threads”. If you run your “SET ROLE” command with F9 rather than F5, this will be enough. If you want to continue running your SET ROLE commands with F5 (as you did in your screen shot), then you should also make sure that “Execute scripts in Toad session” is checked.
If you don’t want to change these settings, look up SET ROLE in the help. You can add entries to your Toad.ini file where you can define roles that you want to set whenever you make specific connections in Toad, and then Toad will make sure to run the SET ROLE command in any of these background sessions that it creates.
thanks for the reply,
Questions/Statements:
-
why does VB_ASSESSMENT show up on the left side of the schema browser even though you cannot select data from it and it is not a view that you have access to per ALL_VIEWS and current role in session_roles is SDS_OBJ_ACCESS and not SDS_ALL_OBJ_ACCESS.
-
It seems that the only way I can get this to work properly is to do the following:
- I have "execute scripts in a Toad session" checked - I am not sure what this does exactly - uncheck "execute queries in threads" - I am not sure what this does exactly - execute the set role command using the 3rd icon "execute as script", not using the down arrow. i am assuming this is F5 - open a new schema browser - I can see VB_ASSESSMENT and it's data -
If I have “execute queries in threads” unchecked, execute set role using F9, open schema browser VB_ASSESSMENT does not show
- if execute "Select * from session_roles" in the editor window it returns "SDS_ALL_OBJ_ACCESS" as it should - if I execute the same set role using the 3rd icon again - if execute "Select * from session_roles" in the editor window it returns "SDS_ALL_OBJ_ACCESS" as it should - open a new schema browser - I can see VB_ASSESSMENT and it's data -
Not sure how to get F9 to work properly
-
not sure what is going on here:
- The current setup: - "execute queries in threads" is unchecked - role is "SDS_ALL_OBJ_ACCESS" - I can see the data in VB_ASSESSMENT - in editor window: - execute "set role SDS_OBJ_ACCESS" using 1st icon, not the down arrow - execute "Select * from session_roles" in the editor window using 1st icon it returns "SDS_OBJ_ACCESS" as it should - open a new schema browser - it shows VB_ASSESSMENT and it's data and it should not since the role should be. SYS.SESSION_ROLES still shows SDS_ALL_OBJ_ACCESS.
-
We select the list of views from user/all/dba_objects. Sometimes this list differs from user/all/dba_views.
-
“Execute scripts in Toad session” means that when you run a script in the Editor (or when scripts are run in other places in Toad), the script is run in the same session as the “rest” of Toad. When that option is unchecked, the scripts are in a separate session.
“execute queries in threads”, when checked, means that you don’t have to wait for queries in the editor to finish before you can go on to do other stuff in Toad. To accomplish this, we have to run that query in a separate session.
In the Editor, F5 = Run as script. F9 = Execute/Compile Statement.
3 - 5) Hmm, well, with the options set that way, the SB and Editor should be running in the same session, and the SET ROLE command should affect both. Maybe a restart of Toad (or at least the editor and SB) after the options are set will do it. I would do things in this order: Make sure options are right. Open Editor and run SET ROLE. Open Schema Browser.
To verify that your editor and SB are running in the same session, you could create this view:
create or replace view
session_view as
select sys_context(‘userenv’,‘sid’) sid,
sys_context(‘userenv’,‘sessionid’) Session_ID
from dual;
then select from it in both editor and Schema Browser - they will only return the same result if they are running in the same session.
Ooops. In #1 I said “we select the list of roles…” I meant views, not roles.
I am fine on what I have to do to get “Set Role” to work properly(I must have “Execute Queries in Threads” unchecked) but I still do not understand what happens in schema browser when “Execute Queries in Threads” is checked and I click the 3rd icon(execute as script). On the Left side VB_ASSESSMENT is being displayed even though the user does have access to it and I thought the ALL views displayed objects that the user has access to. Is this a bug in Toad?
In the editor:
-
L2EDFIAPP.v_session_view displays SID = 1164 and session_id = 11014403
-
L2EDFIAPP.v_session_roles dislays SDS_ALL_OBJ_ACCESS
-
session_roles dislays SDS_ALL_OBJ_ACCESS
In schema Browser:
-
session_view displays SID = 1554 and session_id = 11032461
-
left side displays all the appropriates views and VB_ASSESSMENT displays
- Using session browser I see the select is from all_objects where object_type = ‘VIEW’ and owner = ‘L2EDFIAPP’
-
if I click on VB_ASSESSMENT on the left side and then click on the data tab I get the error “table or view does not exit.”
-
L2EDFIAPP.v_session_roles dislays SDS_OBJ_ACCESS
-
if I then change the user to SYS:
-
display session_roles it displays SDS_OBJ_ACCESS
-
display ALL_VIEWS for L2EDFIAPP is VB_ASSESSMENT does not display
-
When "Execute Queries in Threads" is checked, the SB-Tables-RHS-Data grid uses its own session, so the set role won't affect it. (This affects the Editor's compile/execute button as well). When this option is unchecked, the grid uses the same session that we use for most things in Toad, like loading the list of views in the SB LHS.
When "Execute scripts in Toad session" is checked, then when you run as a script in the Editor (the 3rd button), the session used to execute that script is the same session that we used most things for in Toad, like loading the list of views in the SB LHS.
So, to make sure everything is in the same session, you want "Execute queries in threads" unchecked and "Execute scripts in Toad session" checked.
On the Left side VB_ASSESSMENT is being displayed even though the user does have access to it and I thought the ALL views displayed objects that the user has access to. Is this a bug in Toad?
No, I don't think so. We're just showing what Oracle gives us. Maybe it's an Oracle bug that the view shows in ALL_OBJECTS when it shouldn't, but I'm not sure about that.