Hi. In this lesson, we understand how to deal with security in Oracle. Security is provided at system level, and object level. system level security deals with users and privileges to users. First of all, every user must have a valid username and password to log into Oracle. But that's not enough.

You also need to have a set of privileges called system privileges. And these privileges enable user to perform some operations at the system level. Like create session privilege allows you to create a session so you can connect it to Oracle, CREATE TABLE privilege allows you to create a table and so on. These privileges are granted to users by DBA They might be granted directly to the user, or they might be granted through roll. Roll is a collection of privileges. We'll see more about role little later.

But our focus is on object privileges. These privileges allow you to perform operations on objects, like tables, views sequences. We also have objects such as procedures and functions, which we will discuss later in PL SQL. But for now, object privileges, like select Insert delete update allows you to perform those operations on the objects. to grant a privilege on an object, we need to use a command called grant. But not all privileges are applicable to all objects.

As the table here shows, all the privileges are applicable to table, but only a few are applicable to view. For example, there is nothing like creating index on view are using a view in the context of foreign key is nothing like deleting from sequence. So, the table shows which privileges are applicable to what kind of object. If you want to grant a privilege, you need to use grant command, which is also belonging to a category called dcl data control language commands. The grant command is granting one or more privileges to either a single user or even all the users are to a role. You can give a list of users also if you like the first example here Are shows we are granting select privilege on employees table to a user called demo.

We assume we are in HR account, and demo is some other account. The second example shows how to grant select and delete on job history to demo. But we are also allowing demo to grant these privileges to some other users. So demo not alone uses the privileges he can also pass them on to other users if he wants. That's what with grant option is mentioning. It ellos demo to grant those privileges to other users.

The last example is granting update privilege on employees to demo, but it is granting the privilege only on one column. So demo can update hire date column of employees table, nothing else. These are the examples for grant. You can also grant to multiple users. Or you can grant to public. It means you're granting to every user in the database.

Or you can grant to a roll. Because roll is a collection of privileges. This is how the grantee is going to use our permission. When Hatcher is granting a privilege to demo, we call HR as grantor, and demo as grantee, grantee should always access the object by using the grantor as a prefix. grantor in this case is also the owner of the object. So to be more precise, grantee should use not the grantor but the owner of the option HR is the owner here.

So demo should say select star from HR dot employees. And if he's not using HR as a prefix, or Akela assumes employees table is in demo account, and it throws an error saying table or view does not exist. Well, to make this process a little easier, grantee can also create what is called as synonym. grantee can create a synonym on HR dot employees. That is EMP here so that he can use that instead of always using prefix followed by the object name. Just to make things a little more simplified, we can create a synonym for objects that belong to others and use those synonyms instead of the actual names.

Well, anytime if grantor wants to revoke the privilege he can all that he needs to give us another dcl command revoke and also revoking a privilege is cascading. So if HR grants a privilege to demo demo is granting privileges to Scott, when HR revokes the privilege from demo, Scott automatically lose this privilege. That means all the grantees who got the privilege from demo will automatically lose the privilege because the main grantee that his demo himself is losing the privilege. So revoking a privilege is cascading means you remove the privilege, not just from the main grantee, also all subsequent grantees. The first example is revoking. Select on employs from demo.

The second one is revoking references privilege one more privilege, which ellos the grantee to use this table and column in foreign key reference, we are revoking that privilege, but that privilege is used to create foreign key. And when you revoke the privilege, the foreign key constraint should also go and if foreign key constraint is not removed, then it is not valid. So revoke the privilege and cascade constraints means, remove all the constraints that were created using the privilege that was earlier granted, by revoking the privilege. We are just taking out the permission or the privilege by saying cascade constraints. We are saying we want to delete All constraints which were created using the privileges granted, then there is a concept called role, which is not available to normal users. I mean, as a normal user, you can't create a role.

But DBAs use roles to manage security. A role is a collection of privileges. So I can create a role, and then grant a lot of privileges to role. And when I grant a role to a user, all the privileges that are there in the role are made available to that user. So a role is a very important object in the context of security implementation. Because you can grant as many privileges as you want to enroll.

And when role is granted to user, user gets all those privileges, and moreover, enroll is also dynamic. That means you can add more privileges later, revoke some privileges. And all the users who are granted the role automatically enjoy the new privileges because role is dynamic. So role is a very important concept. Now, just to understand how the whole thing goes, let's get to some demo. I have two different accounts here HR and system.

We have been working with HR. But now I need to go to system because this is where I have a table. I just created a table for our demo. So I have a table called products. It is not a table that you generally find. So I just created this table for this demo.

So if you want get into system account and create a table, but This is not available to HR, and if HR is trying to access this table, for example, if we say products, Oracle things is accessing a table in his own account. Well, remember, I'm using HR and system to different worksheets. And this is not available, that's what the message is showing. But then he says this is coming from system account. Now system dot products go ahead and run. And then still it's not working, because system has not granted any privilege to HR.

Now let us switch to system. And now I'm granting select privilege on products to HR. When I execute this grant command, the grant succeeded. Now go to HR And then if he runs this command, he would succeed. Because now system granted the privilege and HR is able to use the privilege. But if HR wants to call it as something else, then he can create a synonym.

Okay, create a synonym called prod for system dot products. So we can create a synonym like this. And now whenever user says simply prod is going to access the same table, but is using something called synonym. So that's the way you can understand how to grant privileges and so on. And when system eventually revokes the privilege, it's a revoke select on products from headshot now When revoking the privilege, when HR is trying to access this, then it is going to give a message saying there is no such table or view, because we are not going to have access to system that products anymore. That's the difference between having a privilege not having a privilege.

Well, so that's about granting and revoking privileges. So in this lesson, we understood what is security, the difference between system and object privileges, how to grant how to revoke and also the role of an object called roll. Roll is a collection of privileges, not a collection of users, but we can't create role as a normal user, but you might be granted some roles by DBA. So that's all for now. See you in the next lesson.