Home » RDBMS Server » Server Administration » "GRANT CONNECT" only possible as SYSDBA
"GRANT CONNECT" only possible as SYSDBA [message #256211] Fri, 03 August 2007 07:35 Go to next message
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.


Re: "GRANT CONNECT" only possible as SYSDBA [message #256212 is a reply to message #256211] Fri, 03 August 2007 07:38 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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.
Re: "GRANT CONNECT" only possible as SYSDBA [message #256219 is a reply to message #256212] Fri, 03 August 2007 08:00 Go to previous messageGo to next message
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!

Re: "GRANT CONNECT" only possible as SYSDBA [message #256231 is a reply to message #256219] Fri, 03 August 2007 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the purpose of this option.

Regards
Michel
Re: "GRANT CONNECT" only possible as SYSDBA [message #256237 is a reply to message #256231] Fri, 03 August 2007 08:50 Go to previous messageGo to next message
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 "..."

Re: "GRANT CONNECT" only possible as SYSDBA [message #256239 is a reply to message #256237] Fri, 03 August 2007 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: "GRANT CONNECT" only possible as SYSDBA [message #256246 is a reply to message #256239] Fri, 03 August 2007 09:47 Go to previous message
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.

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


Current Time: Sat Dec 10 01:34:21 CST 2016

Total time taken to generate the page: 0.12798 seconds