Creating a User with Database Authentication- Users and Basic Security
Database authentication is established with the CREATE USER SQL statement. Creating users as a DBA, your account must have the CREATE USER system privilege. This example creates a user named HSOLO with the password W3lcomeHere123 and assigns the default permanent tablespace USERS, default temporary tablespace TEMP, and unlimited space quota on the USERS tablespace:
This creates a bare-bones schema that has no privileges to do anything in the database. To make the user useful, you must minimally grant it the CREATE SESSION system privilege:
If the new schema needs to be able to create tables, you need to grant it additional privileges, such as CREATE TABLE:
If the new schema needs to be able to create tables, you need to grant it additional privileges, such as CREATE TABLE:
Creating a User with OS Authentication
OS authentication assumes that if the user can log in to the OS, then database privileges can be associated with and derived from the OS user account. There are two types of OS authentication.
• Authentication through assigning specific OS roles to users (allows database privileges to be mapped to users)
• Authentication for regular database users via the IDENTIFIED EXTERNALLY clause
Authentication through OS roles is detailed in Chapter 2. This type of authentication is used by DBAs and allows them to connect to an OS account, such as oracle, and then connect to the database with SYSDBA privileges without having to specify a username and password.
After logging in to the database server, users created with the IDENTIFIED EXTERNALLY clause can connect to the database without having to specify a username and password.
• Users with access to the server don’t have to maintain a database username and password.
• Scripts that log in to the database don’t have to use hard-coded passwords if executed by OS-authenticated users.
• Another database user can’t hack into a user by trying to guess the username and password connection string. The only way to log in to an OS-authenticated user is from the OS.
When using OS authentication, Oracle prefixes the value contained in the OS_ AUTHENT_PREFIX database initialization parameter to the OS user connecting to the database. The default value for this parameter OPS$, but it is recommended to set it to a null string:
You have to stop and start your database for this modification to take effect, so the decision to use OS authentication and the parameters settings should be done when you are creating the database. If you want an OS-authenticated user to be able to access the database, you still need to create the user:
Now, when jsmith logs in to the database server, this user can connect to SQL*Plus, as follows:
No username or password is required, because the user has already been authenticated by the OS.
