Oracle notes (14) User Management

Oracle notes (14) user management links: Oracle notes (1) Oracle introduction and Installation http://www.bkjia.com/database/201209/154049.html ; Oracle notes (2) SQLPlus command http://www.bkjia.com/database/201209/154051.html Oracle note (3) Scott's table structure http://www.bkjia.com/database/201209/154052.html Oracle notes (4) simple query, restricted query, and data sorting http://www.bkjia.com/database/201209/154054.html Oracle notes (5) Single Row Functions http://www.bkjia.com/database/201209/154056.html ; Oracle notes (6) Multi-Table query http://www.bkjia.com/database/201209/154060.html Oracle notes (7) data update, transaction processing, and data pseudo Columns http://www.bkjia.com/database/201209/154062.html Oracle notes (8) complex queries and summary http://www.bkjia.com/database/201209/154063.html Oracle notes (9) Table creation and management http://www.bkjia.com/database/201209/154316.html Oracle note (10) Constraints http://www.bkjia.com/database/201209/154317.html Oracle notes (11) Comprehensive Table creation, update, and query exercises http://www.bkjia.com/database/201209/154621.html Oracle note (12) set and Sequence http://www.bkjia.com/database/201209/154623.html Oracle note (13) views, synonyms, and indexes http://www.bkjia.com/database/201209/154627.html SQL statements are divided into three types: DML, DDL, and DCL. the DML and DDL operations have been completed before, and the DCL operations are poor now. DCL mainly indicates the control statements of the database, the operation permission is controlled. In DCL, there are two main syntaxes: GRANT and REVOKE. The operation Basis of the permission requires users, at this time, you need to use a new user for demonstration. To create a new user, you must first have the administrator permissions for sys and system operations. Example: Create a dog user with the password wangwangCONN sys/change_on_install as sysdba;
Create user dog identified by wangwang; a new USER is created. Www.2cto.com note: "ORA-00988: Password missing or invalid" error, which usually occurs when an abnormal global database name or system user password is specified when an Oracle routine is created. Note that the global database name cannot start with a number, and the password cannot start with a number.

The Oracle password requirements are as follows: the SYSTEM user (SYS, SYSTEM) password cannot be less than 7 characters long; the password consists of 1 to 30 characters (characters); it must begin with a letter, it cannot be a symbol or number. It can only contain letters, numbers, and "#", "_" and "$ ";
The password cannot contain Oracle/SQL keywords such as "SELECT", "DELETE", and "CREATE". However, the newly created user cannot log on, and the following error message is displayed: ORA-01045: user DOG lacks create session privilege; logon denied prompt that the user has no permission to CREATE the SESSION, as explained before, for sqlplusw, each user represents a SESSION, if you do not have the permission to create a SESSION, you cannot log on. Therefore, we need to authorize it below. Example: GRANT the create session permission TO the dog user grant create session to dog;
Now, after the new user successfully logs on, the following table creation operations can be performed. Create sequence myseq; www.2cto.com create table mytab (id number primary key, name VARCHAR2 (50); explanation: all data tables in Oracle are stored on the hard disk, but not each data table is saved on the hard disk, but the tablespace is saved on the hard disk, the data table is stored in the tablespace. If the hard disk is represented as a whole library, the tablespace represents each bookcase, and each table represents a book on the cabinet. Example: GRANT the TABLE creation permission TO the dog user to create table to dog. In this case, the dog user is granted the TABLE creation permission, but the tablespace operation permission is not granted TO the dog user, therefore, you still cannot create a table because there is no place to save the table.
In order to solve the user's authorization operation, many roles are provided for the user in Oracle. Each role contains multiple permissions, and there are two main roles: CONNECT and RESOURCE. Example: CONNECT and resource to dog; grant connect, resource to dog, users may lose their passwords.
Example: Change Password alter user dog identified by miaomiao, therefore, you can use the following command to invalidate the PASSWORD: alter user dog password expire; you can also control a user lock operation: alter user dog account lock; alter user dog account unlock; the above is for a basic user operation, but I have learned before that different users can access data tables of other users. In this case, you only need to add the complete "User Name. table name. Example: Use the dog user to query scott. emp table SELECT * FROM scott. emp; www.2cto.com, but cannot be searched now. In this case, you need to grant the scott user permission to the dog user to allow the dog user to access the resources of the scott user. The main permissions include: INSERT, DELETE, UPDATE, SELECT.
Example: Set scott. the SELECT and INSERT permissions of the emp table are granted to the dog user grant select and insert on scott. emp TO dog; now there is an authorized function, you can REVOKE permissions, and REVOKE permissions using the REVOKE command. Example: REVOKE the revoke select and insert on scott permissions of a dog user. emp FROM dog; revoke connect, RESOURCE, create table, create session from dog; since the user does not even have the permission, follow the Chinese sentence: "rolling and leaving ". Drop user dog CASCADE; the DBA is responsible for all the preceding operations.