This video provides an overview of the process of creating database users, assigning permissions to users, deleting users and understand the concept of database roles.
- [Instructor] Oracle comes built-in with a super user called Sys. Connecting to Sys from SQL Plus requires either adding as sysdba to the postfix, such as sqlplus sys/ the password, which is oracle in my case, and as sysdba. This will open a connection to the Oracle instance as the most powerful sys or sysdba user.
Alternatively, we can just type sqlplus, quotation marks, as sysdba, and be connected to the instance with the sys, sysdba user. We don't need to supply a password specifically for the sys user, only because we are physically logged on to the same server where the Oracle instance is running. And, we are running SQL Plus from the Oracle Linux user, which is the user that started the Oracle instance.
That is the only reason why we can skip the password for such a powerful user when logging on to the database. A connection with sys from any other machine in our network to our database, would have required us to supply the password. Creating additional users in Oracle is also extremely easy. In order to create users, we'll need to first log on as the sys user, as the most powerful user in our database, because that user has the permission to create other users.
So, make sure you run the commands I'm about to show you with the Oracle sys user. So let's create a new database user. For example, let's type create user and type a name such as melissa. Identified by, Identified by, and type a password, such as, you know what, password. Colon, enter, user created.
However, before that user can do anything, even connect to the database, we have to give it permissions. Each operation in Oracle requires a specific permission to be associated with it. For example, if I want to connect to the database using the melissa user I have just created, I'll get an error. Let's quit SQL Plus and type, sqlplus, melissa, password, and enter.
Oh, that user cannot connect to the database. As you can see, Oracle returned the error user melissa lacks the create session privilege, logon denied, so let's fix that. Let's open SQL Plus again, as the sysdba user, and grant the missing permission to our melissa user. So let's type grant, create session to melissa.
There we have it. Let's try and log on with the melissa user again. sqlplus, melissa, password, and now we are able to establish a database connection. However, what do you think will happen if we try to create a table while we are connected as the melissa user? Any bets? Let's give it a try. Create table, test_table1.
Let's give it a single column. Column one, number one. Let's try to create this test table. Oh, we got another error. Insufficient privileges. That's because I've granted the melissa user with a permission to connect the database but not perform any action. So let's fix that as well. I'm quitting SQL Plus and logging back in as the sysdba user. This time providing the melissa user with two additional permissions.
Grant unlimited table space, which will allow the melissa user to use as much table space storage as required and also the create table privilege to melissa. Note that I can provide several privileges in the same grant statement. Let's press enter. Grant succeeded. And let's connect using the melissa user. Note that I can also open an new connection while directly connected to SQL Plus.
I don't have to exit SQL Plus and open it up again every time I want to switch users. Okay, I'm connected with the melissa user. Let's try and run the same command that failed previously. Are you ready? Let's give it a try. Well, there we go. Table created successfully. In Oracle, every user is also known as a schema, so we can reference tables created by that user by prefixing the username to the table name.
Let's reconnect as the sysdba user and type select * from melissa dot. Note, this is the user I have just created, and the name of the table, test_table1. Now the table doesn't have any rows in it yet, so I won't expect to get any results back, but I'm also expecting not to get an error message telling me that the table doesn't exist.
So let's try. Oh, there we go. The select executed successfully, but gave me a message telling me that no rows returned. That's because the table exists, but it is basically empty. Really, really simple. Remember, this is how you can reference tables which belong, which have been created by, one user from another user. If I omit the melissa prefix and just type select * from test_table1, I'll get an error because I am now connected with the sys user and the sys user doesn't own a table called test_table1.
Deleting a user is also super simple. We can just type the drop user melissa command. However, because that user also owns table, I need to also specify a cascade keyword in my command. So I need to type drop user melissa cascade. This will drop the user, but will also delete all of the tables, indexes views, any data with objects, which that user have created.
And before we finish off this video, let me show you another cool thing about users and security in Oracle. Let's open SQL Plus as the sys user again and talk about database roles. We can group multiple privileges together inside what is known in Oracle as a role and grant them to a user. For example, let's create the connect and create role, so we'll type, create role connect_and_create and then grant privileges to that role, such as grant create table to connect_and_create, which is my role name.
Note that Oracle is not case sensitive when it comes to system commands. Let's grant another privilege to the connect_and_create role such as grant create session, which is the Oracle privilege which allows users to connect to the database to connect_and_create. Now, I can grant this role, connect_and_create, to a user, and by doing that, the user will basically have the permissions contained in that role.
As an example, let's grant connect_and_create to our hr user. There we go, very, very straightforward. Remember to use roles often to simplify granting permissions which are typically granted together to users, that's it, that's the concept of users and roles in an Oracle database.
After completing this course, you'll have fundamentals required for installation, configuration, and administration of an Oracle 12c database.
- Database instance and storage
- Instance memory pools
- Instance background processes
- Client connections
- Database storage file types
- Control files and backup files
- Multitenant databases
- Starting and stopping the database
- Installing Oracle 12c software
- Using the developer tools
- Database management