| Executing Own Table/s intead of owners [message #39538] |
Mon, 22 July 2002 21:13  |
emailarif
Messages: 5
Registered: July 2002
|
Junior Member |
|
|
Hi Everybody,
I have a peculiar problem.
I hope somebody helps me out.
I have an Owner "O" and a User "U"
The Owner "O" has a procedure which computes
some value and insets it into table "T"
Now this table "T" is with Owner "O" and
also a separate table with same name "T"
with User "U"
The User "U" has been given Execution rights
for this procedure.
When User "U" executes it, The table "T"
of Owner "O" gets executed instead of table "T"
of User "U"
How do I get User "U" access table "T" of its own.
Its dead Urgent...
Somebody kindly help me out....
Thanks in advance...
|
|
|
|
| Re: Executing Own Table/s intead of owners [message #39558 is a reply to message #39538] |
Tue, 23 July 2002 08:22  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Please refer to the documentation under 'invoker rights'.
The default is 'definer', which, as you have noticed, means that the procedure executes with the privileges of the owner of the schema in which the procedure resides, and that table names in that schema will be used.
The alternative is 'current_user'. Object names will be resolved in the schema of the current user. This is the option that will allow user 'U' to execute a procedure owned by 'O', but insert into the table 'T' in the 'U' schema.
create or replace procedure my_procedure
(p_parameter1 in varchar2)
authid current_user
is
... |
|
|
|
|
|
| Re: SELECTING from Own Table/s intead of owners [message #39570 is a reply to message #39538] |
Tue, 23 July 2002 21:50 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Yes, that's what "Object names will be resolved in the schema of the current user" means. As long as the procedure is defined with "authid current_user", any references to table T (select, insert, update, delete, DDL) will resolve to the table U.T - if U is executing the procedure that is in the O schema.
|
|
|
|
| "Last Doubt Please" SELECTING from Own Table/s intead of owners [message #39576 is a reply to message #39570] |
Wed, 24 July 2002 00:52 |
emailarif
Messages: 5
Registered: July 2002
|
Junior Member |
|
|
Sorry to trouble you once again
But I have one doubt....
I am working in Oracle version 7.
Will this still work ?
Also "authid current_user" in
Create or Replace Pro....
What does that mean exactly...
Since It did not work in my case...
My case has Owner ID as "CEATDB" and
User ID as "ARIF".
So how do I go about it....
Kindly explain and illustrate....
Thanks ....
Regds.
|
|
|
|
| Re: "Last Doubt Please" SELECTING from Own Table/s intead of owners [message #39582 is a reply to message #39570] |
Wed, 24 July 2002 09:43 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
No, that syntax is not valid in Oracle 7. Posters to this board need to identify which version they are on - we assume 8i or 9i unless told otherwise.
Your only options in v7 are to: 1) also put the procedure in the schema with the table you want to use, or 2) pass the target schema into the procedure and use DBMS_SQL to handle the insert (and the user owning the procedure would need privileges to the other schema's object).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
