Questions on privies and roles on user

[Ratzie]

I need to create another user with it's own schema, this user needs to be able to create tables, delete, etc in his own schema.

Would giving him the dba roll do this, and only in his schema? Or what privies would I give.

[Yogeeraj_1 · Accepted solution]

hi,

Granting the DBA role would be too much. You can simply, create the user and grant the following roles:
create session,
create table

Since, the user is the schema owner, inherently select, insert, update, delete privileges are allowed.

hope this helps!
kind regards
yogeeraj

[Ratzie]

What does "with Admin Option" mean?

[Peter Leddy_1]

Hi,

If you grant a privilege the "with Admin option" then it means that the user can then grant that privilege to other users.

Regards,

Peter

[Peter Leddy_1]

Here's the official explanation from the Oracle documentation.

A user or role that is granted a privilege or role, which specifies the WITH ADMIN OPTION clause, has several expanded capabilities:

*

The grantee can grant or revoke the system privilege or role to or from any user or other role in the database. Users cannot revoke a role from themselves.
*

The grantee can further grant the system privilege or role with the ADMIN OPTION.
*

The grantee of a role can alter or drop the role.

[Ratzie]

Appreciate the help