SqlDbx Forum

SqlDbx

You are not logged in.

#1 2014-05-05 11:16:11

jlab68
Member

Connection issue with logon trigger

I'm currently evaluating SqlDBx (Personal Edition) and have an connection issue with an oracle 11g DB.

The environment is set up as follows:
A user TEST_USER accesses tables defined in the schema TEST_USER_DBA with privileges granted to TEST_USER.
A logon trigger sets the default current schema for TEST_USER to TEST_USER_DBA:

create or replace trigger TEST_USER.set_schema_on_logon after logon on TEST_USER.schema
declare
begin
  execute immediate('alter session set current_schema=TEST_USER_DBA');
end;

The issue: it looks like the logon trigger is ignored:
- When I connect as TEST_USER I get the ORA-00942 and ORA-24374 errors.
- Statements like "select * from test_user.<table>" succeed but not "select * from <table>".

Offline

#2 2014-05-05 14:51:55

sqldbxhelp
Administrator

Re: Connection issue with logon trigger

It's not ignored. This is what happens.
Trigger fired and sets current_schema to TEST_USER_DBA
then SqlDbx sets current schema to TEST_USER if schema in login dialog is empty.
If you specify on login screen schema TEST_USER_DBA than it will work

Offline

#3 2014-05-06 03:08:26

jlab68
Member

Re: Connection issue with logon trigger

If you specify on login screen schema TEST_USER_DBA than it will work

Thanks for your reply. I did specify the schema in the login screen. It doesn't work.

Offline

#4 2014-05-06 09:28:51

sqldbxhelp
Administrator

Re: Connection issue with logon trigger

On login dialog you set Schema to TEST_USER_DBA and User to TEST_USER, right?
What current Schema you see in combo box on top?

Offline

#5 2014-05-22 06:33:05

jlab68
Member

Re: Connection issue with logon trigger

The issue is fixed. There was a permission issue and the user was not able to read the views dba_all_tables and dba_synonyms.

Offline

Board footer

Powered by FluxBB