Managing Processes and Users
Diagnosing and resolving availability and performance issues is a key part of every DBA’s job. When troubleshooting problems, DBAs often start by identifying the processes that are currently running and identifying details about users logged on to the server. It is critical to know how to extract process and user activity from the OS.
When you work with OS users and processes, some of your tasks require root privileges. For example, when you install database software on the server, one of the first steps is to create an OS user and group. Depending on your environment, you might not have a system administrator (SA) to perform these operations. In this scenario, you have to log on to the server with root privileges and perform these tasks yourself. Even if you do have an SA, you have to adequately document and communicate with your SA about the database setup tasks that require root privileges. Therefore, it’s important to know which database tasks require root privileges and the commands to execute them.
This chapter starts by showing commands that get information about processes and users. It then progresses to examples of how to access the root account. The chapter wraps up with common database installation tasks that require root privileges, such as adding OS users and groups.
Problem
You want to view processes currently running on the database server.
Solution
To view process information, use the ps (process status) utility. If you type the ps command without any parameters, you see all processes that have been started by the user you’re currently logged on as:
$ ps
PID TTY TIME CMD
12620 pts/1 00:00:00 bash
14103 pts/1 00:00:00 ps
If you want to view all processes running on a box, use the -e and -f options to show every process in a full output format:
$ ps -ef
If many processes are running on a box, it is useful to pipe the output of ps to grep and display only a particular user or process name. For example, to determine which Oracle instances are running on the server, you can filter the output to display only Oracle system monitor (SMON) background processes executing. In this example, ps and grep are used to show any processes that contain the string smon (the grep -v grep removes grep from the output):
$ ps -ef | grep -i smon | grep -v grep
oracle 7994 1 0 Jan25 ? 00:01:43 ora_smon_TRG
oracle 28035 1 0 Feb23 ? 00:00:50 ora_smon_ORA12CR1
The first column indicates that oracle is the OS owner of the processes. The second column contains the process identifier. The fifth column shows that one Oracle instance was started on January 25th, and the other was started on February 23rd. The seventh column indicates that the TRG database SMON process has consumed 1 hour and 43 minutes of CPU time, and the ORA12CR1 database SMON process has consumed 50 minutes. The last column is the name of the process (and the target of the grep command). There are two databases running on this server, as evidenced by two Oracle SMON background processes.
How It Works
Every time you run a Linux/Solaris command, a process is automatically created for you. Each process is assigned a unique number called a process identifier (PID). DBAs use the ps utility for a couple of important reasons:
When there are database connectivity issues, the ps command is useful to quickly identify whether a required database background processes is running. To list all processes for a specific user, use the -u (user) option and specify a username. This example lists all processes running under the oracle user with a full listing of the output:
$ ps -fu oracle
Here’s a snippet of the output:
UID PID PPID C STIME TTY TIME CMD
oracle 7964 1 0 Jan25 ? 00:03:54 ora_pmon_TRG
oracle 7966 1 0 Jan25 ? 00:11:35 ora_psp0_TRG
oracle 7968 1 0 Jan25 ? 08:55:51 ora_vktm_TRG
oracle 7972 1 0 Jan25 ? 00:02:23 ora_gen0_TRG
oracle 7974 1 0 Jan25 ? 00:01:41 ora_mman_TRG
...
When you diagnose database performance issues, it can sometimes be useful to get an overall count of the number of Oracle processes running on a server. Obtaining the overall count of Oracle processes is especially useful when trying to determine whether you have some sort of runaway process that is unnecessarily spawning SQL connections or parallel processes. This example pipes the output of ps to the wc (word count) command. The -l switch instructs wc to count the number of lines in the output:
$ ps -fu oracle | wc -l
84
This output indicates there are 84 Oracle processes running, which is within the normal range for a server running a database. Although there is no black-and-white rule of thumb to tell you how many processes should be running, if the process count is into the hundreds per database, you should investigate what program is starting the processes and determine whether it is normal for that database and application.
Problem
You’re running a database backup job, and you think the process is hung. You want to kill the process.
Solution
The OS PID can be used to terminate a process with the kill utility. In this example, ps is used to show the PID of an RMAN backup job that seems to be hung and needs to be terminated:
$ ps -ef | egrep ’rman|UID’ | egrep -v egrep
Here is some sample output:
UID PID PPID C STIME TTY TIME CMD
oracle 6822 6234 0 11:40 pts/0 00:00:00 rman target /
The PID is 6822 in this example. To terminate that process, issue a kill command, as shown here:
$ kill -9 6822
The -9 option sends a kill signal to the process, which causes it to terminate. You don’t see any output from the kill command because it unceremoniously removes the specified process. Ensure that you don’t kill the wrong Oracle process. If you accidentally kill a required Oracle background process, your instance will abort.
Note To see a list of all available types of kill signals, use the kill -l command.
How It Works
To run the kill command, you either have to own the process or have root privileges. Sometimes it is necessary to use the kill command to terminate unresponsive database processes. For example, you might sometimes need to kill a long-running or hung Oracle process (e.g., RMAN, SQL*Plus, and so on). If you know the process name, you can use the ps command to identify the PID (as shown in the solution section of this recipe).
In some situations, you might not know the OS process name. In these scenarios, you can identify the PID by querying data dictionary views, as shown here:
ACCEPT active DEFAULT ’y’ PROMPT ’Active only processes y/n? [y is default]: ’
SET LINES 200 PAGES 0 HEAD OFF LONG 100000
COL dummy_value NOPRINT
--
SELECT ’dummy_value’ dummy_value,
’USERNAME : ’ || s.username || CHR(10) ||
’SCHEMA : ’ || s.schemaname || CHR(10) ||
’OSUSER : ’ || s.osuser || CHR(10) ||
’MODULE : ’ || s.program || CHR(10) ||
’ACTION : ’ || s.schemaname || CHR(10) ||
’CLIENT INFO : ’ || s.osuser || CHR(10) ||
’PROGRAM : ’ || s.program || CHR(10) ||
’SPID : ’ || p.spid || CHR(10) ||
’SID : ’ || s.sid || CHR(10) ||
’SERIAL# : ’ || s.serial# || CHR(10) ||
’KILL STRING : ’ || ’’’’ || s.sid || ’,’ || s.serial# || ’’’’ || CHR(10) ||
’MACHINE : ’ || s.machine || CHR(10) ||
’TYPE : ’ || s.type || CHR(10) ||
’TERMINAL : ’ || s.terminal || CHR(10) ||
’CPU : ’ || q.cpu_time/1000000 || CHR(10) ||
’ELAPSED_TIME: ’ || q.elapsed_time/1000000 || CHR(10) ||
’BUFFER_GETS : ’ || q.buffer_gets || CHR(10) ||
’SQL_ID : ’ || q.sql_id || CHR(10) ||
’CHILD_NUM : ’ || q.child_number || CHR(10) ||
’START_TIME : ’ || TO_CHAR(s.sql_exec_start,’dd-mon-yy hh24:mi’) || CHR(10) ||
’STATUS : ’ || s.status || CHR(10) ||
’SQL_TEXT : ’ || q.sql_fulltext
FROM v$session s
JOIN v$process p ON (s.paddr = p.addr)
LEFT OUTER JOIN v$sql q ON (s.sql_id = q.sql_id)
WHERE s.username IS NOT NULL -- eliminates background procs
AND NVL(q.sql_text,’x’) NOT LIKE ’%dummy_value%’ -- eliminates this query from output
AND s.status != DECODE(’&&active’,’n’,’xyz’,’N’,’xyz’,’INACTIVE’)
ORDER BY q.cpu_time;
You’ll be prompted about whether you want to see only active sessions displayed in the output:
Active only processes y/n? [y is default]:
Press Enter; here’s some sample output from the previous query:
USERNAME : SYS
SCHEMA : SYS
OSUSER : oracle
MODULE : rman@cs-xvm (TNS V1-V3)
ACTION : SYS
CLIENT INFO : oracle
PROGRAM : rman@cs-xvm (TNS V1-V3)
SPID : 9458
SID : 102
SERIAL# : 49521
KILL STRING : ’102,49521’
MACHINE : cs-xvm
TYPE : USER
TERMINAL : pts/0
CPU :
ELAPSED_TIME :
BUFFER_GETS :
SQL_ID :
CHILD_NUM :
START_TIME :
STATUS : ACTIVE
SQL_TEXT :
From the name of the program in the prior output, you can tell this is an RMAN process executing. The SPID column in the output is the OS PID. Once the SPID is identified, the kill command can be used to terminate the process:
$ kill -9 9458
Caution In some rare situations, killing an Oracle process associated with a SQL transaction can have an adverse impact on the stability of the instance. For example, killing a process participating in a distributed transaction might cause the instance to crash. To determine whether this is an issue for the version of the database you’re using, see MOS bug IDs 8686128 and 12961905. In older versions of Oracle, various other bugs associated with killing a process have been identified and fixed and are documented in MOS bug IDs 5929055 and 6865378.
You can also kill a database connection with the SQL*Plus alter system kill session command by using the session ID (SID) and serial number. Here is the general syntax:
alter system kill session ’integer1, integer2 [,integer3]’ [immediate];
In this syntax statement, integer1 is the value of the SID column, and integer2 is the value from the SERIAL# column (of V$SESSION). In an RAC environment, you can optionally specify the value of the instance ID for integer3. The instance ID can be retrieved from the GV$SESSION view.
As a DBA privileged user, the following command kills the database connection that has an SID of 102 and a serial number of 49521:
SQL> alter system kill session ’102,49521’
If successful, you should see this output:
System altered.
When you kill a session, the session is marked as terminated, active transactions (within the session) are rolled back, and any locks (held by the session) are released. The session will stay in a terminated state until any dependent transactions are rolled back. If it takes a minute or more to roll back the transaction, Oracle reports the session as “marked to be terminated” and returns control to the SQL prompt. Suppose that you specify IMMEDIATE:
SQL> alter system kill session ’102,49521’ immediate;
Oracle will roll back any active transactions and immediately return control back to you.
Caution Killing a session that is executing a select statement is fairly harmless. However, if you terminate a session that is performing a large insert/update/delete, you might see a great deal of database activity (including I/O in the online redo logs) associated with Oracle rolling back the terminated transaction.
3-3. Listing the Users Logged On
Problem
You are experiencing performance problems with your database server. To help diagnose the issues, you first want to view all users currently logged on to the box.
Solution
Use the who command to display the users logged on to a box:
$ who
The output consists of four columns: users logged on, terminal name, time they logged on, and location where they logged on. Here’s a typical listing of the who command:
ptownshend pts/1 Jun 15 14:17 (vpn-229-150-36-51.com)
rdaltrey pts/2 Aug 10 22:11 (122.120.44.181)
jentwistle pts/3 Aug 16 03:14 (111.155.23.114)
kmoon pts/4 Sep 4 01:23 (10.6.77.121)
kjones pts/6 Dec 4 06:66 (101.120.23.171)
You can also use the who command with the am i option to display your current user information:
$ who am i
oracle pts/2 Aug 4 15:30 (vpn-109-150-32-93.brdstn.com)
Tip You can also use whoami or the id -un to display information about your current user. Contrast this with the who am i command, which always shows which user you initially used to log on to a server. For example, if you su to a different user, whoami displays your current user status, whereas who am i shows which user you originally logged on to the server as.
How It Works
The who command is important for listing a snapshot of users logged on to the server. An alternative to the who command is the w utility. This simply titled but powerful tool is an extension to the who command. The output of the w command is like a combination of the listings from the who, uptime, and ps -a commands. This example uses the w command to eavesdrop on who is logged on to the system and what they are doing:
$ w
17:59:54 up 9 days, 5:37, 4 users, load average: 0.00, 0.00, 0.00
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
enehcd pts/1 vpn-128-156-33-6 12:32 5:46 0.12s 0.12s -bash
evork pts/2 vpn-129-156-33-6 15:22 34:14 0.01s 0.01s -chmod
aznoga pts/3 vpn-129-156-32-6 17:22 55:24 0.03s 0.01s -sleep
wroot pts/4 vpn-129-150-150- 17:48 0.00s 0.02s 0.00s w
The first line of the w output is similar to that produced by the uptime command; it shows current time, how long the system has been up, number of users, and system load averages. After the header line, it displays users logged on, from where and what time, how long they’ve been idle, current job CPU (JCPU), foreground process CPU (PCPU), and what command the user is running.
To specifically look at one user, specify the process name as an option. The following command looks at all oracle accounts logged on to the server:
$ w oracle
The following output indicates that there are two active oracle users on the box:
14:14:58 up 130 days, 21:52, 2 users, load average: 0.00, 0.00, 0.00
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
oracle pts/1 63-231-82-100.hl 13:10 0.00s 0.03s 0.00s w oracle
oracle pts/2 63-231-82-100.hl 14:14 6.00s 0.01s 0.01s -bash
If a user has logged on twice to a server (as in the previous output), you can use the tty command to identify a session. Log on to one of the oracle sessions; here is the tty command and its output:
$ tty
/dev/pts/1
You can also use the finger command to display information about users logged on to a server. If you don’t provide the finger command with a username, it will display all users on a system:
$ finger
Login Name Tty Idle Login Time Office Office Phone
oracle pts/0 Jun 20 13:29 (br-ea-fw-nat.surg.com)
oracle pts/1 2 Jun 20 13:29 (br-ea-fw-nat.surg.com)
The pinky command is a lightweight version of the finger command. If no users are specified as a parameter, all users logged on will display:
$ pinky
Login Name TTY Idle When Where
oracle pts/0 Jun 20 13:29 br-ea-fw-nat.surg.com
oracle pts/1 00:03 Jun 20 13:29 br-ea-fw-nat.surg.com
3-4. Listing the Last Logon Time of a User
Problem
You think that somebody might have hacked into the database OS account, so you want to see when the last time the oracle account was logged on to the database server.
Solution
Use the last command to determine a user’s last logon time. This bit of code determines the last time the oracle account logged on to the database server:
$ last oracle
oracle pts/1 63-227-41-191.hl Fri Dec 21 17:18 still logged in
oracle pts/1 63-227-41-191.hl Mon Dec 17 15:55 - 17:53 (01:58)
oracle pts/1 63-227-41-191.hl Mon Dec 17 11:55 - 13:33 (01:38)
oracle pts/1 63-227-41-191.hl Mon Dec 17 08:28 - 10:45 (02:17)
oracle pts/0 63-227-41-191.hl Sat Dec 15 20:59 - 22:00 (01:00)
oracle pts/0 63-227-41-191.hl Sat Dec 15 20:43 - 20:59 (00:15)
The output indicates that the oracle user is currently logged on and has logged on several times in the month of December.
How It Works
If you use the last command without any arguments, it displays the last logon time of all users. You can pipe the output of last to the less command to display one page at a time:
$ last | less
You can also limit the output of last by piping its output to the head command. The following will display the last ten users logged on:
$ last | head
Tip On Linux servers, the last command retrieves its information from the /var/log/wtmp file, which records all logons and logouts on the server. On Solaris servers, the /var/adm/wtmpx file is used to record logons and logouts.
Another useful command in regard to last logons is the lastb utility. This useful command displays a list of recent bad logon attempts. When first using lastb, you might see the following output:
lastb: /var/log/btmp: No such file or directory
Perhaps this file was removed by the operator to prevent logging lastb info.
The previous message means that the /var/log/btmp file needs to be created. You can enable lastb by running the following commands as root (# indicates that the command is run as root):
# touch /var/log/btmp
# chown --reference=/var/log/wtmp /var/log/btmp
# chmod --reference=/var/log/wtmp /var/log/btmp
3-5. Limiting the Number of User Processes
Problem
For security purposes, your database installation instructions indicate that you need to limit the number of processes that can be started by the database OS account. By limiting the number of processes a user can start, you ensure that no single user can consume inordinate amounts of resources on the server.
Solution
On a Linux system, as the root user, add an entry in the /etc/security/limits.conf file that restricts the number of concurrently running processes for an OS account. The following lines of code (in the limits.conf file) establish a soft limit of 2,047 and impose a hard limit of 16,384 processes for the oracle OS user:
oracle soft nproc 2047
oracle hard nproc 16384
A soft limit enforces a maximum of 2,047 processes that can be running simultaneously by the oracle OS user. The oracle user can manually override the soft limit (with the ulimit command) and increase the limit on the number of processes up to 16,384 processes. Only the root user can modify the hard limit to be higher.
Tip See Chapter 9 for full details on configuring oracle shell limits.
To limit the number of processes on a Solaris system, set the maxusers parameter in the /etc/system file. This parameter doesn’t set a hard limit for the number of processes; instead, it is used by Solaris to derive the overall number of processes allowed. The formula Solaris uses to calculate the maximum number of processes varies by release.
How It Works
For performance and security reasons, you might want to impose a limit on the number of processes allowed on a server. If you’re feeling brave, you can test the maximum number of processes allowed by running this function:
: () { :|:& };:
The previous bit of code creates a : function that recursively calls itself and puts its processes in the background. Be warned that you can lock up your system if you don’t properly have process limits in place. This type of program is known as a fork bomb. It is appropriately named because it continuously creates (forks) new processes on the system. Because the program starts itself in the background, it cannot be stopped by pressing Ctrl+C.
3-6. Viewing How Long the Server Has Been Running
Problem
You want to know how long the server has been running.
Solution
Use the uptime command to determine how long your database server has been running:
$ uptime
08:37:00 up 33 days, 16:14, 1 user, load average: 0.00, 0.00, 0.00
This output shows that this server has been up for a little more than 33 days.
How It Works
When resolving database availability issues, it is sometimes helpful to know when the server was last rebooted because (not surprisingly) there is a direct correlation between the server being down and the database being unavailable. Viewing the server uptime can help determine whether the database was unavailable because of a system reboot.
Interestingly, the output of uptime is identical to the first line of the w command. The next example shows running the w command and the corresponding output:
$ w
08:37:01 up 33 days, 16:14, 1 user, load average: 0.00, 0.00, 0.00
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
oracle pts/0 63-227-41-191.hl 07:11 0.00s 0.06s 0.00s w
In the first line of the output, 08:37:01 is the current time on the server. Also displayed in the first line are load averages for the past 1, 5, and 15 minutes.
3-7. Viewing How Long a Process Has Been Running
Problem
You wonder how long the Oracle database background processes have been running.
Solution
Use the ps -ef command to determine how long a process has been running. This line code uses ps with the egrep command to find out when the Oracle system monitor process was started:
$ ps -ef | egrep ’smon|UID’ | egrep -v egrep
UID PID PPID C STIME TTY TIME CMD
Oracle 8843 1 0 Oct31 ? 00:02:33 ora_smon_RMDB1
In this command, the process status output is filtered by egrep to list any lines that contain either smon or UID. When you filter also for the UID string, the header of the ps command displays. In the output, the SMON process was started on October 31 and has consumed 2 hours and 33 minutes of CPU on the database server.
How It Works
When troubleshooting database problems, you might want to know when your database was last started. For example, you might have had an unexpected server reboot, which caused the databases to stop and restart. You can verify the time your database process was started by using the ps command.
The following is another slight variation of how DBAs use the process status command:
$ ps -ef | grep smon | grep -v grep
oracle 8843 1 0 Oct31 ? 00:02:33 ora_smon_RMDB1
This command limits the output to just the specific process that you’re interested in observing. The grep -v grep code strips out any lines containing the string grep from the output.
3-8. Displaying Your Username
Problem
You want to display your OS username.
Solution
Use the id command to display the OS account you’re current logged on as:
$ id
Listed next is a sample of output. By default, both the user and group information are displayed:
uid=56689(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)
Tip Use the groups command if you just want to display the groups of your current OS account.
How It Works
An effective DBA has to be good at multitasking. You’ll find yourself logged on to multiple boxes in a myriad of development, test, and production environments. It is easy to lose your bearings; when you do, you’ll want to identify the OS account you’re currently using. You can instinctively fulfill this need with the id command.
You can also use the who command with the am i option to display the user you used to log on to the box:
$ who am i
oracle pts/2 Aug 4 15:30 (vpn-109-150-32-93.brdstn.com)
You can also use whoami or id -un to display information about your current user; the who am i command shows information about the user you used to originally log on to the box. For example, if you initially log on to a box as oracle and then switch to the root user, the whoami command will display root, whereas the who am i command will display oracle:
# who am i
oracle pts/2 Jun 20 14:20 (br-ea-fw-nat.surg.com)
# whoami
root
# id -un
root
3-9. Changing Your Password
Problem
You have just been handed a new database server and want to change your password to something more secure than changeme.
Solution
Use the passwd command to change your password. Log on to the user account you want to change the password for and type passwd with no options:
$ passwd
After you type the passwd command, you are prompted to type the current password and then to enter the new password twice:
Changing password for user oracle.
Changing password for oracle
(current) UNIX password:
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
How It Works
As a DBA, you manage passwords for various OS accounts on the database servers that you log on to. Use the passwd command to ensure that your database OS user is secure and available for your use.
If you have access to the root user, you can change passwords for other users. This example changes the password for the oracle OS user:
# passwd oracle
You are then prompted to type a new password for the oracle OS user:
Changing password for user oracle.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
To set up additional password security, use the chage program. This utility can be used to set the maximum amount of time that a password is valid. This example is run as the root user specifies that the oracle user will have to change its password after 60 days:
# chage -M 60 oracle
To verify the changes to the oracle user, use the -l option:
# chage -l oracle
Last password change : Mar 08, 2015
Password expires : May 07, 2015
Password inactive : never
Account expires : never
Minimum number of days between password change : -1
Maximum number of days between password change : 60
Number of days of warning before password expires : -1
The chage utility is part of the Shadow Password Suite. This suite includes programs such as chfn, chpasswd, chsh, dpasswd, expiry, faillog, and so on. You can add this group of programs to your system to provide extra security. These utilities allow you to encrypt user passwords in the /etc/shadow file, which is readable only with root privileges.
3-10. Becoming the System Privileged (root) User
Problem
You’re installing Oracle on a database server, and an SA is not available. You need to become root (sometimes referred to as the superuser) so that you can add new OS groups and user accounts.
Solution
You need the root user password for this recipe to work. Use the su - command to switch to the root account. The - (hyphen) option specifies that the target user’s login shell will be invoked (it runs all login scripts of the target user):
$ su - root
Or you can simply do this:
$ su -
You should now see a prompt similar to this:
Password:
After a successful logon, your shell prompt will change to the # character, indicating that you are now logged on as root:
#
You can now perform such tasks as adding the groups and users required for the database installation. To exit from the root user, type the following:
# exit
Note You can also press Ctrl+D to exit a shell login.
How It Works
Some database installation tasks require root privileges. Sometimes an SA isn’t available to perform these tasks, or your SA might be comfortable with temporarily providing you access with the root account. However, competent SAs rarely give out the root password to DBAs (or any non-SA users, for that matter). There are several problems with providing carte blanche superuser access:
If several people have direct access to the root password, and one of them logs on to the system and accidentally removes critical files, it is hard to tell who did the damaging deed. For this reason, SAs do not usually provide direct root access. Instead, they provide auditable and/or limited access to root privileges through the sudo command (see recipe 3-11 for details).
3-11. Running Commands as the root User
Problem
You want access to commands that can be run only by root, but your security-conscious SA won’t give you the root password.
Solution
Have your SA insert the appropriate entries in the /etc/sudoers file to grant access to restricted commands. You can be granted complete root access or access to only specified commands.
As the root user, use the visudo command to add an entry to the /etc/sudoers file. The visudo command ensures that the /etc/sudoers file is edited in a secure fashion. When using visudo, the sudoers file is locked to disallow multiple users from simultaneously editing the file. The visudo command also performs a syntax check on any edits to the /etc/sudoers file and saves only correct entries.
For example, the SA can provide root access to the oracle account by adding the following line to the /etc/sudoers file:
oracle ALL=(ALL) ALL
The oracle account can use sudo to execute commands (that would otherwise be required to run as the root user). For example, the groupadd command can now be run by oracle as follows:
$ sudo /usr/sbin/groupadd dba
The first time you run sudo, you are prompted for your password (not the root password). For a short period of time (5 minutes by default), you can run sudo without being prompted for your password again.
You can also specify that only certain commands can be run with root privileges. For example, if your SA wanted to limit the oracle account to the commands that add groups and users, the /etc/sudoers entry would be as follows:
oracle ALL=/usr/sbin/groupadd,/usr/sbin/useradd
This method allows your SA to limit access to specific commands that require root privileges. You can view the commands you are allowed to run as sudo with the following command:
$ sudo -l
The following output shows that this server has been configured to allow the oracle account to run the following commands:
User oracle may run the following commands on this host:
(root) /usr/sbin/groupadd, (root) /usr/sbin/useradd
How It Works
The sudo command allows a command to be executed as the root user. The sudo permissions are maintained in the /etc/sudoers file by the SA.
One compelling reason for using sudo is that it allows SAs to grant superuser access without having to give out the root password. This allows SAs to temporarily grant root access to consultants who might have short-term assignments and require root access. SAs can also quickly revoke root access by deleting the appropriate entry from the /etc/sudoers file without affecting other users.
Another advantage of using sudo is that it provides an audit trail. An entry is written to a system log file in the /var/log directory whenever a sudo command is issued. Additionally, you can specify a log file for sudo activity by placing the following line in the /etc/sudoers file:
Defaults logfile=/var/log/sudolog
After successfully adding the previous line to the /etc/sudoers file, all commands run as sudo are logged on to /var/log/sudolog.
3-12. Adding a Group
Problem
You’re performing a database installation and need to add an OS group.
Solution
Use the groupadd command to add OS groups. Typical Oracle installations require that you add two groups: oinstall and dba. If you have root access, you can run the groupadd command as shown here:
# groupadd oinstall
# groupadd dba
If you don’t have access to a root account, your SA has to run the commands in this recipe. Sometimes SAs are amenable to granting root access via the sudo command (see recipe 3-11 for details).
If you have a company requirement that a group be set up with the same group ID on different servers, use the -g option. This example explicitly sets the group ID to 505:
# groupadd -g 505 dba
Sometimes it’s desirable to consistently create groups with the same group ID across multiple servers. For example, in Network File System (NFS) environments, you might encounter permission problems unless the group is set up with the same group ID across multiple servers.
How It Works
Sometimes DBAs are required to perform system administration tasks such as adding (or deleting) users and adding (or deleting) groups because an SA might not be available in some environments. Or you might be installing database software on your workstation and you are the SA. Regardless of the situation, you should be familiar with these types of tasks so that you can build and maintain your database server.
You can verify that the group was added successfully by inspecting the contents of the /etc/group file. Here are the entries created in the /etc/group file after running the commands in the “Solution” section of this recipe:
oinstall:x:500:
dba:x:501:
If you need to remove a group, use the groupdel command (see recipe 3-13 for details). If you need to modify a group, use the groupmod command.
Tip On Linux systems, if you have access to an X Window terminal, you might want to investigate using the system-config-users utility. This graphical tool allows you to add and delete users and groups.
3-13. Removing a Group
Problem
You want to clean up a database server and remove an old dba OS group.
Solution
Use the groupdel command to remove OS groups. This command requires root access. The following command deletes the dba group:
# groupdel dba
If you don’t have access to a root account, your SA has to run the commands in this recipe. Sometimes SAs are willing to grant root access via the sudo command (see recipe 3-11 for details).
How It Works
You will not be prompted to confirm whether you really want to delete a group, so make certain you really want to delete a group before using the groupdel command. You can view the /etc/group file to verify that the group has been deleted.
Note You cannot remove a group that is a user’s primary group. You must first modify or delete the users who have the group to be deleted (so that the group isn’t the primary group of any user on the system).
3-14. Adding a User
Problem
You’re doing a database installation and need to create an OS user.
Solution
Use the useradd command to add OS users. This command requires root access. The following command creates an OS account named oracle, with the primary group being oinstall, and the dba group specified as a supplementary group:
# useradd -g oinstall -G dba oracle
If you don’t have access to a root account, your SA has to run the commands in this recipe. Sometimes SAs are agreeable to granting root access via the sudo command (see recipe 3-11 for details).
If you have a company requirement that a user be set up with the same user ID across multiple servers, use the -u option. This example explicitly sets the user ID to 500:
# useradd -u 500 -g oinstall -G dba
Sometimes it’s desirable to consistently create a user with the same user ID on different servers. For example, in NFS environments, you might encounter permission problems unless the user is set up with the same user ID on all servers.
How It Works
Occasionally as a DBA you will perform system administration–type tasks such as adding (or deleting) users and adding (or deleting) groups. In some environments, an SA might not be available. Or you might be installing database software on your workstation and you are the SA. Regardless of the situation, you should be familiar with these types of tasks so that you are better able to build and maintain your database server.
You can verify user account information by viewing the /etc/passwd file. Here is what you can expect to see after running the useradd command in the “Solution” section of this recipe:
oracle:x:500:500::/home/oracle:/bin/bash
During the process of creating a new user, the /etc/skel directory contains the files and directories that are automatically created for new users added to the server with the useradd command. Typical files included are .bashrc, .bash_profile, and .bash_logout. The location of the /etc/skel directory can be changed by modifying the value of SKEL in the /etc/default/useradd file.
The userdel command can be used to delete a user (see recipe 3-15 for details). Use the usermod command to modify existing accounts.
Tip If you have access to an X Window terminal, you might want to investigate using the system-config-users utility. This is a graphical tool that allows you to add and delete users and groups.
3-15. Removing a User
Problem
You want to clean up a server and remove an old oracle OS account.
Solution
Use the userdel command to remove an OS account. You need root privileges to run the userdel command. This example removes the oracle user from the server:
# userdel oracle
If you don’t have access to a root account, your SA has to run the commands in this recipe. Sometimes SAs are open to granting root access via the sudo command (see recipe 3-11 for details).
How It Works
You will not be prompted to confirm whether you really want to delete a user, so make certain you absolutely want to remove a user before using the userdel command. You can view the /etc/ passwd file to verify that the user has been deleted (by the absence of the user).
You can also instruct the userdel -r command to remove the user’s home directory and any files in that location. This example removes the oracle account and its home directory:
$ userdel oracle -r
Before you use this command, make sure the user doesn’t need any of the files in his or her home directory tree ever again. You can use a command such as tar to do a quick backup of the files in a user’s home directory (see Chapter 6 for details).