Locking Accounts and Expiring Passwords

To begin securing your database, you should minimally change the password for every default account and then lock any accounts that you’re not using. Locking an account means that a user won’t be able to access it unless a DBA explicitly unlocks it. Also consider expiring the password for each account. Expiring the password means that when a user first attempts to access an account, that user will be forced to change the password.

After creating a database, I usually lock every default account and change their passwords to expired; I unlock default users only as they’re needed. The following script generates the SQL statements that lock all users and set their passwords to expired:

select
  'alter user ' || username || ' password expire account lock;'
from dba_users;

A locked user can only be accessed by altering the user to an unlocked state; for example,

SQL> alter user outln account unlock;

A user with an expired password is prompted for a new password when first connecting to the database as that user. When connecting to a user, Oracle checks to see if the current password is expired and, if so, prompts the user, as follows:

ORA-28001: the password has expired
Changing password for ...
New password:

After entering the new password, the user is prompted to enter it again:

Retype new password:
Password changed
Connected.

There is no alter user <user_name> password unexpire command. To unexpire a password, you simply need to change it. The user can change the password (as demonstrated in the prior bits of code), or, as a DBA, you can change the password for a user:

SQL> alter user <username> identified by <new password>;

However, if you have a user with an expired password, and you don’t know the current password and are hesitant to change it (because it’s in use in a production environment, and you’re not sure in how many places within the application code the old password has been hard-coded), what do you do?

Consider this technique to unexpire a password without changing it. In this example the user is MV_MAINT:

1. As a DBA-privileged database user, first note the current profile of the user:

   SQL> select username, profile from dba_users where username='MV_MAINT';
     
   USERNAME             PROFILE
   -------------------- ----------
   MV_MAINT             DEFAULT

2. Next, create a temporary profile:

   CREATE PROFILE temp_prof LIMIT
   PASSWORD_REUSE_MAX unlimited
   PASSWORD_REUSE_TIME unlimited;

3. Now, assign the temporary profile to the user:

   SQL> alter user mv_maint profile temp_prof;

4. Then, view the obfuscated password:

   SQL> select password from user$ where name='MV_MAINT';

   Here is the output for this example:

   PASSWORD
   --------------------
   E88FDA313EC0F3F4

5. Now, set the password to what it was; this unexpires it:

   SQL> alter user mv_maint identified by values 'E88FDA313EC0F3F4';

6. Finally, assign the original profile back to the user:

   SQL> alter user mv_maint profile default; ;

The prior technique allows you to unexpire a password without knowing the password and also avoids issues with passwords that cannot be reused (ORA-28007: the password cannot be reused) because of restrictive profile settings (such as PASSWORD_REUSE_MAX).

Identifying DBA-Created Accounts

If you’ve inherited a database from another DBA, then sometimes it’s useful to determine whether the DBA created a user or if a user is a default account created by Oracle. As mentioned earlier, usually several user accounts are created for you when you create a database. The number of accounts varies somewhat by database version and options installed. Run this query to display users that have been created by another DBA versus those created by Oracle (such as those created by default when the database is created):

select distinct u.username
,case when d.user_name is null then 'DBA created account'
 else 'Oracle created account'
 end
from dba_users    u
    ,default_pwd$ d
where u.username=d.user_name(+);

For default users, there should be a record in the DEFAULT_PWD$ view. So, if a user doesn’t exist in DEFAULT_PWD$, then you can assume it’s not a default account. Given that logic, another way to identify just the default users would be this:

select distinct(user_name)
from default_pwd$
where user_name in (select username from dba_users);

The prior queries aren’t 100 percent accurate, as there are users that exist in DEFAULT_PWD$ that can be created manually by a DBA. Having said that, the prior queries do provide a starting point for separating the default accounts from ones created by you (or another DBA).

Checking Default Passwords

You should also check your database to determine whether any accounts are using default passwords. If you’re using an Oracle Database 11g or higher, you can check the DBA_USERS_WITH_DEFPWD view to see whether any Oracle-created user accounts are still set to the default password:

SQL> select * from dba_users_with_defpwd;

If you aren’t using Oracle Database 11g or higher, then you have to check the passwords manually or use a script. Listed next is a simple shell script that attempts to connect to the database, using default passwords:

#!/bin/bash
if [ $# -ne 1 ]; then
  echo "Usage: $0 SID"
  exit 1
fi
# Source oracle OS variables via oraset script.
# See Chapter 2 for more details on setting OS variables.
. /etc/oraset $1
#
userlist="system sys dbsnmp dip oracle_ocm outln"
for u1 in $userlist
do
#
case $u1 in
system)
pwd=manager
cdb=$1
;;
sys)
pwd="change_on_install"
cdb="$1 as sysdba"
;;
*)
pwd=$u1
cdb=$1
esac
#
echo "select 'default' from dual;" | 
  sqlplus -s $u1/$pwd@$cdb | grep default >/dev/null
if [[ $? -eq 0 ]]; then
  echo "ALERT: $u1/$pwd@$cdb default password"
  echo "def pwd $u1 on $cdb" | mailx -s "$u1 pwd default" [email protected]
else
  echo "cannot connect to $u1 with default password."
fi
done
exit 0

If the script detects a default password, an e-mail is sent to the appropriate DBA. This script is just a simple example, the point being that you need some sort of mechanism for detecting default passwords. You can create your own script or modify the previous script to suit your requirements.

Choosing a Username and Authentication Method

Pick a username that gives you an idea as to what application the user will be using. For example, if you have an inventory management application, a good choice for a username is INV_MGMT. Choosing a meaningful username helps identify the purpose of a user. This can be especially useful if a system isn’t documented properly.

Authentication is the method used to confirm that the user is authorized to use the account. Oracle supports a robust set of authentication methods:

• Database authentication (username and password stored in database)
• OS authentication
• Network authentication
• Global user authentication and authorization
• External service authentication

A simple, easy, and reliable form of authentication is through the database. In this form of authentication, the username and password are stored within the database. The password is not stored in plain text; it is stored in a secure, encrypted format. When connecting to the database, the user provides a username and password. The database checks the entered username and password against information stored in the database, and if there’s a match, the user is allowed to connect to the database with the privileges associated with the account.

Another commonly implemented authentication method is through the OS. OS authentication means that if you can successfully log in to a server, then it’s possible to establish a connection to a local database without providing username and password details. In other words, you can associate database privileges with an OS account or and associated OS group, or both.

Examples of database and OS authentication are discussed in the next two sections. If you have more sophisticated authentication requirements, then you should investigate network, global, or external service authentication. See the Oracle Database Security Guide and the Oracle Database Advanced Security Administrator’s Guide, which can be freely downloaded from the Technology Network area of the Oracle web site (http://otn.oracle.com), for more details regarding these methods.

create user heera identified by chaya
default tablespace users
temporary tablespace temp
quota unlimited on users;

SQL> grant create session to heera;

SQL> grant create table to heera;

grant create table, create session
to heera identified by chaya;

SQL> alter system set os_authent_prefix='' scope=spfile;

SQL> create user jsmith identified externally;
SQL> grant create session to jsmith;

$ sqlplus /

SQL> connect inv_mgmt/f00bar

SQL> describe inventory;

SQL> desc inv_mgmt.inventory;

SQL> alter session set current_schema = hr;

Assigning Default Permanent and Temporary Tablespaces

Ensuring that users have a correct default permanent tablespace and temporary tablespace helps prevent issues of inadvertently filling up the SYSTEM tablespace, which could cause the database to become unavailable as well as engendering performance problems. The concern is that when you don’t define a default permanent and temporary tablespace for your database, when you create a user, by default the SYSTEM tablespace is used. This is never a good thing.

As outlined in Chapter 2, you should establish a default permanent tablespace and temporary tablespace when creating the database. Also shown in Chapter 2 were the SQL statements for identifying and altering the default permanent tablespace and temporary tablespace. This ensures that when you create a user and don’t specify default permanent and temporary tablespaces, the database defaults will be applied. The SYSTEM tablespace will therefore never be used for the default permanent and temporary tablespaces.

Having said that, the reality is that you’ll most likely encounter databases that were not set up this way. When maintaining a database, you should verify the default permanent and temporary tablespace settings to make certain they meet your database standards. You can look at user information by selecting from the DBA_USERS view:

select
 username
,password
,default_tablespace
,temporary_tablespace
from dba_users;

Here is small sample of the output:

USERNAME             PASSWORD   DEFAULT_TABLESPACE        TEMPORARY_TABLESPACE
-------------------- ---------- ------------------------- --------------------
JSMITH               EXTERNAL   USERS                     TEMP
MV_MAINT                        USERS                     TEMP
AUDSYS                          USERS                     TEMP
GSMUSER                         USERS                     TEMP
XS$NULL                         USERS                     TEMP

None of your users, other than the SYS user, should have a default permanent tablespace of SYSTEM. You don’t want any users other than SYS creating objects in the SYSTEM tablespace. The SYSTEM tablespace should be reserved for the SYS user’s objects. If other users’ objects existed in the SYSTEM tablespace, you’d run the risk of filling up that tablespace and compromising the availability of your database.

All your users should be assigned a temporary tablespace that has been created as type temporary. Usually, this tablespace is named TEMP (see Chapter 4 for more details).

If you find any users with inappropriate default tablespace settings, you can modify them with the ALTER USER statement:

SQL> alter user inv_mgmt default tablespace users temporary tablespace temp;

You never want any users with a temporary tablespace of SYSTEM. If a user has a temporary tablespace of SYSTEM, then any sort area for which the user requires temporary disk storage acquires extents in the SYSTEM tablespace. This can lead to the SYSTEM tablespace’s filling up. You don’t want this ever to occur, because a SYS schema’s inability to acquire more space as its objects grow can lead to a nonfunctioning database. To check for users that have a temporary tablespace of SYSTEM, run this script:

SQL> select username from dba_users where temporary_tablespace='SYSTEM';

Typically, I use the script name creuser.sql when creating a user. This script uses variables that define the usernames, passwords, default tablespace name, and so on. For each environment in which the script is executed (development, test, quality assurance (QA), beta, production), you can change the ampersand variables, as required. For instance, you can use a different password and different tablespaces for each separate environment.

Here’s an example creuser.sql script:

DEFINE cre_user=inv_mgmt
DEFINE cre_user_pwd=inv_mgmt_pwd
DEFINE def_tbsp=inv_data
DEFINE idx_tbsp=inv_index
DEFINE def_temp_tbsp=temp
DEFINE smk_ttbl=zzzzzzz
--
CREATE USER &&cre_user IDENTIFIED BY &&cre_user_pwd
DEFAULT TABLESPACE &&def_tbsp
TEMPORARY TABLESPACE &&def_temp_tbsp;
--
GRANT CREATE SESSION TO &&cre_user;
GRANT CREATE TABLE   TO &&cre_user;
--
ALTER USER &&cre_user QUOTA UNLIMITED ON &&def_tbsp;
ALTER USER &&cre_user QUOTA UNLIMITED ON &&idx_tbsp;
--
-- Smoke test
CONN &&cre_user/&&cre_user_pwd
CREATE TABLE &&smk_ttbl(test_id NUMBER) TABLESPACE &&def_tbsp;
CREATE INDEX &&smk_ttbl._idx1 ON &&smk_ttbl(test_id) TABLESPACE &&idx_tbsp;
INSERT INTO &&smk_ttbl VALUES(1);
DROP TABLE &&smk_ttbl;

Basic Password Security

When you create a user, if no profile is specified, the DEFAULT profile is assigned to the newly created user. To view the current settings for a profile, issue the following SQL:

select profile, resource_name, resource_type, limit
from dba_profiles
order by profile, resource_type;

Here is a partial listing of the output:

PROFILE      RESOURCE_NAME               RESOURCE LIMIT
------------ --------------------------- -------- ---------------
DEFAULT      CONNECT_TIME                KERNEL   UNLIMITED
DEFAULT      PRIVATE_SGA                 KERNEL   UNLIMITED
DEFAULT      COMPOSITE_LIMIT             KERNEL   UNLIMITED
DEFAULT      SESSIONS_PER_USER           KERNEL   UNLIMITED
DEFAULT      LOGICAL_READS_PER_SESSION   KERNEL   UNLIMITED
DEFAULT      CPU_PER_CALL                KERNEL   UNLIMITED
DEFAULT      IDLE_TIME                   KERNEL   UNLIMITED
DEFAULT      LOGICAL_READS_PER_CALL      KERNEL   UNLIMITED
DEFAULT      CPU_PER_SESSION             KERNEL   UNLIMITED
DEFAULT      PASSWORD_LIFE_TIME          PASSWORD 180
DEFAULT      PASSWORD_GRACE_TIME         PASSWORD 7
DEFAULT      PASSWORD_REUSE_TIME         PASSWORD UNLIMITED
DEFAULT      PASSWORD_REUSE_MAX          PASSWORD UNLIMITED
DEFAULT      PASSWORD_LOCK_TIME          PASSWORD 1
DEFAULT      FAILED_LOGIN_ATTEMPTS       PASSWORD 10
DEFAULT      PASSWORD_VERIFY_FUNCTION    PASSWORD NULL

A profile’s password restrictions are in effect as soon as the profile is assigned to a user. For example, from the previous output, if you’ve assigned the DEFAULT profile to a user, that user is allowed only ten consecutive failed login attempts before the user account is automatically locked by Oracle. See Table 6-1 for a description of the password profile security settings.

You can alter the DEFAULT profile to customize it for your environment. For instance, say you want to enforce a cap on the maximum number of days a password can be used. The next line of code sets the PASSWORD_LIFE_TIME of the DEFAULT profile to 300 days:

SQL> alter profile default limit password_life_time 300;

The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX settings must be used in conjunction. If you specify an integer for one parameter (it doesn’t matter which one) and UNLIMITED for the other parameter, the then current password can never be reused.

If you want to specify that the DEFAULT profile password must be changed ten times within 100 days before it can be reused, use a line of code similar to this:

SQL> alter profile default limit password_reuse_time 100 password_reuse_max 10;

Although using the DEFAULT profile is sufficient for many environments, you may need tighter security management. I recommend that you create custom security profiles and assign them to users, as required. For example, create a profile specifically for application users:

CREATE PROFILE SECURE_APP LIMIT
PASSWORD_LIFE_TIME 200
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1
PASSWORD_REUSE_MAX 1
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1;

After you create the profile, you can assign it to users, as appropriate. The following SQL generates a SQL script, named alt_prof_dyn.sql, that you can use to assign the newly created profile to users:

set head off;
spo alt_prof_dyn.sql
select 'alter user ' || username || ' profile secure_app;'
from dba_users where username like '%APP%';
spo off;

Be careful when assigning profiles to application accounts that use the database. If you want to enforce that a password change at a regular frequency, be sure you understand the impact on production systems. Passwords tend to get hard-coded into response files and code. Enforcing password changes in these environments can wreak havoc, as you try to chase down all the places where the password is referenced. If you don’t want to enforce the periodic changing of the password, you can set PASSWORD_LIFE_TIME to a high value, such as 10,000 or unlimited.

select
name
,to_char(ctime,'dd-mon-yy hh24:mi:ss')
,to_char(ptime,'dd-mon-yy hh24:mi:ss')
,length(password)
from user$
where password is not null
and password not in ('GLOBAL','EXTERNAL')
and ctime=ptime;

Password Strength

A password that cannot be easily guessed is considered a strong password. The strength of a password can be quantified in terms of length, use of upper/lower case, nondictionary based words, numeric characters, and so on. For example, a password of L5K0ta890g would be considered strong, whereas a password of pass would be considered weak. There are a couple schools of thought on enforcing password strength:

• Use easily remembered passwords so that you don’t have them written down or recorded in a file somewhere. Because the passwords aren’t sophisticated, they aren’t very secure.
• Enforce a level of sophistication (strength) for passwords. Such passwords aren’t easily remembered and thus must be recorded somewhere, which isn’t secure.

You may choose to enforce a degree of password strength because you think it’s the most secure option. Or you may be required to enforce password security sophistication by your corporate security team (and thus have no choice in the matter). This section isn’t about debating which of the prior methods is preferable. Should you choose to impose a degree of strength for a password, this section describes how to enforce the rules.

You can enforce a minimum standard of password complexity by assigning a password verification function to a user’s profile. Oracle supplies a default password verification function that you create by running the following script as the SYS schema:

SQL> @?/rdbms/admin/utlpwdmg

The prior script creates the following password verification functions:

• ora12c_verify_function (Oracle Database 12c)
• ora12c_strong_verify_function (very secure Oracle Database 12c)
• verify_function_11G (Oracle Database 11g)
• verify_function (Oracle Database 10g)

Once the password verify function has been created, you can use the ALTER PROFILE command to associate the password verify function with all users to which a given profile is assigned. For instance, in Oracle Database 12c, to set the password verify function of the DEFAULT profile, issue this command:

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

If, for any reason, you need to back out of the new security modifications, run this statement to disable the password function:

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION null;

When enabled, the password verification function ensures that users are correctly creating or modifying their passwords. The utlpwdmgsql script creates a function that checks a password to make certain it meets basic security standards, such as minimum password length and password not the same as username. You can verify that the new security function is in effect by attempting to change the password of a user to which the DEFAULT profile has been assigned. This example tries to change the password to less than the minimum length:

SQL> password
Changing password for HEERA
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8

Keep in mind that it’s possible to modify the code used to create the password verification function. For example, you can open and modify the script used to create this function:

$ vi $ORACLE_HOME/rdbms/admin/utlpwdmg.sql

If you feel that the Oracle-supplied verification function is too strong, or overly restrictive, you can create your own function and assign the appropriate database profiles to it.

Limiting Database Resource Usage

As mentioned earlier, the password profile settings take effect as soon as you assign the profile to a user. Unlike password settings, kernel resource profile restrictions don’t take effect until you set the RESOURCE_LIMIT initialization parameter to TRUE for your database; for example,

SQL> alter system set resource_limit=true scope=both;

To view the current setting of the RESOURCE_LIMIT parameter, issue this query:

SQL> select name, value from v$parameter where name='resource_limit';

When you create a user, if you don’t specify a profile, then the DEFAULT profile is assigned to the user. You can modify the DEFAULT profile with the ALTER PROFILE statement. The next example modifies the DEFAULT profile to limit CPU_PER_SESSION to 240,000 (in hundredths of seconds):

SQL> alter profile default limit cpu_per_session 240000;

This limits any user with the DEFAULT profile to 2,400 seconds of CPU use. You can set various limits in a profile. Table 6-2 describes the database resource settings you can limit via a profile.

You can also create a custom profile and assign it to users via the CREATE PROFILE statement. You can then assign that profile to any existing database users. The following SQL statement creates a profile that limits resources, such as the amount of CPU an individual session can consume:

create profile user_profile_limit
limit
sessions_per_user 20
cpu_per_session 240000
logical_reads_per_session 1000000
connect_time 480
idle_time 120;

After you create a profile, you can assign it to a user. In the next example, the user HEERA is assigned USER_PROFILE_LIMIT:

SQL> alter user heera profile user_profile_limit;

As part of the CREATE USER statement, you can specify a profile other than DEFAULT:

SQL> create user heera identified by foo profile user_profile_limit;

When should you use database profiles? You should always take advantage of the password security settings of the DEFAULT profile. You can easily modify the default settings of this profile, as required by your business rules.

A profile’s kernel resource limits are useful when you have power users who need to connect directly to the database and run queries. For example, you can use the kernel resource settings to limit the amount of CPU time a user consumes, which is handy when a user writes a bad query that inadvertently consumes excessive database resources.

Assigning Database System Privileges

Database system privileges allow you to do tasks such as connecting to the database and creating and modifying objects. There are hundreds of different system privileges. You can view system privileges by querying the DBA_SYS_PRIVS view:

SQL> select distinct privilege from dba_sys_privs;

You can grant privileges to other users or roles. To be able to grant privileges, a user needs the GRANT ANY PRIVILEGE privilege or must have been granted a system privilege with ADMIN OPTION.

Use the GRANT statement to assign a system privilege to a user. For instance, minimally a user needs CREATE SESSION to be able to connect to the database. You grant this system privilege as shown:

SQL> grant create session to inv_mgmt;

Usually, a user needs to do more than just connect to the database. For instance, a user may need to create tables and other types of database objects. This example grants a user the CREATE TABLE and CREATE DATABASE LINK system privileges:

SQL> grant create table, create database link to inv_mgmt;

If you need to take away privileges, use the REVOKE statement:

SQL> revoke create table from inv_mgmt;

Oracle has a feature that allows you to grant a system privilege to a user and also give that user the ability to administer a privilege. You do this with the WITH ADMIN OPTION clause:

SQL> grant create table to inv_mgmt with admin option;

I rarely use WITH ADMIN OPTION when granting privileges. Usually, a user with the DBA role is used to grant privileges, and that privilege isn’t generally meted out to non-DBA users in the database. This is because it would be hard to keep track of who assigned what system privileges, for what reason, and when. In a production environment, this would be untenable.

You can also grant system privileges to the PUBLIC user group (I don’t recommend doing this). For example, you could grant CREATE SESSION to all users that ever need to connect to the database, as follows:

SQL> grant create session to public;

Now, every user that is created can automatically connect to the database. Granting system privileges to the PUBLIC user group is almost always a bad idea. As a DBA, one of your main priorities is to ensure that the data in the database are safe and secure. Granting privileges to the PUBLIC role is a sure way of not being able to manage who is authorized to perform specific actions within the database.

Assigning Database Object Privileges

Database object privileges allow you to access and manipulate other users’ objects. The types of database objects to which you can grant privileges include tables, views, materialized views, sequences, packages, functions, procedures, user-defined types, and directories. To be able to grant object privileges, one of the following must be true:

• You own the object.
• You’ve been granted the object privilege with GRANT OPTION.
• You have the GRANT ANY OBJECT PRIVILEGE system privilege.

This example grants object privileges (as the object owner) to the INV_MGMT_APP user:

SQL> grant insert, update, delete, select on registrations to inv_mgmt_app;

The GRANT ALL statement is equivalent to granting INSERT, UPDATE, DELETE, and SELECT to an object. The next statement is equivalent to the prior statement:

SQL> grant all on registrations to inv_mgmt_app;

You can also grant INSERT and UPDATE privileges to tables, at the column level. The next example grants INSERT privileges to specific columns in the INVENTORY table:

SQL> grant insert (inv_id, inv_name, inv_desc) on inventory to inv_mgmt_app;

If you want a user that is being granted object privileges to be able to subsequently grant those same object privileges to other users, then use the WITH GRANT OPTION clause:

SQL> grant insert on registrations to inv_mgmt_app with grant option;

Now, the INV_MGMT_APP user can grant insert privileges on the REGISTRATIONS table to other users.

I rarely use the WITH GRANT OPTION when granting object privileges. Allowing other users to propagate object privileges to users makes it hard to keep track of who assigned what object privileges, for what reason, when, and so on. In a production environment this would be untenable. When you’re managing a production environment, when problems arise, you need to know what changed, when, and for what reason.

You can also grant object privileges to the PUBLIC user group (I don’t recommend doing this). For example, you could grant select privileges on a table to PUBLIC:

SQL> grant select on registrations to public;

Now, every user can select from the REGISTRATIONS table. Granting object privileges to the PUBLIC role is almost always a bad idea. As a DBA, one of your main priorities is to ensure that the data in the database are safe and secure. Granting object privileges to the PUBLIC role is a sure way of not being able to manage who can access what data in the database.

If you need to take away object privileges, use the REVOKE statement. This example revokes DML privileges from the INV_MGMT_APP user:

SQL> revoke insert, update, delete, select on registrations from inv_mgmt_app;

Grouping and Assigning Privileges

A role is a database object that allows you to group together system or object privileges, or both, in a logical manner so that you can assign those privileges in one operation to a user. Roles help you manage aspects of database security in that they provide a central object that has privileges assigned to it. You can subsequently assign the role to multiple users or other roles.

To create a role, connect to the database as a user that has the CREATE ROLE system privilege. Next, create a role and assign to it the system or object privileges that you want to group together. This example uses the CREATE ROLE statement to create the JR_DBA role:

SQL> create role jr_dba;

The next several lines of SQL grant system privileges to the newly created role:

SQL> grant select any table to jr_dba;
SQL> grant create any table to jr_dba;
SQL> grant create any view to jr_dba;
SQL> grant create synonym to jr_dba;
SQL> grant create database link to jr_dba;

Next, grant the role to any schema you want to possess those privileges:

SQL> grant jr_dba to lellison;
SQL> grant jr_dba to mhurd;

The users LELLISON and MHURD can now perform tasks such as creating synonyms and views. To see the users to which a role is assigned, query the DBA_ROLE_PRIVS view:

SQL> select grantee, granted_role from dba_role_privs order by 1;

To see roles granted to your currently connected user, query from the USER_ROLE_PRIVS view:

SQL> select * from user_role_privs;

To revoke a privilege from a role, use the REVOKE command:

SQL> revoke create database link from jr_dba;

Similarly, use the REVOKE command to remove a role from a user:

SQL> revoke jr_dba from lellison;