Oracle user privilege

0
votes

I have created two new users and a new role. Given select privilege to the role for one table in schema A and assigned this role to user b. While issuing a select query for the table in schema a with this user I am experiencing table or view not found issue.

CREATE USER READUSER1 IDENTIFIED BY readuser1; CREATE USER READUSER2 IDENTIFIED BY readuser2;

CREATE ROLE READONLY_USER IDENTIFIED BY readonlyuser;

GRANT select ON READUSER1.TESTA TO READONLY_USER;

GRANT READONLY_USER TO READUSER2;

Now from READUSER2 session :

SELECT * FROM READUSER1.TESTA > 00942. 00000 - "table or view does not exist"

1
oracle12c

1 Answers

1
votes

I assume that you created the table successfully in the readUser1 schema though you don't show that step.

When logged in as readUser2, what roles are enabled for the session?

select *
  from session_roles

I'll wager that the role is not enabled for the session. Normally, you don't set passwords on roles because you normally want those roles to be available to the user as soon as they log in. If you set a password on a role, however, then every time the user creates a new session, they have to explicitly enable the role by specifying the password. That's quite useful in some unusual situations but it's not the norm.

Assuming that readonly_user does not appear in session_roles, you can enable the role using the set role command

set role readonly_user
  identified by readonlyuser;

Once you've done that, the role should appear in session_roles and you should be able to query the table.

Normally, though, you'd have created a normal role not a password protected role by omitting the identified by clause

create role readonly_user;