| Privilege problem [message #422223] |
Tue, 15 September 2009 02:58  |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
Respected Sir/Madam,
I am have created a package in my schema. While tried to compile the package I am getting
table or view does not exist compilation error.
I informed the dba that the privilege on the tables should be granted explicitely not as a role.
The dba informed that I have the full privilege on the tables I am using in the package
I am seeing the tables in toad (but under different schema)
To resolve the issue I created the required tables in my own schema.
What I want to inform the dba is..
He should create a synonym for the table and grant privilege to me to execute through package.
Please advice, It would be helpful if someone clear my doubt for the above situation.
Thanks
|
|
|
|
|
|
| Re: Privilege problem [message #422232 is a reply to message #422225] |
Tue, 15 September 2009 03:44  |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
Sir,
Thanks for the reply
1/ Get direct privilege on the table (check all_tab_privs)
-This is what the message I conveyed to the dba.He says that he has given full privilege to the tables and
finds no reason why I cant compile the package.But when I created the tables in my schema compilation error was gone.
When run the below sql in my schema I got:
select * from all_tab_privs where table_name='TRAN_FLOW'
GRANTOR GRANTEE SCHEMA_NAME TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY
A0001 DB00S1 A0001 TRAN_FLOW INSERT NO NO
A0001 DB00S1 A0001 TRAN_FLOW UPDATE NO NO
A0001 DB00S1 A0001 TRAN_FLOW DELETE NO NO
A0001 DB00S1 A0001 TRAN_FLOW SELECT NO NO
2/ Create a synonym on the table if you don't name the schema (check all_synonyms)
- I am not seeing any syonyms being created.So should I ask the dba to create a syonym for the tables and grant privilege directly instead of giving as a role?
Thanks!!
Please advice!
[Updated on: Tue, 15 September 2009 04:20] Report message to a moderator |
|
|
|
| Re: Privilege problem [message #422235 is a reply to message #422232] |
Tue, 15 September 2009 04:22 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:Quote:2/ Create a synonym on the table if you don't name the schema (check all_synonyms)
- I am not seeing any syonyms being created
What Michel is saying is that if you are using a table in another schema, then when you reference that table in your code, you must either refer to it explicitly as <schema>.<table>. or you must have a public or private synonym that points to that table.
It is up to you (or possibly your DBA) to create that synonym.
|
|
|
|
|
|
| Re: Privilege problem [message #422246 is a reply to message #422239] |
Tue, 15 September 2009 04:41 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
I have created those tables in my schema and there is no compilation error.The package got compiled.
And now when I points to the schema A0001 (like this schemaname.tablename) I am getting compilation error!
Thanks
|
|
|
|
|
|
| Re: Privilege problem [message #422280 is a reply to message #422249] |
Tue, 15 September 2009 06:12 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
I fixed it creating a table in my schema.
But thats what not I expected.
SQL> show user
USER is "P8700"
SQL> create or replace package test1 is
2 procedure test;
3 end test1;
4 /
Package created.
SQL> create or replace package body test1
2 as
3 procedure test as
4 cnt number;
5 begin
6 select count(*) into cnt from A0001.TRAN_FLOW ;
7 end ;
8 end test1;
9 /
Warning: Package Body created with compilation errors.
SQL> show errors
Errors for PACKAGE BODY TEST1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PL/SQL: SQL Statement ignored
6/40 PL/SQL: ORA-00942: table or view does not exist
SQL>
I informed the DBA that the privilege should be given directly on the tables not as a role and also to create public syonoym
Are you agree? Or is there anything that I can do to convince the dba who says that he has given me all the priv on the tables?
Many thanks
|
|
|
|
|
|
| Re: Privilege problem [message #422284 is a reply to message #422282] |
Tue, 15 September 2009 06:21 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
Once again pasting the result here that I post in my previous message
select * from all_tab_privs where table_name='TRAN_FLOW'
GRANTOR GRANTEE SCHEMA_NAME TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY
A0001 DB00S1 A0001 TRAN_FLOW INSERT NO NO
A0001 DB00S1 A0001 TRAN_FLOW UPDATE NO NO
A0001 DB00S1 A0001 TRAN_FLOW DELETE NO NO
A0001 DB00S1 A0001 TRAN_FLOW SELECT NO NO
[Updated on: Tue, 15 September 2009 06:21] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Privilege problem [message #422310 is a reply to message #422301] |
Tue, 15 September 2009 08:14 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
You are NOT connected with P8700 when you executed this query.
--I executed the sql from my schema.I dont have privilege to login using the other one mentioned.
Your DBA is right, as you used DB00S1, he says this one has the privileges.
SQL> show user
USER is "P8700"
SQL> create or replace package test1 is
2 procedure test;
3 end test1;
4 /
Package created.
SQL> create or replace package body test1
2 as
3 procedure test as
4 cnt number;
5 begin
6 select count(*) into cnt from A0001.TRAN_FLOW ;
7 end ;
8 end test1;
9 /
Warning: Package Body created with compilation errors.
SQL> show errors
Errors for PACKAGE BODY TEST1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PL/SQL: SQL Statement ignored
6/40 PL/SQL: ORA-00942: table or view does not exist
SQL> select count(*) from A0001.TRAN_FLOW ;
COUNT(*)
----------
0
Thanks
[Updated on: Tue, 15 September 2009 08:16] Report message to a moderator |
|
|
|
| Re: Privilege problem [message #422311 is a reply to message #422310] |
Tue, 15 September 2009 08:17 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:SQL> show user
USER is "P8700"
SQL> create or replace package test1 is
2 procedure test;
3 end test1;
4 /
If you can't connect as P8700, could you explain to us how you managed to produce the results shown above, and how you managed to create the package in the P8700 schema?
|
|
|
|
|
|
| Re: Privilege problem [message #422317 is a reply to message #422313] |
Tue, 15 September 2009 08:31 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Good grief - your ability to complicate a very simple situation leaves me speechless.
Just do this:
1) connect as P8700
2) Run select * from all_tab_privs where table_name='TRAN_FLOW'
3) Cut and paste the results back here.
|
|
|
|
| Re: Privilege problem [message #422320 is a reply to message #422317] |
Tue, 15 September 2009 08:39 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
OOOOuf!!!!!!  I am tired pasting the results again and again
GRANTOR GRANTEE SCHEMA_NAME TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY
A0001 DB00S1 A0001 TRAN_FLOW INSERT NO NO
A0001 DB00S1 A0001 TRAN_FLOW UPDATE NO NO
A0001 DB00S1 A0001 TRAN_FLOW DELETE NO NO
A0001 DB00S1 A0001 TRAN_FLOW SELECT NO NO
Now do you want me to do it from sql session as well? I can.No problem.Because in sql plus also I am seeing the same output.
Sql plus session:
SQL> COLUMN GRANTOR FORMAT A10
SQL> COLUMN GRANTEE FORMAT A10
SQL> COLUMN TABLE_SCHEMA A10
SP2-0158: unknown COLUMN option "A10"
SQL> COLUMN TABLE_SCHEMA FORMAT A12
SQL> COLUMN TABLE_NAME FORMAT A12
SQL> COLUMN PRIVILEGE FORMAT A12
SQL> COLUMN GRANTABLE FORMAT A10
SQL> select * from all_tab_privs where table_name='TRAN_FLOW';
GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRANTABLE HIE
---------- ---------- ------------ ------------ ------------ ---------- ---
A0001 DB00S1 A0001 TRAN_FLOW INSERT NO NO
A0001 DB00S1 A0001 TRAN_FLOW UPDATE NO NO
A0001 DB00S1 A0001 TRAN_FLOW DELETE NO NO
A0001 DB00S1 A0001 TRAN_FLOW SELECT NO NO
SQL> SHOW USER
USER is "P548KB"
SQL> select * from all_tab_privs where table_name='TRAN_FLOW';
GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRANTABLE HIE
---------- ---------- ------------ ------------ ------------ ---------- ---
A0001 DB00S1 A0001 TRAN_FLOW INSERT NO NO
A0001 DB00S1 A0001 TRAN_FLOW UPDATE NO NO
A0001 DB00S1 A0001 TRAN_FLOW DELETE NO NO
A0001 DB00S1 A0001 TRAN_FLOW SELECT NO NO
[Updated on: Tue, 15 September 2009 08:49] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Privilege problem [message #422328 is a reply to message #422323] |
Tue, 15 September 2009 08:54 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
Joy_divison Sir,
Would you PLEASE mind reading what I have mentioned at the very begining?
I told that privilege has not been given directly to role not directly.Thats what I wrote and thats the message I conveyed to the DBA..
And thats what I wanted to tell.
|
|
|
|
|
|
| Re: Privilege problem [message #422331 is a reply to message #422320] |
Tue, 15 September 2009 08:56 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Those results show that the privileges on table A001.Trans_Flow have not been granted directly to the user P8700 - thus if your user can see the table from SQLPlus, the privileges must have been granted to a Role.
If you want to access the table from within a stored procedure, you need to have the privileges granted directly to your user.
|
|
|
|
| Re: Privilege problem [message #422332 is a reply to message #422331] |
Tue, 15 September 2009 08:58 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
Thanks you Sir..
I mentioned the same at my earlier message and trying to convince the dba that privilege on the tables has not been given directly but as a role.
|
|
|
|
|
|
| Re: Privilege problem [message #422337 is a reply to message #422334] |
Tue, 15 September 2009 09:11 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
In my first post I mentioned
"I informed the dba that the privilege on the tables should be granted explicitely not as a role.
The dba informed that I have the full privilege on the tables I am using in the package
"
Also @ message #422280
[Updated on: Tue, 15 September 2009 09:13] Report message to a moderator |
|
|
|
| Re: Privilege problem [message #422342 is a reply to message #422332] |
Tue, 15 September 2009 09:24 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:I mentioned the same at my earlier message and trying to convince the dba that privilege on the tables has not been given directly but as a role.
Your earlier mesage now says:Quote:- I am not seeing any syonyms being created.So should I ask the dba to create a syonym for the tables and grant privilege directly instead of giving as a role? but this post has been updated, and the original line just said:Quote:- I am not seeing any syonyms being created - we can tell this because the line was quoted by other people.
Still, we've got a solution, you can tell your DBA to grant the privs directly, and we can all go home.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
