1

I created a role and it worked. Then I dropped it, and when I try to recreate it it doesn't work anymore.

I have to create a role, grant select to that role and grant that role to a user named user2, to check whether it is able to see the tables made by another user named user1. I made this:

CREATE ROLE access1;

GRANT SELECT, ALTER, DELETE ON user1.table1 TO access1;

GRANT SELECT, ALTER, DELETE ON user1.table2 TO access1;

GRANT SELECT, ALTER, DELETE ON user1.table3 TO access1;

GRANT access1 TO user2;

It worked the first time, but I had to delete the role following the instructions of the university project and I wrote the following:

DROP ROLE access1;

Now I have to check if I can see the tables from user2 and, indeed, I couldn't.

The problem starts when I have to create the role again. I did the same code but not it doesn't work anymore. From user2 I see this error message:

ORA-00942: table or view does not exist

What am I missing? Why did the instructions work the first time but now they don't?

MT0
  • 143,790
  • 11
  • 59
  • 117
Juansa Campoy
  • 11
  • 1
  • 2
    What is the query you are running as user2 that throws the error? Are you fully qualifying the table name? Was there a synonym created? Or did you previously set a non-default current_schema for the session? – Justin Cave Jan 21 '23 at 10:32
  • 2
    Why `GRANT ALTER`? Did you mean `GRANT UPDATE`? – MT0 Jan 21 '23 at 11:00
  • I'm trying to see the tables made by user1 in the user2 connection. It worked the first time, but it works no longer although I used the same code, word by word – Juansa Campoy Jan 21 '23 at 12:27
  • As I said before, I'm a newbie with SQL, so I still don't know all the words and special instructions. I'm just trying things in order to learn as much as possible, so I still don't know the difference between ALTER and UPDATE. Sorry. – Juansa Campoy Jan 21 '23 at 12:28
  • 1
    What is the exact SQL that gives the ORA-00942, error and what user are you connected as when you run it? – William Robertson Jan 21 '23 at 17:02
  • I'm using SQL Developer, and I am using the Sys user to create the role and giving the privileges – Juansa Campoy Jan 22 '23 at 18:16
  • Please post a full query, which throws the error. There are many reasons why it doesn't work (lack of schema name, wrong schema name, role not activated, messed up synonyms, set/not set current schema etc.) - without a query it'll take too long to make guesses. – robertus Jan 27 '23 at 16:37

0 Answers0