Code
CREATE USER target identified by Target; GRANT CONNECT, RESOURCE to target;
The Oracle instance you just created will have two users built in: System and sys.
(1) New user
We first use the system user to log in to the Oracle instance to create a new T_user user.
New user format: Create user username identified by password;
such as: create user t_user identified by T_user;
The format of the modified user is as follows:
Alter user username identified by password;
such as: Alter user t_user identified by t_user001; Modify the password for the T_user user to t_user001
To delete a user format:
Drop user username [cascade]; If the user you want to delete has already created objects such as tables or views, you can remove the user-created item by adding cascade to the Cascade Delete.
Drop user T_user;
(2) Granting user privileges
The newly created user T_user is not yet able to connect to the database because there is no permission. Oracle's permissions are divided into system permissions and object permissions. System permissions allow users to execute specific commands such as the Create session login database, create table creation tables and other system operations, object permissions to objects in the database such as tables, views of the contents of the operation, such as Select on Student can query the data in the student table.
First assign the Create session permission to the T_user User:
The command format that gives permissions or roles is as follows:
Grant permissions 1, permissions 2, role 1, role 2 to user;
Grant Connect, resource to T_user; Connect is the role that connects the database, and resource is the role that operates the database resources.
Object permissions include permissions or roles such as SELECT, Delete, insert, UPDATE, all (containing all permissions for the object) for the table.
If the system user has an EMP table and we want to assign the object permissions of the EMP table to the T_user user, you can use the following format:
Grant all on the EMP to T_user; Give T_user the Select, delete, insert, UPDATE, and so on of the EMP table. This allows us to use T_user to view the contents of the System User EMP table.
SELECT * from System.emp;
If we want to assign permissions to T_user users to other users by T_user users, you can do this when assigning permissions to T_user users:
System permissions with the WITH Admin grant option, object permissions with the GRANT option. such as:
Grant CREATE table to t_user with admin grant option;
Grant SELECT on EMP-t_user with GRANT option; In this way t_user can also give other users the EMP SELECT permission.
(3) Reclaim permissions
When we do not want to t_user the user can delete the contents of the System User EMP table, we can recall the delete on EMP permission in the following format:
Revoke permissions 1, permissions 2, role 1, role 2 from user;
such as:revoke delete on the EMP from T_user; So we can retrieve the delete permission for the EMP table .
If T_user pays the delete permission of the EMP table to the T_user2 user, when we retract the delete permission of the T_user, we also retract the T_user2 delete permission, that is, the collection of object permissions is cascaded.
Collection of system permissions is not cascading.
(4) Role
Oracle provides three standard roles: Connect (connection role), resource (Resource role), DBA (Database Administrator role)
Connect: Users who have this role can connect to the database, but cannot create entity objects.
Resource: Users who have this role can create entities, but cannot create database structures.
DBA: Owning the role can create an entity or create a database structure.
Ordinary users have: Connect, resource role, DBA Administrator: Connect, resource, dba three roles.
Oracle New Object Rights Management