Oracle create a new user, role, authority, built in table space

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Oracle create a new user, role, authority, built in table space 2011-09-06 14:36:36        I'll say something. I want to contribute

System Oracle database is divided into system permissions and permission object. System privileges (database system privilege) allows the user to perform specific command set. For example, the create table permission allows the user to create a table, the grant any privilege permission allows the user to grant any privilege. Object permissions (database object privilege) allows the user to perform operations on each object. For example the delete permission allows the user to delete a table or view, select permission allows the user to the select from a table, view, sequence (sequences) or (Snapshots) the query information in snapshot.

Each Oracle user has a name and password, and has some of the created tables, views and other resources. The role of Oracle (role) is a set of permissions (privilege) (or each user according to the type of access to a required the state and conditions). Users can give the role of grant or give the specified permission, then the role assigned to the corresponding user. A user may also authorize directly to other users.

One, to create the user

Oracle has two completed the user: system and sys. Users can log on to system users to create another user, because the system has to create different user permissions. When Oracle is installed, the user or system administrator can first create a user for yourself. For example :

Sql code

create user user01 identified by u01; 

  This command can also be used to set permissions, see details of 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 user01 password has 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 entered by the user will not appear on the screen. DBA privileges users can order changed by any other password user password; other users can change their password.

When the user enters the password command, the system will prompt the user to enter the old password and the new password, as shown below:


changing password for user01

old password:

new password:

retype new password:

When successfully changed the password, the user will get the following feedback:

password changed

Two, delete user

Delete a user, you can use the drop user command, as shown below:

drop user user01;

If the user has the object, it can not be deleted, otherwise it will return an error value. Specify the keyword cascade, object to delete all the users, and then delete the user. The following example used to delete a user and object:

drop user user01 cascade;

Three, 3 kinds of standard role

Qracle in order to be compatible with the previous version, provides three standard role (role):connect, resource and DBA.

1 connect role (connection role)

Casual users, especially the users who do not need to create table, usually only give them connectrole. Connect is a simple permission to use Oracle, it only has access to other users, including select, insert, update and delete, will become meaningful. With the connect role users can also create a table, view, sequence (sequence), cluster (cluster), synonyms (synonym), session (session) and other database chain(link).

2 resource role (resource role)

More reliable and formal database users can be granted to resource role. Resource provides the user with additional permissions to create their own table, sequence, process (procedure), trigger (trigger), index (index) and cluster(cluster).

3 DBA role (database administrator role)

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

(1)Grant (Shou Quan) command

The user you just created user01 authorization, the following command:

grant connect, resource to user01;

(2)Revoke (undo) authority

Granted permissions can be revoked. For example, undo (1) the authorization, the following command:

revoke connect, resource from user01;

A DBA roles users can undo any other authority other users or even other DBA connect, resource and dba. Of course, this is very dangerous, so, unless you really need, DBA permissions should not awarded to someone who is not very important general user. Revoke all permissions a user, does not mean that the Oracle deleted from the user, will not destroy any table user created; simply ban on the table access. To access these tables users can access these tables such as before.

Four, create the role

In addition to the three roles of ----connect, resource and DBA system described above, the user can also create their own role in oracle. User created role can be constructed by the combination of the table or system permissions or both. In order to create the role, a user must have create permissions in role system. Examples are given below a create role command:

create role student;

This command creates a file called student role.

Once you create a role, users can give him the authorization. To the role authorization of grant command syntax and grammar on the same user. In the role authorization, the command in the grant clause in the to name to use role, as shown below:

grant select on class to student;

Now, all users have student roles are the class permission on the select table.

Five, deleting a role

To delete a character, you can use the drop role command, as shown below:

drop role student;

The specified role together with the relevant permissions will be deleted from the database.

    Six, <span style=" font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 25px; " > delete note table</span>

<span style="font-family: helvetica, tahoma, arial, sans-serif; font-size: 14px; line-height: 25px;">All data in the deleted in a table, must use</span>

Sql code

The truncate table table name 

For drop table, delete * from table, tablespace table space occupied space of the table did not release, repeated several times drop, delete after operation, the tablespace hundreds of megabytes of space is exhausted.  

Summary of the "technology"

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Mandel at November 27, 2013 - 10:47 AM