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.
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 userAn existing OS user (for example,
bob
), who belongs to thekmdba
OS group, in order to connect to the database using OS authentication
Instructions are split into sections for database authentication and OS authentication.
The instructions for database authentication are as follows:
Connect to the database as
sysdba
(or another user that can grant thesyskm
privilege):sqlplus / as sysdba
Grant the
syskm
privilege to userjessica
:grant syskm to jessica;
Connect user
jessica
to the database assyskm
:SQL> connect jessica/oracle_1 as syskm
View privileges:
SQL> select * from user_tab_privs; SQL> select * from session_privs;
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
onV$
(andGV$
) 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
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
Creating password-authenticated users
Creating and using OS-authenticated users
Chapter 8, Transparent Data Encryption