Oracle new user, role, authorization, build table space

The permission system of Oracle database is divided into System permission and object permission. System permissions (Database system privilege) allow the user to execute a specific set of commands. For example, the CREATE TABLE permission allows a user to create a table, and the grant any privilege permission allows the user to grant any system permissions. Object permissions (Database object privilege) enable users to perform certain operations on individual objects. For example, delete permission allows a user to delete rows of a table or view, and select permissions allow the user to query information from a table, view, sequence (sequences), or snapshot (snapshots) through select.

Each Oracle user has a name and password, and has some tables, views, and other resources created by it. An Oracle role is a set of permissions (privilege) (or the type of access each user needs depending on its state and criteria). The user can grant or assign the specified permissions to a role, and then assign the role to the appropriate user. A user can also authorize other users directly.

First, create a user

There are two built-in users within Oracle: System and sys. Users can log on directly to the system user to create additional users because the system has permissions to create other users. When you install Oracle, the user or system administrator can first create a user for themselves. For example

SQL code

Create user User01 identified by u01;

The command can also be used to set additional permissions, as detailed in the self-study materials. To change a password, you can use the ALTER USER command:

<span style= "WHITE-SPACE:PRE;" > Alter user User01 identified by usr01;</span>

<span style= "WHITE-SPACE:PRE;" ><span style= "White-space:normal;" > </span> </span>

Now the User01 password has been changed from "U01" to "Usr01".

In addition to the alter USER command, users can also use the password command. If you use the password command, the new password entered by the user will not be displayed on the screen. A user with DBA authority can change the password of any other user through the password command, and the other user can only change their own password.

When the user enters the password command, the user is prompted to enter the old password and the new password, as follows:

Password

changing password for User01

Old Password:

New Password:

Retype new Password:

When the password is successfully modified, the user receives the following feedback:

Password changed

Second, delete the user

To remove users, you can use the drop user command as follows:

Drop user User01;

If the user owns the object, it cannot be deleted directly or an error value will be returned. Specifies the keyword cascade, which removes all objects from the user and then deletes the user. The following example is used to delete a user and its objects:

Drop user User01 cascade;

Three, 3 kinds of standard roles

Qracle provides three standard roles (role) for compatibility with previous versions: Connect, resource, and DBA.

1. Connect role (Connect roles)

Temporary users, especially those who do not need to build a table, usually give them only connectrole. Connect is a simple permission to use Oracle that only makes sense if you have access to other users ' tables, including SELECT, INSERT, UPDATE, and delete. Users with Connect role can also create tables, views, sequences (sequence), clusters (cluster), synonyms (synonym), sessions (session), and links to other databases.

2. Resource role (Resource roles)

More reliable and formal database users can grant resource role. Resource provides users with additional permissions to create their own tables, sequences, procedures (procedure), triggers (trigger), indexes, and clusters (cluster).

3. DBA Role (database administrator roles)

DBA role has all of the system permissions----including unlimited space limits and the ability to grant various permissions to other users. System is owned by the DBA user. Here are some typical permissions that DBAs often use.

(1) Grant (authorization) Order

The following is a USER01 authorization for the user you just created, with the following command:

Grant Connect, resource to User01;

(2) REVOKE (REVOKE) permission

The granted permission can be undone. For example, revoke the authorization in (1) and command the following:

Revoke connect, resource from User01;

A user with a DBA role can revoke other privileges of Connect, resource, and DBA from any other user, or even another DBA. Of course, this is dangerous, so unless you really need it, DBA authority should not be granted to ordinary users who are not very important. Revoking all permissions for a user does not mean that the user has been removed from Oracle, nor does it break any tables created by the user, but simply prohibits access to those tables. Other users who want to access these tables can access the tables as they did in the past.

Iv. Creating roles

In addition to the three system roles mentioned earlier----connect, resource, and DBA, users can also create their own role in Oracle. A user-created role can consist of a table or system permission or a combination of both. In order to create a role, the user must have the Create role system permission. An example of the Create role command is given below:

Create role student;

This command creates a role named student.

Once a role is created, the user can authorize him. The syntax of the grant command granted to a role is the same as the syntax for the user. When granting role authorization, use the name of role in the to clause of the grant command, as follows:

Grant select on class to student;

Now, all users with the student role have SELECT permissions on the Class table.

V. Delete a role

To remove a role, you can use the drop role command as follows:

Drop role student;

The specified role, together with the permissions associated with it, will be removed from the database altogether.

Vi. <span style= "Font-family:helvetica, Tahoma, Arial, Sans-serif; font-size:14px; line-height:25px; " > Considerations for Deleting a table </span>

<span style= "Font-family:helvetica, Tahoma, Arial, Sans-serif; font-size:14px; line-height:25px; " > When deleting all data from a table, use the </span>

SQL code

TRUNCATE TABLE name

Because of the drop table,delete * from table name, the tablespace tablespace of the table space is not released, after several drop,delete operations, the tablespace on the hundred megabytes of space is consumed.

Oracle new user, role, authorization, build table space