Oracle knowledge sorting notes, oracle sorting notes

Oracle knowledge sorting notes, oracle sorting notes

I want to help you with my learning notes on Oracle knowledge.

1. a brief comparison between SQL Server and Oracle
Let me show you a picture:

After installing SQL Server, we can see that many databases have System databases and user databases after opening and connecting to the database engine. After Oracle is installed, a database corresponds to a service. Before using Oracle, you must enable the corresponding service in the computer service, as shown in, after logging on, you can see a database that contains tables, views, stored procedures, and other data objects. An Oracle database can have multiple users with different permissions. The data objects displayed in the database are different.

2. Basic Concepts and relationships in Oracle
I have summarized five concepts that must be known in Oracle: users, permissions, roles, solutions, and data objects.
Users do not need to go into details. permissions are the proof of whether a role can do something. roles can be seen as a collection of permissions. There are many types of permissions in Oracle. It is very troublesome to authorize users one by one, so some permissions are assigned to a role, therefore, granting a role to a user solves the problem of granting permissions one by one. The solution corresponds to the user. A user, Oracle will give it a solution by default, which will store data objects. So what is the data object? Once you think about it, the data object is a general term for tables, views, stored procedures, and so on. I will use a diagram to illustrate the relationship between them.

System permissions are related to the user's database, such as logon. Object permission is the user's permission to operate on Data Objects of other users. For example, update Tables of other solutions. I don't know if you can see this figure. Do you have any questions: what permissions Does Oracle have? What are roles? How can we operate this database? This will be introduced in later blogs.

3. pl/SQL Developer, a management tool of Oracle
Pl/SQL developer is an integrated development environment used to develop pl/SQL blocks. It is an independent product rather than an accompanying product of oracle. Pl/SQL is procedural language/SQL is an extension of Oracle in the standard SQL language. Pl/SQL not only allows embedding SQL languages, but also defines variables and constants, and allows exceptions to handle various errors, making it more powerful.
The pl/SQL block consists of the definition part, execution part, and Exception Handling part. The definition part and the exception part are optional.

Declear/* definition section ----- defines variables, constants, cursors, exceptions, complex data types */Begin/* Execution Section ----- pl/SQL statements to be executed and SQL statements */Exception/* Exception Handling Section ----- handling various running errors */End; for example, Begin dbms_output.put_line ('hello, World'); End;

Why is there a Chinese garbled pl/SQL problem?
The premise is that Oracle is not installed on the local machine, and pl/SQL is used to remotely access the Oracle database. Oracle client and pl/SQL are installed on the local machine.
Oracle on the server has a character setting, such as UTF-8 or simplified Chinese, which has a default if not manually modified. On the local machine, character settings are also required for the Oracle client and pl/SQL. If the character encoding of the server and client is inconsistent, Chinese garbled characters may occur. So we have to modify them to make them consistent. (Now it seems so simple, how can it be so tangled at that time !)
How can I solve the Chinese garbled characters in pl/SQL?
First, we need to know what character encoding the Oracle on the server end is. Open your pl/SQL, and enter select userenv ('language') from dual; in the Command window.

Then, set the local character to SIMPLIFIED CHINESE_CHINA.ZHS16GBK. You can set environment variables. Step: Right-click my computer and choose Properties> environment variables> system variables> change nls_lang to SIMPLIFIED CHINESE_CHINA.ZHS16GBK. Then restart pl/SQL. Many materials on the Internet say that this step can solve the problem. But I still cannot solve the garbled problem. If your problem cannot be solved, open your registry and find the Directory: HKEY_LOCAL_MACHINE-> SOFTWARE-> ORACLE.

Set the value of NLS_LANG to SIMPLIFIED CHINESE_CHINA.ZHS16GBK. After restarting pl/SQL, delete the records that have been added, add another record, and then test. Previously added records may be written with garbled characters, but they do not have any effect.

4. How to assign permissions and Roles
1). What are permissions and roles?
Permissions are divided into system permissions and object permissions. System permission refers to the right to execute specific types of SQL commands. For example, if you have the create table permission, you can create a table in the solution. If you have the create any table permission, you can create a table in any solution. Object permission refers to the permission to access objects in other schemes. Users can directly access objects in their own schemes. However, to access objects in other schemes, they must have the object permission. For example, if a Scott user wants to access the Jane. emp table (the emp table in the Jane. emp scheme), the user must have the object permission on the Jane. emp table.
A role is a set of commands for related permissions. The purpose of a role is to simplify permission management.
2). Common permissions and role categories
Here we will only briefly describe the most commonly used ones. For more information, see the help documentation and query statements.
A. system permissions: database link, session, User, table space, and role), create, drop, and alter of Data Objects (tables, views, and stored procedures.
B. object permissions: insert -- add, delete -- delete (data), Alter -- modify (modify table structure), Update -- modify (Update data), and Select -- query. That is, four categories are added, deleted, modified, and queried.
C. Roles can be predefined or customized. Predefined roles are the roles provided by Oracle. Common roles include connect, resource, and 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 permission to other users. Note that the dba role does not have the right to start and shut down the database. The Connect role has most of the permissions required by developers. In most cases, you only need to grant the connect and resource roles to users. So what permissions does the connect and resource roles have? There is no need to list them one by one. We can obtain this information through the query. So how to query?
3). Query
How many roles does one query in Oracle? Select * from dba_roles;
How to query how many system and object permissions are available in Oracle? Select * from system_privilege_map order by name; select distinct privilege from dba_tab_privs;
How can I check the roles of a user? Select * from dba_role_privs where grantee = 'username ';
How can I view the system permissions and object permissions of a role? Select * from dba_sys_privs where grantee = 'Role name'; select * from dba_tab_privs where grantee = 'Role name ';
4). Grant and revoke
This part of knowledge is based on one rule during the learning process, and it is especially messy after learning. I organized the items here into an operational line according to a normal operation process. I feel that my knowledge is clear.
A. To use Oracle now, you must have a user. By default, Oracle creates two users, sys and system. we can use these two users to log on and create their own users, such as: create user Ken identified by ken. now, an error message is displayed when you use the Ken to log on. Why? Because the Ken user does not have the logon permission. Now we need to authorize the Ken: grant create session to Ken with admin option. In this way, the Ken User Login will be OK. So what is the role of with admin option? This indicates that the authorized user or role can grant the system permission to other users or roles.
Revoke system permissions: the preceding example is still used: revoke logon permissions: revoke create session from Ken; revoke system permissions is not cascade. For example, if a token grants the logon permission to Jane, Jane can still log on after the token is revoked.
B. For object permissions, let's take an example: if there is a table emp in my solution, now I want the Ken user to have the permission to operate my table. What should I do? The first method is to use the dba user to grant the permissions to operate on my table emp to the Ken. here we need to note that the dba user can grant the object permissions on any object to other users. The second method is to do it myself. How to do it? Grant select on emp to Ken with grant option. here I use with grant option. this can play a role: the Ken user can grant permissions to operate on my table emp to other users. Note one point here. Unlike with admin option, with grant option can only be granted to users and cannot be granted to roles.
Revoke object permission operation: revoke select on emp from Ken; revoke object permission is cascade. For example, if a Ken grants Jane the permission to query the emp table, Jane cannot query the emp table after the Ken is revoked.
C. Two steps are required for role authorization: 1. Authorize the role and 2. Grant the role to a user. Now we will grant the logon permission to a role: grant create session to role name; then give this role to the user Ken: grant role name to Ken. Delete the role using drop role name.

Summary: This blog focuses on the basic mechanism of Oracle, which is the most basic knowledge. It also describes how to assign permissions and roles, including what they are, how to classify them, and how to use them. Here we need to understand the premise that we can understand: we must have the permission to do something. When we do not have the permission, we can use the System user to do it. If the system grants us the permission, we can also do it.

The above is the sorting of Oracle knowledge, and there are still many knowledge points not involved, which will be added in future article updates. I hope you will continue to pay attention to them.