Packt+ | Advance your knowledge in tech

You're reading from Oracle Database 12c Security Cookbook

Product typeBook

Published inJun 2016

PublisherPackt

ISBN-139781782172123

Edition1st Edition

Tools

Oracle Enterprise Manager Oracle Database

Concepts

Database Administration

Authors (2):

Zoran Pavlovic

Maja Veselica

View More author details

Chapter 7. Privilege Analysis

In this chapter, we will cover the following tasks:

Creating a database analysis policy
Creating a role analysis policy
Creating a context analysis policy
Creating a combined analysis policy
Starting and stopping privilege analysis
Reporting on used system privileges
Reporting on used object privileges
Reporting on unused system privileges
Reporting on unused object privileges
How to revoke unused privileges
Dropping the analysis

Introduction

Privilege analysis is a new security feature, introduced in Oracle Database 12c. It is only available in Oracle Database Enterprise Edition, and from licensing viewpoint, it is part of Oracle Database Vault option.

Privilege analysis is very useful to implement and maintain the least privilege principle by identifying both privileges that users are actually using (used privileges) and those that are only granted to them (unused privileges).

General steps to analyze privileges using this feature are shown in Figure 1.

Figure 1 - The steps to analyzethe used and unused privileges

In this chapter, it is assumed that all users have a create session privilege, and in the following table, other privileges and roles granted to the users and roles are listed:

Creating database analysis policy

In this recipe, you'll learn to create database privilege analysis policy. It analyzes privileges in the whole database (except privileges used by SYS user). You can use SQL*Plus and Enterprise Manager Cloud Control 12.1.0.3+ (in our case, EM12cR4) to create privilege analysis policies.

Getting ready

You'll need an existing user who can create a privilege analysis policy (has CAPTURE_ADMIN role and SELECT ANY DICTIONARY privilege), for example, SYSTEM user.

How to do it...

Connect to the database as system or a user who has appropriate privilege:
```
$ sqlplus system
```

Create a privilege analysis policy that captures all the used privileges in the database:

SQL> BEGIN
    SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
    name => '<policy_name>',
    description => '<your_desc>',
    type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
    END;
    /

Figure 2 - Database (unconditional) analysis policy

How it works...

In step 2, you created database-wide policy...

Creating role analysis policy

In this recipe, you'll create a role analysis policy using SQL*Plus and Enterprise Manager Cloud Control 12c (EM). The usage of directly and indirectly granted privileges to the roles listed in the policy, will be captured if the roles are active for the session.

Getting ready

You'll need an existing user who can create a privilege analysis policy (has a CAPTURE_ADMIN role and a SELECT ANY DICTIONARY privilege), for example, SYSTEM user.

How to do it...

Connect to the database as system or a user who has appropriate privileges:
```
$ sqlplus system
```

Create a privilege analysis policy that captures all the used privileges granted through roles DBA and P1_ROLE:

SQL> BEGIN
        SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
        name => '<policy_name>',
        description => '<your_desc>',
        type => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
        roles => role_name_list (<'role1',...,'role10'>));
        END;
        /

Figure 8 - The role...

Creating context analysis policy

In this recipe, you'll create a context analysis policy. After the policy is enabled, it will capture privileges when the condition specified in the policy evaluates to true.

Getting ready

You'll need an existing user who can create a privilege analysis policy (has the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege), for example, the SYSTEM user.

How to do it...

Connect to the database as system or a user who has appropriate privileges:
```
$ sqlplus system
```

Create a privilege analysis policy that captures all the used (and unused) privileges by Steve:

SQL> BEGIN
     SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
	 name => '<policy_name>',
	 description => '<your_desc>',
	 type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
     condition => '<your_condition>');
	 END;
	 /

Figure 11 - The context analysis policy

There's more...

Another way to create a context privilege analysis policy is to use EM12c. Repeat steps 1, 2, and 3 from the...

Creating combined analysis policy

In this recipe, you'll create a combined analysis policy. This type of policy defines that the usage of directly and indirectly granted privilege to specified roles will be gathered if roles are enabled in the session and the context condition is satisfied. The context condition can consist of one or more conditions (you can use the AND or OR Boolean operators).

Getting ready

You'll need an existing user who can create a privilege analysis policy (has the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege), for example, the SYSTEM user.

How to do it...

Connect to the database as system or a user who has appropriate privileges:
```
$ sqlplus system
```

Create a privilege analysis policy that captures the usage of privileges, when using SQL Developer, which are granted through the role P2_ROLE:

SQL> BEGIN
        SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
        name => '<policy_name>',
        description => '<your_desc>',
        type ...

Starting and stopping privilege analysis

To start capturing privileges, you'll enable privilege analysis policies you created in the previous recipes.

Getting ready

You'll need an existing user who can manage privilege analysis policies (has the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege), for example, the SYSTEM user.

How to do it...

Connect to the database as system or a user who has appropriate privileges:
```
$ sqlplus system
```
List all existing privilege analysis policies by querying DBA_PRIV_CAPTURES.
Figure 20 - Finding all defined policies
Enable a privilege analysis (for example, ALL_PRIV_POL, which you created in the first recipe in this chapter):
```
SQL> BEGIN
    SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(
	name => '<policy_name>');
	END;
	/
```
Figure 21 - Start capturing all privileges
Connect to the database as the user alan and view the first names of employees who have salary less than 1000:
Figure 22 - the first test of select privilege
Find first names of employees...

Reporting on used system privileges

In this recipe, you'll view collected data about the usage of system privileges during a capture interval.

Getting ready

You'll need an existing user who can create a privilege analysis policy (has the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege), for example, the SYSTEM user.

How to do it...

Connect to the database as system or a user who has appropriate privileges:
```
$ sqlplus system
```
View system privileges that the user ALAN used:
Figure 37 - The used system privileges
View grant path for the used system privileges generated by ALL_PRIV_POL for the user ALAN:
Figure 38 - The Grant path

There's more...

In EM 12c, after you have generated the report, select the policy and from Actions drop-down menu, select Reports. The Usage Summary report will open (see Figure 39).

Figure 39 - Usage Summary

Click on the tab Used and choose All for Match radio button, Policy: ALL_PRIV_POL, User Name: ALAN, and click on the Search button. Results are shown in Figure...

Reporting on used object privileges

In this recipe, you'll view collected data about the usage of object privileges during the capture interval.

Getting ready

You'll need an existing user who can create a privilege analysis policy (has the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege), for example, the SYSTEM user.

How to do it...

Connect to the database as system or a user who has appropriate privileges:
```
$ sqlplus system
```
View which object privileges the user Alan has used while database policy ALL_PRIV_POL has been active.
Figure 42 - The used object privileges
View grant path by querying DBA_USED_OBJPRIVS_PATH:
Figure 43 - Object privileges grant path

There's more...

In EM 12c, after you have generated the report, select the policy, and from Actions drop-down menu, select Reports. The Usage Summary report will open. Click on the Used tab and verify that the user Alan has used the SELECT and DELETE privileges while ALL_PRIV_POL has been active (see Figure 44):

Figure 44 - Reports

Reporting on unused system privileges

In this recipe, you'll view the collected data about the unused system privileges during the capture interval.

Getting ready

You'll need an existing user who can create a privilege analysis policy (has the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege), for example, the SYSTEM user.

How to do it...

Connect to the database as system or a user who has appropriate privileges:
```
$ sqlplus system
```
View that the user Alan has used all system privileges that have been granted to him (there are no unused system privileges):
Figure 45 - The unused system privileges for the user Alan during the database policy ALL_PRIV_POL capture interval

There's more...

To view report about the unused system privileges in EM12c, see instructions to view the used system privileges and under Privilege Analysis: Reports, choose the Unused tab instead of the Used tab.

Reporting on unused object privileges

In this recipe, you'll view collected data about the unused object privileges during the capture interval.

Getting ready

You'll need an existing user who can create a privilege analysis policy (has the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege), for example, the SYSTEM user.

How to do it...

Connect to the database as system or a user who has appropriate privileges:
```
$ sqlplus system
```
View which object privileges the user Alan has used during the database policy capture interval:
Figure 46 - The unused object privileges

There's more...

In EM 12c, after you have generated the report, select the policy, and from Actions drop-down menu, select Reports. The Usage Summary report will open. Click on the Unused tab and verify that the user Alan hasn't used the INSERT and UPDATE privileges while ALL_PRIV_POL has been active.

Figure 47 - The Unused object privileges report

How to revoke unused privileges

You can manually revoke unused privileges one by one from users, write your own scripts to complete that task, or use Enterprise Manager Cloud Control 12c. In this recipe, you'll use EM12c to efficiently revoke unused privileges based on reports you generated in the previous recipes.

How to do it...

Select policy, and from Actions drop-down menu, choose Revoke Scripts (see Figure 48):
Figure 48 - Create revoke scripts
You'll see a message about required privileges (see Figure 49). Click on the OK button.
Figure 49 - The info message
Select policy (Policy Name) and click on the Generate button (see Figure 50):
Figure 50 - Generating a script
Generate script to revoke all the unused object privileges from the user Alan. Fill out form as shown in Figure 51 and click on the Next button:
Figure 51 - Revoking the script configuration
Click on the Select None link and tick revoke checkbox for the user Alan (see Figure 52):
Figure 52 - Choose to revoke privilege only from the...

Dropping the analysis

In this recipe, you'll drop an existing privilege analysis policy. It has to be disabled before dropping; otherwise, you'll receive an error.

Getting ready

You'll need an existing user who can manage privilege analysis policies (has the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege), for example, the SYSTEM user and an existing privilege analysis policy.

How to do it...

Connect to the database as system or a user who has appropriate privileges:
```
$ sqlplus system
```
Drop a privilege analysis policy (for example, ALL_PRIV_POL, which you created in the first recipe in this chapter):
```
SQL> BEGIN
     SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE(
     name => '<policy_name>');
     END;
	 /
```
Figure 57 - Drop policy
Verify that all the records about the used and unused privileges, which have been gathered according to the policy, are also dropped:
```
SQL> SELECT username, sys_priv, obj_priv, object_owner,
       object_name
	   FROM DBA_USED_PRIVS
	   WHERE capture...
```

The rest of the chapter is locked

You have been reading a chapter from

Oracle Database 12c Security Cookbook

Published in: Jun 2016Publisher: PacktISBN-13: 9781782172123

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Authors (2)

Zoran Pavlovic

Zoran Pavlovic is currently working as an Oracle Technical Architect in his company. He works with Oracle technologies (primary Oracle Database) since 2005. Areas of his expertise are Security and Performance Tuning of Oracle Database. Zoran has worked on various complex database environments including RAC, ASM, Data Guard, etc. He has worked as a consultant for Oracle Consulting as well as instructor for Oracle University across EMEA region. Zoran is first member of Oracle ACE Program in Serbia, and a featured speaker/author at many conferences/magazines.
Read more about Zoran Pavlovic

Maja Veselica

Maja Veselica, MSc in software engineering, is currently working for Parallel d.o.o., Belgrade, as an Oracle Database consultant (security, performance tuning, and so on). She has been working as an instructor for Oracle University since 2010. In the last couple of years, she has also been working for Oracle Consulting. Also, Maja is a member of Oracle ACE Program and has more than 20 Oracle certificates. She enjoys (beta) testing Oracle products and participating in other Oracle-related activities.
Read more about Maja Veselica

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages

USER/ROLE	HR.EMPLOYEES	OE.ORDERS	ROLES/SYS.PRIVS.
`BARBARA`			`P1_ROLE`
`NICK`			`DBA`
`ALAN`	`SELECT`, `INSERT`, `UPDATE`, `DELETE`
`STEVE`			`P2_ROLE`
`P1_ROLE`	`SELECT`
`P2_ROLE`		`SELECT`, `INSERT`, `UPDATE`, `DELETE`	`SELECT ANY TABLE`, `CREATE...`