Oracle daily operation command manual (user management) – from zero to none

Time:2020-11-15

–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