Very basic about Users, Roles & Profiles in Oracle

users are the end-users who will be using the database, like you like me;

the DBA must create a "user" inside the database and grant necessary permissions;

just creating a new "user" and let it there will not make the new user be able to access to the database; necessary roles and privileges must be assigned as well;

when a new user is created, the DBA must at least assing "CREATE SESSION" privilege so the user can connect to the database;

grant create session to the-new-user;

of course, the DBA needs to assign more and more privileges based on his role, for example: the new user needs to be able to update the table DOG or insert a new record to the table PUMPKIN;

to make the life easy, the DBA can create a "role" with appropriate privileges and then assign the role to the user;

create role house-roof-role;
grant insert on DOG to house-roof-role;
grant house-roof-role to user-wife;

profile is something different from user and role;

profile is used for controlling the way a user uses the system resources, for example: how many concurrent sessions a user can open, user password policy...

there are 2 types of "profile": password management and resource management;

resource management:

  • SESSIONS_PER_USER: How many concurrent sessions user can open
  • IDLE_TIME: Total time user can stay inside database without doing any activity
  • CONNECT_TIME: Total time user can stay inside database whether idle of active
select * from dba_profiles;
select * from dba_roles;
select * from dba_role_privs;