Instructor's Notes
(If applicable)
- Lesson 5: Administrative Users
- In essence, this lesson answers the question, "What is an administrative user?". You will learn about the supplied users (SYS and SYSTEM), common administrative privileges, connecting to the database as an administrator and creating new administrators.
- Let’s get started…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The CREATE DATABASE command creates and defines the password for two administrative users: SYS and SYSTEM. (We will study CREATE DATABASE in the lesson Creating a New Database later in this course.)
- SYS is an administrative user (i.e. by default it has been granted the DBA role and the SYSDBA privilege) but SYS is also very special. It owns the Oracle data dictionary tables and views. For example, SYS.OBJ$ and DBA_TABLES. In part because of this, we are better off not using SYS for our day-to-day administrative tasks (we might drop a critical dictionary object and corrupt the database). It is easy to create additional users and grant proper administrative privileges to them.
- The password for the SYS user is defined on the CREATE DATABASE command. Use caution when granting ALTER USER privilege; any user with ALTER USER privilege can change the password and login to SYS.
- When logging in with SYS, you must supply the ‘AS SYSDBA’ keywords. Note that SYSDBA cannot be revoked from SYS. We’ll discuss the meaning of SYSDBA later in this lesson.
- Supplemental Notes
- The CREATE DATABASE command actually executes a script called SQL.BSQ. In this file you will find two CREATE USER commands, one for SYS and one for SYSTEM. Do Not Change The Contents Of This File.
Other Text:
(Examples or comments displayed on slide, if any).
Protect the SYS user!
Must login 'as sysdba’
SQL> connect sys/class as sysdba
Connected.
Instructor's Notes
(If applicable)
- The SYSTEM user is the standard, default administrative userid created at database creation time. SYSTEM is by default granted the DBA role (more on this later in this lesson) and, in most environments, the SYSDBA privilege. The password is set on the CREATE DATABASE command.
Other Text:
(Examples or comments displayed on slide, if any).
Protect the SYSTEM user!
Instructor's Notes
(If applicable)
- SYSDBA is a special administrative privilege. More accurately, it is a group of privileges – though not technically a role. (We will learn more about roles in the Managing Security lesson later in this course.)
- SYSDBA contains, among other privileges, the privilege to startup and shutdown the database. (For those learners who have used previous versions of Oracle, note that the "INTERNAL" connection was desupported as of Oracle9i.)
- User SYSTEM has SYSDBA privilege by default. (So does SYS, but, since SYS owns system objects like the data dictionary, it is best not to use SYS – use SYSTEM.)
- The unique thing about SYSDBA is that you must explicitly specify "AS SYSDBA" in the connect string for the privilege to be active during the session. This is demonstrated in the example above.
- Notes for this slide continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
SQL> connect system/dave as sysdba
Connected.
SQL> show user
USER is "SYS"
Any objects created will be owned by SYS
Instructor's Notes
(If applicable)
- Continued from the previous page:
- After connecting with “/ AS SYSDBA” or “userid AS SYSDBA”, you will be operating in the SYS schema (even if the user name is, for example “dave”). This means that if you create a table with “CREATE TABLE X …”, the fully qualified table name will be SYS.X.
- Here is a complete list of privileges within the SYSDBA privilege:
- SYSDBA Privileges
- All system privileges with the ADMIN option
- CREATE DATABASE privilege
- Time-based database recovery
- Startup and Shutdown privileges (SYSOPER privilege also has this privilege*)
- Mount or open database (SYSOPER privilege also has this privilege)
- Backup database (SYSOPER privilege also has this privilege)
- Initiate archival of redo logs (SYSOPER privilege also has this privilege)
- Database recovery (SYSOPER privilege also has this privilege)
- Altering database to RESTRICTED SESSION mode (SYSOPER privilege also has this privilege)
- *SYSOPER is a restricted, scaled-down version of SYSDBA.
- SYSOPER Privileges
- startup/shutdown
- Mount or open database
- Backup database
- Initiate archival of redo logs
- Database recovery
- Altering database to RESTRICTED SESSION mode
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Oracle provides several predefined roles (collections of privileges) for our convenience. One of these roles is the "DBA" role.
- The DBA role is, by default granted to the SYS and SYSTEM users. It contains all system-level privileges (e.g. CREATE TABLE) with administrator rights. An administrator of a privilege can grant or revoke the privilege to and from any user – even a user who granted you the privilege in the first place. You truly are "an administrator of the privilege." We will study privileges in more detail in the Managing Security lesson later in this course.
- Note that the Oracle9i SQL Reference manual (see the GRANT statement) recommends that you create your own administrator role and warns that the DBA role may not be supported in future releases.
Other Text:
(Examples or comments displayed on slide, if any).
Role is a collection of privileges
Instructor's Notes
(If applicable)
- If you are connected locally to the server (either you are sitting in front of the server or you are Telnet’ed into it), and your operating system (OS) user is in the operating system ORA_DBA group in Unix (or Linux) or has administrative privileges in Windows (check Control Panel / Users and Passwords), you can login without an Oracle password. The “AS SYSDBA” syntax is required as shown above. You can actually connect with any userid, since the AS SYSDBA will always connect you as SYS (SHOW USER in SQL*Plus will display SYS when you connect to any userid AS SYSDBA).
- As the example demonstrates, a local user connected as SYSDBA can shutdown the database. The user can also start the database with the STARTUP Command. We will study the STARTUP and SHUTDOWN commands in the Startup and Shutdown Instance lesson later in this course.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
Instructor's Notes
(If applicable)
- If you need to login remotely, you will need to create a password file and specify the host string and “AS SYSDBA” as shown in the example above. The host string is defined in the following file:
- $ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA
- Host strings will be discussed in more detail in the lesson entitled Introduction to Oracle Networking later in this course.
- Turn to the next page for a discussion of password files.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> connect system/dave@laptop as sysdba
Connected.
SQL>
Instructor's Notes
(If applicable)
- Before we cover the details regarding creating password files, let’s review from the lesson on Architecture: Password files are operating system files used by Oracle to authenticate administrative users (SYSDBA) attempting to connect over a network.
- Most databases – especially those created with the Oracle Database Configuration Assistant (DBCA) – already have a password file. However, if your database does not have a password file or you need to recreate the file, three simple steps are required.
- Errors will be returned if you attempt operations that require a password file. For example, attempting a remote connection as SYSDBA:
- SQL> connect system/dave@laptop as sysdba
- ERROR:
- ORA-01031: insufficient privileges
- Notes continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Or for example, attempting to grant SYSDBA to a user:
- SQL> grant sysdba to dave
- 2 /
- grant sysdba to dave
- *
- ERROR at line 1:
- ORA-01994: GRANT failed: cannot add users to public password file
- Looking at the setting for the REMOTE_LOGIN_PASSWORDFILE initialization parameter reveals that a password file is not being used:
- SQL> show parameter remote_login_passwordfile
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Three steps are required to create (or recreate) and implement a password file:
- Create the password file with the ORAPWD utility. This is an operating system utility. Note in the example above the command is prefaced with a dollar sign (synonym for the HOST command) to send the command to the operating system. The location (path) and file name are specific and fixed – you must use the proper location for your server and operating system. For a Windows environment, use:
$ORACLE_HOME\DATABASE\PWDINSTANCE_NAME.ORA
The password becomes the password for the SYS user. The "entries" parameter specifies the maximum number of users that can have the SYSDBA privilege. You will need to recreate the file if this is exceeded.
- Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to "exclusive". The example above accomplishes this by changing the server parameter file (SPFILE) with the ALTER SYSTEM command. Don’t worry about the details of this command now; we will cover this in detail later in the lesson Initialization Parameter Files.
- Lastly, in order for the change to the server parameter file to take effect, you will need to stop and restart the database.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> $orapwd file=C:\oracle\ora92\database\PWDdave.ora password=class entries=25
SQL> alter system
2 set remote_login_passwordfile=exclusive
3 scope=spfile;
System altered.
SQL> shutdown
ORACLE instance shut down.
SQL> startup
Instructor's Notes
(If applicable)
- You can check the contents of the password file – and thus see who has SYSDBA privilege – by querying the V$PWFILE_USERS view.
- The example above also demonstrates that by granting SYSDBA to user SYSTEM, we are actually adding an entry to the password file.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Creating additional administrative users is reasonably simple:
- Login with an existing administrative userid . Use an AS SYSDBA connection if you want to grant the new user SYSDBA privilege.
- Use the CREATE USER command to create the user and set the password with the IDENTIFIED BY clause.
- Grant the user privileges. This example grants DBA for general day-to-day administration tasks and SYSDBA so that the user can startup and shutdown the database.
- Note that both the DBA role and the SYSDBA privilege have the ability to connect to the database (i.e. they have CREATE SESSION privilege). The example demonstrates that the user can connect as SYSDBA or without SYSDBA.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Administrative Users Workshop
- Login to Oracle with the SYS user.
- Login to Oracle with SYSTEM user with the SYSDBA privilege.
- Execute the SHOW USER command. What user is returned?
- If you are working with a local connection to your Oracle server, login to the database with a local operating system connection. If problems occur, check which OS users are part of the ORA_DBA OS group. If you have only a remote connection, connect as necessary and go on to the next step.
- Determine which users have been granted SYSDBA.
- Create a new user. Use your first name as the user name and password.
- Workshop continues on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Workshop continued from the previous page…
- Grant SYSDBA to the new user.
- Attempt to connect to the new user without the SYSDBA privilege. What happens?
- Attempt to connect to the new user with the SYSDBA privilege. What happens?
- Grant the DBA role to the new user.
- Connect to the database with the new user – without SYSDBA
Other Text:
(Examples or comments displayed on slide, if any).
