How to add users in oracle

In Oracle, you can use the "create user" command to add users. The function of this command is to create a new user. The syntax is "create user username identified by password [i.e. password];".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

The permission system of Oracle database is divided into system permissions and object permissions. System privilege (database system privilege) allows users to execute a specific set of commands. For example, the create table permission allows the user to create a table, and the grant any privilege permission allows the user to grant any system permission.

Object privilege (database object privilege) allows users to perform certain operations on each object. For example, the delete permission allows users to delete rows in a table or view, and the select permission allows users to query information from tables, views, sequences, or 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 privileges (or the type of access each user requires based on their status and conditions). Users can grant or assign specified permissions to roles, and then assign the roles to the corresponding users. A user can also directly authorize other users.

1. Create users

There are two built users inside oracle: system and sys.

Users can log in directly to the system user to create other users, because system has the ability to create another user. permissions of the user. When installing Oracle, the user or system administrator can first create a user for himself.

Syntax [Create User]: create user username identified by password [i.e. password];

Example:

create user test identified by test;

Syntax [change user]: alter user username identified by password[changed password] ];

Example:

 alter user test identified by 123456;

2. Delete user

Syntax: drop user username;

Example:

drop user test;

If the user owns the object, it cannot be deleted directly, otherwise an error value will be returned. Specify the keyword cascade to delete all objects of the user and then delete the user.

Syntax: drop user username cascade;

Example:

drop user test cascade;

3. Authorization role

Oracle provides three standard roles (role) for compatibility with previous versions: connect/resource and dba.

(1) Explain the three standard roles:

1 》. connect role (connection role)

--Temporary users, especially users who do not need to create tables, usually only give them connect role.

--Connect is a simple permission to use Oracle, This permission only has access rights to other users' tables, including select/insert/update and delete, etc.

--Users with connect role can also create tables, views, sequences, clusters, synonyms, sessions and other data links

2》. resource role(resource role)

--More reliable and formal database users can be granted resource role.

--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 quotas and to other users The ability to grant various permissions. The system is owned by the dba user

(2) Authorization command

Syntax: grant connect, resource to username;

Example: grant connect, resource to test;

(3) Revoke permissions

Syntax: revoke connect, resource from username;

Example: revoke connect, resource from test;

4. Create/Authorize/Delete Roles

In addition to the three system roles mentioned earlier - connect, resource and dba, users can also create their own roles in Oracle. User-created roles can consist of table or system permissions or a combination of both. In order to create a role, the user must have the create role system permission.

1》Create role

Syntax: create role role name;

Example:

create role testRole;

2》Grant role

Syntax: grant select on class to role name;

Liezi:

grant select on class to testRole;

Note: Now , all users with the testRole role have select query permissions on the class table

3》Delete role

Syntax: drop role role name;

Example:

drop role testRole;

Note: All permissions related to the testRole role will be deleted from the database

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to add users in oracle. For more information, please follow other related articles on the PHP Chinese website!