Really basic question 1

[mpruett]

I'm playing around with the Oracle 10g evaluation version, and I have it installed and set up, and even imported a database from an old SQL server instance I have.

Although I can see the tablespace and tables, and that there is data in those tables, I cannot figure out how to configure a user so that I can query those tables in SQL Plus.

I figure this has to be simple, but I'm having a tough time with it. I'd really appreciate somebody giving me a simple breakdown of how to set a user up to do this.

Thanks!

Mark

 

[SantaMufasa]

Although I can see the tablespace and tables, and that there is data in those tables, I cannot figure out how to configure a user so that I can query those tables in SQL Plus.

Mark, with which user are you "seeing" the tablespace and tables? (You could use that user to query.) Also, a table cannot exist without an Oracle USER to "own" that table. (So, if you are just wanting to "query those tables in SQL*Plus", then you can "query those tables in SQL*Plus" as the owning user.)


Now, on to your specific question ("I'd really appreciate somebody giving me a simple breakdown of how to set a user up to do this."):

CREATE USER <some_user_name> IDENTIFIED BY '<some_password'
DEFAULT TABLESPACE <some_non-SYSTEM_tablespace>
TEMPORARY TABLESPACE <some_non-SYSTEM_tablespace>;

GRANT CONNECT TO <the_new_user>;

Then, logged in as the Oracle user that owns the tables, you grant privileges to the new user:

GRANT SELECT ON <target_table name(s)> TO <the_new_user>;

Lastly, test the CREATE and GRANTs that just took place:

SQL> connect <the_new_user>/<password>@<alias of instance>
SQL> SELECT COUNT(*) FROM <owning_schema>.<targe_table_name>;

Let us know how all this works for you, Mark.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.