Oracle data manipulation and Control Language (iii)

oracle| Control | data | Details setting up and modifying the user Create USERS statement creates a user. When a user connects to an Oracle database, it must be validated. There are three types of authentication in Oracle: Database external Global defaults to DB authentication, and when a user connects to a database, Oracle detects whether the user is a legitimate user of the database and provides the correct password.external authentication, or Acle will only detect if the user is a legitimate user, password has been authenticated by the network or system.　　Global authentication is also the only test for legitimate users, and password is validated by Oraclesecurity server. Database authentication user account Data bank Authentication account is a good default type and the most common type. Create an account is Piyush, password is welcome account, just execute the following command: Create use Piyush identified by welcome Piyush can change the password to saraswatt:alter through the following statement　　USER Piyush identified by Saraswati; The external authentication user account user account enters the database can not provide the password, in this case replaces the database to identify the password is the client operating system. External authentication account is sometimes called ops$ account, when they first introduced in Oracle6, Oracle account has a keyword prefix ops$, which is why Init.ora parameter os_authent_prefix is ops$-- Default features are consistent with Oracle6. The string defined by Os_authent_prefix must be preprocessed to the name of the operating system account used for the Oracle external identification account. The statement to create the operating system user APPL is: Create user ops$appl identified eaternally but in general, Os_authent_prefix will be set to NULL, as follows: Create user appl IDE　　Ntified eaternally This effect is the same, the keyword identified externally tells Oracle this is an external identification account. The global user account global type User account database does not detect passwords, but is detected by the X.509 directory server. The method to create a global type user account is: Create user Scott IdentiFied globally as "cn=scott,ou=divisional,o=sybex,c=us" keyword identified globally as indicates that a global type of user account is established. Create and change user account creation user to create user accounts and assign values to user accounts. The alter user is used to change user accounts and properties.　　However, the CREATE USER statement must include the username and password. There are some properties that can be set with the creater user and ALTER user statements, which are described below: Assigning a default tablespace tablespace (tablespace) to a user is a user object that places tables, indexes, bundles, and so on. If the table space is not included in the CREATE USER statement, the default is the system tablespace. CREATE USER Piyush identified by Saraswatidefaulte tablespace User_data;　　ALTER USER Manoj Defaulte tablespace dev1_data; Assign temporary table space to user temporary table space, as the name implies is temporary storage table, index and other user objects temporary segment. Create a method like create USER Piyush identified by Saraswatitemporary tablespace User_data;　　ALTER USER Manoj temporary tablespace dev1_data; Use quotas to allocate table spaces to users limit the number of users using the disk in the table space. Quotas can be made in bytes, kilobytes, megabytes, or unrestricted. CREATE USER Piyush identified by Saraswatidefault tablespace User_dataquota Unlimited on User_dataquota 20M on tools;　　ALTER USER Manoj QUOTA 2500K on tools; Assigning a summary summary to a user can limit the resources that a user consumes during a session. These resources include: time to connect to the database, idle time, number of logical read data per session, and so on, and the default summary table is unrestricted for resources. CREATE USER Piyush identified by Saraswatiprofile tablespace User_data; ALTER USERManoj temporary tablespace dev1_data; Specifying roles for user responses This property can only be set by the ALTER USER statement, attempting to use the Create USER statement setting to return an exception.　　ALTER USER Manoj DEFAULT role all EXCEPT salary_adm; Set the expiration time for the user's password to change when the user's password expires at the next logon, forcing the modification password,oracle prompt the user to enter the old password at the next logon, and then enter the new password. This feature is commonly used for new users and must modify password immediately when a new user logs on with the default password. ALTER USER Manoj identified by welcome;　　ALTER USER Manoj PASSWORD EXPIRE; Locked account, is the user can not login alter user QL AC COUNT lock to unlock the account, so that users can log on to the database alter user QL accounts UNLOCK permissions and role permissions allow users to access objects or programs belonging to other users, or The Acle system provides three kinds of permissions: Object-level system-level role roles level these permissions can be granted to a user, a special user public, or a role if a permission is granted to the special user "public" (User public is Oracle-predefined, each　　The user has access to this user, it means that the permission is granted to all users of the database. For administrative permissions, a role is a tool that can be granted to a role, and the role can be granted to another role or user. Users can inherit permissions through roles, and there is no other purpose than to administer the role service outside of a permission.　　Permissions can be granted, or they can be revoked in the same way. Establish and use roles as previously sued, the purpose of the role exists is to make the management of permissions easier. Set up a role using the Create roles statement, his syntax is as follows: The Create character Role_name identified by Passwordcreate role-based Role_name identified externallycreate Role Role_name identified globally the roles established by default do not have password or other recognition. If you use IdentifieD by clause, the role does not respond automatically and must be activated with set roles. The role of SET roles role_name identified by password externally and globally type is validated by the operating system and Oracle Service server. Typically, a user needs permissions to modify data in a form used in an application, but this context-sensitive security can be achieved through a password role only if the application is running and not using the ad hoc tool. When a user connects a database within an application, the code executes the set role command and passes security validation.　　So the user does not need to know the character's password, nor does he need to enter the set role command himself. Object Permission object permission refers to the right to perform special actions on objects such as tables, views, sequences, procedures, functions, or packages. There are nine different types of permissions that can be granted to users or roles. The following table: Permissions ALTER DELETE EXECUTE INDEX INSERT READ REFERENCE SELECT UPDATE Directory No no no no no no no no No no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no no procedure no no no o Table Yes yes No Yes Yes no no no no no no no no no no no no no no no no no no no yes No yes Yes object by more than one permission, Special permissions all can be granted or revoked.　　The all permissions on the table include: Select,insert,update and delete, and index,alter, and reference. How to look at this table we take ALTER permission as an example to illustrate ALTER permission allows ALTER TABLE and lock table operations to be performed, ALTER table can do the following:. Change the table name. Add or remove Columns. Change the data type or size of the column.　　Alter permission to convert a table to a partitioned table on a sequence allows an ALTER sequence statement to be executed, and the sequence is reassigned to the minimum, increment, and buffer sizes. System permission system permissions require the grantor to have the ability to perform system-level activities, such as connecting to the database, changing user sessions, creating tables or creating users, and so on. You can get full system permissions on the data dictionary view System_privilege_map. Both object permissions and system permissions are granted to the user or role through the GRANT statement. Note that the statement should be with the GRANT OPTION clause when granting permission to the object, but the statement is with ADMIN option when granting the system right, so when you try to grant system permissions, use the statement with Grant Option system will report an error: only ADMIN OPTION can is specified.　　Pay special attention to this syntax and error message in your exams. Role and Role permission role permissions are to grant a role to a user. Any permission can be granted to a role. Granting system permissions to the grantee must use the WITH_ADMIN_OPTION clause to grant or revoke role permissions through the set roles statement during the session. However, role permissions cannot depend on the permissions stored in SQL.　　If a function, program, package, trigger, or method uses another object that is scheduled, it must be granted directly to the owner of the object, because permissions do not change between sessions. Grant and Revoke permissions grant permissions to a user or role using the GRANT statement, the syntax of the GRANT statement is as follows: Grant role (or system privilege) to User (Role,public) with ADMIN OPTION (optional) object Permissions are granted with Grant OPTION, the Permissions and data dictionary data dictionary is where Oracle stores information about the structure of the database, the data itself is stored elsewhere, and the data dictionary consists of tables and views. The easiest thing to test about a data dictionary in an exam is to see what kind of permissions have been granted. For example, Dba_tab_priv contains information about the object permissions granted to another user and whether the WITH Grant Otpion substring is granted. Note that Dba_tab_priv not only contains the relationship to the permissions of the table, but also the permissions on functions, packages, queues, and so on. The following table lists the data dictionary views for all permissions and roles:　　Table: The Data dictionary view view of permissions All_col_privs represents the authorization on the column, the user and public are the all_col_privs_made of the delegate on the column, the user is the owner and the grantee ALL_COL_RECD the authorization on the column, the user and P Ublic is the grantee all_tab_privs representing the authorization on the object, the user is public or the grantee or the user is the owner of the all_tab_privs_made representing the permissions on the object, the user is the owner or the grantee ALL_TAB_PRIVS_RECD Represents a permission on an object, the user is public or the grantee Dba_col_privs all the authorization Dba_role_privs on the database column displays the roles that have been granted to the user or other roles Dba_sys_privs the system permissions that have been granted to the user or role Dba_tab_ Privs all permissions on a database object Role_role_privs displays the roles that have been granted to the user Role_sys_privs displays the system permissions that are granted to the user through the role Role_tab_privs displays the object permissions granted to the user through the role session_ PRIVS displays all the system rights that the user is now available user_col_privs the permissions on the display column, the user is the owner, the grantee, or the grantee User_col_privs_made displays the permissions granted on the column, the user is the owner or the grantee User_col_privs _RECD displays the permissions granted on the column, the user is the owner or the grantee User_role_privs displays all the roles that have been granted to the user User_sys_privs displays all system permissions that have been granted to the user User_tab_privs Displays all object permissions that have been granted to the user User_tab_privs_made displays the object permissions that have been granted to other users, the user is the primary USER_TAB_PRIVS_RECD display the object permissions that have been granted to other users, and the user is the grantee