ORACLE 7.3 User Management
At work, I have an application sitting on Oracle 7.3 (old, yes). I recently tried to create an account that will be able to SELECT on all tables, but nothing else other than no updates or deletes. But whenever I create a new account, it already has access to all application tables. I even stripped it for CREATE SESSION only. It didn't help - the user could update any table!
So, I found that there are a whole bunch of public synonyms named exactly the same as tables (i.e. table myTable123, public synonym myTable123). I deleted one of them and the user was unable to select SELECT on that side of the table. Then I created a new table and obviously the new user didn't see it. I added a public synonym for this table, hoping the user can access it. No luck that the new user couldn't see it .. which would be reasonable since no GRANT was given. There must be something else ...
I am not an Oracle expert and I am trying to figure out this issue but no luck so far. Please help with any suggestions you may have. Hooray! Damo
a source to share
As tuinstoel says, you can ignore public synonyms - it won't affect permissions.
It is possible that when your original tables were created the creator did
grant [permissions] on [table] to public
where [permissions] can be as wide as "everyone", which means that any new user will automatically get permissions on these tables.
whether
select * from ALL_TAB_PRIVS where table_name = '[one of your problem tables]'
return anything?
BTW - never used Oracle 7.3.3 - I'm guessing the model with permission hasn't changed.
a source to share
Oracle 7.3 isn't just old, it's very, very, very old. He is 12 or 13 years old.
Creating or deleting public syncs does not change any permissions on the table. The only reason to create public synonyms is to access the table without first specifying its schema name.
I think your old users have privileges like "update any table" and "select any table" or "dba".
a source to share