ORACLE 7.3 user management

Question

Hi,

At work I have an app sitting on Oracle 7.3 (old stuff, yes). Recently I tried to create an account that would be able to SELECT on all tables, but nothing else i.e. no updates or deletes. But whenever I create a new account it already has access to all application tables. I even stripped it down to CREATE SESSION only. This didn't help - the user could still update any table!

So, I found that there is a whole bunch of Public Synonyms named exactly the same as tables (i.e. table myTable123, public synonym myTable123). I removed one of them and the user was not able to SELECT on that particualr table. Then I created a new table and obviously the new user couldn't see it. I added a Public Synonym for that table, hoping that the user would be able to access it. No luck the new user could not see it.. which would make sense as no GRANTs were given. There has to be something else....

I'm not an Oracle expert and I'm trying to get to the bottom of this issue, but no luck so far. Please help with any suggestions you may have. Cheers! Damo

Answer 1

Oracle 7.3 is not just old, it is very, very, very old. It is 12 or 13 years old.

Creating or dropping public syns doesn't change anyones rights to access a table. The only reason to create public synonyms is to make it possible to access a table without prefixing it with its schema name.

I think your old users have priviliges like 'update any table' and 'select any table' or 'dba'.

Answer 2

As tuinstoel says, you can ignore the public synonyms - that won't affect permissions.

It's possible your that when your original tables were created the creator did a

grant [permissions] on [table] to public

where [permissions] could be as broad as "all", which would mean any new user would automatically be granted permissions on those tables.

Does

select * from ALL_TAB_PRIVS where table_name = '[one of your problem tables]'

return anything?

BTW - never used Oracle 7.3.3 - I'm assuming not much has changed in the permissioning model.