Oracle's SQL Simple operation

Operate http://huangsir007.blog.51cto.com/6159353/1854392 based on the previous article

Oracle User sys, System differences:

SYS user is superuser, has the highest privileges, has the SYSDBA role, has the CREATE DATABASE permission

The system user is a management operator with large permissions, a sysoper role, and no permissions to create database

In general, for database maintenance, using system users to log on can

Sqlplus

Usage 2:sqlplus [[<option>] [{logon |/nolog}] [<start>]

<option> is: [-c <version>] [-l] [-m] <options> "] [-R <level>] [-S]

<logon> is: {<username>[/<password>][@<connect_identifier>] |/}

[As {SYSDBA | Sysoper | SYSASM}] [Edition=value]

Oracle a database is a single instance (creating a database is creating a single instance with a few users by default)

Connection command

1, Conn, usage: Conn User name/password @ NETWORK SERVICE name [as Sysdba/sysoper]

When connecting with a privileged user, you must bring as SYSDBA or as Sysoper

Log in with an empty user:

[Email protected] ~]$ Sqlplus/nolog

Sql*plus:release 11.2.0.1.0 Production on Thu Sep 15 22:31:50 2016

Copyright (c) 1982, Oracle. All rights reserved.

Sql> Show user;

USER is ""

Sql>

Log on using System user

[Email protected] ~]$ Sqlplus system/redhat

Sql*plus:release 11.2.0.1.0 Production on Thu Sep 15 19:56:40 2016

Copyright (c) 1982, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

Sql>

Sql> Show user;

USER is "SYSTEM"

Sql>

To switch users to Scott

Sql> Conn Scott/redhat;

ERROR:

Ora-28000:the account is locked indicates that the user has been locked out

Unlocking with system users

Sql> Conn System/redhat

Connected.

sql> alter user Scott account unlock; The command is unlocked

User altered.

Then re-use Scott user Login

Sql> Conn Scott/tiger;

ERROR:

Ora-28001:the Password has expired informs the user that the Scott password expires

Changing password for Scott

New Password:

Retype new Password:

Password changed

Connected.

Sql> Show user;

USER is "SCOTT"

File Operations Command:

1. Run SQL script, Start/root/a.sql

Sql>start/home/oracle/a.sql

2, Spool will sqlplus on the screen input into a file

sql>spool/home/oracle/b.sql;

Sql>select * from EMP;

Sql>spool off; Save the SELECT * from EMP query to a file

&: You can override a variable that, when executed, requires the user to enter

Sql>select * from emp where job= ' &job '; This will enter a value for the job instead

Sql> SELECT * from emp where job= ' &job ';

Enter value for Job:manager

Old 1:select * from emp where job= ' &job '

New 1:select * from EMP where job= ' MANAGER '

EMPNO ename JOB MGR hiredate SAL COMM

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

DEPTNO

----------

7566 JONES MANAGER 7839 02-apr-81 2975

20

7698 BLAKE MANAGER 7839 01-may-81 2850

30

7782 CLARK MANAGER 7839 09-jun-81 2450

To display and set environment variables:

Sql> show Linesize;

Linesize 80

Sql>set Linesize 50;

Create user

In Oracle, to create a new user using the Create USER statement, you typically have DBA authority to use

Sql> create user xiaoming identified by Redhat;

User created.

Sql> Show user;

USER is "SYSTEM"

To change the password for the user, requires DBA authority or alter user's system permissions

sql> Password Xiaoming

changing password for xiaoming

New Password:

Retype new Password:

Password changed

Or

Sql>alter user username identified by new password;

sql> alter user xiaoming identified by Redhat;

User altered.

Note: The newly created user is not able to log in immediately, the new user created does not have any permissions, so cannot log in

Sql> Conn Xiaoming/huang;

ERROR:

Ora-01045:user Xiaoming lacks CREATE SESSION privilege; Logon deniedwarning:you is no longer connected to ORACLE.

Sql> Show user;

USER is ""

To delete a user:

Usually as a DBA to delete a user, if you use other users to delete a user, you need to have drop user permissions

Example: Drop user username [cascade]

When deleting a user, be aware that if the deleted user has already created the table, then you need to add a parameter when deleting the Cascade

The meaning of this parameter is to delete the user and the user-created table

User Management----"Authorization

The new user created does not have any permissions, not even login, you need to set appropriate permissions for it, grant permission with the GRANT command

The command to reclaim permissions is revoke

Oracle has prior permission to this definition, there are two types of

1, System permissions: Users of the database access to the relevant permissions (build a database to build a table index login databases, etc.)

Create session (System permissions 140)

2. Object permissions: User permissions for data object access operations to other users

Data objects: Tables, views, triggers, etc. created by each user (25)

Oracle role: In order to grant permissions, some roles have been defined in advance and some permissions have been given

Select certain permissions from the system permissions assigned to the specified role

Connect is one of the roles (with 7 permissions)

Grant Connect to Xiaoming

There are two types of roles:

1. Custom roles: Define Roles yourself

2. Predefined roles: The database has a well-established role (connect is a predefined role)

Examples of roles:

Connect

DBA: Granting DBA role will have all the privileges of the DBA system

Resource: Allows a user to create a table in a table space

Example:

Sql> Grant connect to Xiaoming; Grant the connect role to Xiaoming and have some permissions

Grant succeeded.

Log in again using the user xiaoming:

Sql> Conn Xiaoming/redhat; After this authorization, the user xiaoming will be able to log in

Connected.

The user you just created does not have any tables, can a newly created user create a table?

Sql> Conn Xiaoming/redhat;

Connected.

Sql> Show user;

USER is "Xiaoming"

Sql> CREATE TABLE student (ID number,name varchar2 (20));

CREATE TABLE student (ID number,name varchar2 (20))

*

ERROR at line 1:

Ora-01031:insufficient Privileges

From the above error found that the grant connect role does not create a table, then add resource role?

Switch users and grant the role to Xiaoming

Sql> Show user;

USER is "Xiaoming"

Sql> Conn System/redhat;

Connected.

Sql> grant resource to xiaoming;

Grant succeeded.

CREATE TABLE again

Sql> Show user;

USER is "Xiaoming"

Sql> Conn System/redhat;

Connected.

Sql> grant resource to xiaoming;

Grant succeeded.

Sql> Conn Xiaoming/redhat

Connected.

Sql> CREATE TABLE student (ID number,name varchar2 (20));

Table created.

sql> desc Student;

Name Null? Type

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

ID number

NAME VARCHAR2 (20)

You can see that the table was created successfully, which is the role of the resource role

Object permissions: Permissions for users to access operations on data objects for other users

1. Select

2. Insert

3. Update

4. Delete

5. All

6, create index and so on

Data objects: Tables, views, triggers, etc. created by each user (25)

Xiaoming can this user query a table under the Scott user?

Sql> select * from EMP;

SELECT * FROM emp

*

ERROR at line 1:

Ora-00942:table or view does not exist can clearly see that you cannot access the table EMP under Scott

How do I do that?

Because the table EMP is under the Scott user, you need to use the Scott user to authorize the Xiaoming user

Sql> Conn Scott/redhat

Connected.

Sql> Grant Select on EMP to Xiaoming; Authorizing with a Scott user

Grant succeeded.

Sql> Conn Xiaoming/redhat Connection xiaoming This user

Connected.

Sql> select * from EMP; Query the table EMP, but still fail due to Scoot's table emp

SELECT * FROM emp

*

ERROR at line 1:

Ora-00942:table or view does not exist

So it needs to be queried as follows

Sql> select * from Scott.emp; The following table for Scott users

EMPNO ename JOB MGR hiredate SAL COMM

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

DEPTNO

----------

7369 SMITH Clerk 7902 17-dec-80 800

20

REVOKE Permissions Revoke

Scott retract xiaoming have query permission

Revoke select on the EMP from Xiaoming;

Revoke revoke permissions, it must be the user that the original user granted permission to take back

Sql> Show user;

USER is "Xiaoming"

Sql> Conn Scott/redhat;

Connected.

sql> revoke select on EMP from Xiaoming;

Revoke succeeded.

Maintenance of permissions:

1. If the object permission

Hopefully xiaoming users can query Scott's EMP table, and also want Xiaoming to give this permission to another user?

Take advantage of the following authorizations from Scott users

Sql> Show user;

USER is "SCOTT"

Sql> Grant SELECT on EMP-xiaoming with GRANT option;

Grant succeeded.

Then create a new user, grant the Connect role permission

Sql> Conn System/redhat

Connected.

Sql> create user Xiaobai identified by Redhat;

User created.

Sql> Grant connect to Xiaobai;

Grant succeeded.

Then use the Xiaoming user to authorize the Xiaobai user

Sql> Conn Xiaoming/redhat

Connected.

Sql> Grant Select on Scott.emp to Xiaobai;

Grant succeeded.

Finally, use the Xiaobai user to query the EMP table

Sql> Conn Xiaobai/redhat

Connected.

Sql> select * from Scott.emp;

EMPNO ename JOB MGR hiredate SAL COMM

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

DEPTNO

----------

7369 SMITH Clerk 7902 17-dec-80 800

20

2, if the system permissions

System user authorizes Xiaoming and enables users to grant permissions to other users

Grant connect to xiaoming with admin option; With admin option it's different here.

Manage user passwords with profile

Profile is a collection of command restrictions, resource constraints, and when the database is supervised, Oracle automatically establishes

Profile with the name default, and Oracle assigns the default to the user when the setup user does not specify the Profiles option

1. Account Lockout

Specify the maximum number of times a password can be entered when the account is logged in, or specify the time that the user is locked out, typically executing the command with the DBA's identity

For example: Specify Tea This user can only try to log in at most three times, lockout time is two days

Create profile (rule)

Sql>create profile Lock_account Limit failed_login_attempts 3 password_lock_time 2; -----"Lock_account for Profile name casually

Sql>alter User Tea profile Lock_account; Add a profile file constraint to a user's tea

2. Fix Lock:

Sql> Conn System/redhat;

Connected.

sql> alter user Scott account unlock;

User altered.

Sql> Conn Scott/redhat;

Connected

3. Termination password

In order for the user to periodically change the password can be done using the command to terminate the password, the same command also requires DBA identity to operate

Example: Create a profile for the user tea created earlier, requiring the user to modify their login password every 10 days

Grace 2 Days-----> grace period 10, remind you that there are two days to modify, equivalent to 12 days

Sql>create profile Myprofile limit password_life_time Password_grace_time 2;

Sql>alter User Tea profile myprofile;

4. Password history

If you want the user to change the password, you can not use the password that was previously used, password history is the way to store password modification information in a data dictionary

This way, when the user modifies the password, Oracle compares the old and new password, and when the old and new password is found, the user is prompted to re-enter the password

Example:

1. Establish profile

Sql>create profile Password_history Limit password_life_time password_grace_time 2 password_reuse_time 10;

---->password_reuse_time: Specifies that the password can be reused for 10 days (as with the old and new password, which takes 10 days to use)

2, assigned to a user---"Tea

Sql>alter User Tea profile password_history;

Delete profile

When you don't need a profile, you can delete the file

Sql>drop profile password_history [Cascade];

Users who have been constrained by this profile will be invalidated

Cascade: Cascading relationships

Oracle's SQL Simple operation