The following command has been deprecated since Oracle 12c.
CREATE USER OPS$COIN_ADMIN IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users
The functionality described above is not supported in Oracle 19c. While creating OS authenticated accounts (using `OPS$`) was possible in older versions, Oracle removed this feature for security reasons starting with 12c Release 2 (12.2). Attempting to create an OS authenticated user in Oracle 19c using the syntax above will result in an error. Here's why:
- Security Concerns: OS authentication bypasses standard database password authentication, potentially creating security vulnerabilities if compromised.
- Alternative Authentication Methods: Oracle recommends using database accounts with strong passwords for user authentication. Additionally, Oracle offers various secure external authentication mechanisms like Kerberos, RADIUS, and LDAP for integrating with directory services.
Therefore, it's essential to use alternative authentication methods in Oracle 19c instead of relying on the discontinued `OPS$` accounts. If you have specific requirements for integrating with your operating system users, explore the recommended external authentication methods or consult the Oracle documentation for more secure solutions.
Oracle 19c offers several techniques for creating authenticated users, providing flexibility based on your specific security needs and environment. Here are the main options:
Local Database Authentication:
- Password-based authentication: This is the classic method where users create passwords stored in the database using `CREATE USER` statement. It's simple to implement but requires strong password policies and security considerations.
- External password files: You can store passwords in external files managed by other systems, increasing complexity but potentially better security through centralized control. Configure with `CREATE USER ... EXTERNAL` and manage files separately.
- Operating system authentication (OS authentication): Leverage existing operating system user accounts for database access. Convenient but requires alignment between database and OS user management and potential security risks if not configured properly.
External Authentication:
- Lightweight Directory Access Protocol (LDAP): Integrate with existing LDAP directories like Active Directory for centralized user management and authentication. Offers single sign-on (SSO) benefits and simplifies user management.
- Kerberos authentication: Use Kerberos protocol for secure authentication, often in conjunction with Active Directory. Provides strong security credentials and avoids storing passwords in the database.
- RADIUS authentication: Integrate with RADIUS servers for centralized authentication, typically used for network access control but can be extended to databases. Suitable for specific scenarios with existing RADIUS infrastructure.
- Public Key Infrastructure (PKI): Use digital certificates and public/private key pairs for strong authentication. More complex to set up but offers high security and eliminates password reliance.
Other Methods:
- Virtual Private Database (VPD): Create virtual users with limited access to specific database subsets based on external attributes. Useful for multi-tenant scenarios or fine-grained access control.
- Oracle Database Vault: Utilize advanced security features and roles available in this add-on, including application roles, dynamic roles, and fine-grained access control (FGAC).
Choosing the right technique: The best approach depends on various factors like:
- Security requirements: Balance convenience with security needs based on sensitivity of data and access control demands.
- Existing infrastructure: Leverag existing directory services, authentication systems, or security infrastructure whenever possible for efficiency and integration.
- Complexity: Consider the implementation and management complexity of each method based on your resources and expertise.
Consulting the Oracle documentation and seeking expert advice when necessary is crucial for making an informed decision.
You can no longer create an operating system authenticated user, by issuing a
CREATE USER statement which includes the keywords
IDENTIFIED EXTERNALLY
These replace the
IDENTIFIED BY clause where the password is usually set.
You also need to give the username a prefix of "
OPS$." So if you had an operating system user named
COIN_ADMIN, you would create an operating system authenticated account like this:
CREATE USER OPS$COIN_ADMIN IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users
...
The rest of the
CREATE command would be exactly like that shown in the previous lesson. Because the prefix OPS$ is used, these accounts are commonly referred to by DBAs as OPS$ ("ops-dollar") accounts.
