OraFAQ Forum: Server Administration » "GRANT CONNECT" only possible as SYSDBA

Home » RDBMS Server » Server Administration » "GRANT CONNECT" only possible as SYSDBA

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

"GRANT CONNECT" only possible as SYSDBA [message #256211]

Fri, 03 August 2007 07:35

braini
Messages: 7
Registered: August 2007

Junior Member

Hi,

is it possible that a user without DBA privileges can grant the "connect" or "create session" privilege?

I want to give my user as few power as possible but he must be able to create new users and allow them to connect to his own database.

I can remember that this is possible with mysql but in oracle it seems to be different and more complicated.

Report message to a moderator

Re: "GRANT CONNECT" only possible as SYSDBA [message #256212 is a reply to message #256211]

Fri, 03 August 2007 07:38

Frank Naude
Messages: 4593
Registered: April 1998

Senior Member

Will this work?

SQL> GRANT create session TO scott WITH ADMIN OPTION;

Grant succeeded.

SQL> GRANT connect TO scott WITH ADMIN OPTION;

Grant succeeded.

Report message to a moderator

Re: "GRANT CONNECT" only possible as SYSDBA [message #256219 is a reply to message #256212]

Fri, 03 August 2007 08:00

braini
Messages: 7
Registered: August 2007

Junior Member

Wow,
is it really this easy? Just add "WITH ADMIN OPTION" to a grant and the grantee can grant it to others?
...very nice, it works - thank you!

Report message to a moderator

Re: "GRANT CONNECT" only possible as SYSDBA [message #256231 is a reply to message #256219]

Fri, 03 August 2007 08:28

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is the purpose of this option.

Regards
Michel

Report message to a moderator

Re: "GRANT CONNECT" only possible as SYSDBA [message #256237 is a reply to message #256231]

Fri, 03 August 2007 08:50

braini
Messages: 7
Registered: August 2007

Junior Member

One more question:
Why is the use of lower case names for db users not possible?
Is this an oracle convention or law? If this is true, why is it possible to enter it in the CREATE clause if it is invalid?

I can connect when I use
CREATE TESTUSER1 IDENTIFIED BY "..."

invalid username/ password reply when I use
CREATE "Testuser1" IDENTIFIED BY "..."

Report message to a moderator

Re: "GRANT CONNECT" only possible as SYSDBA [message #256239 is a reply to message #256237]

Fri, 03 August 2007 09:08

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

By default Oracle converts lower case to upper case except if you enclose the name between ".
Then each time you reference the user (or any object) you have to enclose it between ".

Use "select username from dba_users" to check your actual username.

Regards
Michel

Report message to a moderator

Re: "GRANT CONNECT" only possible as SYSDBA [message #256246 is a reply to message #256239]

Fri, 03 August 2007 09:47

braini
Messages: 7
Registered: August 2007

Junior Member

You're right. I forgot to enter the double quotes in the "Username" field when trying to connect via sqldeveloper.
Thanks and have a nice weekend.

Report message to a moderator

Previous Topic:	Undo management "Flashback Version Query"
Next Topic:	Database upgrade using dbua

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 25 03:16:28 CST 2025