| role can not login [message #526151] |
Sun, 09 October 2011 07:11  |
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  |
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 |
John Watson
Messages: 8931
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 #526156 is a reply to message #526155] |
Sun, 09 October 2011 08:30 |
John Watson
Messages: 8931
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 #526171 is a reply to message #526170] |
Sun, 09 October 2011 20:26 |
 |
Barbara Boehmer
Messages: 9090
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 #526184 is a reply to message #526175] |
Mon, 10 October 2011 01:27 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
andy huang wrote on Mon, 10 October 2011 07:35Hi,
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
