Create a user with all privileges in Oracle
OracleOracle Problem Overview
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 ?
Oracle Solutions
Solution 1 - 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;
Solution 2 - 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
-
Enter password: here you have to type that password that you give at the time of installation of oracle software.
-
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.
- 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.