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 (sysbackup, syskm, 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...

Connect to the database as a user who has create profile privilege:
```
       sqlplus /
```

Create a password profile:

       create profile userprofile limit
       failed_login_attempts 4
       password_lock_time 2
       password_life_time 180;

Alter the user to use a newly created password profile:

       alter user scott profile userprofile;

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.

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...

Connect to the database as a user who has create user privilege:
```
       $ sqlplus /
```
Create a password-authenticated user (for example, username: jessica, password: oracle_1) as follows:
```
       SQL> create user jessica identified by oracle_1;
```

Create a password-authenticated user with a more complex password:

       SQL> create user tom identified by "Qax7UnP!123*";

Create a user that uses a specific password profile:

       SQL> create user mike identified by test1 profile
userprofile;

Create a user and force it to change password upon the first login:

       SQL> create user john identified by password1
password expire;

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

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):
Select Users from the Security drop-down menu:
Click on the Create User tab:
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:
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:
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:
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.

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...

Connect to the database as a user who has alter user privilege:
```
       $ sqlplus /
```
Change the password for user jessica:
```
       SQL> password jessica;
```
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:
```
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:
```
Connect to the database as any user (for example, tom, to change their own password):
```
       $ sqlplus tom/"Qax7UnP!123*"
```
Change the password using the following code:
```
       SQL> password
```
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:
```
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:
```
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.

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...

Connect to the first database as a user who has a DBA role:
```
       $ sqlplus /
```
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;
```
Connect to the second database as a user who has create user privilege:
```
       $ sqlplus zoran@orcl2
```

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:

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
```
Find the hash value of a user's password (for example, user jessica):
```
SQL> select spare4
from user$
where name='JESSICA';
```
Connect to the second database as a user who has create user privilege:
```
$ sqlplus zoran@orcl2
```

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';

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...

Connect to the database as a user who has alter user privilege:
```
$ sqlplus /
```
Lock the account of user mike:
```
SQL> alter user mike account lock;
```
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.

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...

Connect to the database as a user who has the alter user privilege:
```
$ sqlplus /
```
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.

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...

Connect to the database as a user who has a DBA role:
```
$ sqlplus johndba
```

Find the prefix for operating system authentication:

SQL> show parameter os_authent_prefix
   
NAME                  TYPE          VALUE      
-----------------     --------      -----------
os_authent_prefix     string        ops$

Create an OS-authenticated user:

SQL> create user ops$zoran identified externally;

Grant this user the create session privilege:

SQL> grant create session to ops$zoran;

Log in to the operating system as the user zoran:
```
$ su - zoran
```
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...

Connect to the database as a user who has a DBA role:
```
$ sqlplus /
```

Create a proxy user named appserver:

SQL> create user appserver identified by oracle_1;

Grant create session to the user appserver:

SQL> grant create session to appserver;

Alter the user to connect through the proxy user:

SQL> alter user mike grant connect through appserver;

Connect to the database through proxy user:
```
SQL> connect appserver[mike]
```
Enter a password for the appserver user (for example, oracle_1):
```
Enter password:
```
To revoke connection through the proxy user, first connect to the database as a user who has altered user privilege:
```
$ sqlplus /
```
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...

Connect to the database as a user who has a dba role:
```
$ sqlplus /
```
Create the role usr_role:
```
SQL> create role usr_role;
```
Grant system privilege to usr_role:
```
SQL> grant create session to usr_role;
```

Grant object privileges to usr_role:

SQL> grant select, insert on hr.employees to usr_role;

Create another role as follows:
```
SQL> create role mgr_role;
```
Grant usr_role to mgr_role:
```
SQL> grant usr_role to mgr_role;
```
Grant system privileges to mgr_role:
```
SQL> grant create table to mgr_role;
```

Grant object privileges to mgr_role:

SQL> grant update, delete on hr.employees to mgr_role;

Grant usr_role to user (mike):
```
SQL> grant usr_role to mike;
```
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.

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:

Connect to the database as sysdba (or another user that can grant the sysbackup privilege):
```
sqlplus / as sysdba
```
Grant the sysbackup privilege to user tom:
```
grant sysbackup to tom;
```

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`

Test the connection using RMAN:

rman target '"tom/oracle_123 as sysbackup"'

OS authentication

The instructions for OS authentication are as follows:

Verify that the OS user (for example, john) is a member of the backupdba OS group:
```
$ id john
```
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

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:

Connect to the database as sysdba (or another user that can grant the syskm privilege):
```
sqlplus / as sysdba
```
Grant the syskm privilege to user jessica:
```
grant syskm to jessica; 
```
Connect user jessica to the database as syskm:
```
SQL> connect jessica/oracle_1 as syskm
```

View privileges:

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

OS authentication

The instructions for OS authentication are as follows:

Verify that an OS user (for example, bob) is a member of the kmdba OS group.
```
$ id bob
```
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

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:

Connect to the database as sysdba (or another user who can grant the sysdg privilege):
```
sqlplus / as sysdba
```
Grant SYSDG privilege to user mike:
```
SQL> grant sysdg to mike; 
```
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:

Verify that the OS user (for example, kelly) is a member of the dgdba OS group:
```
$ id kelly
```
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