Oracle19c -Create Role User Under the Tablespace

Question

Hello How I can Create Roles with

ReadOnly(Select any Tables under the tablespace)

And

InsertUpdateRoleOnly(To insert and update Data ,not delete)

Under My tablespace with local access this users?

Answer 1

Tables are owned by someone. The owner grants privileges to other users or to roles; in your case, it'll be a role. As the role doesn't depend on a tablespace (you mentioned), you'd create it as simple as

create role r_read_only;

Then, the owner would grant SELECT privilege on its tables to that role, e.g.

grant select on emp  to r_read_only;
grant select on dept to r_read_only;

Such a role would be granted to other users, e.g.

grant r_read_only to littlefoot;

and user littlefoot will be able to select from those tables.

---

The same goes for your another role, no difference:

create role r_upd_ins;
grant insert, update on emp to r_upd_ins;
grant r_upd_ins to bigfoot;