Summary of oracle Permissions

1. system permission management 1.1 three default users: oracle 9i sys change_on_install [as sysdba] -- sqlplus/as sysdba system manager www.2cto.com scott triger oracle 10 Gb or above sys. [as sysdba] set during system installation. scott triger is locked by default. Alter user account lock/UNLOCK sys: oracle network administrator. Global Administrator system: normal administrator scott: normal user oracle usage condition: Enable listener (lsnrctl start), enable database instance (oradin-startup-sid orcl) 1.2 create user login SYS: sqlplus/as sysdba conn lisi/lisi show user (sqlplus) whoami (linux) create (alter) user lisi identified by lisi; 1.3 assign permission keywords: grant XX to user; grant create session to lisi; grant create table to lisi; grant unlimited tablespace to lisi; grant the create table permission to select, insert, update, the delete table and drop table permissions are granted to the tables created by the user. All permissions of the table are granted. 1.4 undo permission keywords: revoke XX from user; revoke create session from lisi; revoke create table from lisi; revoke unlimited tablespace from lisi; 1.5 view the data dictionary of the system permission www.2cto.com SELECT * FROM user_sys_privs;
2. Object permission 2.1 concept: access objects created by other users. Each object belongs to a user. To access other objects, You need to grant the permission of 2.2. Two users belong to two different transactions. One user does not have a COMMIT and another user cannot access it. COMMIT is similar to the concept of storage. 2.3 authorization object permission Keyword: grant xx on table to user. Example: grant select, insert, update, delete on table to lisi; grant all permissions: grant all on table to user (public); 2.4 revoke permission Keyword: revoke XX on table from user (public); example: revoke all on table from lisi (public); 2.5 who has permissions and who can authorize 2.6 to view the data dictionary of object permissions SELECT * FROM user_tab_privs; 2.7 display format SET linesize 400 dbms_output.disable; the table will be locked during insertion. Table-Level Lock 2. 8. Permission control and authorization for table columns: grant xx (column) on table to user; revoke permission: revoke xx (column) on table from user; insert, update can be controlled to columns, while select and delete cannot be used as data dictionaries: select * from user_col_privs; 2.9 concepts of query DDL (Data Definition Language), DML (data manipulation language ), DCL (grant and revoke permissions ). Only the DML language has TRANSACTION control.
3. permission transfer 3.1 system permission transfer default: permission can be used, but cannot be passed (9i and 10g are different) Permission transfer: grant XX to user with admin option Cancel transfer: the source is disconnected, you cannot pass the 3.2 object permission to www.2cto.com. By default, the permission can be used and the permission cannot be transferred: grant XX on table to user with grant option; Cancel transfer: the source is disconnected, it cannot be passed (9i and 10g are different)
4. role management 4.1 concept: a role is a set of permissions 4.2 created and role: create role XX; drop role XX; 4.3 permission on role: grant XX to role; 4.4 permission authorized to user: grant XX to user; 4.5 revoke permission: revoke XX from user or delete role 4.6 scope of use: limited permission is too high, you cannot directly grant role, you can only grant user, for example: unlimited tablespace 4.7 differences between create table and create any table create table: You can only create a table for yourself: not only can create a table for yourself, you can also create a table for any user. 4.8 only the alter any table and drop any table permissions have the create table permission, and the alter table and drop The table 4.9 role does not belong to any user. Is shared.
5. three login Methods: 5.1 change the password of a common user log on with SYS user. Change the password: Command Line: alter user scott identified by tiger. The graphic interface can also be modified: SQL statement 5.2 SYS (SYSDAA, SYSOPER. You cannot directly modify the three authentication mechanisms of the 5.3 oracle database.. operating system verification B. password File verification c. common oracle users for database verification: to use database verification, first start the database, and then verify SYS (SYSDAA, SYSOPER) Users: operating system verification and password file verification 5.4 oracle Startup Process 5.4.1.linux oracle startup process:. lsnrctl start (listener enabled) B. sqlpuls/(sys/oracle) as sysdba c. startup starts the instance and links to the listener before the database instance. linux has a strict data listener: to distinguish between SYS (SYSDAA, SYSOPER) Users and general users after 10 Gb usage: sqlplus/(sys/oracle) as sysdba before 10 Gb usage: sqlplus/nolog; conn sys/oracle as sysdba 5.4.2 wi In ndows, the oracle Startup Process lnsrctl start www.2cto.com oradin-start-sid orcl 5.5 is verified by the operating system by default. If it is not verified, it is managed by the computer in which the password file is verified: group: ora_dba sqlplus/as sysdba (operating system verification) sqlpuls sys/oracle as sysdba (password file verification) 5.6 in actual application. Generally, you forget to cancel the 5.7 SYS password for operating system verification. You can only change the password file to back up PWDorcl under product \ 10.2.0 \ db_2 \ database. the ora file uses commands to regenerate the password file: PWDorcl. ora command: orapwd file = pwdora10g. ora password-sys entries = 10 (privileged user) force (whether overwrite) SYS privileged user viewing data dictionary: SELECT v $ pwdfile_users (dynamic performance table );
6. account Management details 6.1 create user username identified by password default tablespace (belongs to the USER's TABLE, INDEX, constraints) temporary tablespace (mainly used for sorting. group by order by, etc.) QUOTA (QUOTA) integer K | M | limited on tablespace (QUOTA 50 m on tablespace) 6.2 refer to the graphic interface (QUOTA) of the orcle client ). If unlimited tablespace is assigned, there is no limit of 6.3 when a user is created on the graphic interface. The role is assigned by default, and the user does not have any data. There are no users in the solution. By default, the "www.2cto.com 6.4" USER locks and PASSWORD becomes invalid. Command Line: alter xx user account lock/UNLOCK; user password becomes invalid immediately: alter user xx password expire; (When EBS creates a USER) 6.5 DROP user XX [CASCADE] CASCADE: indicates to delete all USER objects drop user XX cascade; Author: cunxiyuan108