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?
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.
You can use a profile to implement your password policy.
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
.
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;
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.
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.
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.
In this task, you will create several users.
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.
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 isusers
, temporary tablespace istemp
, and who has their quota set tounlimited
on theusers
tablespace:SQL> create user richard identified by oracle_2 default tablespace users temporary tablespace temp quota unlimited on users;
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.
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.
Start EM Express and log in to it using the user that has either
EM_EXPRESS_BASIC
orEM_EXPRESS_ALL
role (you can usesys
orsystem
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 userted
), 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 is easy. You will practice it by changing passwords for several users in this recipe.
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
).
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:
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 is another way to change the user's password using the alter user
statement as follows:
SQL> alter user jessica identified by oracle_2;
This recipe explains a way to create a user with the same credentials on another database.
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
)
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, userjessica
):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';
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 is another way to accomplish the task.
This way requires select
on the sys.user$
table:
Connect to the first database as a user who has the
select
privilege on thesys.user$
table (for example, user who has thesysdba
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';
In this recipe, you'll learn to lock and unlock user accounts.
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
).
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;
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.
The expiration of user's password is a very easy task.
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
).
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;
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.
In this recipe, you'll learn about OS-authenticated users.
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.
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 /
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
In this recipe, you'll learn about proxy users.
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
).
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 userappserver
: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;
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.
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
.
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.
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).
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.
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
tomgr_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;
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;
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
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
.
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
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.
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 userAn existing OS user (for example,
john
), who belongs to thebackupdba
OS group, in order to connect to the database using OS authentication
Instructions are given in the Database authentication and OS authentication sections.
The instructions for database authentication are as follows:
Connect to the database as
sysdba
(or another user that can grant thesysbackup
privilege):sqlplus / as sysdba
Grant the
sysbackup
privilege to usertom
:grant sysbackup to tom;
Verify that there is an entry in the password file that grants user
tom
thesysbackup
administrative privilege. Select data from thev$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"'
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) | |||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This is how you can check enabled roles:
SQL> select * from session_roles;
ROLE
-------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_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 |
|
|
|
|
|
|
|
|
INSERT/DELETE |
|
|
|
| |
EXECUTE | |
|
|
|
|
|
|
|
|
|
|
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
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
It is recommended that you use the sysdg
administrative privilege instead of sysdba
administrative privilege to perform operations related to data guard tasks.
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 userAn existing OS user (for example,
kelly
), who belongs to thedgdba
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 who can grant thesysdg
privilege):sqlplus / as sysdba
Grant
SYSDG
privilege to usermike
:SQL> grant sysdg to mike;
Exit SQL*Plus, connect
mike
using thedgmgrl
command-line interface:SQL> exit $ dgmgrl DGMRRL> connect mike/test_1
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 | |
|
|
|
|
|
|
|
|
(including |
|
|
|
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