Home » RDBMS Server » Server Administration » role can not login (11.1.0.7 windows xp)
role can not login [message #526151] Sun, 09 October 2011 07:11 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
I created a role,and it can not login,why?

session1:
SQL> Create Role con_role Identified By hxl;

Role created.

SQL> Grant  Connect To con_role;

Grant succeeded.

SQL> Grant  Create Session To con_role;

Grant succeeded.


session2:
sql>connect con_role/hxl@myserver
ORA-01017: Invalid username/password.
Re: role can not login [message #526152 is a reply to message #526151] Sun, 09 October 2011 07:47 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
The role con_role have privileges:Connect,Create Session and then i grant the role con_role to user chenli,but the user chenli can not login,why?
SQL> Create Role con_role Identified By hxl;

Role created.

SQL> Grant  Connect To con_role;

Grant succeeded.

SQL> Grant  Create Session To con_role;

Grant succeeded.

SQL> Create User chenli Identified By chenli;

User created.

SQL> Grant con_role To chenli;

Grant succeeded.

SQL> connect chenli/chenli

ORA-01045: user CHENLI lacks CREATE SESSION privilege
Re: role can not login [message #526153 is a reply to message #526152] Sun, 09 October 2011 08:03 Go to previous messageGo to next message
John Watson
Messages: 4870
Registered: January 2010
Location: Global Village
Senior Member
You chose to protect the role with a password. This means that it is disabled until you set it, within an already connected session, by giving the password. Try this:
create user jon identified by jon;
grant connect to jon;
create role r1 identified by pw;
grant r1 to jon;
conn jon/jon;
select * from session_roles;
set role r1 identified by pw;
select * from session_roles;

You might want to read up on commands such as ALTER USER...DEFAULT ROLE ALL|NONE|ALL EXCEPT



Re: role can not login [message #526155 is a reply to message #526153] Sun, 09 October 2011 08:26 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
my issue is i grant role con_role to user chenli,does it can not login?
Re: role can not login [message #526156 is a reply to message #526155] Sun, 09 October 2011 08:30 Go to previous messageGo to next message
John Watson
Messages: 4870
Registered: January 2010
Location: Global Village
Senior Member
I'm sorry, Andy, but if you can't be bothered to read my replies, I am not going to reply again. You haven't even said "thank you". Goodbye.
Re: role can not login [message #526166 is a reply to message #526155] Sun, 09 October 2011 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
andy huang wrote on Sun, 09 October 2011 15:26
hi,
my issue is i grant role con_role to user chenli,does it can not login?


Roles identified by password are not enable by default.
So your user has no role and no privilege at creation time.

Regards
Michel
Re: role can not login [message #526169 is a reply to message #526166] Sun, 09 October 2011 19:56 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
The new user chenli have no any privilgge when created,how can i grant role_role to it?
Re: role can not login [message #526170 is a reply to message #526169] Sun, 09 October 2011 20:08 Go to previous messageGo to next message
BlackSwan
Messages: 23188
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: role can not login [message #526171 is a reply to message #526170] Sun, 09 October 2011 20:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
-- problem:
SCOTT@orcl_11gR2> create role con_role identified by hx1
  2  /

Role created.

SCOTT@orcl_11gR2> grant connect to con_role
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> grant create session to con_role
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> create user chenli identified by chenli
  2  /

User created.

SCOTT@orcl_11gR2> grant con_role to chenli
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> connect chenli/chenli
ERROR:
ORA-01045: user CHENLI lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.


-- solution:
SCOTT@orcl_11gR2> grant connect to chenli
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> connect chenli/chenli
Connected.
CHENLI@orcl_11gR2> set role con_role identified by hx1
  2  /

Role set.


-- Alternatively, you could just create the role without the password:
SCOTT@orcl_11gR2> create role con_role
  2  /

Role created.

SCOTT@orcl_11gR2> grant connect to con_role
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> grant create session to con_role
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> create user chenli identified by chenli
  2  /

User created.

SCOTT@orcl_11gR2> grant con_role to chenli
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> connect chenli/chenli
Connected.

[Updated on: Sun, 09 October 2011 20:34]

Report message to a moderator

Re: role can not login [message #526174 is a reply to message #526171] Mon, 10 October 2011 00:23 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Re: role can not login [message #526175 is a reply to message #526174] Mon, 10 October 2011 00:35 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Hi,
Is it a bug that it can login without password of role,otherwise it can not login?
Re: role can not login [message #526178 is a reply to message #526175] Mon, 10 October 2011 00:55 Go to previous messageGo to next message
BlackSwan
Messages: 23188
Registered: January 2009
Senior Member
>Is it a bug that it can login without password of role,otherwise it can not login?
no

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
Re: role can not login [message #526184 is a reply to message #526175] Mon, 10 October 2011 01:27 Go to previous message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
andy huang wrote on Mon, 10 October 2011 07:35
Hi,
Is it a bug that it can login without password of role,otherwise it can not login?


No it is expected and it was a bug I pointed Oracle to when it was not the case.
Think about it.
You created a role that can be activated ONLY if you give the password.
So if it was activated by default, it would not be activated giving the password.

By the way, do NOT use the default roles (CONNECT, RESOURCE, DBA), create your own roles with the limited list of privileges you need to do the job.

Regards
Michel

Previous Topic: ORA-01019: unable to allocate memory in the user
Next Topic: oratab
Goto Forum:
  


Current Time: Fri Dec 26 23:13:27 CST 2014

Total time taken to generate the page: 0.12623 seconds