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