Common Oracle DBA statements 7 (password, role, user)

################## Managing password security and resources ################# ### alter user user_name account unlock/open; ---- lock/open the user; Alter user user_name password expire; --- set the Password Expiration/* set the password configuration file, the number of times the failed_login_attempts password is locked, password_lock_times indicates how many days after the password is automatically unlocked */create profile profile_name limit failed_login_attempts 3 password_lock_times 1/1440; /* Create a password configuration file */create profile profile_name limit 3 password_lock_time unlimited password_life_time 30 password_reuse_time 30 password_verify_function verify_function password_grace_time 5; /* create a resource configuration file */create profile prfile_name limit session_per_user 2 cpu_per_session 10000 idle_time 60 connect_time 480; Alter user user_name profile profile_name; /* set the password unlock time */alter profile profile_name limit password_lock_time 1/24;/* password_life_time indicates the password file expiration time, password_grace_time indicates the number of days after the password expires after the first successful logon. The password can be changed */alter profile profile_name limit password_lift_time 2 password_grace_time 3; /* password_reuse_time indicates the maximum number of days for Password reuse and password_reuse_max indicates the maximum number of times the password can be reused */alter profile profile_name limit password_reuse_time 10 [password_reuse_max 3]; alter user user_name identified by input_password; ----- modify the User Password drop profile profile_name;/* after the profile is created, it is specified to a user, you must use cascade to delete */drop profile profile_name cascade; Alter system set resource_limit = true; --- enable voluntary restriction, the default value is false/* configure resource parameters */alter profile profile_name limit cpu_per_session 10000 connect_time 60 idle_time 5;/* resource parameters (session level) cpu_per_session the CPU usage time unit of each session is 1/100 seconds. sessions_per_user allows parallel sessions of each user. After connect_time allows the connection time unit of minute, after how much time the idle_time connection is idle, the number of read blocks of logical_reads_per_session in minutes is automatically disconnected. private_sga indicates the number of private spaces that the user can use in SGA. The unit is bytes (call level) cpu_per_call (1/100 seconds) CPU call time: logical_reads_per_call the number of items that can be read by each call */alter profile profile_name limit cpu_per_call 1000 logical_reads_per_call 10; DESC dbms_resouce_manager; --- Resource Manager package/* Tables or views for obtaining resource information */select * From dba_users/dba_profiles; ###### managing users ########### show parameter OS; create user testuser1 identified by kxf_001; grant connect, createtable to testuser1; alter user testuser1 quota 10 m on tablespace_name;/* create user */create user user_name identified by password default tablespace tablespace_name temporary tablespace tablespace_name quota 15 m on tablespace_name password expire; /* set the default temporary tablespace at the database level */alter database default temporary tablespace tablespace_name;/* set the default tablespace at the database level */alter database default tablespace tablespace_name; /* If you create an OS-level Audit user, You Need To Know OS _authent_prefix, which indicates the prefix corresponding to the Oracle and OS passwords. 'Ops $ 'indicates the value of this parameter, this value can be set arbitrarily */create user user_name identified by externally default ops $ tablespace_name temporary tablespace tablespace_name quota 15 m on tablespace_name password expire;/* modify the user's quota for using tablespaces, the limit */alter user user_name quota 5 m on tablespace_name cannot be granted to roll back tablespaces and temporary tablespaces;/* delete users or cascade users (cascade is used for objects under a user object, delete some objects under it) */drop user user_name [cascade];/* What are the limits of each user's tablespace */DESC dba_ts_quotas; select * From dba_ts_quotas where username = '... ';/* change the user's default tablespace */alter user user_name default tablespace tablespace_name; ######### managing privileges ############# grant create table, create session to user_name; grant create any table to user_name; revoke create any table from user_name;/* grant permission syntax, public identifies all users, with admin option allows permissions granted to third parties */grant system_privs, [...] to [user/role/public], [...] [With admin option]; select * from V $ pwfile_users;/* When the o7_dictionary_accessiblity parameter is true, select any table can be identified, including system tables. Otherwise, does not contain system tables. The default value is false */show parameter O7;/* Because o7_dictionary_accessiblity is a static parameter and cannot be changed dynamically, use scope = spfile, it takes effect at next Startup */alter system set o7_dictionary_accessiblity = true scope = spfile;/* grant permissions on certain fields in the object, for example, select the permissions of certain fields in a table */grant [object_privs (column,...)], [...] on object_name to user/role/public ,... with grant option;/* Oracle does not allow you to grant select permissions to a column, but you can grant insert and update permissions to a column */grant insert (column_name1, column_name2 ,...) on table_name to user_name with grant option; select * From dba_sys_privs/session_privs/dba_tab_privs/user_tab_privs/dba_col_privs/user_col_privs; /* dB/OS/NONE audits are recorded in the database/operating system/non-audit. The default value is none */show parameter audit_trail; /* Start the select action on the table */audit select on user. table_name by session;/* by session each session sends a command only once, by access, each command records */audit [create table] [select/update/insert on object by session/access] [whenever successful/not successful]; DESC dbms_fga; --- for further design, you can use the dbms_fgs package/* cancel audit */noaudit select on user. table_name;/* query audited information */select * From all_def_audit_opts/dba_stmt_audit_opts/dba_priv_audit_opts/dba_obj_audit_opts; /* obtain audit records */select * From dba_audit_trail/dba_audit_exists/dba_audit_object/dba_audit_session/dba_audit_statement; ########### managing role ################# create role_name; grant select on table_name to role_name; grant role_name to user_name; set role role_name; Create role_name; create role role_name identified by password; Create role_name identified externally; set role role_name; ---- activate roleset role role_name identified by password; Alter role_name not identified; Alter role_name identified by password; Alter role_name identified externally; grant priv_name to role_name [with admin option]; grant Update (column_name1, col_name2 ,...) on table_name to role_name; grant role_name1 to role_name2;/* Create a default role. When a user logs on, the default role */alter user user_name default role_name1, role_name2 ,...; alter user user_name default role all; Alter user user_name default role all role t role_name1 ,...; alter user user_name default role none; set role role1 [identified by password], role2 ,....; set role all; set role t role1, role2 ,...; set role none; Revoke role_name from user_name; Revoke role_name from public; drop role role_name; select * From dba_roles/Tables/role_sys_privs/role_tab_privs/session_roles;