Oracle new user, role, authorization, table space Example (1/2)

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 their status and conditions). The user can grant or give the role the specified permissions, and then assign the role to the appropriate user. A user can also authorize other users directly.

First, create users

Oracle creates the user's syntax:

The code is as follows

Copy Code

Oracle Create user (password authenticated user), you can use the Create user command. 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]

which

CREATE user username: User name, typically alphanumeric and "#" and "_" symbols.

Identified by password: User password, typically alphanumeric and "#" and "_" symbols.

Identified exeternally: Indicates that the user name is validated under the operating system and that the user name must be the same as the user name defined in the operating system.

Identified globally as ' Cn=user ': User name is validated by Oracle Security Domain Center Server, and the CN name represents the user's external name.

[Default tablespace tablespace]: Table space for defaults.

[Temporary tablespace tablespace]: The default temporary tablespace.

[QUOTA [Integer k[m]] [unlimited]] On tablespace: The number of bytes in the table space that the user can use.

[PROFILES Profile_name]: The name of the resource file.

[PASSWORD EXPIRE]: Immediately set the password to an expired state, the user must modify the password before logging on.

[Account LOCK or UNLOCK]: The user is locked out and is not locked by default.

There are two built users within Oracle: System and sys. Users can log on to the system user directly to create other users, because system has the right to create another user. When Oracle is installed, the user or system administrator can first establish a user for themselves. e.g.

Create user User01 identified by u01;

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

Alter user User01 identified by Usr01;

Now User01 's 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 appear on the screen. A user with DBA authority can change the password of any other user through the password command, and other users can only change their password.

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

Password
changing password for User01
Old Password:
New Password:
Retype new Password:

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

Password changed

Second, delete users

To delete a user, 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 is returned. Specifies the keyword cascade, which deletes all the user's objects and then deletes the user. The following examples are used to delete users and their objects:

Drop user User01 cascade;

three, 3 standard roles

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

1. Connect role (Connection roles)

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

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 (index), and clusters (cluster).

3. DBA roles (database Administrator role)

The DBA role has all the system privileges----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 privileges 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) permissions

The permissions that have been granted can be undone. For example, to undo the authorization in (1), the command is as follows:

Revoke connect, resource from User01;

A user with a DBA role can revoke other permissions from Connect, resource, and DBA for any other user or even another DBA. Of course, this is dangerous, so, unless really needed, DBA authority should not be arbitrarily granted to ordinary users who are not important. Revoking all of the permissions of a user does not mean that the user is removed from Oracle or that any tables created by the user are not destroyed, but simply prevents access to those tables. Other users who want to access these tables can access them as before.

Home 1 2 last page