Oracle Data Operation and Control Language (Part 2)

Http://wanwentao.blog.51cto.com/2406488/457603

Create and modify users

Create user
Statement to create a user. When a user connects to the Oracle database, it must be verified. There are three types of authentication in Oracle:

Database
External
Global
The default value is database verification. When a user connects to the database, Oracle checks whether the user is a legal user of the database and provides the correct password. external verification, Oracle will only check whether the user is a valid user, and the password has been verified by the network or system. Global verification only checks whether the user is valid.
Server Authentication.
　　Database verification User Account
The database verification account is the default type and the most common type. To create an account that is piyush and whose password is welcome, run the following command:
Create use piyush identified by welcome
Piyush can use the following statement to change the password to saraswatt:
Alter user piyush identified by Saraswati;
　　External User Account Verification
The user account does not provide a password when entering the database. In this case, the client operating system is used to identify the password instead of the database. The external verification account is also called the ops $ account. When they first introduced oracle6, the Oracle account had a keyword prefix ops $, Which is why init. ora
The OS _authent_prefix parameter is ops $ -- the default feature is consistent with oracle6. The string defined by OS _authent_prefix must be preprocessed to the operating system account name used for external Oracle recognition accounts. The statement for creating an operating system user appl is:
Create user ops $ appl identified eaternally

However, in general, OS _authent_prefix will be set to null, as shown below:
Create user appl identified eaternally
The results are the same. The keyword identified externally tells Oracle that this is an external recognition account.
Global user account
The password of the global user account database is detected by the X.509 Directory Server. To create a global account, follow these steps:
Create user Scott identified globally as "cn = Scott, ou = divisional, O = sybex, c = us"
The keyword identified globally as indicates that a global user account is created.
　　Create and Change User Accounts
Create user
Used to create a user account and assign values to the attributes of the user account. Alter user is used to change user accounts and attributes. However, the create user statement must contain the user name and password.
Some attributes can be set using the creater user and alter user statements. The following describes the attributes:

Allocate default tablespace to users
Tablespace is used to place user objects such as tables, indexes, and clusters. If the create user statement does not contain a tablespace, the system tablespace is used by default.
Create user piyush identified by Saraswati
Defaulte tablespace user_data;
Alter user Manoj defaulte tablespace dev1_data;
Allocate temporary tablespace to users
Temporary tablespace, as its name implies, is a temporary segment for temporarily storing tables, indexes, and other user objects. Same Method
Create user piyush identified by Saraswati
Temporary tablespace user_data;
Alter user Manoj temporary tablespace dev1_data;
Allocate table space quota to users
Quota is used to limit the number of disks used by users in tablespaces. The quota can be set by byte, kilobytes, megabytes, or unlimited.
Create user piyush identified by Saraswati
Default tablespace user_data
Quota unlimited on user_data
Quota 20 m on tools;
Alter user Manoj quota 2500 K on tools;
Assign a simple table to the user
A simple table can limit the resources consumed by users during sessions. These resources include the database connection time, idle time, and the number of logical reads of data in each session. The default table has no limit on the resources.
Create user piyush identified by Saraswati
Profile tablespace user_data;
Alter user Manoj temporary tablespace dev1_data;
Specify role for user response
This attribute can only be set by the alter user statement. An exception is returned if you try to use the create user statement.
Alter user Manoj default role all privileges t salary_adm;
Set the expiration time for the user's password so that the user can change it upon next login
When the user's password expires, the password will be forced to be modified during the next login. Oracle prompts the user to enter the old password and then enter the new password. This function is often used by new users. When new users log on with the default password, they must modify the password immediately.
Alter user Manoj identified by welcome;
Alter user Manoj password expire;
Locked account, the user cannot log on
Alter user QL AC
Count lock
Unlock the account so that the user can log on to the database
Alter user QL account unlock

Permissions and Roles
Permissions allow users to access objects or programs belonging to other users. The Oracle system provides three permissions:
Object-level
System system level
Role role-level
These permissions can be granted to users and special users as public or roles. If you grant a permission to a special user "public" (the user public is predefined by Oracle, each user has the permissions of this user), which means that the permission is granted to all users of the database.
For management permissions, a role is a tool that can be granted to one role, and a role can also be granted to another role or user. Users can inherit permissions through roles. Besides management permissions, role services have no other purpose. Permissions can be granted or revoked in the same way.
　　Create and use roles
As mentioned above, a role is designed to make permission management easier. Create a role using the create role statement. Its syntax is as follows:
Create role role_name identified by password
Create role role_name identified externally
Create role role_name identified globally
By default, the created role does not have a password or other identifiers. If you use the identified by clause to create a role, the role does not automatically respond and must be activated with set role.
Set role role_name identified by password
Externally and globally roles are verified by the operating system and Oracle service server. Users usually need the permission to modify the data in the form used in the application, but only when the application is running and not using the ad hoc tool, this type of context-sensitive security can be achieved through the role of password. When you connect to the database within the application, the code runs the set role command and passes security verification. Therefore, you do not need to know the role password or enter the set role command.
　　Object permission
Object permission is the right to execute special actions on objects such as tables, views, sequences, processes, functions, and packages. There are nine different types of permissions that can be granted to users or roles. See the following table:

Permission	Alter	Delete	Execute	Index	Insert	Read	Reference	Select	Update
Directory	No	No	No	No	No	Yes	No	No	No
Function	No	No	Yes	No	No	No	No	No	No
Procedure	No	No	Yes	No	No	No	No	No	No
Package	No	No	Yes	No	No	No	No	No	No
DB object	No	No	Yes	No	No	No	No	No	No
Libary	No	No	Yes	No	No	No	No	No	No
Operation	No	No	Yes	No	No	No	No	No	No
Sequence	Yes	No	No	No	No	No	No	No	No
Table	Yes	Yes	No	Yes	Yes	No	Yes	Yes	Yes
Type	No	No	Yes	No	No	No	No	No	No
View	No	Yes	No	No	Yes	No	No	Yes	Yes

An object has more than one permission. special permissions can be granted or revoked. For example, table's all permissions include:
Select, insert, update, and delete, as well as index, alter, and reference.
The alter permission is used as an example to describe how to view the table.
Alter permission
The alter table and lock table operations are allowed. The alter table operation can be performed as follows:
. Change table name
. Add or delete Columns
. Change the data type or size of a column.
. Convert a table into a partitioned table
The alter permission on sequence allows you to execute the alter sequence statement and assign the minimum value, increment, and buffer size to the sequence again.
　　System Permissions
System permissions must be granted to system-level activities, such as database connection, user session modification, table creation, and user creation. You can obtain complete system permissions on system_privilege_map. Both the object permission and system permission are granted to the user or role through the grant statement. Note that the statement should be the with grant option clause when granting object permission, but the statement is with admin option when granting system permission, so when you try to grant system permission, when you use the statement with grant option, the system reports an error: only
Admin option can be specified. Pay special attention to this syntax and error message during the exam.