SQL tutorial 48: How to create Roles in Oracle Database

SQL tutorial 48: How to create Roles in Oracle Database


What’s up internet?
I am Manish From RebellionRider.com. Till so far we have learned about system as
well as object privileges and so today we will going to learn user Roles in Oracle Database.
User Roles is kind of an extension to user privileges and gives an ease in managing and
controlling the privileges in oracle database, using roles you can save a lot of time and
efforts. How? We will see ahead… So let’s start todays tutorial
The first question which arises here is, what exactly are roles?
Roles are named group of privileges that you can assign to users or other roles.
So let’s move ahead and see how we can create a role and grant it some privileges.
By the way if you want to know what are the features and advantages of user roles with
many other information you can read my blog on the same topic. Link is in the description
box. Query 1: Role without any Security Authentication
Let’s see how we can create a role. Only user with CREATE ROLE system privilege
can create a role and to create a role we use Create Role statement.
And if you are a DBA and can log on to your database using Sys user than that’s the
best way to create any role because sys user has all the system privileges. That’s why
I am going to connect my database using my user sys. [email protected] is my sys connection
so I am going to connect using this connection. And here I am. Let me quickly show you the
user. As you can see right now I am connected to my database using Sys user with sysdba
privileges Let’s create a very simple role by the name
of Demo1. Let me close this script output ok
CREATE ROLE demo1 NOT IDENTIFIED; Query is pretty simple. First we have create
role both of which are the Oracle reserved keywords followed by the name of the role
which is Demo1 in our case, Name of the role is user defined thus you can give whatever
name you want. Then at the end of the statement we have NOT IDENTIFIED clause, NOT IDENTIFIED
clause indicates that this role is authorized by the database and no password is required
to enable the role. This is an optional clause statement thus if you want you can skip it.
Let’s move on to our next query. Query 2: Role with Security Authentication
We just created a very simple role demo1 with no authentication or security. Now let’s
create a role with password authentication. Again we will use Create Role statement to
create this role. CREATE ROLE Demo2 IDENTIFIED BY password123;
The first half of this query is very identical to the first query but in the second half
we have Identified By clause. IDENTIFIED BY clause indicates that a role must be authorized
by the specified method. In our case the specific method is password.
Followed by Identified By clause we have our password. Let’s execute, User created.
Query 3: External Role We just created a role Demo2 with password
authentication in oracle we can also create external roles. The authentication of external
roles is handled by external services such as your Operating System. Let’s see how
we can create an external user. CREATE USER Demo3 IDENTIFIED externally;
On executing query, oracle will create an external role.
Similarly you can also create global role in oracle database. The authentications of
global role are handled by enterprise directory service or say Central Oracle Security Server
before the role is enabled at login. Let me again close this script output. The
query for creating a global role will be. Please read my blog for more information about
global and other authentication of a role. The link is in the description box.
Grant Privileges to a role After creation every role is like an empty
box because it doesn’t have any privilege within it thus it’s of no use. To make a
role useful we need to grant it some privileges. We can grant both ‘system’ as well as
‘object’ privileges to a role; we can even grant a role to another role. To do the
same we use GRANT statement. Let see how: First we will grant a system privilege to
a role demo1. You can grant any system privilege to any role in your database. You can even
grant system privilege wit Admin Option to the role as well.
I will suggest you to watch my videos on System and Object privileges for the better understanding
of the concept. Ok let’s write the query and see how we
can grant a create table system privilege to our role demo1. If you want you can also
use with admin option flag here. GRANT create table TO Demo1 WITH ADMIN OPTION;
In this query I am granting create table system privilege with admin option to our role Demo1.
Let’s execute Similarly we can grant Object privilege to
our role. Let’s grant select object privilege over
employees table of hr user to the role demo1.first let me close this script output. Ok let’s
write our query 6 GRANT select ON hr. employees TO demo1;
While granting an object privilege to a role oracle doesn’t allow us to use With Grant
Option Flag. Let’s execute. Grant succeeded. Similarly you can grant a role to another
role. For the demonstration I will grant the role Demo1 to role Demo2.ok let’s write
the query. let me close this script output. Let me make some space I think everything
is set and you people can see things clearly. So let’s write the query
Grant demo1 to demo2; On executing this query role demo2 will get
all the privileges which we granted to role demo1. So let’s execute this query
Grant Role to a User Ok let’s move ahead and see how we grant
a role to database users. A role can be granted to any user in Oracle Database and a user
with GRANT ANY ROLE system privilege can grant and revoke a role except a global role to
or from other users or roles of the database. Say you want to grant a role demo1 to our
user batman. For that we will again use GRANT statement. And the query will be. Let’s
close this script output If you want you can grant the role demo1 to
user batman WITH ADMIN OPTION also GRANT demo1 TO batman;
Ok let’s execute this query As role demo1 doesn’t have any authentication
and instead is authorized by database itself thus its by default enabled. But sometime
roles with authentication are not enabled by default in such cases you have to enable
them. Let’s see how. We have role Demo2 which has password authentication
and have create table System privilege. Let’s grant this role Demo2 to our user hulk.
GRANT demo2 TO HULK; Let’s execute this query.
Though this role is successfully granted but still user hulk cannot use the privileges
of this role as it is in disabled state. May be possible that it’s enabled in your case,
but in case it’s disabled then you can enable it by using SET ROLE statement;
Let me show you how. SET ROLE demo2 IDENTIFIED BY password123;
On executing this query oracle engine will enable this role and user hulk can use all
its privileges. Let’s execute this query. Role Demo2 Succeeded. Means now role demo2
is enabled. I highly suggest you to read my blog on this
topic as it is hard to explain everything within a tight time frame on the video. Link
is in the description box. That’s it for today. Hit the like button
to support this video and keep me motivated for doing more such videos.
Don’t forget to subscribe. Will see you soon with another video till
then take care this is Manish from RebellionRider.

Daniel Ostrander

Related Posts

9 thoughts on “SQL tutorial 48: How to create Roles in Oracle Database

  1. saneesh sundaram says:

    do u have any profit by uploading oracle videos???

  2. masum khan says:

    thanks bro, I like your tutorials very much. It benefits   me very much. I learned a lot from your videos. Wish a happy life!

  3. tariq mehmood says:

    thank you Manish for such knowledgeable tutorials.

  4. Zakir's Camera says:

    hey sir
    i need more info about privileges

  5. Ravindra Zade says:

    can u help me out for how to link 2 different database with each other using dblink.
    please share video about auto job scheduler data refresh every 15 minutes

  6. Aryan Patle says:

    Hello Sir,I downloaded the Oracle sqldeveloper software and in connection,I forget my password of 'syst' connection.How can I access the connection again?

  7. prasun karar says:

    nice post..!!! but how to view the privileges that has been assigned to a specific role(which is created by admin) ???

  8. mahesh Kumar says:

    sir as you mentioned the previous video on system privileges, assigning roles are of same use what is the difference in it

  9. Miyuki Umeki says:

Leave a Reply

Your email address will not be published. Required fields are marked *