롤(ROLE)을 생성하고 부여했음에도 유저가 롤을 통해 접근이 불가능할  때

유저는 자신에게 부여된 복수의 롤을 가질수 있습니다. 하지만 받았다고 모두 사용할 수 있는건 아닙니다.  유저는 디폴트로 설정된, 활성화된 롤만을 사용할 수 있습니다. 

롤 생성

SQL> CREATE ROLE role_name;

생성된 롤에 권한 부여

SQL> GRANT SELECT, UPDATE, INSERT, DELETE on user_name.table_name to role_name;

생성된 롤을 유저에게 부여

SQL> GRANT role_name TO user_name;

유저에게 롤이 부여되었는지 확인

SQL > SELECT * 

    FROM DBA_ROLE_PRIVS 

    WHERE GRANTEE = 'user_name';

롤이 정상적으로 생성이 되었는지 확인이 되었으면 권한을 부여한 유저로 SELECT, UPDATE, INSERT, DELETE를 해본다.

SQL> SELECT *

   FROM user_name.table_name;

SQL> UPDATE user_name.table_name 

   SET column_name1 = column_value

   WHERE column_name2 = 'column_string';

SQL> INSERT INTO user_name.table_name (column_name, ...)

   VALUES(column_value);

SQL> DELETE user_name.table_name;

이와 같은 명령문들을 실행하였을 때 정상적으로 실행되는 것이 정상입니다.

하지만 아래와 같이 권한이 부족하다는 오류 메세지가 뜨면서 안될 때가 있습니다.

SQL 오류: ORA-01031: insufficient privileges

01031. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to perform a database operation without

           the necessary privileges.

*Action:   Ask your database administrator or designated security

           administrator to grant you the necessary privileges

롤이 부여된 유저에게 이미 기본 값으로 설정된 롤이 존재할 경우 이런 상황이 생길 수 있습니다.

이때 활성화된(enabled) 롤을 SELECT 해보면 위에서 생성한 롤은 보이지 않겠죠.

SQL> select *

from session_roles;

1)해결방법은 부여한 롤을 default 값으로 만들어 주거나

SQL>alter user user_name default role hr_clerk;

2)롤을 활성화/비활성화해주는 다음 쿼리문으로 롤을 새롭게 set해주시면 됩니다.

SQL>set role all

또는

SQL>set role role_name;

Oracle Help Center -> SET ROLE

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10004.htm