When your databases has lots of tables, object privileges can become unwieldy and hard to manage. You can simplify the management of privileges with the use of a database object called a role. A role acts in two capacities in the database. First, the role can act as a focal point for grouping the privileges to execute certain tasks. Second, the role can act as a “virtual user” of a database, to which all the object privileges required to execute a certain job function can be granted, such as data entry, manager review, batch processing, and so onStep 1: Design and Crate roles is a process that can happen outside the database. A role mechanism can be used to provide authorization. A single person or a group of people can be granted a role or a group of roles. One role can be granted in turn to other roles. By defining different types of roles, administrators can manage access privileges much more easily. You simply sit down and think to yourself, how many different purposes will users be accessing this application for? Once this step is complete, you can create the different roles required to manage the privileges needed for each job function. Let’s say people using the EMP table have two different job roles: those who can query the table and those who can add records to the table. The next step is to create roles that correspond to each activity. This architecture of using roles as a middle layer for granting privileges greatly simplifies administration of user privileges. Let’s take a look at how to create the appropriate roles: Create role role_name;Step 2 : Granting Privileges to RolesThe next step is to grant object privileges to roles. You can accomplish this task using the same command as you would use to grant privileges directly to users—the grant command. The following code block demonstrates how to grant privileges to both our roles:Grant select on emp to rpt_writer;Revoking privileges from roles works the same way as it does for users. System privileges are granted and revoked with roles in the exact same way they are with users as well.Step 3: Granting Roles to UsersOnce a role is created and privileges are granted to it, the role can then be granted to users. This step is accomplished with the grant command. Let’s see an example of how this is done:Grant rpt_writer to turner;If a role already granted to a user is later granted another privilege, that additional privilege is available to the user immediately. The same statement can be made for privileges revoked from roles already granted to users, too.Step 4: Revoking and Dropping RolesFinally, a role can be revoked by the role’s owner or by a privileged administrative user using the revoke statement, much like revoking privileges:revoke rpt_writer from turner;Roles can be deleted from the database using the drop role statement. When a role is dropped, the associated privileges are revoked from the users granted the role. The following code block shows how to eliminate roles from Oracle:drop role rpt_writer;Step 5: Modifying Roles if necessaryLet’s say that after we create our roles, we realize that changing records in the EMP table is serious business. To create an added safeguard against someone making a change to the EMP table in error, the DATA_CHANGER role can be altered to require a password by using the alter role identified by statement. Anyone wanting to modify data in EMP with the privileges given via the DATA_CHANGER role must first supply a password. Code for altering the role is shown in the following example:Alter role data_changerIdentified by highly#secure;Step 6: Defining User Default RolesNow user TURNER can execute all the privileges given to him via the RPT_WRITER role, and user FORD can do the same with the privileges given from DATA_CHANGER. Or can he? Recall that we specified that DATA_CHANGER requires a password in order for the grantee to utilize its privileges. Let’s make a little change to FORD’s user ID so that this status will take effect:alter user ford default role none;You can use the following keywords in the alter user default role command to define default roles for users: all, all except rolename, and none. Note that users usually cannot issue alter user default role themselves to change their default roles—only a privileged user such as the DBA can do it for them.Step 7: Enabling the Current RoleFORD knows he is supposed to be able to accomplish this task because he has the DATA_CHANGER role. Then he remembers that this role has a password on it. FORD can use the set role command to enable the DATA_CHANGER role in the following way:set role data_changer identified by highly#secure;Now FORD can make the change he needs to make:SQL> insert into scott.emp (empno, ename, job) 2 values (1234, ‘SMITHERS’,’MANAGER’);You must already have been granted the roles that you name in the SET ROLE statement. Also, you can disable all roles with the SET ROLE NONE statement.

Privileges are the right to execute particular SQL statements. Two types of privileges exist in Oracle: object privileges and system privileges. Objectprivilegesregulate access to database objects in Oracle, such as querying or changing data in tables and views, creating foreign key constraints and indexes on tables, executing PL/SQL programs, and a handful of other activities. System privileges govern every other type of activity in Oracle, such as connecting to the database, creating tables, creating sequences, creating views, and much, much more.Privileges are given to users with the grant command, and they are taken away with the revoke command. The ability to grant privileges to other users in the database rests on users who can administer the privileges. The owner of a database object can administer object privileges related to that object, whereas the DBA administers system privilegesObject privileges(Grant Privileges on Tables)You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

The syntax for granting privileges on a table is: grant privileges on object to user;For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement: grant select, insert, update, delete on suppliers to smithj;You can also use the all keyword to indicate that you wish all permissions to be granted. For example:grant all on suppliers to smithj;The keyword all can be use as a consolidated method for granting object privileges related to a table. Note that all in this context is not a privilege; it is merely a specification for all object privileges for a database objectIf you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example: grant select on suppliers to public;Revoke Privileges on TablesOnce you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.The syntax for revoking privileges on a table is: revoke privileges on object from user;// Grant this on this to thisFor example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:revoke delete on suppliers from anderson;If you wanted to revoke all privileges on a table, you could use the all keyword. For example:revoke all on suppliers from anderson;If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement: revoke all on suppliers from public;

System Privileges

Several categories of system privileges relate to each object. Those categories determine the scope of ability that the privilege grantee will have. The classes or categories of system privileges are listed in this section. Note that in the following subtopics, the privilege itself gives the ability to perform the action against your own database objects, and the any keyword refers to the ability to perform the action against any database object of that type in Oracle.Database Access These privileges control who accesses the database, when he or she can access it, and what he or she can do regarding management of his or her own session. Privileges include create session, alter session, and restricted session. Users These privileges are used to manage users on the Oracle database. Typically, these privileges are reserved for DBAs or security administrators. Privileges include create user, become user, alter user, and drop user.Tables You already know that tables store data in the Oracle database. These privileges govern which users can create and maintain tables. The privileges include create table, create any table, alter any table, backup any table, drop any table, lock any table, comment any table, select any table, insert any table, update any table, and delete any table. The create table or create any table privilege also enables you to drop the table. The create table privilege also bestows the ability to create indexes on the table and to run the analyze command on the table. To be able to truncate a table, you must have the drop any table privilege granted to you.Indexes You already know that indexes are used to improve SQL statement performance on tables containing lots of row data. The privileges include create any index, alter any index, and drop any index. You should note that no create index system privilege exists. The create table privilege also enables you to alter and drop indexes that you own and that are associated with the table.Synonyms A synonym is a database object that enables you to reference another object by a different name. A public synonym means that the synonym is available to every user in the database for the same purpose. The privileges include create synonym, create any synonym, drop any synonym, create public synonym, and drop public synonym. The create synonym privilege also enables you to alter and drop synonyms that you own.Views You already know that a view is an object containing a SQL statement that behaves like a table in Oracle, except that it stores no data. The privileges include create view, create any view, and drop any view. The create view privilege also enables you to alter and drop views that you own.Sequences You already know that a sequence is an object in Oracle that generates numbers according to rules you can define. Privileges include create sequence, create any sequence, alter any sequence, drop any sequence, and select any sequence. The create sequence privilege also enables you to drop sequences that you own.Roles Roles are objects that can be used for simplified privilege management. You create a role, grant privileges to it, and then grant the role to users. Privileges include create role, drop any role, grant any role, and alter any role.

Transactions These privileges are for resolving in-doubt distributed transactions being processed on the Oracle database. Privileges include force transaction and force any transaction.PL/SQL There are many different types of PL/SQL blocks in Oracle. These privileges enable you to create, run, and manage those different types of blocks. Privileges include create procedure, create any procedure, alter any procedure, drop any procedure, and execute any procedure. The create procedure privilege also enables you to alter and drop PL/SQL blocks that you own.Triggers A trigger is a PL/SQL block in Oracle that executes when a specified DML activity occurs on the table to which the trigger is associated. Privileges include create trigger, create any trigger, alter any trigger, and drop any trigger. The create trigger privilege also enables you to alter and drop triggers that you own.Examplesgrant create session to turner;// Grant this to thisrevoke create session from turner;// Revoke this from thisOpen to the PublicAnother aspect of privileges and access to the database involves a special user on the database. This user is called PUBLIC. If a system privilege or object privilege is granted to the PUBLIC user, then every user in the database has that privilege. Typically, it is not advised that the DBA should grant many privileges or roles to PUBLIC, because if a privilege or role ever needs to be revoked, then every stored package, procedure, or function will need to be recompiled. Let’s take a look:GRANT select, update, insert ON emp TO public;Tip Roles can be granted to the PUBLIC user as well. We’ll talk more about roles in the next discussion.Granting Object Privileges All at OnceThe keyword all can be use as a consolidated method for granting object privileges related to a table. Note that all in this context is not a privilege; it is merely a specification for all object privileges for a database object. The following code block shows how all is used:GRANT ALL ON emp TO turner;Giving Administrative Ability along with PrivilegesWhen another user grants you a privilege, you then have the ability to perform whatever task the privilege enables you to do. However, you usually can’t grant the privilege to others, nor can you relinquish the privilege without help of the user who granted the privilege to you. If you want some additional power to administer the privilege granted to you, the user who gave you the privilege must also give you administrative control over that privilege. For example, let’s say KING now completely trusts TURNER to manage the creation of tables (a system privilege) and wants to give him access to the EMP table (an object privilege). Therefore, KING tells SCOTT to give TURNER administrative control over both these privileges, as follows:GRANT CREATE TABLE TO turner WITH ADMIN OPTION;GRANT SELECT, UPDATE ON turner TO SPANKY WITH GRANT OPTION;Tip The GRANT OPTION is not valid when granting an object privilege to a role.A system privilege or role can be granted with the ADMIN OPTION. A grantee with this option has several expanded capabilities. The grantee can grant or revoke the system privilege or role to or from any user or other role in the database. However, a user cannot revoke a role from himself. The grantee can further grant the system privilege or role with the ADMIN OPTION. The grantee of a role can alter or drop the role.Finally, if a role is granted using the with admin option clause, the grantee can alter the role or even remove it. You’ll learn more about roles in the next discussion.You can grant INSERT, UPDATE, or REFERENCES privileges on individual columns in a table.

Cascading Effects

No cascading effects of revoking system privileges from users occur.when an object privilege is revoked from a grantor of that privilege, all grantees receiving the privilege from the grantor also lose the privilege. However, in cases where the object privilege involves update, insert, or delete, if subsequent grantees have made changes to data using the privilege, the rows already changed don’t get magically transformed back the way they were before

In this chapter, you will learn about and demonstrate knowledge in the following areas of user access and privileges in the Oracle database:

Creating users

Granting and revoking object privileges

Using roles to manage database access

The basic Oracle database security model consists of two parts. The first part consists of password authentication for all users of the Oracle database. Password authentication is available either directly from the Oracle server or from the operating system supporting the Oracle database. When Oracle’s own authentication system is used, password information is stored in Oracle in an encrypted format. The second part of the Oracle security model consists of controlling which database objects a user may access, the level of access a user may have to these objects, and whether a user has the authority to place new objects into the Oracle database. At a high level, these controls are referred to as privileges. We’ll talk about privileges and database access later in this section.Create UsersThe most basic version of the command for creating users defines only the user we want to create, along with a password, as seen in the following example:Create User USERNAME identified by PASSWORD;create user turner identified by ike;Tip : The user does not have privileges at this point. The DBA can then grant privileges to the user. The privileges determine the actions that the user can do with the objects in the database. Also, usernames can be up to 30 characters in length and can contain alphanumeric characters as well as the $, #, and _ characters.

This site uses cookies and other tracking technologies to assist with navigation and your ability to provide feedback, analyse your use of our services, and provide content from third parties. By continuing to browse the site, you are agreeing to our use of cookies.

Cookie and Privacy Settings

How we use cookies

We may request cookies to be set on your device. We use cookies to let us know when you visit our websites, how you interact with us, to enrich your user experience, and to customize your relationship with our website.

Click on the different category headings to find out more. You can also change some of your preferences. Note that blocking some types of cookies may impact your experience on our websites and the services we are able to offer.

Essential Website Cookies

These cookies are strictly necessary to provide you with services available through our website and to use some of its features.

Because these cookies are strictly necessary to deliver the website, you cannot refuse them without impacting how our site functions. You can block or delete them by changing your browser settings and force blocking all cookies on this website.

Google Analytics Cookies

These cookies collect information that is used either in aggregate form to help us understand how our website is being used or how effective our marketing campaigns are, or to help us customize our website and application for you in order to enhance your experience.

If you do not want that we track your visist to our site you can disable tracking in your browser here:

Click to enable/disable google analytics tracking.

Other external services

We also use different external services like Google Webfonts, Google Maps and external Video providers. Since these providers may collect personal data like your IP address we allow you to block them here. Please be aware that this might heavily reduce the functionality and appearance of our site. Changes will take effect once you reload the page.

Google Webfont Settings:

Click to enable/disable google webfonts.

Google Map Settings:

Click to enable/disable google maps.

Vimeo and Youtube video embeds:

Click to enable/disable video embeds.

Privacy Policy

You can read about our cookies and privacy settings in detail on our Privacy Policy Page.