From the course: Oracle Database 12c: Security

Users, schemas, and the SYSDBA - Oracle Database Tutorial

From the course: Oracle Database 12c: Security

Start my 1-month free trial

Users, schemas, and the SYSDBA

“

- [Instructor] Before we get into all of the exciting hands-on demos of this chapter, there is one very unique concept we have to understand when it comes to the Oracle database. That concept is the unique relationship between Oracle database users and Oracle database schemas. This relationship is unique and special because in Oracle, users and schemas are essentially the same thing. Consider an Oracle user as the account you use to connect to the database with and a database schema as the set of objects that is tables, views, procedures and so on that belong to that account. You cannot create schemas and users separately. When you create a database user, you also create a database schema with the same name. So when you run the CREATE USER command in Oracle, which we'll see later in our chapter, you create a user that will be used for logging in to the database and a schema which will be used to store database objects in. The user that will be created will be used for database login and the schema which will initially be created empty will be used for objects such as tables, views, procedures and so on. There is no way to separate users and schemas in Oracle. Even if you wanted to, you cannot just create a user account without a schema or a schema without a user account. This relationship will make more sense as we continue the hands-on examples of this chapter. Also, be aware that Oracle comes with several built-in users and schemas such as SYS, SYSTEM, SYSMAN and Others. Some of these schemas serve special purpose that enable certain Oracle features such as the CTXSYS user schema that power the Oracle full text search capabilities or the DBSNMP account that is used by the management agent component of Oracle's enterprise manager to monitor and manage the database. These users are outside a scope of our course. However, it is important to take a couple of minutes to discuss the SYS and SYSTEM users that come built-in with the Oracle database. These are Oracle's superusers that are used to perform administrative tasks in the database. When you create an Oracle database, the SYS user and schema are automatically created and granted the DBA role. This is the most powerful role in the database. The Oracle database data dictionary, which is the core internal tables that power the Oracle database, are owned and stored in the SYS schema. The SYS user is so powerful that as a safeguard when using it to connect to the Oracle database, you will also have to specify a special administrative privilege clause as part of the connection string. This special clause is the SYSDBA role that you'll have to explicitly specify when you log in to the database with the SYS user. So you add AS SYSDBA to your login command. We will see a hands-on example of opening a SYSDBA connection to the database in our next video. For now, just be aware that certain administrative database action, this requires superuser privileges, will require you to log in as the SYS user and use the AS SYSDBA parameter in your connection stream. For example, when connecting to an Oracle database instance that is down. Another more restricted role that exists is the SYSOPER role but the differences between the two are outside the scope of this course and well SYSOPER is rarely used. You cannot connect to the database as the all powerful SYS user who's out specifying AS SYSDBA or AS SYSOPER. Similarly to the SYSTEM user is also automatically created during database setup and granted a DBA role. The Oracle SYSTEM user create additional tables and views that display administrative information. From a security standpoint, make sure that you and only you, the DBA, know the passwords for the SYS and SYSTEM users. Never give these users or password to regular users inside your organization.

Contents