Re: Database link creation error - repost & thanks
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: veeeraman@xxxxxxxxx
- Date: Sun, 15 Mar 2009 21:20:04 -0500
Hello
Just thought, I will summarize the issue, as Ram and I were working
offline to resolve this.
Trace files for these events and processstate dump shows that root cause
of this issue was a logon trigger. Logon trigger explicitly set
current_schema to sysadm for few users. Create database link command from
those users( such as DMUE113R) tries to create database link in sysadm
schema and erroring out with ORA-1031 errors.
Trigger from the trace file:
BEGIN
IF (ora_login_user IN ('XXXXXDO','DMUE113R','RMITXX')) THEN
execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=SYSADM';
END IF;
END;
--
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
Original Message:
On Tue, Mar 10, 2009 at 11:28 AM, Riyaj Shamsudeen <
riyaj.shamsudeen@xxxxxxxxx> wrote:
Ram
I am unable to guess why this would happen. One possible guess is that if
you create that user as first user, does this still happen? My thinking is
that, may be there is only one database link allowed to sysadm in your
environment (somehow). May be, some limitations through password file. Just
a wild guess!
Can you also set these two events, run create database link and send
output please:
alter session set events '1031 trace name errorstack level 15';
alter session set events '10046 trace name context forever, level 12';--
On Tue, Mar 10, 2009 at 6:44 AM, Ram Raman <veeeraman@xxxxxxxxx> wrote:
Hi all,
I am wondering if anyone can guide me here, I am reposting. Thanks for the
help. The database link creation seems to fail with one user id, but
succeeds with other ids, even though the user creation follow the same
steps:
It looks like a very simple thing, but I am not sure what I am missing. I
have modified the user name and the directory name slightly, but other than
it is the same. The link creation seem to work OK with other user names that
I have created like MM, DUMMY, TEST, etc. They all work. I can assure you
the passwd was the same in both cases. It is just that when for the user
DMUE113R (different from the real id I used) I get this error:
/throra10/scripts/DCL/script_location >sqlplus
>
> SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 6 15:09:18 2009
>
> Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
>
> Enter user-name: / as sysdba
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
> Production
> With the Partitioning, OLAP and Data Mining options
>
> SYS@HRDEV> @create_user.sql\
> Enter value for user_name: VV
> old 1: create user &&user_name
> new 1: create user VV
> old 2: identified by &&user_name
> new 2: identified by VV
>
> User created.
>
> SYS@HRDEV> @grant_sys_privs.sql
> old 1: grant create session to &&user_name
> new 1: grant create session to VV
>
> Grant succeeded.
>
> old 1: grant create table to &&user_name
> new 1: grant create table to VV
>
> Grant succeeded.
>
> old 1: grant create procedure to &&user_name
> new 1: grant create procedure to VV
>
> Grant succeeded.
>
> old 1: grant create view to &&user_name
> new 1: grant create view to VV
>
> Grant succeeded.
>
> SYS@HRDEV>
>
> SYS@HRDEV>
> SYS@HRDEV> grant create database link to VV;
>
> Grant succeeded.
>
> SYS@HRDEV> conn VV
> Enter password:
> Connected.
> VV@HRDEV> create database link HRTST
> 2 connect to SYSADM identified by passwd
> 3 using 'HRTST';
>
> Database link created.
>
> VV@HRDEV> exit
> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
> - 64bit Production
> With the Partitioning, OLAP and Data Mining options
> /throra10/scripts/DCL/script_location >sqlplus
>
> SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 6 15:11:19 2009
>
> Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
>
> Enter user-name: / as sysdba
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
> Production
> With the Partitioning, OLAP and Data Mining options
>
> SYS@HRDEV> @create_user.sql
> Enter value for user_name: DMUE113R
> old 1: create user &&user_name
> new 1: create user DMUE113R
> old 2: identified by &&user_name
> new 2: identified by DMUE113R
>
> User created.
>
> SYS@HRDEV> @grant_sys_privs.sql
> old 1: grant create session to &&user_name
> new 1: grant create session to DMUE113R
>
> Grant succeeded.
>
> old 1: grant create table to &&user_name
> new 1: grant create table to DMUE113R
>
> Grant succeeded.
>
> old 1: grant create procedure to &&user_name
> new 1: grant create procedure to DMUE113R
>
> Grant succeeded.
>
> old 1: grant create view to &&user_name
> new 1: grant create view to DMUE113R
>
> Grant succeeded.
>
> SYS@HRDEV> grant create database link to DMUE113R;
>
> Grant succeeded.
>
> SYS@HRDEV> conn DMUE113R
> Enter password:
> Connected.
> DMUE113R@HRDEV> create database link HRTST
> 2 connect to SYSADM identified by passwd
> 3 using 'HRTST';
> create database link HRTST
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
>
> DMUE113R@HRDEV>
>
> Thanks.
>
>
Other related posts:
