Basic commands for Oracle beginner user management

1. Switch users and log on:

Conn system/password @ myoracle as sysdba; myoracle is the database name or service name. As sysdba logs on as an administrator. If the database name is not added, a protocol adapter error may be reported. If you have multiple databases, log on to the local machine.
2. Create a user create Xiaoming identified by password; Delete the user drop user Xiaoming;

If you have already created a table, you must include the cascade parameter;
3. Authorize a user
SQL> grant DBA to Xiaoming; // DBA is a role that contains multiple permissions.
 
Grant succeeded

4. Table authorization

Assume that Scott is currently logged on.

1)

SQL> conn Scott/lab2012 @ myoracle;

Now you need to allow the gaotong user to access Scott's EMP table, so that the authorization is as follows:

2)

SQL> grant select on EMP to gaotong; (for all permissions, use all)
 
Grant succeeded

3)

SQL> conn gaotong/gaotong @ myoracle;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as gaotong
 
SQL> select * from Scott. EMP; // table of the Scott user, which is called a solution.

5. revoke the revoke permission

SQL> revoke select on EMP from gaotong;
 
Revoke succeeded

6. Maintain permissions.

1)

If it is an object permission, you can add with grant option at the end to grant the recipient the permission.

2)

For system permissions, grant connect to Xiaoming with admin option.

If the permission is revoked, the permission is revoked. (permissions granted to other users by Xiaoming are also revoked )!

7. Profile Management User Password (blue indicates modifiable)

1)

Create a configuration file:

SQL> Create Profile
Lock_account limit failed_login_attempts
3 password_lock_time
2;

Profile created

If an error occurs three times, the instance is locked for 2 days.

2)

Configuration file takes effect

SQL> alter user Xiaoming profile lock_account;

User altered

3)

Unlock an account:

Alter user
Xiaoming account unlock;

8. Terminate the password (force the password to be changed every 10 days)

Create profile myprofile limit password_life_time 10 password_grace_time 2;