? Create and manage database user accounts: – Authenticate users – Assign default storage (tablespace)
1. Database user accountTo access the database, the user must specify a valid database user account and also successfully authenticate against the requirements of that user account.
Each database user has a unique database account。 Oracle recommends this approach to avoid potential security breaches and to provide meaningful data for specific audit activities. However, sometimes several users share a common database account. In these rare cases, the operating system and the application must provide sufficient security for the database. Each user account includes the following items:
? Unique user name: The user name cannot exceed 30 bytes, cannot contain special characters, and must begin with a letter.
? Authentication method:The most common authentication method is a password, but Oracle Database 11g supports a number of other validation methods, including biometric validation, certificate validation, and token validation.
? Default tablespace:If the user does not specify a different table space, the object can be created at this location. Note that having a default tablespace does not imply that the user has permission to create objects in the tablespace, nor does it mean that the user has a space limit for creating objects in that tablespace. These two items require separate authorization.
? Temp Table Space:This is where the instance creates temporary objects, such as sorting and staging tables, on behalf of the user. There is no limit to the temporary table space.
? User profile:The limit of a set of resources and passwords assigned to a user.
? Initial user group:Used by the resource manager.
? account Status:Users can only access the open account. Account_status may be a combination of "lock" and "fail". Note: Database users are not necessarily people. A common practice is to create a user who has a database object for a particular application, such as HR. A database user can be a device, an application, or simply a method of grouping database objects for security purposes. Database users do not need to have personally identifiable information.
2. Pre-defined accounts: SYS and system? SYS account: – was granted DBA role – All rights with admin option – the account to be used to perform startup, shutdown, and some maintenance commands – owning data dictionary – having an automated workload profile archive (awr:automatic Workload Repository )? The system account is granted the DBA role. These two accounts are not used in a routine operation (routine operations). By default, the SYS and system accounts are granted the database Administrator (DBA) role. In additionThe SYS account also has all the permissions of admin option and has a data dictionary. To connect to the SYS account, you must use the AS SYSDBA clause for the DB instance, and for automatic Storage Management (ASM) instances, you must use the AS SYSASM clause. Any user granted the SYSDBA permission can connect to the SYS account by using the AS SYSDBA clause.only authorized users who are granted SYSDBA, Sysoper, or sysasm permissions can start or close the instance. By default, the system account is granted the DBA role, not the SYSDBA permission. Best practices Tip: If you apply the least privilege principle, these accounts are not used in routine operations. A user who requires DBA authority has a separate account that is granted the required permissions. For example, Jim has a low-privilege account named Jim and an authorized account named JIM_DBA. Use this method to apply the principle of least privilege, do not require a shared account, and you can audit operations.SYS and the SYSTEM account are the required accounts for the database and cannot be deleted.
3. Create UserSelect Server > Users (Server > Users), and then click the Create button. It is also possible to create users with simple and practical sql: Create user test identified by test account unlock; On the Users page of Enterprise Manager, you can manage a list of database users who have access to the current database. Use this page to create, delete, and modify user settings. To create a database user, do the following: 1. In Enterprise Manager database Control, click the Server tab, and then in the Security section, click Users. 2. Click the Create button. Provide the information you need. Required items are marked with an asterisk (*), such as "name". Each user can assign a default tablespace and a temporary table space. If the user does not specify a tablespace when creating the object, the object is created in the default tablespace assigned to the owner of the object. This allows you to control where user objects are created. If no default tablespace is selected, the system-defined default persistent tablespace is used. This is also true for temporary table spaces: If no temporary tablespace is specified, a system-defined temporary tablespace is used.
4. Verify the user? Password? External validation? Global Authentication "Authentication" refers to the authentication of the identity of the person or object (user, device, or other entity) that is using the data, resource, or application. By validating this identity, you establish a trust relationship for further interaction. Authentication enhances security by linking access and operations to specific identities. When validation is complete, the authorization process allows or restricts the level of access and operations that the entity can perform.When you create a user, you must determine which validation method to use, and you can modify this method later.
Password:Also known as Oracle DB authentication. Each user created has an associated password that must be provided when the user attempts to establish a connection. When you set a password, you can invalidate the password immediately.
This forces the user to change the password after the first logon。 If you decide to invalidate the user password, make sure that the user can change the password. Some applications do not have this capability. By default, created in Oracle Database 11g
All passwords are case sensitive. These passwords can also contain multibyte characters, butlength is limited to 30 bytes。 Before the change, each password that was created in the database upgraded to Oracle Databases 11g is still case-insensitive. During a network (client/server and server/server) connection, the system always uses the Advanced Encryption Standard (AES) algorithm to encrypt the password automatically and transparently before sending the passwords over the network. Database validation, using the SQL language as follows: [email protected]> create user Aaron identified
by
Soccer2 default tablespace users3 temporary tablespace temp4 account Unlock5/user created.
External validation:This is the method used outside the database (
Operating System, Kerberos, or radius) to perform the validation. Kerberos or RADIUS requires the use of advanced security options. Users can connect to Oracle DB without specifying a user name or password. With advanced security options that perform rigorous validation, the system can identify users by using biometrics, X509 certificates, and tagged devices. When using external authentication, the database relies on the underlying operating system, the network authentication service, or the external authentication service to restrict access to the database account. The database password is not used for this type of login. You can use external authentication to authenticate users if the operating system or network service allows it.
If you are using operating system authentication, set the Os_authent_prefix initialization parameter and use the prefix in the Oracle user name. The Os_authent_prefix parameter defines a prefix that Oracle DB adds before each user's operating system account name. To achieve backward compatibility with earlier versions of Oracle software, the default value for this parameter is ops$. [Email protected]> show parameter os_authent_prefix NAME TYPE VALUE----------------------------------------------- ------------------------------Os_authent_prefix string ops$ When a user attempts to establish a connection, Oracle DB compares the user name prefixed with the Oracle user name in the database. For example, suppose Os_authent_prefix is set up as follows: os_authent_prefix=ops$, if a user's operating system account name is Tsmith and the user needs to connect to Oracle DB and is validated by the operating system, the Oracle DB Checks if there is a corresponding database user Ops$tsmith, and if such a user exists, allows the user to establish a connection. The prefixes shown in Ops$tsmith must be included in all references to the user (which the operating system has verified). Note: In some operating systems, the text of the Os_authent_prefix initialization parameter is case-sensitive. Operating system validation, using the SQL language is as follows: [email protected]> create user Aaron1 identified
Externally2 default tablespace users3 temporary tablespace temp4 account Unlock5/user created.
Global Validation:With Oracle Advanced security options, you can use Oracle Internet Directory to identify users by using global authentication.
5. Administrator AuthenticationOperating system security:? The DBA must have operating system permissions to create or delete files. Normal database users should not have operating system permissions to create or delete database files. Administrator security:? For SYSDBA, Sysoper, and SYSASM Connections: – Audit DBA users by name when using password files and strict authentication methods – Audit operating system account names when operating system authentication – for authorized users, operating system validation takes precedence over password file validation – Password files use case-sensitive passwords Operating system security: In Unix and Linux, DBAs belong to the install operating system group by default, and the group has the permissions required to create and delete database files. Administrator security: Allows authorized users to establish connections SYSDBA, Sysoper, and sysasm only after they have been authenticated with a password file or operating system permissions. If you use operating system authentication, the database does not use the provided user name and password. The operating system validation is used when the password file does not exist, or if the user name and password provided are not present in the file, or if a user name and password are not provided. By default, the password file in Oracle Database 11g uses a case-sensitive password. However, if authentication is successfully completed with a password file, the connection is logged using the user name. If authentication is successfully completed using the operating system, it means that this is a connect/connect, which does not log specific users. Note:
operating system validation takes precedence over password file validation。 Specifically, if you are a member of the OSDBA or Osoper group in the operating system and are connected as SYSDBA, Sysoper, or sysasm, you will be connected using the associated administrative permissions, regardless of the user name and password you specify. In Oracle Database 11g, authorized users can use strict authentication methods: Kerberos, SSL, or directory authentication (if Advanced security options are available).
6. Unlock the user account and reset the passwordDuring the installation and creation of the database, multiple database user accounts provided by Oracle can be unlocked and reset. If you did not select to unlock the user account during this period, you can unlock the user by selecting the user on the Users page, selecting Unlock User (unlock users) from the Actions list, and then clicking Go (Execute). This does not change the password. If the password is invalidated when the user is unlocked, the password will remain in the failed state until the user is edited and the password is changed. or use the SQL language: [email protected]> alter user bi account unlock; User altered. To unlock the user and reset the password, perform the following steps on the Edit Users page: 1. Enter the new password in the Enter Password (enter password) and Confirm Password (Confirm password) fields. 2. Select the unlocked (unlocked) check box. 3. Click Apply to reset the password and unlock the user account. or use the SQL language: [email protected]> alter user bi identified by bi account unlock; User altered. Source: http://blog.csdn.net/rlhua/article/details/12272827
