How to create a user in Oracle 11g and grant permissions

Connect as SYSTEM.

CREATE USER username IDENTIFIED BY apassword;

GRANT CONNECT TO username;

GRANT EXECUTE on schema.procedure TO username;

You may also need to:

GRANT SELECT [, INSERT] [, UPDATE] [, DELETE] on schema.table TO username;

to whichever tables the procedure uses.

Follow the below steps for creating a user in Oracle.
--Connect as System user

CONNECT <USER-NAME>/<PASSWORD>@<DATABASE NAME>;

--Create user query

CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;

--Provide roles

GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;

--Provide privileges

GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO <USER NAME>;
GRANT UNLIMITED TABLESPACE TO <USER NAME>;

--Provide access to tables.

GRANT SELECT,UPDATE,INSERT ON <TABLE NAME> TO <USER NAME>;

The Oracle documentation is comprehensive, online and free. You should learn to use it. You can find the syntax for CREATE USER here and for GRANT here,

In order to connect to the database we need to grant a user the CREATE SESSION privilege.

To allow the new user rights on a stored procedure we need to grant the EXECUTE privilege. The grantor must be one of these:

• the procedure owner
• a user granted execute on that procedure with the WITH ADMIN option
• a user with the GRANT ANY OBJECT privilege
• a DBA or other super user account.

Note that we would not normally need to grant rights on objects used by a stored procedure in order to use the procedure. The default permission is that we execute the procedure with the same rights as the procedure owner and, as it were, inherit their rights when executing the procedure. This is covered by the AUTHID clause. The default is definer (i.e. procedure owner). Only if the AUTHID is set to CURRENT_USER (the invoker, that is our new user) do we need to grant rights on objects used by the procedure. Find out more.