Home » RDBMS Server » Security » privs for creating functions using tables from another schema
privs for creating functions using tables from another schema [message #203612] Wed, 15 November 2006 16:55 Go to next message
swedstar
Messages: 7
Registered: November 2006
Junior Member
Hi all,

I'm having problems creating a function in the following scenario.
I have a user that has 'select' system privileges on certain tables in another users schema. I've created local synonyms for these tables and I can query them without any problems. However, I've been trying to create a function with a select query using the tables from the other schema but I constantly receive compilation errors where it says that the table/view doesn't exist. There are no problems creating this function in the schema where the tables belong but I can't do it from this user. I believe there is an issue with privileges for this user but I'm not sure what the minimum privs are is to make this work.

Many thanks
Erik

Re: privs for creating functions using tables from another schema [message #203639 is a reply to message #203612] Wed, 15 November 2006 22:31 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi
I've been trying to create a function with a select query using the tables from the other schema but I constantly receive compilation errors where it says that the table/view doesn't exist.

you have two option.
1. Use "schema" name before table name in ur function.
2. Or use synonym in function.

sql>conn scott/tiger
sql>grant select on emp to hr;

sql>conn hr/hr
sql>select * from [B]scott.emp[/B];
sql>create synonyn emp_scott for scott.emp;
sql>select * from emp_scott;

sql>create or replace function "name"
...
...
here use 1. synonym or table name with schema. 
like [b] emp_scott or scott.emp [/b]

end "name"



hope this helps.
Mohammad Taj.
Previous Topic: not able to access Oracle Advance Securty
Next Topic: Application Audit issue
Goto Forum:
  


Current Time: Fri Mar 29 03:44:46 CDT 2024