Home Data Oracle Database 12c Security Cookbook

Oracle Database 12c Security Cookbook

By Zoran Pavlovic , Maja Veselica
books-svg-icon Book
eBook $47.99 $32.99
Print $60.99
Subscription $15.99 $10 p/m for three months
$10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
BUY NOW $10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
eBook $47.99 $32.99
Print $60.99
Subscription $15.99 $10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
  1. Free Chapter
    Basic Database Security
About this book
Businesses around the world are paying much greater attention toward database security than they ever have before. Not only does the current regulatory environment require tight security, particularly when dealing with sensitive and personal data, data is also arguably a company’s most valuable asset - why wouldn’t you want to protect it in a secure and reliable database? Oracle Database lets you do exactly that. It’s why it is one of the world’s leading databases – with a rich portfolio of features to protect data from contemporary vulnerabilities, it’s the go-to database for many organizations. Oracle Database 12c Security Cookbook helps DBAs, developers, and architects to better understand database security challenges. Let it guide you through the process of implementing appropriate security mechanisms, helping you to ensure you are taking proactive steps to keep your data safe. Featuring solutions for common security problems in the new Oracle Database 12c, with this book you can be confident about securing your database from a range of different threats and problems.
Publication date:
June 2016
Publisher
Packt
Pages
388
ISBN
9781782172123

 

Chapter 1. Basic Database Security

In this chapter, we will cover the following tasks:

  • Creating a password profile

  • Creating password-authenticated users

  • Changing a user's password

  • Creating a user with the same credentials on another database

  • Locking a user account

  • Expiring a user's password

  • Creating and using OS-authenticated users

  • Creating and using proxy users

  • Creating and using database roles

  • The sysbackup privilege - how, when, and why should you use it?

  • The syskm privilege - how, when, and why should you use it?

  • The sysdg privilege - how, when, and why should you use it?

 

Introduction


Authentication is a very important process, whose purpose is to determine whether someone or something is, in fact, who or what it claims to be.

In this chapter, you'll learn basic stuff about some of the different authentication methods supported by Oracle Database 12c. Also, a brief overview about creating and using database roles will be given.

There are three new administrative privileges introduced in Oracle Database 12c (sysbackupsyskm, and sysdg). Their purpose is to enable better separation of duties and they are designed in such a way to also enable implementation of the least privilege principle. Although it may seem that implementation of this principle in systems is easy or straightforward, usually it's quite tricky.

Note

For all recipes in this chapter, you will use non-CDB 12c. We assume that the database is up and running and each user has at least the create session privilege.

In this set of recipes, you will learn to perform, mostly basic, user administration tasks.

 

Creating a password profile


You can use a profile to implement your password policy.

Getting ready

To complete this recipe, you'll need an existing user who has create profile privilege (such as an OS-authenticated user who has database administrators (dba) role, for example, ops$zoran). Also, you'll need an unlocked user account named scott.

Make sure that the resource_limit parameter is set to true.

How to do it...

  1. Connect to the database as a user who has create profile privilege:

           sqlplus /
    
    
  2. Create a password profile:

           create profile userprofile limit
           failed_login_attempts 4
           password_lock_time 2
           password_life_time 180;
    
  3. Alter the user to use a newly created password profile:

           alter user scott profile userprofile;
    
    
  4. Alter the default password profile:

           alter profile default limit
    failed_login_attempts 4;
    
    

How it works...

In step 1, you used OS authentication to connect to the database.

In step 2, you created a password profile with the name userprofile that has the following restrictions:

  • The system allows four login attempts before locking a user account (failed_login_attempts)

  • After locking a user account, it will remain locked for two days (password_lock_time)

  • A password for the user can remain unchanged for 180 days - after which the password will expire, and the user will have to change the password for his next login (password_life_time)

In step 3, we assigned a newly created password profile to the user scott. If we don't assign a password profile to the user, that user uses the default password profile.

In step 4, we altered the default password profile with the failed_login_attempts restriction.

There's more...

You can create different password profiles for different users in the database. There are a lot of restrictions that can be applied to a password profile.

In Oracle Database 12c, there are three password verify functions, out of which, two are new and improved:

  • verify_function_11G (carried over)

  • ora12c_verify_function (new)

  • ora12c_strong_verify_function (new)

If password complexity checking is not enabled, and you want to use it, you should run the utlpwdmg.sql script provided by Oracle. It's located in $ORACLE_HOME/rdbms/admin. The ora12c_verify_function function is the default function that the utlpwdmg.sql script uses. If you want, you can customize password verify functions.

Note

Password complexity checking, even when enabled, doesn't apply to sys user.

If you want to choose which verify function will be used in the default profile, you can achieve that by using the following statement:

alter profile default limit password_verify_function ora12c_strong_verify_function;

In subsequent recipes, it is assumed that default values are set for the default profile and the password verify function is not used.

See also

  • Creating password-authenticated users

  • Locking a user account

  • Creating and using OS-authenticated users

 

Creating password-authenticated users


In this task, you will create several users.

Getting ready

To complete this recipe, you'll need an existing user who has create user privilege (you may use the OS-authenticated user who has the DBA role).

You'll use Oracle Enterprise Manager Database Express 12c (EM Express). To learn more about it (for example, how to configure an HTTPS port for EM Express and how to start it), see the third chapter of the official Oracle guide -Oracle Database 2 Day DBA, 12c Release 1.

How to do it...

  1. Connect to the database as a user who has create user privilege:

           $ sqlplus /
    
    
  2. Create a password-authenticated user (for example, username: jessica, password: oracle_1) as follows:

           SQL> create user jessica identified by oracle_1;
    
    
  3. Create a password-authenticated user with a more complex password:

           SQL> create user tom identified by "Qax7UnP!123*";
    
    
  4. Create a user that uses a specific password profile:

           SQL> create user mike identified by test1 profile
    userprofile;
    
    
  5. Create a user and force it to change password upon the first login:

           SQL> create user john identified by password1
    password expire;
    
    
  6. Create a user richard, whose default tablespace is users, temporary tablespace is temp, and who has their quota set to unlimited on the users tablespace:

           SQL> create user richard identified by oracle_2 default
    tablespace users temporary tablespace temp quota unlimited
    on users;
    
    

How it works...

In step 1, you used OS authentication to connect to the database.

In step 2, you created a password-authenticated user jessica with simpler password.

In step 3, you created a password-authenticated user tom with more complex password. In this case (because a password contains special characters), you are using quotation marks (") to enclose the password.

Both of these users are using the default password profile.

In step 4, you created a password-authenticated user with the assigned password profile userprofile.

In step 5, you created user john. This user has to change his password at the first database login.

In step 6, you created the user richard. In the create user statement, quota unlimited on users means that you want to let the user allocate space in the tablespace without bound. The quota clause lets you define the maximum amount of space the user can allocate in the tablespace. You can have multiple quota clauses for multiple tablespaces within one  create user statement. The unlimited tablespace system privilege enables users to have an unlimited quota on all tablespaces in the database.

Note

If you grant unlimited tablespace system privilege to a user and afterwards you revoke it, all explicitly granted quotas will also be revoked.

There's more...

You can also create users using Oracle Enterprise Manager Cloud Control 12c or Oracle Enterprise Manager Database Express 12c (EM Express). Oracle Enterprise Manager Database Control is no longer available in Oracle Database 12c.

How to create a user using EM Express

  1. Start EM Express and log in to it using the user that has either EM_EXPRESS_BASIC or EM_EXPRESS_ALL role (you can use sys or system users, but that isn't recommended):

  2. Select Users from the Security drop-down menu:

  3. Click on the Create User tab:

  4. Enter user details in the pop-up dialog (for example, username: ted, password: oracle_123, here you can also choose the authentication method, password profile, lock account, expire password) leave the default values and click on the Nextbutton (see image here) as follows:

  5. In this step, you can choose default tablespace and temporary tablespace from the drop-down lists. Leave the default values, as shown in the following screenshot:

  6. In this step, you can grant privileges to user ted by selecting them in the left pane and moving them to the right pane (use > button). If you want to revoke privileges, do the opposite (select them in right pane and use < button). When you are satisfied with the list of privileges in the right pane (the ones you are going to grant to user ted), click on the OK button as follows:

  7. A pop-up window confirmation should appear with the following message: SQL statement has been processed successfully.

Click on the OK button to close the window.

See also

  • Creating and using OS-authenticated users

 

Changing a user's password


Changing a user's password is easy. You will practice it by changing passwords for several users in this recipe.

Getting ready

To complete this recipe, you'll need an existing user who has alter user privilege (you may use OS-authenticated user who has the DBA role) and other existing users (for example, jessica and tom).

How to do it...

  1. Connect to the database as a user who has alter user privilege:

           $ sqlplus /
    
    
  2. Change the password for user jessica:

           SQL> password jessica;
    
    
  3. Enter a new password (for example, oracle_2) on a command line (note that typing will not be visible in the command line):

           New password:
    
    
  4. Retype the new password (for example, oracle_2) on the command line (note that typing will not be visible in the command line):

           Retype new password:
    
    
  5. Connect to the database as any user (for example, tom, to change their own password):

           $ sqlplus tom/"Qax7UnP!123*"
    
    
  6. Change the password using the following code:

           SQL> password
    
    
  7. Enter the old password (for example, Qax7UnP!123*) on the command line (note that typing will not be visible on the command line):

           Old password:
    
    
  8. Enter the new password (for example, oracle_123) on the command line (note that typing will not be visible on the command line):

           New password:
    
    
  9. Retype the new password (for example, oracle_123) on the command line (note that typing will not be visible on the command line):

           Retype new password:
    
    

How it works...

In step 1, you used OS authentication to connect to the database.

In steps 2 through 4, a privileged user changed jessica's password, where in steps 6 through 9, the user tom changed his own password.

There's more...

There is another way to change the user's password using the alter user statement as follows:

SQL> alter user jessica identified by oracle_2;

Tip

This approach is not recommended because password remains in the command-line history.

See also

  • Creating and using OS-authenticated users

 

Creating a user with the same credentials on another database


This recipe explains a way to create a user with the same credentials on another database.

Getting ready

To complete this recipe, you'll need:

  • An existing user who has dba role in the first database (you can use an OS-authenticated user)

  • An existing user in the first database (for example, jessica)

  • An existing (for example, password-authenticated) user, who has create user privilege, in the second database (for example, zoran)

How to do it...

  1. Connect to the first database as a user who has a DBA role:

           $ sqlplus /
    
    
  2. Find a Data Definition Language (DDL) statement (ddl) that is used for user creation (for example, user jessica):

           SQL> select dbms_metadata.get_ddl('USER', 'JESSICA') from
    dual;
    
    
  3. Connect to the second database as a user who has create user privilege:

           $ sqlplus zoran@orcl2
    
    
  4. Create a user using the value you found in step 2:

    SQL> create user "JESSICA" identified by values
    'S:D82E6EF961F2EA7A878BCDDBC7E5C542BC148C4759D19A7
    20A96BBF65658;H:F297A50FD538EF4AB119EB0278C9E72D;
    C50B1E9C9AA52EC2';
    

How it works...

In step 1, you used OS authentication to connect to the database.

In step 2, you found a DDL statement that has been used for user creation. This DDL statement may contain default and temporary tablespace assignments (note that even if you haven't explicitly assigned these tablespaces during user creation, the system will assign them implicitly using default values for the database). For instance, output in step 2 may look like this:

SQL> select dbms_metadata.get_ddl('USER', 'JESSICA') from dual;
    
DBMS_METADATA.GET_DDL('USER','JESSICA')
------------------------------------------------------------------
CREATE USER "JESSICA" IDENTIFIED BY VALUES 'S:D82E6EF961F2EA7A878BCDDBC7E5C542BC148C4759D19A720A96BBF65658;H:F297A50FD538EF4AB1 19EB0278C9E72D;C50B1E9C9AA52EC2'
DEFAULT TABLESPACE "USERS"            TEMPORARY TABLESPACE "TEMP"

However, we used only the first part of this DDL in step 4 to create a user on the second database (and let the database decide about default tablespaces).

There's more...

There is another way to accomplish the task.

Note

You can only reveal the hash value of user's password (you cannot reveal the actual password).

This way requires select on the sys.user$ table:

  1. Connect to the first database as a user who has the select privilege on the sys.user$ table (for example, user who has the sysdba privilege):

    $ sqlplus / as sysdba
    
    
  2. Find the hash value of a user's password (for example, user jessica):

    SQL> select spare4
    from user$
    where name='JESSICA';
    
    
  3. Connect to the second database as a user who has create user privilege:

    $ sqlplus zoran@orcl2
    
    
  4. Create a user with the same username (for example, jessica) using the hash value of the password that you have found in step 2:

    SQL> create user jessica identified by values 
    'S:2724193130FC67E7E23E3E44E33AF143F7A6C36489792B
    5856133DCB331D;H:184895E50EA2FBCC2311ED76A3E5CF35;
    T:BECCD5FC6F6E62BC34DF1C826AEE899EC6A6025FA0D5071659DA
    7DD1ABB37763483B5C821E5A34C1184A56BE4B1C92CED79639D11101D
    61B86ACBE60A30F19CC277D5753F7D3756DC1B7705C0ACE81F3';
    

See also

  • Creating and using OS-authenticated users

 

Locking a user account


In this recipe, you'll learn to lock and unlock user accounts.

Getting ready

To complete this recipe, you'll need an existing (for example, OS-authenticated) user who has alter user privilege (you may use user who has a DBA role) and another existing user (for example, mike).

How to do it...

  1. Connect to the database as a user who has alter user privilege:

    $ sqlplus /
    
    
  2. Lock the account of user mike:

    SQL> alter user mike account lock;
    
    
  3. Unlock the account of user mike:

    SQL> alter user mike account unlock;
    
    

How it works...

In step 1, you used OS authentication to connect to the database.

In step 2, you locked the account of user mike. This means that user mike cannot connect to the database:

    SQL> alter user mike account lock;

    User altered

    SQL> connect mike/welcome1

    ERROR: ORA-28000: the account is locked

However, objects in mike's schema are available, so users can access them (considering that they have necessary privileges):

    SQL> select a, b from mike.table1;
      A         B 
     ---------- --------- 
             1         3 
             2         4 
             4         9

Tip

It is recommended that you lock the accounts of users that own your application objects (application schemas).

In step 3, you unlocked the account of user mike. Now user mike can successfully connect to the database:

    SQL> alter user mike account unlock;

    User altered.

    SQL> conn mike/welcome1

    Connected.

See also

  • Creating and using OS-authenticated users

 

Expiring a user's password


The expiration of user's password is a very easy task.

Getting ready

To complete this recipe, you'll need an existing (for example, OS-authenticated) user who has the alter user privilege (you may use user who has a DBA role) and another existing user (for example, mike).

How to do it...

  1. Connect to the database as a user who has the alter user privilege:

    $ sqlplus /
    
    
  2. Mike's password expires with the following command:

    SQL> alter user mike password expire;
    
    

How it works...

In step 1, you used OS authentication to connect to the database.

In step 2, you expired password for the user mike. This means that the password is no longer valid and user mike must change his password after the next login:

    SQL> alter user mike password expire;

    User altered.

    SQL> conn mike/welcome1
    ERROR: ORA-28001: the password has expired
    Changing password for mike
    New password:
    Retype new password:
    Password changed
    Connected.

See also

  • Creating and using OS-authenticated users

 

Creating and using OS-authenticated users


In this recipe, you'll learn about OS-authenticated users.

Getting ready

To complete this recipe, you'll need an existing user who has a dba role, for example, johndba. It is assumed that you are working on Linux.

How to do it...

  1. Connect to the database as a user who has a DBA role:

    $ sqlplus johndba
    
    
  2. Find the prefix for operating system authentication:

    SQL> show parameter os_authent_prefix
       
    NAME                  TYPE          VALUE      
    -----------------     --------      -----------
    os_authent_prefix     string        ops$
    
  3. Create an OS-authenticated user:

    SQL> create user ops$zoran identified externally;
    
    
  4. Grant this user the create session privilege:

    SQL> grant create session to ops$zoran;
    
    
  5. Log in to the operating system as the user zoran:

    $ su - zoran
    
    
  6. Connect to the database without entering a user name or password:

    $ sqlplus /
    
    

How it works...

In OS authentication, database delegates user authentication to the operating system. This means that in order for OS authentication to work, user must exist as the user of the operating system. In database, these users are created with a prefix that is defined in the os_authent_prefix parameter (default is ops$). If an OS-authenticated user has the create session privilege, he or she can connect to the database using the following syntax:

    SQL> connect /
    Connected.
    
    SQL> show user
    USER is "OPS$ZORAN"

Note

Note that you cannot grant a sysdba,  sysoper,  sysbackup,  sysdg, or  syskm privilege to users that are identified externally, using a  grant statement:

 SQL> grant sysdba to ops$zoran;
    grant sysdba to ops$zoran

ERROR at line 1: ORA-01997: GRANT failed: user
      'OPS$ZORAN' identified externally

If you want to connect as sysdba using OS authentication, you have to add OS user zoran to OS group DBA:

[root@db121 ~]# usermod -a -G dba zoran
[root@db121 ~]# su - zoran
[zoran@db121 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 03 20:14:03 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64 bit
Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

There's more...

You can change the os_authent_prefix parameter with custom value (or you can leave it blank if you want OS-authenticated database users to have the same name as OS users).

 

Creating and using proxy users


In this recipe, you'll learn about proxy users.

Getting ready

To complete this recipe, you'll need an existing (for example, OS-authenticated) user who has a DBA role and another existing user (for example, mike).

How to do it...

  1. Connect to the database as a user who has a DBA role:

    $ sqlplus /
    
    
  2. Create a proxy user named appserver:

    SQL> create user appserver identified by oracle_1;
    
    
  3. Grant create session to the user appserver:

    SQL> grant create session to appserver;
    
    
  4. Alter the user to connect through the proxy user:

    SQL> alter user mike grant connect through appserver;
    
  5. Connect to the database through proxy user:

    SQL> connect appserver[mike]
    
  6. Enter a password for the appserver user (for example, oracle_1):

    Enter password:
    
  7. To revoke connection through the proxy user, first connect to the database as a user who has altered user privilege:

    $ sqlplus /
    
  8. Revoke connection through the proxy user appserver from user mike:

    SQL> alter user mike revoke connect through appserver;
    

How it works...

Proxy authentication is best-suited type of authentication for three-tiered environments. The middle tier is represented as a proxy user in the database and this user can authenticate end-users in such a way that these end users can be audited by the database. In the second step, you created a user appserver (to be the proxy user). In the third step, you granted this user only the create session privilege.

Tip

It is recommended that you grant only the create session privilege to proxy users.

In step 4, you authorized user mike to connect through proxy user appserver. This means that the user appserver can connect to the database on behalf of user mike:

SQL> connect appserver[mike]

Enter password:
Connected.

SQL> show user
USER is "MIKE"

SQL> select sys_context('USERENV','PROXY_USER') from dual;
SYS_CONTEXT('USERENV','PROXY_USER')
-----------------------------------
APPSERVER

To see proxy users, you can query the proxy_users view:

SQL> select * from proxy_users;

PROXY      CLIENT  AUT   FLAGS---------- ------- ---- ------------------------------------
APPSERVER   MIKE    NO   PROXY MAY ACTIVATE ALL CLIENT ROLES

In the last step, you revoked authorization from user mike to connect through proxy user appserver. This means that the user appserver can no longer connect to the database on behalf of user mike.

There's more...

You can control which roles the proxy user can activate for user. By default, all user roles are activated. If you want the proxy user to activate only particular roles (or no roles) for a user, you can do that by adding the WITH ROLES <role1, role2, .., roleN> (or WITH NO ROLES) clause at the end of the alter user statement. For instance, if the user mike has many roles (including usr_role), and you want him to have only usr_role when he is connected through proxy user appserver, statement will look like this:

SQL> alter user mike grant connect through appserver with roles usr_role;


User altered.


SQL> connect appserver[mike]


Enter password:
Connected.


SQL> select * from session_roles;


ROLE
------------
USR_ROLE


SQL> connect mike


Enter password:
Connected.


SQL> select count(*) from session_roles;


COUNT(*)
--------
25

You can request reauthentication of a user to the database. This means that during proxy authentication, a user's password must be provided. This is done by using the authentication required clause at the end of alter user statement:

SQL> alter user mike grant connect through appserver authentication required;
User altered.
 

Creating and using database roles


In this recipe, you'll learn the basics about database roles. Roles group together related system and/or object privileges and they can be granted to users and other roles. They simplify privilege management (for example, rather than granting the same set of privileges to many users, you can grant those privileges to a role and then grant that role to users that need those privileges).

Getting ready

For this recipe, you will need an existing (for example, OS-authenticated) user that has a dba role and another three existing users (for example, mike, tom, and jessica). It is assumed that sample schemas are installed.

How to do it...

  1. Connect to the database as a user who has a dba role:

    $ sqlplus /
    
  2. Create the role usr_role:

    SQL> create role usr_role;
    
  3. Grant system privilege to usr_role:

    SQL> grant create session to usr_role;
    
  4. Grant object privileges to usr_role:

    SQL> grant select, insert on hr.employees to usr_role;
    
  5. Create another role as follows:

    SQL> create role mgr_role;
    
  6. Grant usr_role to mgr_role:

    SQL> grant usr_role to mgr_role;
    
  7. Grant system privileges to mgr_role:

    SQL> grant create table to mgr_role;
    
  8. Grant object privileges to mgr_role:

    SQL> grant update, delete on hr.employees to mgr_role;
    
  9. Grant usr_role to user (mike):

    SQL> grant usr_role to mike;
    
  10. Grant mgr_role to user (tom):

    SQL> grant mgr_role to tom;
    

How it works...

In the first step, you used OS authentication to connect to the database. In steps 2 and 3, you granted system privileges and object privileges, respectively, to the role usr_role. In the next steps, you practiced using database roles; you granted the following:

  • A role to another role

  • System and object privileges to role

  • Roles to users

You revoke privileges and roles by using a revoke statement. For example:

SQL> revoke usr_role from mike;

Note

Circular granting of roles is not allowed.

SQL> grant role1 to role2;
Grant succeeded.

SQL> grant role2 to role1;
grant role2 to role1
*
ERROR at line 1: ORA-01934: circular role grant detected

There's more...

Tip

You should be careful about granting privileges to the PUBLIC role because then every database user can use these privileges.

Suppose that user mike grants object privilege to user jessica with a grant option and user jessica grants that privilege to user tom. If user mike revokes that privilege from jessica, it will be automatically revoked from tom.

Note

Revoking a system privilege will not cascade.

SQL> grant select on hr.employees to jessica with grant option;
Grant succeeded.

SQL> connect jessica
Enter password:
Connected.

SQL> grant select on hr.employees to tom;
Grant succeeded.

SQL> connect tom/oracle_123
Connected.

SQL> select count(*) from hr.employees;
COUNT(*)
----------
 107

SQL> connect mike/welcome1
Connected.

SQL> revoke select on hr.employees from jessica;
Revoke succeeded.

SQL> connect tom/oracle_123
Connected.

SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist

Note

You cannot revoke object privileges you didn't grant.

See also

  • If you want to learn more about roles, see the official Oracle documentation—Oracle Database Security Guide 12c Release 1 (refer Chapter 4, Configuring Privilege and Role Authorization, of this documentation).

 

The sysbackup privilege – how, when, and why should you use it?


It is recommended that you use the sysbackup administrative privilege instead of the sysdba administrative privilege to perform operations related to backup and recovery tasks.

Getting ready

For this recipe, you'll need:

  • An existing database user (for example, tom) and a password file in 12c format, if you want to complete it using a password-authenticated user

  • An existing OS user (for example, john), who belongs to the backupdba OS group, in order to connect to the database using OS authentication

How to do it...

Instructions are given in the Database authentication and OS authentication sections.

Database authentication

The instructions for database authentication are as follows:

  1. Connect to the database as sysdba (or another user that can grant the sysbackup privilege):

    sqlplus / as sysdba
    
  2. Grant the sysbackup privilege to user tom:

    grant sysbackup to tom;
    
  3. Verify that there is an entry in the password file that grants user tom the sysbackup administrative privilege. Select data from the v$pwfile_users view:

    select * from v$pwfile_users;
    

    The following table is the result of the preceding command:

    Username

    sysdb

    sysop

    sysas

    sysba

    sysdg

    syskm

    con_id

    sys

    TRUE

    TRUE

    FALSE

    FALSE

    FALSE

    FALSE

    0

    sysdg

    FALSE

    FALSE

    FALSE

    FALSE

    TRUE

    FALSE

    0

    sysbackup

    FALSE

    FALSE

    FALSE

    TRUE

    FALSE

    FALSE

    0

    syskm

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    TRUE

    0

    tom

    FALSE

    FALSE

    FALSE

    TRUE

    FALSE

    FALSE

    0

  4. Test the connection using RMAN:

    rman target '"tom/oracle_123 as sysbackup"'
    

OS authentication

The instructions for OS authentication are as follows:

  1. Verify that the OS user (for example, john) is a member of the backupdba OS group:

    $ id john
    
  2. Connect to the database using the sysbackup privilege (SQL*Plus or RMAN):

    $> sqlplus / as sysbackup
    $> rman target '"/ as sysbackup"'
    

How it works...

You can use either Oracle Recovery Manager (RMAN) or SQL*Plus to perform the operations. When you connect to the database as sysbackup, you are connected as a predefined user sysbackup. If you want to check this, run the following statement:

SQL> select user from dual;

Otherwise, the following statement:

SQL> show user

Using the sysbackup privilege, you can connect to the database even when it is not open. This privilege enables better separation of duties and the implementation of the least privilege principle.

Note

From a security perspective, it is recommended that you implement the least privilege principle. The least privilege principle is an important security concept that requires that users are given only those privileges they need to perform their job.

To view the list of privileges a user can exercise when connected to the database using sysbackup privilege, you can create a user (for example, tom) and grant the user only sysbackup privileges. The next step is to connect to the database as user tom, using the sysbackup privilege and the execute statement:

select * from session_privs;

These privileges are shown in the following table:

Privileges (output from the previous statement)

   

sysbackup

select any transaction

select any dictionary

resumable

create any directory

alter database

audit any

create any cluster

create any table

unlimited tablespace

drop tablespace

alter tablespace

alter session

alter system

This is how you can check enabled roles:

SQL> select * from session_roles;

ROLE
-------------------
 SELECT_CATALOG_ROLE
 HS_ADMIN_SELECT_ROLE

Note

HS_ADMIN_SELECT_ROLE is granted to SELECT_CATALOG_ROLE.

If you want to view the roles and privileges granted to sysbackup, you can query DBA_ROLE_PRIVS and DBA_SYS_PRIVS:

SQL> select * from dba_role_privs where grantee='SYSBACKUP';
SQL> select * from dba_sys_privs where grantee='SYSBACKUP';

Also, this new administrative privilege enables you to select, insert, delete, execute, and perform operations:

SELECT

PERFORM operations

X$ tables

STARTUP, SHUTDOWN

V$ and GV$ views

CREATE PFILE, CREATE SPFILE

APPQOSSYS.WLM_CLASSIFIER_PLAN

CREATE CONTROLFILE

SYSTEM.LOGSTDBY$PARAMETERS

FLASHBACK DATABASE

INSERT/DELETE

DROP DATABASE

SYS.APPLY$_SOURCE_SCHEMA

CREATE/DROP RESTORE POINT (including GUARANTEED restore points)

SYSTEM.LOGSTDBY$PARAMETERS

EXECUTE

 

SYS.DBMS_BACKUP_RESTORE

SYS.DBMS_DATAPUMP

SYS.DBMS_RCVMAN

SYS.DBMS_IR

SYS.DBMS_PIPE

SYS.SYS_ERROR

SYS.DBMS_TTS

SYS.DBMS_TDB

SYS.DBMS_PLUGTS

SYS.DBMS_PLUGTSP

Tip

It is important for you to remember that: When using the sysbackup privilege, you can't view application data.

There's more...

You can't drop user sysbackup. In a multitenant environment, you can restrict a user to be able to perform backups only for the PDB it can connect to. You can accomplish that by creating a local user in the PDB and granting the sysbackup privilege to the user. When you are connected to the database as the sysbackup, you are connected as sysbackup user to SYS schema:

SQL> connect / as sysbackup
Connected.

SQL> show user
USER is "SYSBACKUP"

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')---------------------------------------SYS

See also

  • Creating password-authenticated users

  • Creating and using OS-authenticated users

 

The syskm privilege – how, when, and why should you use it?


It is recommended that you use the syskm administrative privilege instead of the sysdba administrative privilege to perform operations related to managing the transparent data encryption (TDE) keystore.

Getting ready

For this recipe, you'll need:

  • An existing database user (for example, jessica) and a password file in the 12c format, if you want to complete it using a password-authenticated user

  • An existing OS user (for example, bob), who belongs to the kmdba OS group, in order to connect to the database using OS authentication

How to do it...

Instructions are split into sections for database authentication and OS authentication.

Database authentication

The instructions for database authentication are as follows:

  1. Connect to the database as sysdba (or another user that can grant the syskm privilege):

    sqlplus / as sysdba
    
  2. Grant the syskm privilege to user jessica:

    grant syskm to jessica; 
    
  3. Connect user jessica to the database as syskm:

    SQL> connect jessica/oracle_1 as syskm
    
  4. View privileges:

    SQL> select * from user_tab_privs;
    SQL> select * from session_privs;
    

OS authentication

The instructions for OS authentication are as follows:

  1. Verify that an OS user (for example, bob) is a member of the kmdba OS group.

    $ id bob
    
  2. Connect to the database using syskm privilege:

    $ sqlplus / as syskm
    

How it works...

When you connect to the database as syskm, you are connected as a predefined user, syskm. Using the syskm privilege, you can connect to the database even when it is not open.

In most circumstances when using TDE, you don't have to have syskm administrative privilege. For a more detailed discussion about TDE operations and which privileges users need, see recipes in Chapter 8, Transparent Data Encryption.

In the Database authentication section after completing step 3, you can perform operations related to managing the TDE keystore. Step 4 is not necessary and its sole purpose is to show you which privileges you can use when connected as syskm. These privileges are:

  • ADMINISTER KEY MANAGEMENT

  • CREATE SESSION

  • SELECT on V$ (and GV$) views:

    • SYS.V$ENCRYPTED_TABLESPACES

    • SYS.V$ENCRYPTION_WALLET

    • SYS.V$WALLET

    • SYS.V$ENCRYPTION_KEYS

    • SYS.V$CLIENT_SECRETS

    • SYS.DBA_ENCRYPTION_KEY_USAGE

    • SYS.DATABASE_KEY_INFO

Tip

It is important for you to remember that: When using syskm privilege, you can't view the application data.

There's more...

You can't drop user syskm. When you are connected to the database as syskm, you are connected as the syskm user to SYS schema:

SQL> connect / as syskm
Connected.

SQL> show user
USER is "SYSKM"

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------------
SYS

See also

  • Creating password-authenticated users

  • Creating and using OS-authenticated users

  • Chapter 8, Transparent Data Encryption

 

The sysdg privilege – how, when, and why should you use it?


It is recommended that you use the sysdg administrative privilege instead of sysdba administrative privilege to perform operations related to data guard tasks.

Getting ready

For this recipe, you'll need:

  • An existing database user (for example, mike) and a password file in the 12c format if you want to complete it using a password-authenticated user

  • An existing OS user (for example, kelly), who belongs to the dgdba OS group in order to connect to the database using OS authentication

How to do it...

Instructions are split into sections for database authentication and OS authentication.

Database authentication

The instructions for database authentication are as follows:

  1. Connect to the database as sysdba (or another user who can grant the sysdg privilege):

    sqlplus / as sysdba
    
  2. Grant SYSDG privilege to user mike:

    SQL> grant sysdg to mike; 
    
  3. Exit SQL*Plus, connect mike using the dgmgrl command-line interface:

    SQL> exit
    $ dgmgrl
    DGMRRL> connect mike/test_1
    

OS authentication

The instructions for OS authentication are as follows:

  1. Verify that the OS user (for example, kelly) is a member of the dgdba OS group:

    $ id kelly
    
  2. Connect using the dgmgrl utility and OS authentication:

    $ dgmgrl
    
    DGMGRL> connect /
    

How it works...

When you connect to the database as sysdg, you are connected as a predefined user, sysdg. Using the sysdg privilege, you can connect to the database even when it is not open.

After completing step 2 successfully in the Database authentication section, user mike, as expected, can grant/revoke sysdg privilege to/from another existing user. If you want to try it out, type the statements given here.

After you connect to the database using the sysdg administrative privilege, you can perform the following operations:

Operations

 

STARTUP, SHUTDOWN

CREATE SESSION

ALTER SESSION

SELECT ANY DICTIONARY

ALTER DATABASE

FLASHBACK DATABASE

ALTER SYSTEM

EXECUTE SYS.DBMS_DRS

CREATE/DROP RESTORE POINT

(including GUARANTEED restore points)

SELECT X$ tables, V$ and GV$ views

DELETE APPQOSSYS.WLM_CLASSIFIER_PLAN

SELECT APPQOSSYS.WLM_CLASSIFIER_PLAN

Tip

It is important for you to remember that:

When using the sysdg administrative privilege, you can't view application data.

There's more...

You can't drop user sysdg. When you are connected to the database as sysdg, you are connected as sysdg user to the SYS schema:

SQL> connect / as sysdg
Connected.

SQL> show user
USER is "SYSDG"

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
------------------------------------------------------------------
SYS

See also

  • Creating password-authenticated users

  • Creating and using OS-authenticated users

About the Authors
  • 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.

    Browse publications by this author
  • 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.

    Browse publications by this author
Latest Reviews (3 reviews total)
Nice book, good ebook formatting
A pretty good helping an Oracle administrator to easely find any solution regarding database and data protection. Love it!
Great resource, very comprehensive
Oracle Database 12c Security Cookbook
Unlock this book and the full library FREE for 7 days
Start now