Oracle User Management

In Oracle, users can be established and authorized to operate.

• To create the user's syntax:

CREATE USER  by password;

Example: Create a test user with a password of test123

CREATE USER  by test123;

If you want to create a user, you should first use the administrator to log in, after creating the user, open a new SQLPLUSW window, and use this user to log in, the following error message appears:

The test user does not have permission to create the session, and the permission without the session means that it cannot be logged in.

• For user authorization, you can use the following format:

GRANT 1 2  to User

Example: Create SESSION permissions to the test user

GRANT CREATE  to test;

Then, using the test user to connect, you can connect to the database, which means that a session was created.

Example: Creating a Tab table

CREATE TABLE tab (ID  numberPRIMARYKEYnotNULLVARCHAR2 ());

In fact, a new user has all the permissions to be given separately, if you now assume that you want to assign multiple permissions to one user at a time, you can define these permissions as a set of roles.

• There are two main roles available in Oracle: CONNECT, RESOURCE, which can be directly assigned to test users by the two roles

GRANT  to test;

• Modify a user's password in the following format:

ALTER USER  by password

• in the general system exists, the user can change the password when the first landing, so to complete this function, you can manually let a password fails with the following format:

ALTER  user username PASSWORD EXPIRE;

• Lock User:

ALTER  user username account LOCK;

• Unlock users:

ALTER  user username account UNLOCK;

To access other users ' tables, you need to grant access to this table.

Example: Send the query and delete permissions of the EMP table under the Scott user to the test user

GRANT SELECT,DELETE on to test;

Reclaim permissions using the REVOKE syntax :

REVOKE  on  from user;

Example: Retrieving SELECT and DELETE permissions for test users

REVOKE SELECT,DELETE on from

Oracle User Management