Suggestions as to how users can see Tables owned by administrator

[JMac]

I know how it supposed to work ... BUT ...

I'm supporting an application. I don't have access to source code but I can see how the database was created by looking at the scripts that were run to create it.

ROLES were created, Tablespaces were created and then a system owning USER was created with ADMIN privileges.

Then connecting as this admin user/database owner (let's call him ADMIN1) the tables, indexes,constraints, views, functions and triggers were all created. Then a GRANTS script was run to grant SELECT, INSERT etc privs to the roles created in the ROLES script that was run at the very begining.

Ok? No users are created - just ROLES that have access to the tables.

It is the application itself that creates users and grants them ROLEs etc. so that they can see/use the tables. I can't see what it does but I'm told that's what happens.

My question is - and I've tried to recreate the scenario by creating a DB using similar scripts and procedure (but no application) - how do the users see the tables? When I try I manually create users, grant them the ROLES (which have the privileges to SELECT etc from the tables), but when I connect as user/username I can't do anything as 'the table does not exist'.

I have to create public synonyms for the users to see the tables - yet in my supported application I can't see where any synonyms are created. I've checked ALL_SYMONYMS etc and it's empty.

What have I done wrong in my recreation? Or - What does the application do that I can't see?

[Peer Jones]

It should be sufficient to grant any user dba rights.
For a new user this could be:
'grant resource, connect,dba to [username];'
Or have I misunderstood the question?

[JMac]

Can't give users DBA rights. Users have ROLES specific to certain areas of the application. they can only SELECT, INSERT and DELETE into and from a certain number of tables.

What's confusing me is HOW they see the tables at all, given that they are are owned by ADMIN1. I would imagine ADMIN1 has to create synonyms for all his tables, so that users can see them. But I can't find evidence of any synonyms.

So - how does it work? Where can I find what synonyms exist. There are no entries in either USER or ALL_SYMONYMS so ... ?

[Peer Jones]

And have these roles you're testing got dba or 'select any table' rights?

What I'm actually getting at is: Is there a possibility that the application grants dba or 'select any table' rights during run-time and immediately revokes them afterwards?

[Edited by Peer Jones on 11-30-2001 at 08:11 AM]

[jrpm]

When you try to see a table are you qualifying it? (owner.table)

Also, are you setting a default role, or even a role, as part of your id and/or session.

Try 'select owner,table_name from all_tables' as userx to see if userx does have access.

When you show v$session what usernames show up? That is, are the users logging in under their own id or is the app using its id and some internal manipulation for the user ids?