Oracle user creation operations are familiar to everyone. The following describes the syntax of Oracle user creation, hoping to help you learn how to create a user in Oracle.
Oracle user creation operations are familiar to everyone. The following describes the syntax of Oracle user creation, hoping to help you learn how to create a user in Oracle.
The permission system of the oracle database includes system permission and object permission. Database system privilege allows users to execute specific command sets. For example, the create table permission allows users to create tables, and the grant any privilege permission allows users to grant any system permissions. Database object privilege allows you to perform certain operations on each object. For example, the delete permission allows you to delete rows in a table or view. The select permission allows you to query information from a table, view, sequence, or snapshot through the select permission.
Each oracle user has a name and password, and has tables, views, and Other Resources created by it. An oracle role is a set of permissions (privilege) (or the access type required by each user based on their status and conditions ). You can grant or assign the specified permissions to the role and then assign the role to the corresponding user. One user can also directly authorize other users.
1. Create a user
Syntax for creating a user in Oracle:
You can run the create user command to CREATE a USER (password verification USER) in Oracle.
Create user username identified by password
OR IDENTIFIED EXETERNALLY
Or identified globally as 'cn = user'
[Default tablespace tablespace]
[Temporary tablespace temptablespace]
[QUOTA [integer K [M] [UNLIMITED] ON tablespace
[, QUOTA [integer K [M] [UNLIMITED] ON tablespace
[PROFILES profile_name]
[Password expire]
[Account lock or account unlock]
Where,
Create user username: USER name, which is generally in the alphanumeric format and the "#" and "_" symbols.
Identified by password: the user's password, which is generally in the alphanumeric format and "#" and.
Identified exeternally: indicates that the user name is verified under the operating system. The user name must be the same as the user name defined in the operating system.
Identified globally as 'cn = user': the user name is verified by the Oracle Security Domain Center Server. The CN name indicates the user's external name.
[Default tablespace tablespace]: The default tablespace.
[Temporary tablespace tablespace]: The default temporary tablespace.
[QUOTA [integer K [M] [UNLIMITED] ON tablespace: the number of bytes in a table space that you can use.
[PROFILES profile_name]: name of the resource file.
[Password expire]: Set the PASSWORD to expired immediately. You must change the PASSWORD before logging on again.
[Account lock or account unlock]: whether the user is locked. By default, it is not locked.
Oracle has two users: system and sys. Users can directly log on to the system user to create other users, because the system has the permission to create other users. When installing oracle, the user or system administrator can first create a user for himself. For example:
The Code is as follows:
Create user user01 identified by u01;
This command can also be used to set other permissions. For more information, see self-learning materials. To change a password, run the alter user command:
The Code is as follows:
Alter user user01 identified by usr01;
Now the user01 password has been changed from "u01" to "usr01 ".
In addition to the alter user command, you can also use the password command. If you use the password command, the new password is not displayed on the screen. Users with the dba privilege can use the password command to change the passwords of any other users. Other users can only change their own passwords.
When you enter the password command, the system prompts you to enter the old and new passwords, as shown below:
Password
Changing password for user01
Old password:
New password:
Retype new password:
When the password is successfully modified, the user will receive the following feedback:
Password changed
Ii. delete a user
To delete a user, run the drop user command as follows:
The Code is as follows:
Drop user user01;
If you have an object, you cannot delete it directly. Otherwise, an error value is returned. Specify the keyword cascade to delete all objects of a user and then delete the user. The following example deletes a user and an object:
The Code is as follows:
Drop user user01 cascade;
3. Three standard roles
To be compatible with earlier versions, qracle provides three standard roles: connect, resource, and dba.
1. connect role (connection role)
Temporary users, especially those who do not need to create tables, usually only give them connectrole. Connect is a simple permission for using oracle. This permission can be meaningful only when you have access to tables of other users, including select, insert, update, and delete. Users with connect role can also create tables, views, sequences, clusters, synonyms (synonym), and sessions) and link with other databases ).
2. resource role (resource role)
Resource role can be granted to more reliable and formal database users. Resource provides users with additional permissions to create their own tables, sequences, procedures, triggers, indexes, and clusters ).
3. dba role (database administrator role)
Dba role has all system permissions, including unlimited space limits and the ability to grant various permissions to other users. System is owned by dba users. The following describes some typical permissions frequently used by DBAs.
(1) grant (authorization) command
Run the following command to grant permissions to user01:
Grant connect, resource to user01;
(2) revoke (UNDO) Permission
The granted permissions can be revoked. For example, to revoke the authorization in (1), run the following command:
The Code is as follows:
Revoke connect, resource from user01;
A user with a dba role can revoke the connect, resource, and dba permissions of any other users or even other DBAs. Of course, this is very dangerous. Therefore, unless necessary, dba permissions should not be granted to general users who are not very important. Revoking all permissions of a user does not mean that the user is deleted from oracle, nor does it damage any table created by the user; it simply disallow access to these tables. Other users who want to access these tables can access these tables as before.
4. Create a role
In addition to the three system roles-connect, resource, and dba, you can also create your own role in oracle. A role created by a user can be composed of a table or system permission or a combination of the two. To create a role, you must have the create role system permission. The following is an example of the create role command:
The Code is as follows:
Create role student;
This command creates a role named student.
Once a role is created, the user can authorize it. The syntax of the grant command authorized to role is the same as that for the user. When authorizing a role, you must use the role name in the to clause of the grant command, as shown below:
The Code is as follows:
Grant select on class to student;
Now, all users with the student role have the select permission on the class table.
5. delete a role
To delete a role, run the drop role command as follows:
The Code is as follows:
Drop role student;
The specified role and related permissions will be deleted from the database.
Vi. Notes for table Deletion
When deleting all data in a table, you must use
SQL code
The Code is as follows:
Truncate table name
Because when the drop table and delete * from table names are used, the space occupied by the table in tablespace is not released, and the drop operation is performed several times. After the delete operation, the hundred megabytes of space on the tablespace are exhausted.
Author's "Technical Summary"
