Oracle user security management

1. create and manage database user accounts 1. Each database user account has the following items: (1) Unique User name: it cannot exceed 30 bytes and cannot contain special Pearl letters, it must start with a letter. (2) Verification Method: Password, external verification, global verification (3) default tablespace: note that you must separately authorize the creation of objects and setting the space limit.
Www.2cto.com (4) Temporary tablespace: You can create a tablespace for temporary objects (in sorting and temporary tables) in it (5) user profile: limits on a group of resources and passwords assigned to users. (6) User Group: used by the resource manager. (7) Lock status: the user can only access the "Unlocked" account. Create user account create user gyj3 identified by gyj3 default tablespace tp1 QUOTA 10 m on tp1 temporary tablespace temp -- PROFILE app_user -- PASSWORDEXPIRE--ACCOUNT LOCK; CREATE a simplest USER operation command as follows: sys @ OCM> create user gyj3 2 identified by gyj3 3 default tablespace tp1 4 QUOTA 10 m on tp1 5 temporary tablespace temp; User created. 3. query user information sys @ OCM> col username for a30sys @ OCM> col default_tablespace for a10sys @ OCM> col temporary_tablespace fora10sys @ OCM> selectusername, default_tablespace, temporary_tablespace,
To_char (created, 'yyyy-mm-dd: hh24: mi: ss'), account_statusfrom dba_users; USERNAME DEFAULT_TA TEMPORARY_TO_CHAR (CREATED,
'Yy ACCOUNT_STATUS certificate ------------------ ------------------- using MGMT_VIEW system temp 2009-08-13: 23: 24: 58 opensys system temp 2009-08-13: 23: 00: 59 opensystem temp 2009-08-13: 23: 00: 59 opendbsnmp sysaux temp: 23: 06: 35 opensysman sysaux temp: 23: 22: 32 OPENGYJ3 TP1 TEMP 2013-03-01: 10: 51: 33 OPENGYJ TP1 TEMP 2013-02-28: 15: 21: 03 openoutln system temp: 23: 01: 00 EXPIRED & LOCKEDFLOWS_FILES sysaux temp: 23: 26: 08 EXPIRED & lockedmdsys sysaux temp: 23: 12: 05 EXPIRED & lockedordsys sysaux temp 2009-08-13: 23: 12: 05 EXPIRED & lockedexfsys sysaux temp 2009-08-13: 23: 09: 35 EXPIRED & lockedwmsys sysaux temp 2009-08-13: 23: 07: 30 EXPIRED & lockedappqossys sysaux temp 2009-08-13: 23: 06: 36 EXPIRED & sysaux temp 2009-08-13: 23: 26: 09 EXPIRED & LOCKEDOWBSYS_AUDIT sysaux temp 2009-08-13: 23: 35: 05 EXPIRED & lockedorddata sysaux temp: 23: 12: 05 EXPIRED & lockedctxsys sysaux temp: 23: 09: 45 EXPIRED & lockedanonymous sysaux temp: 23: 10: 12 EXPIRED & lockedxdb sysaux temp: 23: 10: 12 EXPIRED & lockedordplugins sysaux temp: 23: 12: 05 EXPIRED & lockedowbsys sysaux temp: 23: 35: 03 EXPIRED & LOCKEDSI_INFORMTN_SCHEMA sysaux temp 2009-08-13: 23: 12: 05 EXPIRED
& Lockedolapsys sysaux temp 2009-08-13: 23: 18: 04 EXPIRED & lockedscott users temp 2009-08-13: 23: 35: 44 EXPIRED & LOCKEDORACLE_OCM users temp 2009-08-13: 23: 02: 20 EXPIRED & LOCKEDXS $ null users temp 2009-08-13: 23: 11: 44 EXPIRED & lockedbi users temp 2013-02-27: 23: 11: 56 EXPIRED & lockedpm users temp 2013-02-27: 23: 11: 56 EXPIRED & lockedmddata users temp 2009-08-13: 23: 19: 11 EXPIRED & lockedix users temp 2013-02-27: 23: 11: 56 EXPIRED & lockedsh users temp 2013-02-27: 23: 11: 56 EXPIRED & lockeddip users temp 2009-08-13: 23: 01: 49 EXPIRED & lockedoe users temp 2013-02-27: 23: 11: 56 EXPIRED & LOCKEDAPEX_PUBLIC_USER users temp 2009-08-13: 23: 26: 09 EXPIRED
& Lockedhr users temp 2013-02-27: 23: 11: 56 EXPIRED & LOCKEDSPATIAL_CSW_ADMIN_USR usr temp 2009-08-13: 23: 22: 20 EXPIRED
& LOCKEDSPATIAL_WFS_ADMIN_USR users temp 2009-08-13: 23: 22: 15 EXPIRED
& LOCKED 2. Grant and revoke permissions 1. permissions are classified into two categories: system permissions and object permissions (1) system permissions: allow the user to perform specific operations in the database. The permissions of A and SYSDBA/SYSOPER are special. gyj @ OCM> select * from dba_sys_privs where granteein ('sysdba ',
'Sysoper '); the system permissions of no rows selected B and DBA are available gyj @ OCM> select * from dba_sys_privs where grantee = 'dba '; grantee privilege adm placement --- dba drop any cube build process yesdba create cube yesdba alter any cube dimension yesdba alter any mining model yes (intermediate results omitted) ................................ 202 rows selected. c. System permission gyj @ OCM> select * fromdba_sys_privs where grantee = 'gyj '; grantee privilege adm ---------------------------- policgyj unlimited tablespace no d, system permission GYJ @ OCM> select * from session_privs; PRIVILEGE --------------------------------------- -alter systemaudit systemcreate sessionalter sessionrestricted session (the intermediate result is omitted) ................................. 202 rows selected. (2) object permissions: allow users to access and manipulate specific objects. A. Check object permissions. gyj @ OCM> select * fromdba_tab_privs where grantee = 'gyj '; no rows selected B. Check the permission of the column on the object. gyj @ OCM> select * from dba_col_privs wheregrantee = 'gyj'; no rows selected does not show the permission of the object and the permission of the column on the object, user GYJ clearly object Ah: gyj @ OCM> show USER; user is "GYJ" gyj @ OCM> select table_name from tabs; good TABLE_NAME------------------------------T10, I log on to the HR user and grant the GYJ user the object permission sys @ OCM> conn hr/hrConn Ected. hr @ OCM> grant select on employees togyj; Grant succeeded. hr @ OCM> grant update (department_id) onemployees to gyj; Grant succeeded. check the object permission again and you will see the result. Now you can understand this, OK! This is not much to say. Hr @ OCM> select * from dba_tab_privs wheregrantee = 'gyj '; grantee owner TABLE_NAME grantor privilege gra hie issue ---------------------------------------- gyj hr employees hr select no hr @ OCM> select * from dba_col_privs where grantee = 'gyj '; grantee owner TABLE_NAME COLUMN_NAME grantor privilege gra too large ------------- gyj hr employees DEPARTMENT_ID hr update no 2. Difference Between with admin option and with grantoption user, if the first user is canceled
This permission applies to other users who grant this permission through the first user.
Is canceled, and other users will not be affected if they are not cascade .) (1) with admin option system permissions are granted to cascade operations. cancellation is not cascade operations, as shown in: with admin option indicates that the user granted this permission has the right to grant a certain permission (such as create
Any table) is granted to other users or roles, and cancellation is not cascade. For example, the Administrator DBA grants Jeff the system permission to create session with admin option, and then Jeff
Grant the createsession permission to Emi, but when DBA revokes the create session permission of Jeff, Emi
Still have the create session permission. However, the Administrator DBA can explicitly recover the Emi
Create session permission, that is, directly revoke create session from Emi; good! Let's do A test: A. First-built dual-account Jeff and Emihr @ OCM> conn/as sysdbaConnected. sys @ OCM> create user Jeff identified byJeff; User created. sys @ OCM> create user Emi identified byEmi; User created. b. The administrator gives Jeff the system permission to createsession with admin option; sys @ OCM> grant create session to Jeff with admin option; Grant succeeded. c. Jeff again granted the create session permission to Emisys @ OCM> conn Jeff/JeffConnected. jeff @ OCM> grant create session to Emi; Gra Nt succeeded. D. Use Emi to log on to jeff @ OCM> conn Emi/EmiConnected. Emi. The connection with adminoption is successful. E. The Administrator adds Jeff's grant createsession permission. Emi @ OCM> conn/as sysdbaConnected. sys @ OCM> revoke createsession from Jeff; Revoke succeeded. f. Jeff user Login sys @ OCM> conn Jeff/JeffERROR: ORA-01045: user JEFF lacks CREATE SESSIONprivilege; logon denied Warning: You are no longer connected toORACLE. an error is reported, indicating that the permission is successfully revoked. Finally, let's see how to log on to sys @ OCM> conn Emi/EmiConnected with an Emi user. the logon is successful, indicating that the permission revocation is not cascade. (2) with grant option object permissions are granted to cascade, and cancellation is also cascade, as shown in: the difference is that when the Administrator revokes the user object permission authorized by withgrant option, the permission will be propagated
This permission is invalid. For example, if grant select on table with grant option to Jeff, Jeff grants this permission
Emi, but when administrator Bob revokes Jeff's permission, the permission of Emi will also be invalid, but the administrator cannot
Directly revoke the select on table permission of Emi. Well, this test is as simple as above. I will not do it anymore. If you do not understand it, you can test it on your own. 3. grant the permission grant create any table to gyj; grant select any table to gyj; -- The gyj user cannot access the DBA series view,
Data dictionary table grant update on t10 to gyj with grant option; grant update (id) on t10 to gyj with grant option; 4. revoke the revoke create any table from gyj; revoke update on t10 from gyj; 3. Create and Manage Roles 1. roles (1) Simplify permission management: grant some permissions to a role, then assign the role to each user,
Instead of granting the same group of permissions to multiple users. (2) dynamically manage permissions: If the permissions associated with the role are modified, all users of the role will be granted immediately.
Automatically obtain the repaired permissions. (3) selective application permissions: You can enable or disable a role to temporarily enable or disable permissions. 2. Role features (1) a role is like a user who can grant or revoke permissions to a role. (2) A role is like a system permission, which can be granted to a user or another role, or revoked from a user or
Other roles. (3) A role can be composed of system permissions and object permissions. (4) You can enable or disable a role for each user authorized to a role. (5) You may need a password to enable the role. (6) A role cannot be owned by any user or belongs to any program. 3. pre-defined role (1) CONNECT: create session (2) RESOURCE: create cluster/create indextype/create operator/CREATEP
ROCEDURE/create sequence/create table/create trigger/
Create type (3) DBA: has most system permissions and several other roles. Do not grant it to the Administrator
Other personnel 4. Create a role. Run the following command: (pay attention to the red font) (1) first query gyj @ OCM> select * from dba_role_privs where grantee = 'emi '; no rows selected gyj @ OCM> select * from role_sys_privs where role = 'Role _ GYJ1 '; no rows selected gyj @ OCM> select * from role_tab_privswhere ROLE = 'Role _ gyj1 '; no rows selected select * from session_privs; gyj @ OCM> conn Emi/EmiConnected. emi @ OCM> select * from session_privs; PRIVILEGE --------------------------------------- -create session (2) create a role and authorize gyj @ OCM> create role_gyj1; Role created. gyj @ OCM> grant select any table torole_gyj1; Grant succeeded. gyj @ OCM> grant role_gyj1 to Emi; Grant succeeded. emi @ OCM> conn Emi/EmiConnected. emi @ OCM> select * from gyj. t10; id name Generation 1 gyj1 emi @ OCM> select * from session_privs; PRIVILEGE --------------------------------------- -create sessionselect any table 4. CREATE and manage profiles 1. create profile app_user LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 45 limit default limit 1000 PRIVATE_SGA 15 k limit 5000000 LIMIT 5 PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_TIME 60 PASSWORD_REUSE_MAX 5 PASSWORD_LOCK_TIME 1/24 PASSWORD_GRACE_TIME 10; 2. Password Management (1) user locks FAILED_LOGIN_ATTEMPTS n: the correct password is not entered after the user logs on N times,
The user is automatically locked. PASSWORD_LOCK_TIME n: When the above parameter limit is reached and the user is locked
Number of days when the user is locked. Createprofile pro2 limit failed_login_attempts 3 PASSWORD_LOCK
_ TIME 1; select * from dba_profiles where PROFILE = 'pro2'; (2) password validity period PASSWORD_LIFE_TIME n: Set the password validity period, in days. PASSWORD_GRACE_TIMEn: grace period. After the password is invalid, the user should change the password immediately. Within the specified grace period, if the user
The user cannot log on because the password has not been changed. Each time you log on within the grace period, you will receive a warning message. (3) Historical password limit PASSWORD_REUSE_TIME: the number of days before the previous password can be reused. That's it.
Yes. After the new password is set, you cannot use the old password that you have used in the past (4) for the new password within N days. PASSWORD_REUSE_MAX the maximum number of times a password is used. If you set PASSWORD_REUSE_MAX to 3, a string of symbols is
After being set as a password three times, the string cannot be set as a password. 3. Resource Management (1) enable resource limit show parameterresource_limit (2) Session-level resource limit, mainly for sessions. In the configuration file, there are several session-level resources
Source limit parameter: CPU_PER_SESSION: total CPU time that a session can use. Unit: 1% seconds. SESSIONS_PER_USER: Maximum number of sessions that a user can open. CONNECT_TIME: The session duration in minutes. IDLE_TIME: the idle time in minutes. LOGICAL_READS_PER_SESSION: the number of data blocks that the session can read. PRIVATE_SGA: in Shared Server mode, each user connects to the space in the SGA that can be used,
The Unit is byte. If a session with resource limits enabled exceeds these limits, a ORA-02391 error is reported,
ORACLE will disconnect the session at the same time. Www.2cto.com (3) invocation-level resource limit the invocation-level resource limit, mainly for the execution of each command. Each Command is called once,
The call-level resource limit determines the CPU time and I/O count that can be used for each call. Invocation-level resources
There are two restricted parameters: CPU_PER_CALL: CPU time that can be consumed by each call. Unit: 1% seconds LOGICAL_READS_PER_CALL: number of data blocks that can be read by each call. 4. Check the profile information. select * from dba_profiles;