Hi all, i have created a table on the db through webfocus with this commands:

 ENGINE SQLORA SET CONNECTION_ATTRIBUTES  dbName1/user1,pwd1

TABLE FILE WF_COMMERCIAL
WRITE COL1 COL2 COL....
ON TABLE HOLD AS COMMERCIAL FORMAT SQLORA
END 

and these commands works perfectly

there are some accounts that can login on dbName1:
user1 has Read Write permission
user2 has Read permission

When other users, that use MS Access, try to link the COMMERCIAL table with user2 they can't find this table on the table list.
On the other hand if they login with user1, the table is there on the table list, but they can modify the data of all the tables in dbName1...

i have tried to drop the table and recreate with user2 login

 ENGINE SQLORA SET CONNECTION_ATTRIBUTES  dbName1/user2,pwd2
....

and the table was created perfectly, the users can link the COMMERCIAL table with user2... but they can still modify this table with user2 account!!! a read only account!!!
How can this be possible???
How can I create the table with user1 account, and give to user2 the read only access to this table?

Thank you for the help.

This message has been edited. Last edited by: Kerry,

"How can I create the table with user1 account, and give to user2 the read only access to this table" - this sounds like an Oracle issue, not a WebFOCUS issue.

"When other users, that use MS Access, try to link the COMMERCIAL table with user2 they can't find this table on the table list." - this sounds like a MS Access issue.

quote:

but they can still modify this table with user2 account!!! a read only account!!!

There are no "read only" accounts in Oracle.

A user account is a user account. That account can query and/or modify any table based on specific privileges that must be granted to that account on the table/view or any other object you want.

Although the idea of creating dynamic DB objects from WebFOCUS looks rather interesting, it is something that is best left to the database.

I'd recommend that you use your database tools to create the table you need and GRANT the privileges you want to each specific user or better yet, a role. Assuming you're using SQL*Plus:

connect user1/pdw1@dbName1

create table wf_commercial (
  <list of columns here>
);

grant select on wf_commercial to user2;

As the table was created by user1, that account can both select and modify contents of the table. However, user2 needs a specific privilege to be able to at least READ from the table, therefore the GRANT statement you are using there.

Please do that and I'm almost sure that user2 will not only be able to query the table but also MS Access should be able to "see" it now.

quote:

grant select on wf_commercial to user2;

thank you! this is the way
usually technician manage the oracle db create drop grant etc. tables... but now in this time technician are halved and I only use webfocus

so...

i've logged in SQL*PLUS and executed these 2 commands:

SQL> GRANT SELECT ON USER1.COMMERCIAL TO USER2;

Grant succeeded.

SQL> REVOKE INSERT ON USER1.COMMERCIAL FROM USER2;
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

SQL> REVOKE UPDATE ON USER1.COMMERCIAL FROM USER2;
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

SQL> REVOKE DELETE ON USER1.COMMERCIAL FROM USER2;
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

and user2 can link USER1.COMMERCIAL!!!
but user2 still can modify the table...

it's a good step but it's risky to let user2 modify the table

I've tried to grant SELECT, INSERT, UPDATE, DELETE to user2 with Grant succeeded
and tried to revoke INSERT, UPDATE, DELETE to user2 with Revoke succeeded.
but user2 still can modify the table from access...



EDIT:
i've executed this:

SQL> GRANT SELECT ON USER1.COMMERCIAL TO USER3;

Grant succeeded.

and USER3 can only do a SELECT on the table...
what's wrong with USER2???

This message has been edited. Last edited by: ExoR,

First things first:

1. Whom does the table belong to? user1 or user2?
2. Which user are you using to connect to Oracle from SQL*Plus? you should connect as the user who OWNS the table.

i've created the table with user1
the table name is user1.commercial
and in SQL*PLUS i login with user1 account

Which roles and system privileges does USER2 have in Oracle?

It may be possible that USER2 has a "UPDATE ANY TABLE" privilege which would allow it to modify data in any table even if no specific privilege on the latter has been granted.

Please check that and let us know.

thank you!!! i've found that to user2 was granted the UPDATE ANY TABLE privilege.
So i'll give to users the user3 account

thank you njsden!!!!

You're very welcome

Glad it helped!

- Neftali.