–Time: August 24, 2020
–Author: Flying piggy
Manual directory:
#############################################################
5、 Managing users
5.1 add users
5.1.1 create a new user specific table space
5.1.2 limit the number of sessions connected by users
5.1.3 view and confirm the user table space
5.1.4 user authorization
5.1.5 create CDB service name
5.1.6 create PDB service name
5.2 deleting users
5.2.1 view user default table space
5.2.2 deleting users
5.3 modify user status
5.3.1 lock user password
5.3.2 unlock users
5.3.3 modify user password
#############################################################
5.1 add users
5.1.1 create a new user specific table space
Generally speaking, creating accounts in Oracle needs to specify a dedicated table space and a temporary table space for users, so as to achieve table space isolation.
Set the default table space file storage path
SQL>show parameter db_create_file_dest; #View DB_ create_ file_ Is dest set
SQL>set linesize 200;
SQL>col name for a80;
SQL>select file#,status,name from v$datafile; #View the directory where the current data file is located
SQL>alter system set db_create_file_dest=’Query the path ‘scope = both; # set the default data file directory
SQL>show parameter db_ create_ file_ Dest; ා view DB_ create_ file_ Is the dest setting normal
SQL> create tablespace add_user_tb datafile size 2G; #The size of the created heart surface space is 2G
SQL > create temporary tablespace addtmp tempfile size 2G; # create temporary table space with size 2G
5.1.2 limit the number of sessions connected by users
In the actual production system, one library may carry multiple services. In order to ensure that the number of database connections is full, it is necessary to limit the maximum number of sessions that each business account can open. For large databases, multiple spfiles of different levels can be created and bound to corresponding users to realize resource management.
Create a temporary profile to set per_ user_ Modify the sessions to the specified size, and then change the user’s profile to a temporary profile file.
SQL> show parameter resource_limit; #Check if resource constraints are on
SQL> alter system set resource_limit=true scope=both; #Set support limit to on
SQL> show parameter resource_limit;
#Create a profile file named temp_ profile_ Name limits the maximum number of user sessions to 80.
SQL> create profile temp_profile_name limit sessions_per_user 80;
SQL> select * from dba_profiles where profile=’TEMP_PROFILE_NAME’;
SQL> create user lvan_ test1 identified by yinwan default tablespace add_ user_ tb temporary tablespace addtmp profile temp_ profile_ Name; ා create user and set default table space, temporary table space and profile file.
12CWhen creating users for container databases and above, PDB database users create users in their own PDB, and the global account of CDB needs to add C ා.
example:
create user c##wan_test identified by yinwan;
5.1.3 view and confirm the user table space
SQL> set linesize 200; #Set display line length 200 characters
SQL> col username for a40; #Set the display length of the username field a40
SQL> col temporary_tablespace for a40; #Setting temporary_ Tablespace field display length a40
SQL> select username,temporary_ tablespace,default_ tablespace,profile from dba_ users where username=’LVAN_ Test1 ‘; ා view Lvan_ Table space, temporary table space and profile settings of test1 users
5.1.4 user authorization
General permissions are granted according to the user’s purpose, and DBA permissions cannot be granted to business accounts.
According to the business requirements, users are granted corresponding permissions
SQL> grant connect,resource to LVAN_TEST1;ා set the user’s permission to connect and use resources
SQL>alter user LVAN_TEST1 quota UNLIMITED on add_user_tb; #Set unlimited resources in user table space
SQL> select * from dba_ role_ privs where grantee=’LVAN_ Test1 ‘; ා view role permissions granted by users
You can also log in to the user to view
SQL> conn lvan_test1/yinwan;
SQL> col GRANTED_ROLE for a20;
SQL> select * from USER_ROLE_PRIVS;
5.1.5 create CDB service name
In RAC cluster, multiple nodes are usually used. The database administrator can create a special service name according to different business, and the business can only connect to a specific node through the service name. The service name is created and viewed under the Oracle user, and the service can be used to implement the suffix of user access provided by the load.Skip this step in a stand-alone environment.
When creating a service in the container database, you need to determine whether you need to connect to the CDB or the PDB.
#View database name
[[email protected] ~]$ srvctl config database
#Create a new service name admin_ Lvan for 10G, 11g
[[email protected] ~]$ srvctl add service -d orcl -s admin_lvan -r orcl1 -a orcl2
-S: service name
-r: Preferred instance name
-a: Alternate instance name
-P: TAF policy. The optional values are none (default value), basic and preconnect.
#View service name status
[[email protected] ~]$ srvctl status service -d orcl
#Start service name
[[email protected] ~]$ srvctl start service -d orcl -s admin_lvan
#Stop a service name
[[email protected] ~]$ srvctl stop service -d orcl -s sys_lvan_group
#Disable a service from running on an instance
[[email protected] bin]# ./srvctl enable service -d raw -s
rawservice -i rac1
[[email protected] bin]# ./srvctl disable service -d raw -s rawservice -i rac1
#Delete the service name on the database
[[email protected] ~]$ srvctl remove service -d orcl -s admin_lvan
5.1.6 create PDB service name
When creating a PDB service name, you need to determine the CDB and PDB names first.
#Create a PDB service name
#srvctl add service – d orcl – s admin_pdb -pdb racpdb -r orcl1 -a orcl2
#View the service name configured in PDB
[[email protected] ~]$ srvctl status service -d orcl -pdb racpdb
#View the existing service name in CDB
[[email protected] ~]$ srvctl status service -d orcl
#Start PDB service name
[[email protected] ~]$ srvctl start service -d orcl -s admin_pdb
[[email protected] ~]$ srvctl status service -d orcl
5.2 delete user
In the production system, each user has an independent table space, so when deleting users, try not to delete the table space.If the table space is shared with other users, do not delete the table space.
5.2.1 view user default table space
First find the default and temporary table spaces of users, and then delete the corresponding table spaces later.
SQL> set linesize 200; #Set display line length 200 characters
SQL> col username for a40; #Set the display length of the username field a40
SQL> col TEMPORARY_TABLESPACE for a40; #Setting temporary_ Tablespace field display length a40
SQL> col DEFAULT_TABLESPACE for a60; Set default_ Tablespace field display length A60
SQL> select username,temporary_tablespace,default_tablespace from dba_users where username=’LVAN_TEST1′;
5.2.2 delete user
In the production environment, it is not recommended to delete accounts that are not used. You can perform locking instead. You must ensure that there are no tables and views under the user to successfully delete the user.
Check the user name and user status.
SQL> set pagesize 200; #Set 200 row Pagination
SQL> set linesize 200; #Set display line length 200 characters
SQL> col username for a25; #Set the display length of the username field a25
SQL> col PROFILE for a20; #Set the display length of profile field A20
SQL> select username,created,profile,account_ status from dba_ Users; ා view user status
delete user
SQL> drop user LVAN_TEST1; #delete user
SQL> select username,created,profile,account_ status from dba_ Users; ා view user status
Confirm that the user deletion is completed. There is no corresponding user name in the user list.
SQL > drop user Lvan cascade
Before confirmation, there is no table data under the Lvan user.
View and confirm the file before deleting
SQL> set linesize 200;
SQL> col file_name for a80;
SQL> col TABLESPACE_NAME for a60;
SQL> select file_name,tablespace_name from dba_data_files;
SQL>set linesize 200;
SQL> col file for a80;
SQL> select f.file#,t.ts#,f.name “File”,t.name “Tablespace” from v$tempfile f,V$tablespace t where f.ts# = t.ts#;
Confirm the location of the table space file.
Delete a table space
SQL> drop tablespace addtmp;
SQL> drop tablespace add_user_tb;
SQL> set linesize 200;
SQL> col file for a80
SQL> select f.file#,t.ts#,f.name “File”,t.name “Tablespace” from v$tempfile f,V$tablespace t where f.ts# = t.ts#;
SQL> col file_name for a80;
SQL> col TABLESPACE_NAME for a60;
SQL> select file_name,tablespace_name from dba_data_files;
Confirm that the temporary and default table spaces are deleted.
5.3 modify user status
Users will set account status to unlock, lock, modify password and other operations according to the business situation
Lock user password 3.5
View the current status of Lvan users.
SQL> set linesize 200;
SQL> col username for a20;
SQL> select username,user_id,created,account_status,lock_date from dba_users;
Confirm that the Lvan user status is open.
SQL> alter user lvan account lock;
SQL> set linesize 200;
SQL> col username for a20;
SQL> select username,user_id,created,account_status,lock_date from dba_users;
Confirm that the Lvan user is locked
5.3.2 unlock users
SQL> alter user lvan account unlock;
SQL> select username,user_id,created,account_status,lock_date from dba_users order by created desc;
Check to confirm that the user’s status is open. User unlocked successfully.
5.3.3 modify user password
SQL> show user;
SQL> alter user lvan identified by yinwan;
SQL> conn lvan/yinwan;
SQL> show user;
Confirm that the user can log in with the new password