CREATE USER ERIC IDENTIFIED BY pass1w0rd DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; ALTER USER ERIC QUOTA UNLIMITED ON USERS; GRANT CONNECT, RESOURCE TO ERIC;
Oracle states in its documentation
"When you first create a user, the default user role setting is ALL, which causes all roles subsequently granted to the user to be default roles."
So we should have two default roles enabled for our user ERIC, confirmed below:
SQL> connect eric/pass1w0rd Connected. SELECT * FROM SESSION_ROLES; ROLE ------- CONNECT RESOURCE
Let's create a new role:
CREATE ROLE online_users NOT IDENTIFIED; Role created.Because the user ERIC was created with ALL roles enabled by default, all subsequent sessions established by ERIC will now have access to the role online_users:
GRANT online_users to ERIC; Grant succeeded. connect eric/Pass1w0rd Connected. SELECT * FROM SESSION_ROLES; ROLE ------- CONNECT RESOURCE ONLINE_USERS
If you want to prevent this behavior, you can alter the user with a limited default setting:
ALTER USER ERIC DEFAULT ROLE CONNECT; User altered. connect eric/Pass1w0rd Connected. SELECT * FROM SESSION_ROLES; ROLE ------- CONNECT
User ERIC must now explicitly enable the required roles from within his session:
set role connect, online_users; Role set. SELECT * FROM SESSION_ROLES; ROLE ------- CONNECT ONLINE_USERS
These features can be incorporated into a security-oriented application setup, where specific people should only be granted specific privileges through database roles.
No comments:
Post a Comment