CODEWITHSUNDEEP
sqloracle-database
Tgy
Tgy

Reputation: 15

Question about Grant (DCL) in Oracle SQL Developer

I have problem with understanding what exatly i do wrong. Impossibile to give access to another user via role. Example:

System user:

create table testtable (id number);--Table TESTTABLE created
create role testrole;--Role TESTROLE created
grant insert on testtable to testrole;--Grant succeeded
grant testrole to hr;--Grant succeeded

Hr user:

insert into system.testtable values(1)

SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist"

Upvotes: 0

Views: 128

Answers (1)

wolφi
wolφi

Reputation: 8361

TL;DR: It works, just reconnect the user hr.

The documentations says

"If you grant a role to a user, then the database makes the role available to the user. The user can immediately enable the role and exercise the privileges in the privilege domain of the role."

So your user hr needs to enable the role before being able to use it:

insert into a.testtable values(1);
ORA-00942: table or view does not exist

SELECT * FROM session_roles;
no rows selected

SET ROLE testrole;
Role set.

SELECT * FROM session_roles;
TESTROLE

insert into a.testtable values(1);
1 row created.

Roles are normally automatically enabled during log on, so you have to do the enabling only if hr has a session open while it gets the role granted. Standard procedure is to disconnect/reconnect the user if there are problems with new roles.

Upvotes: 0

Related Questions