The first of Oracle database learning

Rights Management:

The default three username and password in Oracle 9:

SYS Change_on_install//highest-privileged administrator

System Manager//Normal administrator

Scott Tiger//ordinary user

In Oracle 10, these three users are still used as default users. However, the password for the SYS and system users is no longer default. When the database is installed, it can be specified by the user. From a security point of view, the Scott user is locked by default, so to use the user, you need to unlock the lock first.

Note: We want to use the Oracle database to start at least two services, one is the listening service, the other is the database instance.

Create a user;

Log in as a system administrator.

Use statement: Create user Lisi identified by Lisi; A user named Lisi is created and the password is Lisi

Although the user was created, the user does not now have any permissions. There is no access to the login database. If use: Sqlplus Lisi/lisi login database, will be an error, show no permission to create session.

Therefore, the first use of the system administrator to Lisi this user to specify the right to login.

The statement is: Grant create session to Lisi;

After authorization, Lisi can log in to the database. However, you do not have permission to create a database table, and you still need to specify it.

The statement is: Grant CREATE table to Lisi;

Using the Lisi account, create the database table: Creation table mytable (id int);

An error was prompted after execution: No permissions to Tablespace ' USERS '. Each database table has its own table space, which is equivalent to a file that must be located under a folder.

Although the Lisi user has permission to create a table, but does not have permission to use the table space, the table cannot be created at the end. It's like you have the key to my room, but without the key to my door, you can't get into my room at last.

Grant Lisi users permission to use tablespace through the system administrator:

Grant Unlimited tablespace to Lisi so that the user Lisi use of the tablespace is not limited.

Under Lisi account, CREATE table: Creation table mytable (id int);

Insert a record: INSERT INTO mytable values (1);

Insert succeeded.

You can also delete a table: drop table mytable;

Some people may have doubts, since the database authority management is so strict, above we only grant the Lisi user to create the table the permission. Did not give its insert, delete and other permissions ah. Here we can understand that: the current user created a table, then the table belongs to the user, since the user created a table, the table naturally has all the permissions.

Also: The database does not have permission to drop table. Use: Grant drop table to Lisi: Error message with missing or invalid permissions.

The above is granted permissions, so how do you revoke a user's permissions?

You can revoke LISI permission to create a table by using the following statement: Revoke CREATE table from Lisi;

Using the Lisi account to create the table, you will receive an error message: Insufficient permissions.

In most cases, if we modify the user's permissions frequently, how do we know what permissions the user has?

The database maintains a view by default some system information (called a data Dictionary) is provided externally to view the user's specific permissions.

Use the following statement to view system permissions for the current user:

SELECT * from User_sys_privs;

Usrename Privilege ADM

----------------------------------------------- ---------------------------------------- ----

LISI CREATE Session NO

LISI Unlimited tablespace NO

Permissions in Oracle are divided into system permissions and object permissions.

System permissions are some of the privileges mentioned above.

Object permissions are: For example, a user Lisi creates a table that can be viewed as an object. Does another user have permission to access the table? This is called object Rights Management.

We are creating a user Wangwu. The password is also WANGWU. Under the user, create a table mytab. If Lisi users to access the table Mytab, will it succeed?

Under Lisi window, enter: SELECT * from Mytab; error: Table or view does not exist. We know that the table belongs to the creator of the table.

Here we directly query the table Mytab, the database will go to the current user to find the table, obviously the current user Lisi no table mytab. So the hint table or view does not exist.

Then we specify the owner of the table and query again: the SELECT * from Wangwu.mytab window displays an error message with insufficient permissions. Therefore, although the Mytab table was found, there is no access to the permissions.

Only the owner of the table can grant related permissions to the table to other users. Use the User Wangwu operation window, use the following statement, the query statement to grant Lisi;

Grant SELECT on Mytab to Lisi;

After executing this statement, Lisi can query the Mytab table of the user Wangwu.

If you want to obtain additional permissions for the Mytab table, you still need to specify (multiple permissions are specified at the same time, separated by commas):

Grant Update,select,delete on Mytab to Lisi;

If you want to give all the permissions of the table to the user Lisi, you can write this;

Grantallon Mytab to Lisi;

Under the Wangwu window, insert a few data into the mytab. The query then displays "unselected rows". Note that the insertion just did not sync to the database.

Under Oracle, the SQL statement needs to be manually submitted by default. So after a few insert statements, you can execute a COMMIT, statement submission. Query again, the table has data.

If you want to grant a permission to all users, you can use the Public keyword:

Grant Create session Topublic;

View the current user's object permissions, using the following statement:

SELECT * from User_tab_privs;

Oracle's permissions control granularity is fine-grained and can even be accurate to a column of permissions.

Grant update (name) on Mytab to Lisi;

The effect of this sentence is that the Lisi user has only the right to update the Name column for table Mytab.

Grant Insert (ID) on Mytab to Lisi;

To view the current user's permissions on a database table's columns:

SELECT * from User_col_privs;

Under Lisi permissions, execute: Update wangwu.mytab set name= ' FDSFA ', id= ' Dfs ' where id=1;

Insufficient permissions are displayed after execution.

Update Wangwu.mytab set name= "FSA" where id=1;

That's it.

Also execute: INSERT INTO Wangwu.mytab values (4, ASF), and also show insufficient permissions after execution.

The modified statement is: Inset into Wangwu.mytab (ID) values (4); successful execution.

You can only control the permissions of a column precisely to the update and insert settings, not the query and deletion settings.

Command: Show user can view current user

There are three kinds of statements in the database:

DDL: Data definition language, specifying operations such as database table creation, deletion, and so on.

DML: Data manipulation language, for table additions and deletions to check operations, only DML needs to submit operations.

DCL: Data Control Language, management of system permissions and object permissions.

Transfer of permissions:

Delivery of System permissions:

The SYS user authorizes some system permissions to the Lisi user.

Grant alter any table to Lisi;

If you view the Lisi system permissions, you have permission to alter any table.

Now Lisi wants to grant this permission to the WANGWU user to execute the following statement: Grant alter any table to Wangwu, execute post "insufficient permissions."

To Lisi, you can also pass permissions, which can be added with the option "admin option" when the SYS user authorizes it, which explains the administrative ability to also have permissions.

That is: Grant alter any table to lisiwith admin option so that Lisi can pass permission on alter any table to WANGWU.

You can also pass this permission if you want to WANGWU, and use the admin option.

To view Lisi system permissions, the ADM field value for the same row of his alter any table permission is changed from No to Yes, which means that Lisi has an allocation function for that permission.

The delivery of object permissions:

is similar to the delivery of system permissions, but the following options change:

Adding SYS creates a table of a. Select permission granted to Lisi:

Grant SELECT on A to Lisi;

If you want Lisi to have the ability to allocate SELECT permissions on table A, simply modify to:

Grant SELECT on A to Lisiwithgrantoption;

Think: If the SYS administrator revokes Lisi permissions, is the WANGW permission revoked?

Manage permissions through roles

If according to the above authority management method, each user assigns the permission one by one, must be very confusing, causes the management difficulty. So Oracle provides roles to manage permissions in a collective.

A role is a collection of permissions.

To create a role under SYS:

Create role Myrole;

To add permissions to a role:

Grant create session to Myrole;

Grant CREATE table to Myrole;

To create a user:

Create user Zhangsan;

Grant Myrole to zhangsan;//gives the above two permissions to Zhangsan

Some system permissions cannot be assigned directly to a role because the permission is too large, such as unlimited tablespace.

For example: Execute Grant unlimited tablespace to Myrole;

Error prompted: Unable to grant unlimited tablespace role

To delete a role:

Drop role Myrole;

Permission examples:

CREATE TABLE create any table

[ALTER TABLE] ALTER ANY table

[Delete Table] Delete any table

Supplemental: The Oracle database does not contain a purple permission type. Because the CREATE TABLE permission is available, everything on the table is owned by the creator. You do not need to also specify ALTER TABLE and DROP TABLE permissions, which are available by default.

And the Create any table this permission indicates that the user can create a table for another user.

Example: Wangwu creates a table for Lisi temp

Create tablelisi.temp (id int);//It is possible to report "a space limit exceeding the tablespace ' users" error prompt, because Lisi user may not have tablespace permissions, perform the assigned Lisi user Unlimited Tablespace permissions, the problem can be resolved.

Note: The table belongs to a user. And the role does not belong to a user.

Oracle Three login authentication mechanism

Operating system validation

Password file verification

Database validation

For a large number of ordinary users, database startup, the user log in when the database validation.

For the SYS user, it has the largest permissions. Its permissions even include starting and shutting down the database. It is connected to the Oracle database when the Oracle database is not started and is started. In this way, it is not difficult to understand that the authentication of SYS cannot be verified by database because the database is not yet started. So SYS's authentication uses operating system authentication and password file validation (which is not very strict, and should be logged as both SYSDBA and sysoper connections).

When a user connects to the database, the client first connects to the listening service, listens to send the request to the database, if the authentication passes, then does not need to listen, the client directly and the database instance communicates.

Early Oracle, which runs on Linux and Unix, has a strict boot sequence: Start listening first (just knock a command, do not need any permissions), and then start the database instance (requires permissions)

Sequence of commands executed: Lsnrctl start//Start listening service

Sqlplus Sys/oracle as sysdba//initiates a request for an instance of the database, discovers that it is connected as SYSDBA, so it does not validate the database, but instead uses the operating system and password files. If validation passes, run the Startup database instance

Startup//Start database instance

Earlier versions command to write this:

Lsnrctl start

Sqlplus/nolog

Conn Sys/oracle as Sysdba

Startup

In Windows Oracle's boot process, a fool-type encapsulation:

Lsnrctl start

Oradim-starup-sid ORCL

Add: When connecting to the database, you can write this: Conn/as Sysdba can also repeatedly, even randomly specify a username and password, such as: Conn ABC/ABC as SYSDBA can be landed. This is because the connection is SYSDBA identity, first operating system validation. When we install the database, the account for the current system is added to the System Admins group of Oracle. Connected as above, it is validated by default based on the current account of the system. After the system account is deleted from the admin group, he will use the password authentication mechanism and must specify the username and password.

Question: How do I lose my password?

We know that if the normal user's password is forgotten, we can be the administrator's identity of the user's password to modify (unable to view, because the password is added secret, can only modify)

Can be modified directly under a graphical tool. You can also use the command:

Alter user Scott identified by Tiger;

In actual development, we have to cancel the operating system validation. After that, the password verification is used. But if we forget the password, how can we solve it?

We can delete the password file and generate a password file.

Locate the location of the password file:.. \db_2\database\pwdorcl.ora, the red part is a fixed part of the password file named, ORCL refers to the SID of the database and may not be the same

After you delete the password file, regenerate it into one. Use the ORAPWD command, as follows:

orapwdfile=< password file, the name of the password file to follow the previous > password=< specified password > entries=< This password file save the maximum number of DBA > Force= Whether to force overwriting file operations only

Example: Orapwd file=e:\oracle\ora92\database\pwdora9i.ora password=sys entries=10;

Use the following statement to see how many privileged users are placed in the password file:

SELECT * from V$pwfile_users;

To create a user:

Create User User Name

Identified by password

Default Tablespace table Space

Temporary tablespace table Space

Quota integer kmunlimited on table space

Example:

Create user ABC

Identified by ABC

Default table space for users/tablespace is user, where users can create tables

Temporary table space for temporary tablespace temp//user, for indexing, sorting, and so on, the equivalent of Windows temporary folder

Quota 50M on users//Specify the quota size for the users table space

Quota Unlimited on temp; Specify the quota size for temporary table spaces

Restrict user

User-Added lock

Alter user name account lock

User unlock

Alter user name account unlock

User Password immediately expires

Alter USER username password expire

Delete User:

Drop user user name [cascade]

Cascade is used to force cascading deletes when there are deleted objects (such as some tables) that are not deleted under the user. It represents the deletion of all objects of a user.