Home » SQL & PL/SQL » SQL & PL/SQL » Privilege problem (oracle 9.2.0.3)
Privilege problem [message #422223] Tue, 15 September 2009 02:58 Go to next message
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 #422225 is a reply to message #422223] Tue, 15 September 2009 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is nothing to do but:
1/ Get direct privilege on the table (check all_tab_privs)
2/ Create a synonym on the table if you don't name the schema (check all_synonyms)

Regards
Michel
Re: Privilege problem [message #422232 is a reply to message #422225] Tue, 15 September 2009 03:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #422239 is a reply to message #422232] Tue, 15 September 2009 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not seeing any syonyms being created.

So either create one or precise the schema in your package.

Regards
Michel
Re: Privilege problem [message #422246 is a reply to message #422239] Tue, 15 September 2009 04:41 Go to previous messageGo to next message
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 #422249 is a reply to message #422246] Tue, 15 September 2009 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am getting compilation error!

So fix it, you are the only one to know the code and the error.

Regards
Michel
Re: Privilege problem [message #422280 is a reply to message #422249] Tue, 15 September 2009 06:12 Go to previous messageGo to next message
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 #422282 is a reply to message #422280] Tue, 15 September 2009 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Connect as P8700 and query all_tab_privs against A0001.TRAN_FLOW.

Regards
Michel
Re: Privilege problem [message #422284 is a reply to message #422282] Tue, 15 September 2009 06:21 Go to previous messageGo to next message
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 #422286 is a reply to message #422284] Tue, 15 September 2009 06:35 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I think only DB00S1 has been granted the 'Insert,Update,Delete and Select' privilege on this table and not P8700

[Updated on: Tue, 15 September 2009 06:41]

Report message to a moderator

Re: Privilege problem [message #422301 is a reply to message #422284] Tue, 15 September 2009 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are NOT connected with P8700 when you executed this query.
YOU MUST DO IT IF YOU WANT TO EFFICIENTLY TALK WITH YOUR DBA.
Your DBA is right, as you used DB00S1, he says this one has the privileges.

Regards
Michel


Re: Privilege problem [message #422310 is a reply to message #422301] Tue, 15 September 2009 08:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #422313 is a reply to message #422311] Tue, 15 September 2009 08:20 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thats what I told sir..I could connect as p8700 and package I have created is using p8700

" You are NOT connected with P8700 when you executed this query.
"
- I can not login using other than p8700

Thanks

[Updated on: Tue, 15 September 2009 08:28]

Report message to a moderator

Re: Privilege problem [message #422317 is a reply to message #422313] Tue, 15 September 2009 08:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
OOOOuf!!!!!! Smile 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 #422323 is a reply to message #422320] Tue, 15 September 2009 08:43 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
prachij593 wrote on Tue, 15 September 2009 09:39
OOOOuf!!!!!! Smile I am tired pasting the results again and again


Well, maybe if you read up on privileges you would know how they work. Please show me where in your output does it show that schema P8700 has privileges on A0001.TRANS_FLOW? It doesn't. It is given to a role and that's why you can select from it at a SQL prompt. You really just do not get it. Would you mind reading the concepts manual? Yeesh!
Re: Privilege problem [message #422327 is a reply to message #422320] Tue, 15 September 2009 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SQL> SHOW USER
USER is "P548KB"

And we asked many times to connect as P8700.
If you did it the first, you would have to post only once.

Regards
Michel
Re: Privilege problem [message #422328 is a reply to message #422323] Tue, 15 September 2009 08:54 Go to previous messageGo to next message
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 #422330 is a reply to message #422327] Tue, 15 September 2009 08:55 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
@Michel Sir,
The user is masked.And while pasting this time.I forgot to remove
Re: Privilege problem [message #422331 is a reply to message #422320] Tue, 15 September 2009 08:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #422334 is a reply to message #422330] Tue, 15 September 2009 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prachij593 wrote on Tue, 15 September 2009 15:55
@Michel Sir,
The user is masked.And while pasting this time.I forgot to remove

And you are gotten to your own hoax.
If we ask you something why do you do something else saying it is what we asked?

Quote:
I told that privilege has not been given directly to role not directly.

Where did you say that? Never.

Now I really don't know what you want, you already had many times the answer(s) to your problem.

Regards
Michel

Re: Privilege problem [message #422337 is a reply to message #422334] Tue, 15 September 2009 09:11 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: GROUP BY FUNCTION
Next Topic: how to take values by user
Goto Forum:
  


Current Time: Tue Sep 27 07:40:03 CDT 2016

Total time taken to generate the page: 0.09377 seconds