How to grant user privileges in Oracle

Question

I want to grant a system privilege to the user so, that he can create a table in the database also I want to grant object privilege to the same user so that he can insert, delete or update data in the table and the user should be able to grant the same permission to other users. If the username is martin, what would be the SQL statement in oracle?

Answer 1

martin has to have create session first; otherwise, he won't be able to connect to the database. Then you can grant other privileges you mentioned. Who will grant them? A privileged user, such as sys (basically, the same user that created martin):

grant create table to martin;

Martin - once connected - will be able to create tables in his own schema. He doesn't need any additional privileges to perform operations on those tables (DML (insert, update, delete, select) or DDL (alter table)) as he owns them.

In order for someone to be able to grant privileges he acquired to other users, those privileges have to be granted with grant option, e.g.

grant create table to martin with grant option;

Much more info in GRANT documentation.