Oracle Knowledge Collation Notes _oracle

Small compiled an article on the knowledge of Oracle Learning notes, hope to help everyone.

1. A simple comparison of SQL Server and Oracle
First, I'll show you a picture:

After installing SQL Server, we can see that many databases have system databases and user databases when we open and connect the database engine. and Oracle installation, a database corresponding to a service, we need to use in the computer services before the corresponding services to open, as shown in the figure, service open, user login to see a database, a database can have tables, views, stored procedures and other data objects. An Oracle database can have multiple users, and the user's permissions are different, and the data objects you see in the database are different.

Basic concepts and relationships in 2.Oracle
I've summed up 5 of the concepts that Oracle has to know: Users, permissions, roles, scenarios, and data objects.
The concept of the user is known not to repeat; the permission is the proof of the ability to do something, and the role can be seen as a collection of certain permissions. There are many kinds of permissions in Oracle, if the user authorization can be cumbersome, so that some permissions assigned to a role, then give the role to the user to solve the problem of granting permissions. Scenario here corresponds to the user. A user, Oracle will give it a solution by default, which holds data objects. So what is the data object? A thought to know, data objects are tables, views, stored procedures, such as the general name. The relationship between them I use a picture to illustrate.

System permissions are user-related permissions on the database, such as: Login. Object permissions are the user's permission to manipulate data objects for other users. For example, a table that updates other scenarios. Do you have any questions about Oracle's permissions when you see this picture? What are the roles? How do we operate this database? The following blog will introduce these.

A management tool for 3.Oracle Pl/sql Developer
Pl/sql Developer is an integrated development environment for developing PL/SQL blocks, which is a stand-alone product, not a by-product of oracle. Pl/sql is procedural language/sql is an extension of Oracle's standard SQL language. Pl/sql not only allows you to embed SQL languages, you can also define variables and constants, allowing you to use exceptions to handle various errors, making it more powerful.
The Pl/sql block is composed of three parts: the definition part, the execution part, the exception handling part. The definition section and the exception section are optional.

Declear 
/* Definition section-----Define variables, constants, cursors, exceptions, complex data types 
* 
/Begin/* Execute part-----The PL/SQL statement to execute and the SQL statement * * 
Exception 
/* Exception processing part-----Handle all kinds of error 
/end; 
For the simplest example: 
Begin 
dbms_output.put_line (' Hello,world '); 

Why will appear pl/sql Chinese garbled problem?
If Oracle is not installed on this computer, use Pl/sql to remotely access the Oracle database. 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 there is no manual modification then there will be a default. Character settings are also required for Oracle clients and Pl/sql on this computer. If the server and the client's character encoding is inconsistent, there will be a Chinese garbled problem. So, what we have to do is revise them to make them consistent. (now it seems so simple, how could it be so tangled!) ）
How to solve Pl/sql Chinese garbled problem?
First, we need to know what character encoding Oracle is on the server side first.   Open your Pl/sql and enter select Userenv (' language ') from dual in the command window; You'll see it first. As shown in figure:

All you have to do is set the character of this machine to simplified Chinese_china. ZHS16GBK. The idea is to set up environment variables. Step: My Computer--> Right-click the--> attribute--> environment variable--> system variable--> change Nls_lang to simplified Chinese_china. ZHS16GBK, and then you need to reboot the Pl/sql. A lot of information on the Internet that this step can solve the problem. But my garbled problem still cannot be solved. If your problem can not be solved, please do this: Open your registry to find such a directory: find: Hkey_local_machine->software->oracle

Set the right Nls_lang and set its value to simplified Chinese_china. ZHS16GBK. After restarting the pl/sql, delete the records that have been added, and then add a second Test. Previously added records are likely to be garbled write, do not see the effect.

4. How to assign permissions and roles
1. What is a privilege and what is a role?
Permissions are divided into system permissions and object permissions. System permissions are the rights to execute specific types of SQL commands. For example, the user has permission to create TABLE, you can build a table in its scheme, and when you have the Create any table, you can build a table in any scenario. Object permissions are rights to access other scenarios, and users can access objects directly to their scenarios, but you must have permission to the object if you want to access the object of another scenario. For example, Scott. Users must have object permissions on the Jane.emp table to access the Jane.emp table (Jane scheme, EMP table).
Roles are a collection of commands for related permissions, and the purpose of using roles is primarily to simplify the administration of permissions.
2). Common Permissions and role classifications
Here we only briefly introduce the most commonly used, if you want to know can be consulted to help the document can also be obtained through the query statement.
A. System permissions: About database connections, sessions (session), users (user), Tablespace (tablespace), roles (role), Data Objects (tables, views, stored procedures), and other actions such as the Create, Drop and Alter.
B. Object permissions: Insert--Add, delete--delete (data), Alter--Modify (modify table structure), UPDATE--Modify (update data), Select--query. That is to change the four categories of check and delete.
C. The roles are divided into predefined and custom two types. Predefined refers to the roles that Oracle provides, commonly included: Connect,resource,dba.
The DBA role has all system permissions, and the default DBA user is SYS and system. These two users can grant any system permissions to other users. Note that the DBA role does not have the right to start shutting down the database. The Connect role has most of the permissions the general developer needs, and in most cases it is enough to authorize connect and resource roles for the user. So what are the privileges of Connect and resource roles? There is no need to enumerate here. We can get it by query. So how to query it?
3). Query
How do I query Oracle for a number of roles? SELECT * from Dba_roles;
How do I query Oracle for the number of system permissions and object permissions? SELECT * from System_privilege_map order by name; Select distinct privilege from Dba_tab_privs;
How do I see what role a user has? SELECT * from Dba_role_privs where grantee= ' username ';
How do I 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 Recycle
This part of the knowledge in the learning process is a rule to come, after learning to feel particularly chaotic. I put the things here in accordance with a normal operating process into a line of operation. Feel the knowledge more clear.
A. Now I want to use Oracle, I have to have users. Oracle creates two user sys and system by default. We can use these two users to login up and set up their own users, such as: Create user Ken identified by Ken. Now we use Ken to log in and we'll give you an error message. Why? Because Ken user does not have permission to log on. Now we need to give Ken the authorization: Grant create session to Ken with admin option. So Ken User login on OK. So what's the function with admin option? This means that the authorized user, or role, can also give the system permissions to other users or roles.
Recycle system permissions Operation: Still use the above example: Reclaim login rights: Revoke create session from Ken; The collection of system permissions is not cascading. For example: Ken also grants Jane Login rights, so after the recovery of Ken's permissions, Jane can still log in.
B. For object permissions, let's take an example: if there is a table emp in my scenario, now I want Ken to have permission to manipulate my table. So what do I do? The first option is to give Ken the right to operate my table EMP with the DBA user, and here's a point to note: DBA users can grant object permissions on any object to other users.  The second way is for me to do it myself. How to do it? Grant SELECT on EMP to Ken with GRANT option. I use the WITH GRANT option here. This works: Ken users can grant other users permission to manipulate my table emp. Here's a little note. Unlike with admin option, the WITH GRANT option can only be granted to a user and cannot be granted a role.
Reclaim object Permission action: Revoke select on EMP from Ken; The collection of object permissions is cascading. For example: Ken granted the right to the EMP table query to Jane, so after the recovery of Ken's permissions, Jane will not be able to query the EMP table.
C. Role authorization requires two steps: 1 gives role authorization 2 to a user. Now we're going to give the login permission to a role: the grant create session to the role name, and then the role is given to the user ken:grant the role name to Ken. Delete roles take the drop role role name.

Summary: This blog focuses on introducing Oracle's basic mechanism, which is the most basic knowledge, and finally describes how to assign permissions and roles, including what they are, how they are categorized, and how to use them. Here we have to understand a premise is very good to understand: we have to do a certain thing must have permission. When we do not have permission to use the system user to do, if the system gives us the right to do so we can do.

The above is to Oracle knowledge collation, there are many knowledge points did not involve, in the future article updates will be supplemented, I hope that we continue to pay attention to.