Cannot See Table Due to Missing System Privileges

[dpatrickcollins]

I created a database using the database configuration wizard provided with Oracle 9i installer (Personal Oracle). I then created a user, because I wanted to log in with that user using, say Access or Crystal. Problem was, WAY too many tables were being displayed. I only wanted a single table to be displayed (which I created). I traced the problem down to the PUBLIC user and revoked all SELECT privileges. I then applied all privileges to the user directly for that single table. And this is the problem: no tables appear when I log in now, not even the one I granted privileges to the user.

When I add the default DBA role to the new user, I am back to seeing every table again. When I revoke the role, I am back to seeing NO table. What am I missing?

I have poured through on-line documentation for hours. Any help would be greatly appreciated.

 

[BJCooperIT]

Are you referencing the table with just the table name? If you granted the user privileges to the table, but did not create a synonym, then the user can only see the table by referencing

schema.table_name

. Schema is the user that owns the table.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[dpatrickcollins]

Hi BJCooperIT,

Per yoru question, "are you referencing the table ... ", are you referring to when the table is created, or when I am attempting to access it? I will assume the latter.

I am actually accessing it via a GUI, Acess 2002 or Crystal, take your pick. The table does not appear. However, in SQL+ I am able to query the table just fine. Conclusion on my end is that there must be some basic privileges both GUI's look for that I am lacking.

 

[carp]

Which user were you logged in as when you created the table? The fact that you can see the table when granted the DBA role tends to support this hunch.

If it was a different user than the one you are trying to log in as, then you need to log in as the table owner and grant the privilege to the user you are trying to log in as from your GUI.

As BJCooper indicated, you will also need to either reference the table via a synonym (public or private) or the fully-qualified (schema.table_name) format.

 

[dpatrickcollins]

Thanks carp and BJCooper. This is what I have just done:

-----------------------------------
Logged in as SYSTEM with SYSDBA
-----------------------------------

CREATE TABLE "CLARITY"."" ("PATIENTID" VARCHAR2(10))

GRANT ALTER
ON "CLARITY"."SAMPLE_TABLE" TO "CLARITY"
GRANT DELETE ON "CLARITY"."SAMPLE_TABLE" TO "CLARITY"
GRANT INDEX ON "CLARITY"."SAMPLE_TABLE" TO "CLARITY"
GRANT INSERT ON "CLARITY"."SAMPLE_TABLE" TO "CLARITY"
GRANT REFERENCES ON "CLARITY"."SAMPLE_TABLE" TO "CLARITY"
GRANT SELECT ON "CLARITY"."SAMPLE_TABLE" TO "CLARITY"
GRANT UPDATE ON "CLARITY"."SAMPLE_TABLE" TO "CLARITY"

When I log in the GUI as "Clarity", I still see nothing. Know I am showing my ignorance here, but I don't understand the Synonym thing at all. Is defining a synonym necessary if I have taken the above steps?

 

[BJCooperIT]

When logged in as CLARITY you will have to use:

select * from system.sample_table

to see the granted table without a synonym.

To create a synonym you have 2 options:

1. SYSTEM can create a public synonym:

create public synonym sample_table for sample_table;

2. CLARITY can create a private synonym:

create synonym sample_table for system.sample_table;

With either of these two options now CLARITY can use:

select * from sample_table

to access the table created and granted by SYSTEM.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[dpatrickcollins]

Hate to break this to you, but I completed all the above steps. (specifically, I logged in as clarity and created a private synonym, even logged in as system (after that field) and create a public synonym.) To no avial. Still no database table showing in the GUI.

Keep in mind, I can query the table using SQL+ just fine. But both third-party gui's (Access and Crystal) refuse to display the table when logged in as clarity user. Any more thoughts?

 

[BJCooperIT]

Perhaps a question posted in the Access or Crystal forums?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[dpatrickcollins]

Tell me about it. Thanks one and all!

 

[THoey]

Did you try to drop the MS Access link and then re-create it?

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[dpatrickcollins]

Yes, I did. But I must let you in on the recent conclusion to our story. I had revoked most object privileges from the PUBLIC user thinking they were optional. Well, as my DBA friend told me, they're not. "Good thing I am not working on any of YOUR datatbases," I remarked. His reply: "I'd kill you."