Oracle-Oracle user management

Oracle-Oracle user management 1. Oracle Installation Default User Username Password user description SYSCHANGE_ON_INSTALL user has SYSDBA or SYSOPER permissions; All Oracle data dictionaries base tables and views are stored under SYS users. The SYSTEMMANAGER user has the NORMAL permission. the user is used to store level-1 Internal data. 2.

Oracle-Oracle user management 1. Oracle Installation Default User Username Password user description SYS CHANGE_ON_INSTALL users have SYSDBA or SYSOPER permissions; All Oracle Data Dictionary base tables and views are stored under SYS users. The system manager user has the NORMAL permission. the user is used to store level-1 Internal data. 2.

Oracle-Oracle user management

1. Oracle Installation Default User

User Name	Password	User description
SYS	CHANGE_ON_INSTALL	The user has the SYSDBA or SYSOPER permission. The base tables and views of all Oracle data dictionaries are stored under the SYS user.
SYSTEM	MANAGER	The user has the NORMAL permission. the user is used to store level 1 Internal data.

2. Common Oracle user management commands

2.1. log in as a sys Super User

(1) In the command line: sqlplus "/as sysdba"

(2) In the command line, enter the User name:/as sysdba.

C:/Users/ChrisLue> sqlplus SQL * Plus: Release 11.2.0.1.0 Production on Monday June 20 13:59:06 2011 Copyright (c) 1982,201 0, Oracle. All rights reserved. Enter the username:/as sysdba Connect: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>

(3) In the command line: sqlplus/nolog> conn/as sysdba

C:/Users/ChrisLue> sqlplus/nolog SQL * Plus: Release 11.2.0.1.0 Production on Monday June 20 14:02:43 2011 Copyright (c) 1982,201 0, Oracle. All rights reserved. SQL> conn/as sysdba Connected. SQL>

2.2 sqlplus command

(1) sqlplus/nolog

Note:Enter the sqlplus environment, do not log on

(2) sqlplus '/as sysdba' (sqlplus sys/liuyubin as sysdba)

Note:Log in directly with the sys permission

(3) SQL> connect/as sysdba

Note:Log on to the sqlplus environment with the sys permission (local login)

(4) SQL> connectSys @ service_nameAs sysdba

Note:After entering the sqlplus environment, log in with the sys permission according to the locally configured TNS service name

(5) sqlplus username/passwd (as sysdba)

Note:Local User Login

(6) sqlplus username/passwd @ service_name (as sysdba)

Note:Log On by TNS service name

2.3. Create a user

Command syntax: SQL> CREATE USER username IDENTIFIED BY passwd [ACCOUNT LOCK | UNLOCK]

2.4. DeleteUser

Command syntax: SQL> DROP USER username CASCADE

2.5. View All Users

(1) view all DBA users: SELECT * FROM dba_users;

(2) view all users: SELECT * FROM all_users;

(3) view this user: SELECT * FROM user_users;

2.6. permission management

(1) user permission Classification

Permission	Description
Create session	Create session permission, that is, login permission
Alter session	Modify session Permissions
Create cluster	Create cluster Permissions
Create database link	Create database link Permissions
Create sequence	Create sequence permission
Create synonym	Create a synonym
Create view	View creation permission
Create procedure	Permission for the creation process
Create trigger	Create a trigger
Create type	Create type Permissions
Create table	Create Table permission
Drop table	Delete table Permissions
Insert table	Insert table permission
Update table	Modify Table Permissions
Unlimited tablespace	Use tablespace Permissions
Connect	Basic permissions for temporary users
Resource	Resourcer role permissions are granted to developers.
All	All Permissions

(2) grant User Permissions

Command syntax: SQL> GRANT privelige TO username

(3) mutual authorization between common users (by default, common users cannot access each other)

Command syntax: SQL> GRANT privilege TO username

Command parameters:

Privilege:

Select on tablename

Drop on tablename

Insert on tablename

Update on tablename

Insert (id) on tablename

Update (id) on tablename

Alter all table

(4) Revoke User Permissions

Command syntax: SQL> REVOKE privelige FROM username

(5) Permission Transfer

Command syntax: SQL> GRANT privilege TO username with admin option;

SQL> GRANT privilege TO username with grant option;

(6) view Permissions

View All permissions of the current user: SQL> SELECT * FROM user_sys_privs;

View the permissions of the current user on all tables: SQL> SELECT * FROM user_tab_privs;

View all the system permissions provided by Oracle: SQL> SELECT name FROM sys. system_privilege_map;

2.7. role)

Note:A role is a set of permissions. roles are introduced to facilitate management. The concept is similar to the user group in WINDOWS.

(1) CREATE a ROLE: SQL> CREATE ROLE rolename;

(2) GRANT the role permission set: SQL> GRANT privilege TO rolename;

(3) GRANT the user permission using the role: GRANT rolename TO username;

(4) Delete a ROLE: SQL> DROP ROLE rolename;

2.8. Modify user attributes

Command syntax: alter user username operation

(1) modify the USER Password: SQL> Alter USER username IDENTIFIED BY passwd;

(2) modify the user lock status: SQL> Alter USER username ACCOUNT LOCK | UNLOCK;