Home » RDBMS Server » Server Utilities » how should i grant connect , resource privilege to a user (without DBA privilege) (10.2.0 0 , oel 4)
how should i grant connect , resource privilege to a user (without DBA privilege) [message #564317] Wed, 22 August 2012 10:17 Go to next message
thiyagusham
Messages: 112
Registered: April 2012
Location: Chennai
Senior Member
I am checking GRANTS option with LOGICAL BACK UP ( EXP/IMP) . I did following commands from initial stage. please see it.

SQL> connect sys as sysdba
Enter password:
Connected.

SQL> grant create user ,drop user , lock any table to sam;

SQL> conn sam
Enter password:
Connected.

SQL> create user san identified by san;
User created

Initially user sam having  CREATE USER privilege. 

User sam trying to grant  following privilege.

SQL> grant connect ,resource to san identified by san;

grant connect ,resource to san identified by san
* ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant create session to san;
grant create session to san
* ERROR at line 1:

ORA-01031: insufficient privileges


Quote:


** Is there any specific system privilege to grant ?? (without DBA privilege ) **
** I googled more than 1 hour could n't find out ... **



So i done finally with DBA privilege.



conn  /as  sysdba
SQL> grant dba to sam;
Grant succeeded.

SQL> conn sam
Enter password:
Connected.

SQL> grant connect ,resource to san identified by san;
Grant succeeded.

SQL> grant connect , resource to rose identified by rose;
Grant succeeded.

SQL>  grant connect , resource  to preeti identified by preeti;
Grant succeeded.

SQL> grant connect ,resource to san identified by san;
Grant succeeded.


Quote:

My question is without DBA privilege
how should i grant connect , resource privilege to user san connect to the database.


Thanks and Regards
Thiyagusham.G
Re: how should i grant connect , resource privilege to a user (without DBA privilege) [message #564324 is a reply to message #564317] Wed, 22 August 2012 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ NEVER grants Oracle roles, create your own
2/ Read Database Concepts, especially the chapter 20 Database Security, it explains in details what you have to know to answer yourself to your question
3/ When you'll understand it, post it here.

Regards
Michel
Re: how should i grant connect , resource privilege to a user (without DBA privilege) [message #564329 is a reply to message #564324] Wed, 22 August 2012 12:31 Go to previous messageGo to next message
thiyagusham
Messages: 112
Registered: April 2012
Location: Chennai
Senior Member
@ Michel

I read it , here mentioning general information's and security policies about system privilege.
I could n't find exact answer for my question. i got following answer only.

Quote:

To grant a system privilege or role,
must have the ADMIN OPTION for all system privileges and roles being granted


Thanks and Regards
Thiyagusham .G
Re: how should i grant connect , resource privilege to a user (without DBA privilege) [message #564331 is a reply to message #564329] Wed, 22 August 2012 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which is the answer to your question, isn't it?
In your first test, sam has not CONNECT with ADMIN OPTION so it can't grant it.

Regards
Michel
Re: how should i grant connect , resource privilege to a user (without DBA privilege) [message #564333 is a reply to message #564331] Wed, 22 August 2012 13:04 Go to previous messageGo to next message
thiyagusham
Messages: 112
Registered: April 2012
Location: Chennai
Senior Member
@ michel

I checked , again getting error. Please say , what i did wrongly??


SQL>grant connect ,resource to x identified by x;
User created.

SQL> grant create user to x with admin option;
Grant succeeded.

SQL> conn x
Enter password:
Connected.

SQL> create user y identified by y;
User created.

SQL> grant connect ,resource to y;
grant connect ,resource to y
* ERROR at line 1:
ORA-01031: insufficient privileges



Thanks



Re: how should i grant connect , resource privilege to a user (without DBA privilege) [message #564335 is a reply to message #564333] Wed, 22 August 2012 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
everything is forbidden; except that which is explicitly GRANTED

user "X" does not have privilege to issue GRANT.
Re: how should i grant connect , resource privilege to a user (without DBA privilege) [message #564336 is a reply to message #564333] Wed, 22 August 2012 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I checked , again getting error. Please say , what i did wrongly??


Take my previous post and replace sam by x.

Regards
Michel
Re: how should i grant connect , resource privilege to a user (without DBA privilege) [message #564337 is a reply to message #564336] Wed, 22 August 2012 13:33 Go to previous messageGo to next message
thiyagusham
Messages: 112
Registered: April 2012
Location: Chennai
Senior Member
@ Michel , @BlackSwan

SQL> conn sam
Enter password:
Connected.

SQL> create user test identified by test;
User created.

SQL> grant connect,resource to test;
Grant succeeded.

SQL> grant connect , resource to chris identified by chris;
Grant succeeded.



Ya it's ok ..
Thanks ..
Re: how should i grant connect , resource privilege to a user (without DBA privilege) [message #564338 is a reply to message #564337] Wed, 22 August 2012 13:52 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sam is DBA so it can do anything it wants, this does not prove you understood the issue.

Regards
Michel
Previous Topic: split partition in trigger
Next Topic: how to move all data from one tablespace to another tablespace
Goto Forum:
  


Current Time: Thu Apr 18 18:58:53 CDT 2024