Вы здесь

Grants by role changed in Oracle 11g?

 
CREATE ROLE my_role IDENTIFIED BY "passwd";
GRANT CONNECT TO my_role;

CREATE USER my_user IDENTIFIED BY "passwd";
GRANT my_role TO my_user;

When I try this in 10g it works fine, while in 11g login is rejected:

ORA-01045:user MY_USER lacks CREATE SESSION privilege; logon denied

Granting CREATE SESSION to the role does not make a difference.
I can only login after directly granting CONNECT (or CREATE SESSION) to the user.

Has Oracle changed this behavior or am I doing something wrong?

I think you might have gotten away with a security "feature" in 10g. The way I read the 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.

 

Oracle Knowledge Base [ID 745407.1] explains this.

The DEFAULT clause in the:

alter user default roles ; specifies the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement, or roles created by the user with the CREATE ROLE privilege. You cannot use the DEFAULT ROLE clause to enable:

  1. Roles not granted to the user

  2. Roles granted through other roles

  3. Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

  4. Roles that are password authenticated.

  5. Roles that are implemented as secure application roles.

For password authenticated roles, the change has been introduced in version 10.2.0.5 and 11.1.0.7. For secure application roles, the change has been introduced in the Oracle releases 10.2.0.4 and 11.1.0.7 These changes will apply to all future releases. The above mentioned restrictions will be introduced in the future documentation.

One can easily turn the password enabled roles into standard roles by running the script resulting from:

select 'alter role '||role||' not identified;' from dba_roles where password_required='YES' and role not in (select role from dba_application_roles);

Oracle Knowledge Base [ID 745407.1] explains this.

The DEFAULT clause in the:

alter user default roles ; specifies the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement, or roles created by the user with the CREATE ROLE privilege. You cannot use the DEFAULT ROLE clause to enable:

  1. Roles not granted to the user

  2. Roles granted through other roles

  3. Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

  4. Roles that are password authenticated.

  5. Roles that are implemented as secure application roles.

For password authenticated roles, the change has been introduced in version 10.2.0.5 and 11.1.0.7. For secure application roles, the change has been introduced in the Oracle releases 10.2.0.4 and 11.1.0.7 These changes will apply to all future releases. The above mentioned restrictions will be introduced in the future documentation.

One can easily turn the password enabled roles into standard roles by running the script resulting from:

select 'alter role '||role||' not identified;' from dba_roles where password_required='YES' and role not in (select role from dba_application_roles);

Post new comment

Filtered HTML

  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Разрешённые HTML-теги: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Строки и параграфы переносятся автоматически.

Plain text

  • HTML-теги не обрабатываются и показываются как обычный текст
  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Строки и параграфы переносятся автоматически.
CAPTCHA
Этот вопрос задается для того, чтобы выяснить, являетесь ли Вы человеком или представляете из себя автоматическую спам-рассылку.
6 + 5 =
Решите эту простую математическую задачу и введите результат. Например, для 1+3, введите 4.