Create a user with all privileges in Oracle

My issue was, i am unable to create a view with my "scott" user in oracle 11g edition. So here is my solution for this

Error in my case

SQL>create view v1 as select * from books where id=10;

insufficient privileges.

Solution

1)open your cmd and change your directory to where you install your oracle database. in my case i was downloaded in E drive so my location is E:\app\B_Amar\product\11.2.0\dbhome_1\BIN> after reaching in the position you have to type sqlplus sys as sysdba

E:\app\B_Amar\product\11.2.0\dbhome_1\BIN>sqlplus sys as sysdba

2) Enter password: here you have to type that password that you give at the time of installation of oracle software.

3) Here in this step if you want create a new user then you can create otherwise give all the privileges to existing user.

for creating new user

SQL> create user abc identified by xyz;

here abc is user and xyz is password.

giving all the privileges to abc user

SQL> grant all privileges to abc;

 grant succeeded. 

if you are seen this message then all the privileges are giving to the abc user.

4) Now exit from cmd, go to your SQL PLUS and connect to the user i.e enter your username & password.Now you can happily create view.

In My case

in cmd E:\app\B_Amar\product\11.2.0\dbhome_1\BIN>sqlplus sys as sysdba

SQL> grant all privileges to SCOTT;

grant succeeded.

Now I can create views.

There are 2 differences:

2 methods creating a user and granting some privileges to him

create user userName identified by password;
grant connect to userName;

and

grant connect to userName identified by password;

do exactly the same. It creates a user and grants him the connect role.

different outcome

resource is a role in oracle, which gives you the right to create objects (tables, procedures, some more but no views!). ALL PRIVILEGES grants a lot more of system privileges.

To grant a user all privileges run you first snippet or

grant all privileges to userName identified by password;