ORA-01045: user TESTME lacks CREATE SESSION privilege; logon denied
I just review a answer from OTN forums.
I tested here
SQL> create role testme_role identified by test; Role created. SQL> grant create session to testme_role; Grant succeeded. SQL> create user testme identified by testme; User created. SQL> grant testme_role to testme; Grant succeeded. SQL> conn testme/testme; ERROR: ORA-01045: user TESTME lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE. The issue is caused by the fact that the roles are not active in the user's session. Although the roles have been granted, they may not be default roles SQL> select * from dba_role_privs where grantee='TESTME'; GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- TESTME TESTME_ROLE NO YES SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CONNECT CREATE SESSION NO SQL> grant create session to testme; Grant succeeded. SQL> conn testme/testme; Connected. SQL> select * from session_roles; no rows selected SQL> create table abc(empno number); create table abc(empno number) * ERROR at line 1: ORA-01031: insufficient privileges SQL> conn / as sysdba Connected. SQL> grant create table to testme_role; Grant succeeded. SQL> conn testme/testme Connected. SQL> set role testme_role; set role testme_role * ERROR at line 1: ORA-01979: missing or invalid password for role 'TESTME_ROLE' SQL> set role testme_role identified by test; Role set. SQL> select * from session_roles; ROLE ------------------------------ TESTME_ROLE SQL> create table abc(empno number); Table created. SQL>
2 Comments
Post a comment
Interesting…
thank you for sharing.
You’re Welcome…^ ^