Oracle Rights related content summary ____oracle

1. System Rights Management
1.1 3 Default Users
Oracle 9i
SYS Change_on_install [as Sysdba]--sqlplus/as sysdba
System Manager
Scott Triger
Oracle 10g above
SYS Setup when it is installed. [As SYSDBA]
Setup when system is installed.
Scott Triger is locked by default. should be unlocked (ALTER USER account Lock/unlock)
Sys:oracle network administrator. Administrator for Global
System: Normal Administrator
Scott: normal Users
Oracle usage Conditions: Enable listening (lsnrctl start), enable database instance (Oradin-startup-sid ORCL)
1.2 Creating users
Landing Sys:sqlplus/as SYSDBA Conn Lisi/lisi
Show User (Sqlplus) whoami (Linux)
Create (Alter) user Lisi identified by Lisi;
1.3 Assigning permissions
Keyword: Grant XX to user;
Grant create session to Lisi;
Grant CREATE table to Lisi;
Grant unlimited tablespace to Lisi;
With CREATE TABLE permission, you have the Select,insert,update,delete table and drop table permissions
The table you created has all the permissions on this table.
1.4 Revoke Permissions
Keywords: revoke XX from user;
Revoke create session from Lisi;
Revoke CREATE table from Lisi;
Revoke unlimited tablespace from Lisi;
1.5 Data dictionary to view system permissions
SELECT * from User_sys_privs;
2. Object permissions
2.1 Concept: Access to objects created by other users, each of which belongs to one user. Other object access, you need to assign permissions
2.2 Two users belong to two different transactions. A user without commit, another user can not access, commit similar to the concept of disk
2.3 Authorized Object Permissions
Keyword: Grant xx on table to user;
Example: Grant Select,insert,update,delete on table to Lisi;
Authorize all permissions: Grant all on table to user (public);
2.4 Revoke permissions
Keyword: revoke XX on table from user (public);
Example: Revoke all on table from Lisi (public);
2.5 who has permission, who can authorize
2.6 Data dictionary for viewing object permissions
SELECT * from User_tab_privs;
2.7 Display format
SET Linesize 400
dbms_output.disable;
The table is locked and table-level locks are inserted
2.8 Permissions control on the columns of the table
Authorization: Grant XX (column) on the table to user;
Revoke permission: Revoke XX (column) on the table from user;
Insert,update can control the columns, Select,delete not.
Data dictionary: SELECT * from User_col_privs;
2.9 The concept of a query
DDL (data definition Language), DML (Data Manipulation language), DCL (authorization and revocation permissions). Only the DML language has transaction control.
3. Permission Transfer
3.1 Transfer of system permissions
Default: Permissions can be used, but cannot be passed (9i and 10g are not the same)
Permission delivery: Grant XX to user with admin option
Undo Delivery: The source is broken and cannot be transmitted.
3.2 Transfer of object permissions
Default: Permissions can be used and cannot be passed
Permission pass: Grant XX on table to user with GRANT option;
Undo Delivery: The source is broken and cannot be passed (9i and 10g are not the same)
4. Role Management
4.1 Concept: A role is a collection of permissions
4.2 Create and role:create role XX; Drop role XX;
4.3 Permissions on role:grant XX to role;
4.4 Authorization to the user:grant XX to user;
4.5 REVOKE permission: Revoke XX from user or delete role
4.6 Use scope: limited permission is too high, can not give role directly, can only give user, for example: Unlimited tablespace
The difference between 4.7 create table and create any table
CREATE TABLE: Only creating tables for yourself
Create any table: not only can you make a table for yourself, you can also create a table for any user
4.8 Only alter any table and drop any table permissions
Owning the CREATE TABLE, you have ALTER TABLE and drop table
4.9 The role does not belong to any one user. is shared.

4.10 View the role's data dictionary:

SELECT * from Dba_roles; --Queries All current role lists, including custom
SELECT * from Dba_role_privs; --Querying a user's role permissions
SELECT * from Role_sys_privs; --Querying the role of the current user and the system permissions it contains

5. Three ways of landing
5.1 Normal User Password modification
Log in with sys user, modify password:
Command line: Alter user Scott identified by Tiger;
Graphical interface can also be modified: will be converted to SQL statements
The 5.2 SYS (sysdaa,sysoper) user password was lost. cannot be directly modified
Three authentication mechanisms for 5.3 Oracle database
A. Operating system validation
B. password file verification
C. Verification of the database
General Oracle Users: Use database validation, start the database, and then verify
SYS (sysdaa,sysoper) User: Validation of operating system validation and password files
5.4 Oracle's startup process
5.4.1.linux Oracle Boot process:
A.lsnrctl start (Enable listening)
B.sqlpuls/(sys/oracle) as Sysdba
C.startup Startup instance
Link the listener first, then link the database instance. Linux has strict boot data
Listening: to differentiate between the SYS (sysdaa,sysoper) user and the average user
10g usage: sqlplus/(sys/oracle) as Sysdba
10g previous usage: Sqlplus/nolog;conn sys/oracle as Sysdba
5.4.2 the boot process for Oracle under Windows
Lnsrctl start
Oradin-start-sid ORCL
5.5 The default is operating system verification, not validated through the password file in the authentication
Computer Management: Group: ORA_DBA
Sqlplus/as SYSDBA (validation of the operating system)
Sqlpuls sys/oracle as SYSDBA (password file verification)
5.6 Practical applications. Generally cancel the operating system verification
5.7 sys password forgotten. Only password files can be modified
Backup Pwdorcl.ora ora files under product\10.2.0\db_2\database
To regenerate a password file using a command: Pwdorcl.ora ora
Command:
Orapwd File=pwdora10g.ora Password-sys entries=10 (Privileged user) force (whether overwrite)
SYS privileged User view data dictionary: SELECT v$pwdfile_users (Dynamic performance table);
6. Account Management Details
6.1 Creating users
CREATE User User Name
Identified by password
Default Tablespace table space (table,index for user, constraint)
Temporary tablespace temporary table spaces (primarily using sorting.) GROUP by order BY, etc.)
QUOTA (limit) integer k| m| LIMITED on table space (QUOTA 50M on tablespace)
6.2 Reference Orcle Client's graphical interface (quota). Unlimited tablespace assigned, there's no limit.
6.3 Graphical interface when creating users. The default to the role, the user does not have any data. No users in the scheme, default in security
6.4 User lock unlock and password expiration
Command line: ALTER XX USER account Lock/unlock;
Immediate expiration of user password: ALTER user XX PASSWORD EXPIRE (when EBS creates a user)
6.5 DROP USER XX [CASCADE]
CASCADE: Means to delete all objects of a user
Drop user XX cascade;