This is an oracle knowledge organization note,Including the comparison of sql server and oracle, the basic concepts and relationships in oracle, the management tool pl/sql developer, and how to assign permissions and roles,

We have compiled a study note about oracle knowledge,I hope everyone has to help.

1. A brief comparison of sql server and oracle

Let me show you a picture I drew:

After installing sql server, we can see that many databases have system database and user database after opening and connecting to the database engine.After oracle is installed, a database corresponds to a service,Before using it, we need to enable the corresponding service in the computer service.As shown in the figure,Service is on,What the user sees after logging in is a database,A database can have data objects such as tables, views, and stored procedures.An oracle database can have multiple users,Users have different permissions.The data objects you see when entering the database are also different.

2. Basic concepts and relationships in oracle

I summarized the five concepts that must be known in Oracle:users, permissions, roles, schemes and data objects.

Everyone knows the concept of users, so we don't need to repeat them;Authority is proof of being able to do something;The role can be seen as a collection of certain permissions.There are many kinds of permissions in oracle,It would be troublesome to authorize users one by one,So assign some permissions to a role,Then granting this role to the user solves the trouble of granting permissions one by one.The plan here corresponds to the user.A user,oracle will give it a scheme by default,Data objects are stored in this scheme.So what are data objects?Just think about it,Data objects are the general term for tables, views, stored procedures, and so on.The relationship between them is illustrated by a picture.

System permissions are the user's related permissions to the database.For example:login. Object authority is the user's operation authority for data objects of other users.For example:update the table of other schemes.I don't know if you see this picture:What are the permissions of oracle?What are the roles?How do we operate this database?Later blogs will cover these.

3.oracle a management tool pl/sql developerpl/sql developer is an integrated development environment for developing pl/sql blocks.It is a standalone product,Not a by-product of oracle.pl/sql is a procedural language/sql is an extension of oracle on the standard sql language.pl/sql not only allows embedding SQL language, but also can define variables and constants,Allow exceptions to handle various errors,Make it more powerful.

The pl/sql block consists of three parts:a definition part, an execution part, and an exception handling part.The definition and exception sections are optional.

/* Definition section ----- define variables,Constants, cursors, exceptions, complex data types * /
/* Execution part ----- pl/sql statement and sql statement to be executed * /
/* Exception handling section ----- Handle various errors in operation * /
Take the simplest example:
dbms_output.put_line ("hello, world");

Why is there a problem of PL/SQL Chinese garbled?

The premise is:Oracle is not installed on this machine, and pl/sql is used to remotely access the oracle database. The oracle client and pl/sql are installed on this machine.

Oracle on the server has a character set,Such as utf-8 or simplified Chinese,If not manually modified then there is a default.The oracle client and pl/sql also need character settings on this machine.If the character encodings of the server and client are inconsistent,Chinese garbled problems will occur.So, what we have to do is modify them to make them consistent.(It seems so simple now,How could it be so tangled! )

How to solve pl/sql Chinese garbled problem?

First, we need to know what character encoding of the server-side oracle.Open your pl/sql and type select userenv ("language") from dual;in the command window and you will see it first.As shown in the figure:

Then all you have to do is set the local character to simplified chinese_china.zhs16gbk. The solution is to set environment variables.Steps:My Computer->Right-click->Properties->Environment Variables->System Variables->Modify nls_lang to simplified chinese_china.zhs16gbk, and then need to restart pl/sql. Many materials on the Internet say that this step can solve the problem.But my garbled problem still can't be solved.If your problem cannot be solved,Please do this:open your registry to find this directory:find:hkey_local_machine->software->oracle

Set nls_lang on the right and set its value to simplified chinese_china.zhs16gbk. After restarting pl/sql, delete the records that have been added,Add another one and test again.Previously added records are likely to be written in garbled,See no effect.

4.How to assign permissions and roles1). What are permissions,What is a role?

Permissions are divided into system permissions and object permissions.System authority refers to the right to execute specific types of SQL commands.For example, the user has the permission to create a table and can create a table in its scheme;When you have create any table, you can create tables in any scheme.Object permissions are the right to access other programs,Users can directly access the objects of their own schemes,But if i want to access the objects of other schemes,You must have permissions on the object.For example, to access the jane.emp table (jane scheme, emp table), the scott user must have object permissions on the jane.emp table.

A role is a collection of commands with related permissions,The purpose of using roles is mainly to simplify the management of permissions.

2). Common permissions and role classifications

Here we only briefly introduce the most commonly used ones,If i want to know, you can refer to the help document or get it through the query.

a. System permissions:database connection (database link), session (session), user (user), table space (tablespace), role (role), data objects (tables, views, stored procedures) and other operations such as create, drop and alter.

b. Object permissions:insert-add, delete-delete (data), alter-modify (modify table structure), update-modify (update data), select-query. In other words, there are four categories:addition, deletion, modification, and investigation.

c. Roles are divided into predefined and custom.Predefined refers to the role provided by oracle,Commonly used include:connect, resource, dba.

The dba role has all system permissions,The default users with the dba role are sys and system. These two users can grant any system privilege to other users.Note that the dba role does not have the right to start and close the database.The connect role has most of the permissions that an average developer needs,in most cases,It is enough to grant users the connect and resource roles.So what permissions do the connect and resource roles have?There is no need to enumerate them here.We can get it by query.So how to query it?

3). Query

How to query how many roles does oracle have?select * from dba_roles;

How to query how many system permissions and object permissions does oracle have?select * from system_privilege_map order by name;select distinct privilege from dba_tab_privs;

How can I see what role a user has?select * from dba_role_privs where grantee="username";

How to see what system permissions and object permissions a role includes?select * from dba_sys_privs where grantee="role name";select * from dba_tab_privs where grantee="role name";

4). Grant and recovery

This part of the knowledge comes according to rules one by one during the learning process.I feel very messy after school.I compiled the things here into an operation line according to a normal operation process.It feels clearer.

a. Now I want to use oracle, there must be a user.oracle creates two users sys and system by default. We can use these two users to log in,Create your own users,Such as:create user ken identified by ken. Now we use ken to login will give an error message.why?Because the ken user does not have login permission.Now we need to authorize ken:grant create session to ken with admin option. This way ken user login is OK. So what can the with admin option do?This means that authorized users or roles can also grant the system permissions to other users or roles.

Reclaim system permissions operation:The above example is still used:Reclaim login permissions:revoke create session from ken;Recycling of system permissions is not cascading.For example:ken grants login permission to jane, so after reclaiming ken's permission,jane can still log in.

b. For object permissions,Let's take an example:if I have a table emp in my solution, now I want to give the ken user permission to operate my table.then what do I do?The first method is to use the dba user to give ken permission to operate my table emp. Here is a point to explain:the dba user can grant object permissions on any object to other users.The second way is to do it myself. How to do?grant select on emp to ken with grant option. Here I used with grant option. This can play such a role:the ken user can grant permissions to operate my table emp to other users.Pay attention here.It is different from with admin option, with grant option can only be granted to users,Roles cannot be granted.

Recycling object permissions operation:revoke select on emp from ken;Recycling of object permissions is cascading.For example:ken grants jane the permission to query the emp table, then after recovering the permission of ken,Jane also cannot query the emp table.

c. Role authorization here requires two steps:1 to authorize the role 2 to give the role to a certain user.Now we give login role to a role:grant create session to role name;then give this role to user ken:grant role name to ken. Delete role uses drop role role name.

Summary:This blog focuses on introducing the basic mechanism of oracle,Is the most basic knowledge,Finally, it explains how to assign permissions and roles,Including what they are,Categories and how to use them.Here we have to understand a premise is well understood:we must have permissions to do something.When we do n’t have permission, we can use system users to do it,We can do it if the system grants us permissions.

  • Previous jQuery simple method to set the text box enter event
  • Next Talking about the difference between MySQL and mariadb