Oracle Study Notes (-)

AsProgramClerk, Oracle database is what we must learn. Below I will summarize my basic knowledge and hope to share and learn!

After the database is installed, we will get a default database. The default name of my database is orcl and we will get three Default usernames, namely sys, system, and Scott. The default logon password of sys is Manager and the default logon password of system is change_on_install. Among them, database management users are mainly sys and System. The relationship between these two Super Users is like the relationship between the Chairman and General Manager,Of course, sys is the chairman of the board, and system is the general manager. Now you know who has more rights!

SYS: All ORACLE data dictionaries and views are stored in SYS users, which are maintained by the database and cannot be changed manually by any user. This super user has the role and permissions of DBA, sysdba, and sysoper. It is the user with the highest permissions in Oracle and is equivalent to the SA user in Microsoft SQL Server. Of course, sys must be logged on in the form of sysdba and sysoper, that is, Conn sys/manager as sysdba or conn sys/manager as sysoper.

System: used to store internal data at the next level. It has the role or system permissions of DBA and sysdba. Normally, you log on as a DBA user. However, if you log on as sysdba, you actually Log On As A sys user.

SQL> conn system/change_on_install as sysdba; connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 connected as sys
 

To create a new user, use the followingCodeCreate a new user. xianrongbin is the username created by me, and 123 is the login password of this user.

 
SQL> Create UserXianrongbin identifiedBy 123;
 

After a new user is created, we cannot use this account to log on to the database. Why? For example, if a person is given the title of a commander, but he is not given any right, what can a person do in the army, we must grant certain permissions to this user. here we need to introduce the basic concept of role and permission.

In my understanding, permission refers to the scope and extent of decision-making on a matter to ensure the effective performance of duties. To put it bluntly, what can be done. A role is a set of permissions. It has multiple permissions, one or more permissions.

Therefore, after creating a user, we should also assign some permissions to it. Roles are divided into predefined roles and custom roles. The common roles are connect, resource, and DBA. There are two types of permissions: system permission and object permission. System permissions describe the user's permissions to access the database. object permissions are the user's permissions to operate on Data Objects of other users.

The CONNECT role has the following permissions: Alter session, create cluster, create database link, create table, create view, and create sequence.

Resource allows you to create tables in any tablespace. The resource role implies the unlimited tablespace system permission, has the following options: Create cluster, create indextype, create table, create sequence, create type, create procedure, and create trigger.

DBA has all system permissions, but does not have the sysdba sysoper privilege. It starts and closes databases and establishes roles.

Therefore, you must grant the new user the permission to log on to the database. The command is as follows:

SQL> GrantConnectToXianrongbin;
 

Then you can log on to the database again!

If you want to delete this user, use the following command. When deleting a user, if the deleted user already has a table, you need to include the cascade parameter during the deletion.

 
SQL> Drop UserXianrongbin;
 

Next, create a role. The command for creating a role is as follows:

SQL> CreateRole role1NotIdentified;---Verification method not required
SQL> CreateRole role2 identifiedByM123;--Verification Method
 

After a role is created, it has no permissions. Therefore, you must grant the system and object permissions. The command is as follows:

 
SQL> GrantOwb_userToRole1;
 

Deleting a role is generally performed by DBA. If it is another user, the user is required to have the system permission to drop any role;

SQL>DropRole role2;--Delete a role
 

Use select * From dba_roles to display all roles of a user.

Use the command statement select privilege, admin_option from role_sys_privs where role = 'Role name' to display the system permissions that the role has. Here, use the role olap_user to view the system permissions.

 SQL> SelectPrivilege, admin_optionFromRole_sys_privsWhereRole='Olap_user'; 

Privilege admin_option 
 ----------------------------------------------------
CreateCube build process no 
 CreateCube dimension no 
 CreateJob No 
 CreateMeasure folder No 
Create TableNo 
 Create ViewNo 
 CreateSequence No 
 CreateCube 

Okay. Let's summarize it here today!