I was googling about how to create a user and grant all privileges to him.
I found these two methods :
The first method :
create user userName identified by password;
grant connect to userName;
grant all privileges to userName;
The second method :
grant connect , resource to userName identified by password;
So what's the difference between those two methods ?
This question is related to
oracle
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;
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.
Source: Stackoverflow.com