| ORA-01031: insufficient privileges when creating trigger/synonym [message #157577] |
Fri, 03 February 2006 07:03  |
horstborscht
Messages: 13
Registered: April 2005
|
Junior Member |
|
|
Hi all,
I am logged on as a user Horst.
Horst has been granted DBA role.
I created a table horsts_table in Horsts schema.
So far everything works fine.
When i try to create a trigger or a synonym for this table,
i get ORA-01031: insufficient privileges.
If i log on as user system i can create the trigger/synonym.
like:
CREATE PUBLIC SYNONYM HORSTS_TABLE FOR HORST.HORSTS_TABLE;
or:
CREATE OR REPLACE TRIGGER HORST.HO_INSERT AFTER INSERT
ON HORST.HORSTS_TABLE
FOR EACH ROW
BEGIN
...
END;
/
But i've got the impression that this doesn't seem to be the right way to do it...
Shouldn't Horst be able to create a trigger/synonym for a table he created in his own schema?
What am i missing here?
Thanks in advance
Horst Borscht
|
|
|
|
| Re: ORA-01031: insufficient privileges when creating trigger/synonym [message #157616 is a reply to message #157577] |
Fri, 03 February 2006 09:41  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Yes, you are correct in that you should not be doing things like this as system or sys. But in order to tell what is going wrong in what you are trying to do, can you post your sqlplus session showing exactly what it is you did and the errors that resulted? Like this:
MYDBA > create table mytest(a number, b number);
Table created.
MYDBA > create or replace trigger bi_mytest before insert on mytest for each row
2 begin
3 :new.b := :new.a;
4 end;
5 /
Trigger created.
MYDBA > insert into mytest (a) values (1);
1 row created.
MYDBA > select * from mytest;
A B
---------- ----------
1 1
|
|
|
|
| Re: ORA-01031: insufficient privileges when creating trigger/synonym [message #157839 is a reply to message #157577] |
Mon, 06 February 2006 09:56 |
horstborscht
Messages: 13
Registered: April 2005
|
Junior Member |
|
|
Hi smartin,
Thanks for your answer,
This is my sqlplus session:
---------------------------------------------------
horst@testdb> Create table horsts_table(
2 F1 NUMBER (8,0) NOT NULL,
3 F2 VARCHAR2 (10) NOT NULL,
4 CONSTRAINT PK_HORSTS_TABLE PRIMARY KEY (F1) USING INDEX TABLESPACE TS_INDEX)
5 TABLESPACE TS_DATA
6 STORAGE
7 (
8 INITIAL 3 M
9 NEXT 3 M
10 MINEXTENTS 1
11 MAXEXTENTS UNLIMITED
12 PCTINCREASE 0
13 BUFFER_POOL DEFAULT
14 )
15 NOCACHE
16 NOPARALLEL
17 LOGGING
18 /
Table created.
horst@testdb> insert into horsts_table(f1,f2)values(1,'hallo');
1 row created.
horst@testdb> select * from horsts_table;
F1 F2
---------- ----------
1 hallo
horst@testdb> Create horsts_table_old(
2 F1 NUMBER (8,0) NOT NULL,
3 F2 VARCHAR2 (10) NOT NULL,
4 CONSTRAINT PK_horsts_table_old PRIMARY KEY (F1) USING INDEX TABLESPACE TS_INDEX)
5 TABLESPACE TS_DATA
6 STORAGE
7 (
8 INITIAL 3 M
9 NEXT 3 M
10 MINEXTENTS 1
11 MAXEXTENTS UNLIMITED
12 PCTINCREASE 0
13 BUFFER_POOL DEFAULT
14 )
15 NOCACHE
16 NOPARALLEL
17 LOGGING
18 /
Table created.
horst@testdb> CREATE OR REPLACE TRIGGER horsts_table_i AFTER INSERT
2 ON horsts_table
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO horsts_table_old (f1,f2)
6 VALUES (:NEW.f1,:new.f2);
7 END;
8 /
*
ERROR at line 1:
ORA-01031: insufficient privileges
horst@testdb> create public synonym horsts_table for horst.horsts_table;
*
ERROR at line 1:
ORA-01031: insufficient privileges
--------------------------------------------------------
Any Idea about where/what i could check to find out what's wrong?
Tanks again
Horst
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
