Oracle Data Operation and Control Language (3)

Create and modify users

The create user statement creates 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 authentication only checks whether a user is valid and the password is verified by oraclesecurity server.

　　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, Oracle accounts all had the keyword prefix ops $, Which is why init. the ora parameter OS _authent_prefix 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 is 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 with grant option statement, the system reports an error: Only admin option can be specified. Pay special attention to this syntax and error message during the exam.

　　Roles and permissions

Role permissions are used to grant permissions of a user to a role. Any permission can be granted to a role. You must use the with_admin_option clause to grant the system permission to the grantee. You can use the set role statement to grant or revoke the role permission during a session. However, role permissions cannot rely on permissions stored in SQL. If a function, program, package, trigger, or method uses an object owned by another plan, you must directly authorize the owner of the object because the permission will not change between sessions.

　　Grant and revoke permissions

Grant permissions to a user or role to use 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 permission is granted with grant option,

　　Permission and Data Dictionary

A data dictionary is a place where Oracle stores information about the database structure. data is stored elsewhere. A data dictionary consists of tables and views. The easiest way to take a data dictionary test during the test is to view the permissions that have been granted. For example, dba_tab_priv contains the object permission granted to another user by the user and whether the permission is granted with the with Grant otpion substring. Note that dba_tab_priv not only includes the relationship between table permissions, but also permissions on functions, packages, queues, and so on. The following table lists the data dictionary views of all permissions and roles:

Table: Permission data dictionary View

View	Function
All_col_privs	Indicates column authorization. The user and public are granted to the column.
All_col_privs_made	Indicates column authorization. The user is the owner and the authorized user.
All_col_recd	Indicates column authorization. The user and public are granted to the column.
All_tab_privs	Indicates the authorization on the object. The user is public or authorized or the user is the owner.
All_tab_privs_made	Indicates the permission on the object. The user is the owner or an authorized user.
All_tab_privs_recd	Indicates the permission on the object. The user is public or authorized.
Dba_col_privs	All permissions on Database Columns
Dba_role_privs	Show roles that have been granted to users or other roles
Dba_sys_privs	System permissions granted to users or roles
Dba_tab_privs	All permissions on database objects
Role_role_privs	Show roles granted to users
Role_sys_privs	Displays the system permissions granted to users through roles
Role_tab_privs	Displays the object permissions granted to users through roles
Session_privs	Displays all current system permissions that a user can use
User_col_privs	Displays the permissions on the column. The user is the owner, the grantee, or the grantee.
User_col_privs_made	Displays the permissions granted to the column. The user is the owner or the principal.
User_col_privs_recd	Displays the permissions granted to the column. The user is the owner or authorized.
User_role_privs	Show all roles granted to users
User_sys_privs	Displays all system permissions granted to users
User_tab_privs	Show all object permissions granted to the user
User_tab_privs_made	Displays the object permissions granted to other users. The user is the owner.
User_tab_privs_recd	Displays the object permissions granted to other users. The user is an authorized user.