Oracle Users and Profiles
2012-09-01 15:05:47 | Category: Oracle | Tags: user and profile | report | font size < Span class= "FC04 stag" > great small subscription
User management seems to be simple, but it is also one of the most common problems, as it includes not only the creation of users and authorizations, but also security policy and resource management. In large applications, the management of resources is a test for DBAs. By default, the user is assigned the default profile, which assigns the file to each created user. However, the file does not have any restrictions on resources, so administrators often need to set up their own profiles based on their own database system environment.
1. User creation can be created using the Create User command, as follows:sql> create user djp01 identified by djp2012 2/ user created. sql> Description: Create the user name Djp01, user identified by the specified user's password, password is djp2012. The above statement does not specify a permanent tablespace and a temporary tablespace for the user djp01, at which point Oracle will specify a default permanent tablespace for the newly created user and a temporary table space. The following:sql> select Default_tablespace,temporary_tablespace 2 from dba_users 3 where username = upper (' djp01 ') 4/ defa Ult_tablespace------------------------------------------------------------Temporary_ Tablespace------------------------------------------------------------userstemptbsgrp sql> When you create a new user, Users must be granted the necessary permissions before they can work. For DJP01 users, you cannot connect to the data because it does not have the associated permissions. To enable the user to connect to the database, it must be granted connection permissions, as follows:sql> Conn Djp01/djp2012error:ora-01045:user DJP01 lacks CREATE SESSION privilege; Logon denied warning:you is no longer connected to ORACLE. Sql>sql> Grant Create session to DJP01 2/ grant succeeded. sql> Conn djp01/djp2012connected.sql> Description: When the user Djp01 granted connection permissions, the user successfully connected. For connectivity, Oracle also provides a connected role of connect. You can grant a connection role to a user. Below we look at the user djp01 on the table spaceIn case:sql> Select tablespace_name,bytes/1024/1024 as Use_size 2 from Dba_ts_quotas 3 where username = upper (' djp01 ') 4/& Nbsp;no rows selected sql> We found that user DJP01 does not have any quotas on the table space users. Here's our quiz paper under User djp01 CREATE table,sql> CREATE TABLE T 2 (3 x number, 4 y varchar2 (Ten) 5) 6/create table T*error at line 1:ora-01031 : Insufficient permissions SQL>SQL> grant CREATE table to DJP01 2/ grant succeeded. sql>sql> CREATE TABLE T 2 (3 X number, 4 y varchar2 (10) 5) 6/Description: When we create a Table object under the DJP01 user, we prompt for no permission, then grant the appropriate permission and recreate it, and it is found that the statement has been pending. Because, user DJP01 does not have a certain quota in the table space users. (The "ORA-01950" error occurs when there is no quota, but is missing from the pending state in 11g.) This is based on the actual environment to analyze). Below we give the user djp01 to allocate a certain quota in the Tablespace users and create:sql> alter user DJP01 2 quota 100M on the users 3/ user altered. sql>sql> CREATE TABLE T 2 (3 x number, 4 y varchar2 (Ten) 5) 6/ table created. sql> At this point, we found that it was possible to create it successfully. Let's look at the usage of user djp01 on the tablespace:sql> select tablespace_name,bytes/1024/1024 as Quota_size 2 from Dba_ts_quotas 3 where Username = Upper (' djp01 ') 4/ tablespace_name quota_size----------------------------------------------------------------------USERS .0625 sql> We can also specify a specific tablespace and quota when creating a user, as follows:sql> create user djp02 identified by djp2012 2 default tablespace imtabdata 3 Temporary tab Lespace TEMPTBSGRP 4 Quota 200M on Imtabdata 5/ user created. sql> Description: Use default Tablespac Specify the user djp02 the default persistent tablespace imtabdata,temporary tablespace Specify a temporary tablespace or a temporary tablespace group (refer to Http://blog.163.com/donfang_ for temporary tablespace groups) jianping/blog/static/13647395120127252910384/). Use quota to assign quotas to the user in the specified tablespace. &NBSP;2, user changes and deletions can be changed by using the Altter User command to change the user's entry as: User's password user's Tablespace quota assignment profile and default role (profile see below) such as: We change the password for user djp01, SQL > Alter user DJP01 identified by DJP01 2/ user altered. sql> Other changes are similar to the change password, I do not list here, if necessary, please consult the relevant information or leave me a message. If you modify the password by using the ALTER USER command, you must have ALTER user permission or DBA change. If the user changes themselves, you can use the password command under Sql*plus, as follows:sql> passwordchanging password for djp01old password:new password:retype New Password:password changedsql> conn djp01/djp2012connected.sql> password changed successfully. for deletion of the user, you can delete it using the drop user command as follows: SQl> Drop user Djp01;drop user Djp01*error at line 1:ora-01922: must specify CASCADE to remove ' DJP01 ' SQL> we found using drop use R deletes the user Djp01 and throws an error. This is because the DJP01 user owns the object and cannot be easily deleted. If you really want to delete it, you can add the Cascade command. The following:sql> drop user djp01 cascade; user dropped. sql> is then deleted along with its object. If the user does not have a specific object, use the drop user <username > Delete.
3. Profile we create a user to grant permissions to, then it can be used by that user. If a user frantically uses resources, it will result in a lack of database resources or a user will not be a crazy password to crack, it is likely to cause the disclosure and loss of information. For some, we can use profiles (profile) in Oracle to set constraints on resources and password-related features. When we create a user, if we do not specify the corresponding profile, Oracle assigns it a default profile. The following:sql> Select Profile 2 from Dba_users 3 where username = upper (' djp02 ') 4/ profile----------------------------- -------------------------------default sql> Description: The profile used by user djp02 is DEFAULT. Below we look at the features of the profile default, as follows:sql> select DISTINCT Resource_name,limit 2 from Dba_profiles 3 where profiles = Upper (' Default ' ) 4/ resource_ NAME----------------------------------------------------------------LIMIT------------------------------------------------ --------------------------------password_lock_time1 connect_timeunlimited idle_timeunlimited resource_ NAME----------------------------------------------------------------LIMIT------------------------------------------------ --------------------------------Password_grace_time7 private_sgaunlimited logIcal_reads_per_sessionunlimited resource_ NAME----------------------------------------------------------------LIMIT------------------------------------------------ --------------------------------Logical_reads_per_callunlimited failed_login_attempts10 password_ Verify_functionnull resource_ NAME----------------------------------------------------------------LIMIT------------------------------------------------ --------------------------------Cpu_per_sessionunlimited sessions_per_userunlimited password_life_ Time180 resource_ NAME----------------------------------------------------------------LIMIT------------------------------------------------ --------------------------------Password_reuse_maxunlimited password_reuse_timeunlimited cpu_per_ Callunlimited resource_ NAME----------------------------------------------------------------LIMIT------------------------------------------------ --------------------------------Composite_limitunlimited 16 rows selected. sql> by looking at the discovery, the profile default value for most parameters is almost unlimited. If you specify a default element file for the user, you may end up with a lack of a problem. Below we look at a general overview of the creation and use of:sql> Create Profile miser 2 limit 3 Connect_time 4 idle_time 5 Sessions_per_user 2 6 failed_ Login_attempts 3 7/ profile created. sql> Description: We created the profile for miser (Chinese translated as "Scrooge", I often read some master-level books, they are very particular about the extraction of names, I use it here too), Connect_time points out that the database allows a maximum connection of 120 seconds, idle_time indicates that if the user is idle for more than 60 seconds, the user exits; Sessions_per_user indicates that only two sessions are restricted to the user; Failed_ Login_attempts points out that if a user logs on three times without success, the user will be locked. The profile can be specified when the user is created, or it can be specified after the user is created, as in the example:sql> create user djp01 identified by djp2012 2 default tablespace imtabdata 3 Temporary Tablespace TEMPTBSGRP 4 Quota 100M on Imtabdata 5 profile miser 6/ user created. sql> Description: We use the Profile keyword on line 4th to refer to The profile is miser. We can also use the following test to specify:sql> alter user DJP01 2 profile miser 3/ user altered. sql> at this point, the user djp01 already has the features in the profiles. Below we make a simple test, the following:sql> conn Djp01/aaaerror:ora-01017:invalid Username/password; Logon denied warning:you is no longER connected to oracle. sql> conn Djp01/bbberror:ora-01017:invalid Username/password; Logon denied sql> conn Djp01/cccerror:ora-01017:invalid Username/password; Logon Denied sql> conn djp01/ddderror:ora-28000:the account is locked sql> conn Djp01/2012error : ora-28000:the account is locked sql> we found that when the user djp01 logged on three times unsuccessfully, the user will be locked. When the user is locked, we can use the superuser to unlock it, as follows:sql> alter user DJP01 account unlock 2/ user altered. sql> Conn djp01/ After the djp2012connected.sql> is unlocked, it can be successfully connected. There are parameters about the profile, which can be divided into two categories, one for the resource class and the other for the cipher class. Here are the following: (1) Resource class Connect_time: Specifies the total time that a session can remain connected to the database Cpu_per_call: limits the time that the CPU is used per call within a transaction. Cpu_per_sessin: Limits the time that the CPU is used within each session. Sessions_per_time: Limit the maximum number of sessions that a user can open concurrently. Idle_time: Limit the user's maximum idle time. Logical_reads_per_session: Limit the total number of data block reads. Logical_reads_per_call: Limit the total number of logical reads per session call. PRIVATE_SGA: Specifies a space quota allocated in the shared pool component of the SGA (for shared servers only). Composite_limit: Use a total limit for resource settings. Oracle considers four parameters to calculate a weighted composite_limit. respectively: CPU_PER_SESSION,LOGICAL_READS_PER_SESSIONS,CONNECT_TIME,PRIVATE_SGA. You can use ALTER resourceCost to set: (2) Password class failed_login_attempts: Refers to the maximum number of logins that can be attempted before a user is locked out. Password_life_time: Specifies the time limit for using a specific password, and if this interval is exceeded, the password expires. Password_grace_time: Set a time period during which a password expiration warning will be issued. Password_lock_time: Sets the number of days that the user is locked out, and after that, the user will unlock themselves. Password_reuse_time: Specifies how many days it will take to re-use the password. Password_reuse_max: Specifies how many modifications are needed before a particular password is reused. Passwrod_verify_function: This parameter allows you to specify the password validation function provided by Oracle to establish automatic password validation. Parameters slightly more, for these parameters, I suggest not to go back, as long as you know the meaning of it, if there is a parameter forgotten, you can view the Dba_profiles dictionary. You can use ALTER profile to change the profiles, as follows:sql> alter MISER 2 limit 3 Password_life_time 4 password_grace_time 5/ Profile altered. sql> If you use the settings of the resource in the profiles, you must change the initialization parameter Resource_limit to True if you make it effective, and the default value for this parameter is false. You can use drop profile to delete, as below:sql> drop profile miser 2/drop-Miser*error at line 1:ora-02382: Profiles miser specified users, no CASCADE Delete SQL> prompt delete error because profile miser specifies the user, and if no user is specified, you can use this command to delete it. If you specify a user, to delete, you can use the following deletion method:sql> drop profile miser Cascade 2/ profile dropped. sql> successfully deleted. Most profiles are used for password management, and for resource management, Oracle provides a more advanced management tool--database Rsource Manager,Chapter I will describe the use of the tool.
Oracle_ Users and Profiles
