CODEWITHSUNDEEP
sqloracle-database
kmd
kmd

Reputation: 79

Oracle SQL creating user and granting permissions

I'm new to SQL and programming so I apologize if this isn't clearly worded.

I have the following code to create a user and role. I want user1 to be able to select and view table1.

  1. When I run this code user1 isn't able to select table1 and I get the error that the table does not exist.
  2. When prompted with the Database Entry screen I input user and password.
  3. When prompted with the statement "Enter TNS_ADMIN entry or blank for Current Worksheet Database:" I don't input anything.

USER1 is able to connect, but not view the table. Under "other users" in the connection I see USER1 but none of the tables I've established are listed.

Thanks!

CREATE USER user1 
    IDENTIFIED BY user1
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    ACCOUNT UNLOCK;

CREATE ROLE ReadOnly;
GRANT SELECT ON table1 to ReadOnly;
--GRANT SELECT ON table2 to ReadOnly;
--GRANT SELECT ON table3 to ReadOnly;
GRANT CONNECT to user1;
GRANT ReadOnly to user1;

grant create session to user1;

connect user1
show user

select * from table1

Upvotes: 0

Views: 433

Answers (1)

Justin Cave
Justin Cave

Reputation: 231851

Assuming that table1 is not owned by user1, you have three options

You can use the full name of the table in your query

SELECT * FROM <<schema name>>.table1

You can create a synonym for table1. A private synonym in the user1 schema would generally be preferred but a public synonym would also work. As user1

CREATE SYNONYM table1 FOR <<schema name>>.table1

SELECT * FROM table1;

Or you can change the default schema (for name resolution purposes, this has no impact on privileges)

ALTER SESSION SET current_schema=<<schema that owns table1>>

SELECT * FROM table1;

Upvotes: 1

Related Questions