This content has been marked as final. Show 7 replies
Please read the following lines from the Oracle online documentation;
CREATE SESSION privilege for access control Privilege to connect to a TimesTen data store must be explicitly granted to every user other than the instance administrator, through the CREATE SESSION privilege. This is a system privilege. It must be granted by an administrator to the user, either directly or through the PUBLIC role. Refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for additional information and examples.
A newly created user cannot connect to the database until you grant the user the CREATE SESSION system privileges. So, immediately after you create the user account, use the GRANT SQL statement to grant the user these privileges. If the user must access Oracle Enterprise Manager, you should also grant the user the SELECT ANY DICTIONARY privilege.
Here is the answer to your question;
ORA-01045: user string lacks CREATE SESSION privilege; logon denied Cause: A connect was attempted to a userid which does not have create session privilege. Action: Grant the user CREATE SESSION privilege.
So either issue the steps above or grant create session directly to the relevant user not through a role. This is happening because the create session is a system privilege.
SQL Reference and Security Guide for 11g indicates that password-enabled roles require the use of the SET ROLE my_role IDENTIFIED BY passwd before any rights granted by that role are effective. You can't CREATE SESSION until you have the role, and you can't have the role until you issue SET ROLE.
Hope That Helps.
Thanks for your answer. I've read the documentation you've posted, but I still have doubts. I think I'm missing something. You've said:
"You can't CREATE SESSION until you have the role, and you can't have the role until you issue SET ROLE."
As I can't CREATE SESSION, I can't connect to the database. How am I going to execute SET ROLE if I'm not connected?
The other suggestion worked:
"It must be granted by an administrator to the user, either directly or through the PUBLIC role."
I've granted CREATE SESSION to PUBLIC, and now all my users can connect to the database. But this doesn't seem to be a good practice.
Is there another way to solver the problem?
What happens when you grant the create session privilege directly to the relevant user and than that user connects and runs the SET ROLE command? Than revoke the create session privilege and let the relevant user has only the role that has create session privilege granted. Now try to connect again?
Ahh it is a bit weird :)
What makes you think that CREATE SESSION to PUBLIC does not seem to be a good practice? Because your are granting a system privilege to the public? Well i think it depends, you can not have the performance / security / functionality at the same time.
You are creating users to connect to the database, is that true? I can not think of another situation or operation without the create session privilege.
Hope That Helps.
Hi again, Ogan!
Thanks for your attention.
Answering your question: I didn't want to grant CREATE SESSION to PUBLIC because there can be other users in database only for organization purposes, not to connect do database.
But I've found another solution to my case. I just need to create my role with "NOT IDENTIFIED" instead of "IDENTIFIED BY MEGA". This way, all the privileges are trasmitted to the users, like in 10g.
CREATE ROLE MY_ROLE NOT IDENTIFIED;
GRANT CONNECT TO MY_ROLE;
CREATE USER MY_USER IDENTIFIED BY MEGA;
GRANT MY_ROLE TO MY_USER;