Grant create privilage on Role in OracleDBMS

Question

I want to create a role which has permission only to create table name emp and update emp table. I found some web pages that says it is not possible.(As i understand) Also I try the following code.

GRANT CREATE ON emp TO C##HR;

But this is not working.It will trigger following message.

Do not attempt to grant or revoke EXECUTE privilege on tables.

Is there any way to do the above scenario.? Or is it impossible?

NOTE:- I'm using an ORACLED DBMS 12c

Answer 1

I'm not quite sure that I understand exactly what you're trying to accomplish.

You can grant a user (or a role) the CREATE TABLE privilege. That allows them to create any table they would like in their own schema. You can't grant a user the ability to create a table but restrict the name of the table they can create. I suppose if you wanted to get really tricky, you could grant them the CREATE TABLE privilege but then create a DDL trigger that prevented them from creating a table that wasn't named EMP. That's possible but it's a poor architecture-- granting privileges with one hand and partially restricting them with another makes it very difficult to understand what privileges a user actually has.

If a user owns a table, that user will always have permission to INSERT, UPDATE, and DELETE data from that table as well as running DDL like TRUNCATE or DROP. Even if you manage to restrict a user to only being able to create a table named EMP, that user will have full control over the table.

Now, you always have the option of wrapping whatever you'd like in a stored procedure owned by a more privileged user (i.e. a DBA) and giving a user (or a role) just the ability to execute that stored procedure. A DBA user, for example, could create a procedure that accepted a CREATE TABLE statement, created the table in a schema other than the one that the caller owns, and then granted the user UPDATE privileges on this table. You could then grant EXECUTE privileges on this procedure to your user (or role). Of course, I'm hard-pressed to understand why you'd want to do such a thing. But you could.