Thanks for the question, rk.
Asked: June 29, 2011 - 2:29 pm UTC
Last updated: July 01, 2011 - 11:37 am UTC
Version: 9.2.0.5
Viewed 10K+ times! This question is 
You Asked
Tom,
If I create a user with the alter user test default role none option, the user cannot connect without the grant create session privilege begin given.
With the default role all privilege, the user can connect without the create session privilege being granted, why is this so?
CREATE USER TEST_USER
IDENTIFIED BY TEST_USER
DEFAULT TABLESPACE TABLE01
TEMPORARY TABLESPACE TEMP2
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for TEST_USER
GRANT CONNECT TO TEST_USER;
GRANT RESOURCE TO TEST_USER;
ALTER USER TEST_USER DEFAULT ROLE NONE;
-- 1 System Privilege for TEST_USER
GRANT UNLIMITED TABLESPACE TO TEST_USER;
SQL*Plus: Release 8.1.6.0.0 - Production on Tue Jun 28 10:46:03 2011
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter user-name: test_user
Enter password:
ERROR:
ORA-01045: user TEST_USER lacks CREATE SESSION privilege; logon denied
I modify the user privs with the command below
2) ALTER USER TEST_USER DEFAULT ROLE ALL;
SQL*Plus: Release 8.1.6.0.0 - Production on Tue Jun 28 10:46:03 2011
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter user-name: test_user
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL>
--
Thanks
Rk
and Tom said...
if you make them have "default role none", they will have no roles enabled upon logging in. Since both connect and resource are roles - they will not be enabled. Therefore, the user logging in will only have PUBLIC privileges and PUBLIC is not granted create session - therefore they will not have create session. Unless you grant them create session directly.
Why this is surprising is surprising to me. It seems pretty clear that the goal of:
alter user test_user default role none;
is to make it so that no roles are active - therefore - they will have no privileges available to them that come from a role - and if the only privileges they have above and beyond PUBLIC are granted through roles - they will effectively have no privileges whatsoever.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment