Creating User, Providing Roles,Assigning Privileges..

Creating a User:

CREATE USER username IDENTIFIED BY apassword;

EX: CREATE USER DINGA IDENTIFIED BY DINGI;

Providing Roles:

GRANT CONNECT TO username;

EX:GRANT CONNECT TO DINGA;

In some cases to create a more powerful user, you may also consider adding the RESOURCE role (allowing the user to create named types for custom schemas) or even the DBA role, which allows the user to not only create custom named types but alter and destroy them as well

GRANT CONNECT, RESOURCE, DBA TO USERNAME;

EX:GRANT CONNECT, RESOURCE, DBA TO DINGA;

Assigning Privileges:

We also need to ensure our new user has disk space allocated in the system to actually create or modify tables and data, so we’ll GRANT TABLESPACE like so:

GRANT UNLIMITED TABLESPACE TO USERNAME;

EX:GRANT UNLIMITED TABLESPACE TO DINGA;

Table Privileges:

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

EX: GRANT SELECT, INSERT,UPDATE,DELETE ON SCOTT.EMP TO DINGA;

Grant Select on all Tables Owned By Specific User

Select 'GRANT SELECT ON OwningUser.'||Table_Name||'TO ReceivingUser'

From All_Tables Where Owner='OWNINGUSER'

EX:

 Select 'GRANT SELECT ON SCOTT.'||Table_Name||'TO DINGA'

 From All_Tables Where Owner='SCOTT';

1.     How to grant a System Privilege to a user

   GRANT  create  table  TO  dinga

2.     How to grant more than one system privilege to only one user in a single Grant statement.

GRANT  create synonym,  create view,  create sequence  TO  dinga;

3.     How to grant privileges to more than one user in single Grant Statement.

GRANT  create  procedure  TO  dinga,  dingi;