7 Replies Latest reply: Aug 16, 2010 6:10 PM by 618702 RSS

    Grant CREATE SESSION on 11g

    Adelson_Smania
      Hi!

      In Oracle 10g the statements bellow works perfectly. After execute them, I can connect with user MY_USER.

      CREATE ROLE MY_ROLE IDENTIFIED BY MEGA;
      GRANT CONNECT TO MY_ROLE;

      CREATE USER MY_USER IDENTIFIED BY MEGA;
      GRANT MY_ROLE TO MY_USER;


      Although, in 11g I get the "ORA-01045: user MY_USER lacks CREATE SESSION privilege; logon denied" in the same situation. It only works if I grant "CREATE SESSION" directly to MY_USER.

      Is there anyway to grant the privilege to my users through roles in 11g?

      Thanks!
        • 1. Re: Grant CREATE SESSION on 11g
          sb92075
          when all else fails Read The Fine Manual

          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6012.htm#i2066772
          IDENTIFIED Clause
          
          Use the IDENTIFIED clause to indicate that a user must be authorized by the specified method before the role is enabled with the SET ROLE statement.
          • 2. Re: Grant CREATE SESSION on 11g
            618702
            Dear Adelson_Smania,

            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.
            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.
            Here is the answer to your question;
            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.
            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.

            Hope That Helps.

            Ogan
            • 3. Re: Grant CREATE SESSION on 11g
              Adelson_Smania
              Hello, Ogan!


              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?


              Thanks again.
              • 4. Re: Grant CREATE SESSION on 11g
                Chinar
                Also you can do this role as default for this user as alter user MY_USER default role MY_ROLE then try again
                • 5. Re: Grant CREATE SESSION on 11g
                  618702
                  Dear Adelson_Smania,

                  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.

                  Ogan
                  • 6. Re: Grant CREATE SESSION on 11g
                    Adelson_Smania
                    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;

                    Best regards!
                    • 7. Re: Grant CREATE SESSION on 11g
                      618702
                      Dear Adelson_Smania,

                      That is great that you have found the solution. It is indeed weird but the thing here is the "identified by" clause.

                      Regards.

                      Ogan