[SOLVED] Change oracle table accesses

"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.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Virtuoso

posted

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.

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

Posts: 1533 | Registered: August 12, 2005

Ignored post by njsden posted

ExoR

Member

posted

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,

WebFOCUS Version 8.2.0.1
Windows Server 2012

WebFOCUS Version 5.3.3
Windows 2000
Oracle 8i

Posts: 15 | Registered: June 23, 2008

Ignored post by ExoR posted

Virtuoso

posted

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.

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

Posts: 1533 | Registered: August 12, 2005

Ignored post by njsden posted

ExoR

Member

posted

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

WebFOCUS Version 8.2.0.1
Windows Server 2012

WebFOCUS Version 5.3.3
Windows 2000
Oracle 8i

Posts: 15 | Registered: June 23, 2008

Ignored post by ExoR posted

Virtuoso

posted

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.

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

Posts: 1533 | Registered: August 12, 2005

Ignored post by njsden posted

ExoR

Member

posted

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!!!!

WebFOCUS Version 8.2.0.1
Windows Server 2012

WebFOCUS Version 5.3.3
Windows 2000
Oracle 8i

Posts: 15 | Registered: June 23, 2008

Ignored post by ExoR posted

Virtuoso

posted

You're very welcome

Glad it helped!

- Neftali.

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

Posts: 1533 | Registered: August 12, 2005

Ignored post by njsden posted