For a demonstration of Flashback Data Archive, I had to create a daily update to a column in a test table.
The following function did the trick:
select dbms_random.string( 'L',dbms_random.value(10,30) ) from dual;
by returning strings similar to jsytbbtmxrusgmlz or gilqpizjaislueuocqlxydpngtup
"L" is an optional paramter to the STRING function, stating what type of string that should be produced. "L" means "always lowercase".
The numbers 10 and 30 in the value () function returns a string between 10 and 30 characters in length.
Other types of strings you can generate are:
• 'u', 'U' - uppercase alpha characters
• 'l', 'L' - lowercase alpha characters
• 'a', 'A' - mixed case alpha characters
• 'x', 'X' - uppercase alpha-numeric characters
• 'p', 'P' - any printable characters
Oracle-Base contains an excellent article about dbms_random.
Friday, November 15, 2013
How to use the PRODUCT_USER_PROFILE (PUP) table to prevent SQL*Plus access to database
This article is relevant to versions < 18.1.
In Oracle 18c, the PRODUCT_USER_PROFILE table is deprecated, and will be completely removed in Oracle 19c.
For more information, consult the documentation
In cases where you have an oracle client installed on a user's PC, skilled users can easily us sqlplus as a "backdoor" to the database by connecting directly through sqlplus instead of using their designated applications. Here is a trick to prevent that from happening, without too much effort for the DBA nor for the PC maintenance guys.
What is it?
-----------
Users can be restricted based on the SQL*Plus product usage.
This is achieved by inserting the restrictions in the PRODUCT_USER_PROFILE (PUP) table, owned by the SYSTEM user.
SQL*Plus reads the restrictions from the PUP table when a user logs in and applies those restrictions for the session.
How does it work?
-----------------
SQL*Plus will not read the PUP table if a user logs in with the SYSDBA or
SYSOPER privilege and therefore no restrictions will apply.
How to set up?
--------------
The table SQLPLUS_PRODUCT_PROFILE is automatically created on installation in the SYSTEM schema.
To explicitly create it, run pupbld.sql script
Then insert the following lines to prevent user SCOTT from doing DML and SELECT:
insert into product_user_profile(product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','SELECT','DISABLED');
insert into product_user_profile (product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','UPDATE','DISABLED');
insert into product_user_profile (product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','INSERT','DISABLED');
insert into product_user_profile (product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','DELETE','DISABLED');
Check with:
SELECT product,userid,attribute,char_value FROM system.product_user_profile; PRODUCT USERID ATTRIBUTE NUMERIC_VALUE ---------- --------- -------------------- ------------- SQL*Plus SCOTT DELETE DISABLED SQL*Plus SCOTT INSERT DISABLED SQL*Plus SCOTT SELECT DISABLEDNB!
To prevent access from using PL/SQL:
For example, the DELETE privilege disabled for user SCOTT can easily be executed through a PL/SQL block!
This can be avoided by removing the PL/SQL block creation access itself.
insert into system.product_profile (product, userid, attribute, char_value)
values ('SQL*Plus', 'SCOTT', 'DECLARE', 'DISABLED');
insert into system.product_profile (product, userid, attribute, char_value)
values ('SQL*Plus', 'SCOTT', 'BEGIN', 'DISABLED');
Result:SQL> connect scott/tiger
Connected.
SQL> select * from user_tables;
SP2-0544: Command "select" disabled in Product User Profile
Restrictions:
- Can only be used for SQL*Plus
- Only applies to local databases. The PUP table restrictions will not be implemented using a database link to a remote database.
More info here
How to temporarily alter a PROFILE
Temporarily disable the password verify function and set it back to the original value again when you're done:
SQL> ALTER PROFILE DEFAULT LIMIT password_verify_function NULL; Profile altered. SQL> ALTER PROFILE DEFAULT LIMIT password_verify_function VERIFY_PASSWORD; Profile altered.
How to enable automatically implemented SQL profiles recommended by the SQL Tuning Advisor
During the maintanace window of an Oracle 11g database, several out-of-the box jobs are scheduled to run. One of them is the Automatic SQL Tuning Advisor, which will come up with recommendations on how to improve the performance of your most performance intensive SQL statements.
One of the findings of the Automatic SQL Tuner may be that a profile can improve the performance of the SQL statement.
You can set up oracle to automatically implement the SQL Profile for you. Here is how:
First, make sure the required parameters are set:
Note: This feature is not necessarily beneficial for your particular database. Try it out carefully and observe the overall results of the peformance.
To disable automatic SQL tuning, use the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
One of the findings of the Automatic SQL Tuner may be that a profile can improve the performance of the SQL statement.
You can set up oracle to automatically implement the SQL Profile for you. Here is how:
First, make sure the required parameters are set:
alter system set optimizer_use_sql_plan_baselines=true scope=both; (default) alter system set optimizer_capture_sql_plan_baselines=true scope=both;Check the status of the Automatic Tuning job:
SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client WHERE client_name = 'sql tuning advisor';If not enabled already, enable the automatic tuning job:
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
Then customize so that profiles are automatically implemented:BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/
Done!Note: This feature is not necessarily beneficial for your particular database. Try it out carefully and observe the overall results of the peformance.
To disable automatic SQL tuning, use the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
Thursday, November 14, 2013
Guaranteed restore points in standby databases
"Before an important release, I decided to create a guaranteed restore point on the primary database.
Unfortunately I forgot to create the same on my standby database. Can I use the as of timestamp to create a guaranteed restore point in the past?"
No, this is not supported.
The syntax below:
ERROR at line 1:
ORA-38867: database not open: cannot create restore point with the specified
timestamp
How about using the SCN?
Unfortunately I forgot to create the same on my standby database. Can I use the as of timestamp to create a guaranteed restore point in the past?"
No, this is not supported.
The syntax below:
SQL> create restore point PRE_RELEASE2_STBY as of timestamp to_date('09.11.2013 09:15:00','DD.MM.YYYY HH24:MI:SS') guarantee flashback database;
Will fail with:ERROR at line 1: ORA-38864: cannot create a guaranteed restore point with user specified SCN or time.
// *Cause: An SCN or timestamp was specified when creating a guaranteed // restore point. This is not supported. You can only create a // guaranteed restore point as of the current time. // *Action: Avoid this combination of options for creating a restore point.You will have to do with a normal restore point instead:
CREATE RESTORE POINT PRE_RELEASE2_STBY
AS OF TIMESTAMP TO_DATE('09.11.2013 09:15:00','DD.MM.YYYY HH24:MI:SS');
But you will receive the following error:ERROR at line 1:
ORA-38867: database not open: cannot create restore point with the specified
timestamp
// *Cause: An attempt was made to create a restore point with the specified // timestamp while the database was not open. The database must be // open in order to map the timestamp to an SCN. // *Action: Open the database first or pick another way to create the restore // point.So on a standby database, using the AS OF TIMESTAMP is not allowed on a normal restore point, either.
How about using the SCN?
SQL> SELECT timestamp_to_scn(to_date('09.11.2013 09:15:00','DD.MM.YYYY HH24:MI:SS')) SCN
FROM dual;
SCN
--------
170259165
SQL> create restore point PRE_RELEASE2_STBY AS OF SCN 170259165; Restore point created.
How does the profile password security setting work?
Oracles profile settings for password security can be somewhat confusing at first, particularly when you consider the ramification of the default values.
Here are some of my notes on the matter:
The PASSWORD_LOCK_TIME sets the number of days an account will be locked after the specified number of consecutive failed login attempts. After the time passes, then the account becomes unlocked. If you specify PASSWORD_LOCK_TIME as UNLIMITED, then the account must be explicitly unlocked using an ALTER USER statement.
The FAILED_LOGIN_ATTEMPTS specifies the number of failed attempts to log in to the user account before the account is locked. The default is three failed attempts.
The PASSWORD_LIFE_TIME specifies the number of days the same password can be used for authentication.
The default setting of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.
The PASSWORD_GRACE_TIME specifes the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires and no further logons to the account is allowed. If you also set a value for PASSWORD_GRACE_TIME, then the password expires if it is not changed within the grace period, and further connections are rejected.
The PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. UNLIMITED means never, you can always reuse the password.
The PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. UNLIMITED means never, you are ignoring the setting altogether.
These two parameters PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX must be set in conjunction with each other. For these parameter to have any effect, you must specify an integer for both of them.
* If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.
For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.
* If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.
* If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile.
If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.
* If you set both of these parameters to UNLIMITED, then the database ignores both of them.
The PASSWORD_VERIFY_FUNCTION clause lets a PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement.
Oracle Database provides a default script, but you can create your own routine or use third-party software instead.
Example:
The first time scott tries to log in to the database after 90 days (this can be any day after the 90th day, that is, the 70th day, 100th day, or another day), he receives a warning message that his password will expire in three days.
If three days pass, and he does not change his password, then the password expires.
After this, he receives a prompt to change his password on any attempt to log in, and cannot log in until he does so.
To reveal if the account was locked as a consequence of a violation of the FAILED_LOGIN_ATTEMPTS, look at the STATUS column of the DBA_USERS view: If the value shows "LOCKED(TIMED)" then you know you're dealing with a user who has tried to log on too many times.
Here are some of my notes on the matter:
The PASSWORD_LOCK_TIME sets the number of days an account will be locked after the specified number of consecutive failed login attempts. After the time passes, then the account becomes unlocked. If you specify PASSWORD_LOCK_TIME as UNLIMITED, then the account must be explicitly unlocked using an ALTER USER statement.
The FAILED_LOGIN_ATTEMPTS specifies the number of failed attempts to log in to the user account before the account is locked. The default is three failed attempts.
The PASSWORD_LIFE_TIME specifies the number of days the same password can be used for authentication.
The default setting of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.
The PASSWORD_GRACE_TIME specifes the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires and no further logons to the account is allowed. If you also set a value for PASSWORD_GRACE_TIME, then the password expires if it is not changed within the grace period, and further connections are rejected.
The PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. UNLIMITED means never, you can always reuse the password.
The PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. UNLIMITED means never, you are ignoring the setting altogether.
These two parameters PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX must be set in conjunction with each other. For these parameter to have any effect, you must specify an integer for both of them.
* If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.
For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.
* If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.
* If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile.
If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.
* If you set both of these parameters to UNLIMITED, then the database ignores both of them.
The PASSWORD_VERIFY_FUNCTION clause lets a PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement.
Oracle Database provides a default script, but you can create your own routine or use third-party software instead.
* For function, specify the name of the password complexity verification routine.
* Specify NULL to indicate that no password verification is performed.
Example:
The first time scott tries to log in to the database after 90 days (this can be any day after the 90th day, that is, the 70th day, 100th day, or another day), he receives a warning message that his password will expire in three days.
If three days pass, and he does not change his password, then the password expires.
After this, he receives a prompt to change his password on any attempt to log in, and cannot log in until he does so.
CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 <-- number of login attempts is 4 PASSWORD_LOCK_TIME 30 <-- account will be locked for 30 days on the 5th attempt to logon PASSWORD_LIFE_TIME 90 <-- the password is valid for 90 days PASSWORD_GRACE_TIME 3; <-- after 90 days, the user has 3 days to change it. ALTER USER scott PROFILE prof;Another example, where I alter the default profile to require the following minimum of any new user:
ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME 90 <-- Rewnewal of password every 90 days PASSWORD_GRACE_TIME 10 <-- I give the user 10 days grace period PASSWORD_REUSE_MAX 12 <-- Ensure at least 12 different passwords before reusing a password PASSWORD_REUSE_TIME 90 <-- The number of days that must pass before a password can be reused FAILED_LOGIN_ATTEMPTS 5 <-- Allow 5 attempts to logon PASSWORD_LOCK_TIME .0208 <-- After 5 unsuccessful logons, lock account for 30 min, then allow new attempts PASSWORD_VERIFY_FUNCTION VERIFY_PASSWORD; <-- enforce password complexity as desired
To reveal if the account was locked as a consequence of a violation of the FAILED_LOGIN_ATTEMPTS, look at the STATUS column of the DBA_USERS view: If the value shows "LOCKED(TIMED)" then you know you're dealing with a user who has tried to log on too many times.
How to map the operating system sessions with RMAN Channels
When only one RMAN session is active, the easiest method for determining the server session ID for an RMAN channel is to execute the following query on the target database while the RMAN job is executing:
COLUMN CLIENT_INFO FORMAT a30 COLUMN SID FORMAT 999 COLUMN SPID FORMAT 9999 SELECT s.SID, p.SPID, s.CLIENT_INFO FROM V$PROCESS p, V$SESSION s WHERE p.ADDR = s.PADDR AND CLIENT_INFO LIKE 'rman%' ;If you do not run the SET COMMAND ID command in the RMAN job, then the CLIENT_INFO column displays in the following format:
rman channel=channel_id
For example, in my case:
SID SPID CLIENT_INFO ---- ------------ ------------------------------ 14 8374 rman channel=ORA_SBT_TAPE_1
As pointed out by the oracle documentation, you can monitor the sbt events:
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 's%bt%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR
;
In an example, the output from the above could look as below:
SPID EVENT SEC_WAIT STATE CLIENT_INFO ---- ----------------- ---------- -------------------- ------------------------------ 8642 Backup: sbtbackup 600 WAITING rman channel=ORA_SBT_TAPE_1indicating that RMAN has been waiting for the sbtbackup function to return for ten minutes.
Subscribe to:
Posts (Atom)