Home » RDBMS Server » Security » Database Field size (Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production, win 2003)
Database Field size [message #571614] Wed, 28 November 2012 02:48 Go to next message
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
Senior Member
Dear all,
We have Employee table, there is a field name Employee_no. field size is 6.
can we restrict some one not to increase or decrease the field size of Employee_no. even User has admin role.

is there any way to restrict admin user that he should not allow to enter the value of field more or less than 6 characters through Toad or SQL Plus 8.0.
------------------------------------------------------
for example

field: employee_no
feild_size: 6
field_type: Varchar2

When we enter Employee no A000001(7 digit) then database not allowed to update because its field size is 6 characters we want to restrict admin user in Toad or SQL Plus 8.0 because he is Toad and SQL Plus user.
-------------------------------------------------------


thanx & regards,

[Updated on: Wed, 28 November 2012 02:57]

Report message to a moderator

Re: Database Field size [message #571615 is a reply to message #571614] Wed, 28 November 2012 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a DDL trigger for this.

Regards
Michel
Re: Database Field size [message #571617 is a reply to message #571615] Wed, 28 November 2012 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance:
SQL> create or replace trigger trg before ddl on database
  2  begin
  3    if ora_sysevent='ALTER' and ora_dict_obj_owner='SCOTT' and ora_dict_obj_name='EMP' then
  4      raise_application_error(
  5        -20000, 
  6        ora_sysevent||' is not allowed on '||
  7          ora_dict_obj_owner||'.'||ora_dict_obj_name||' table  <====='
  8      );
  9    end if;
 10  end;
 11  /

Trigger created.

SQL> conn test/test
Connected.
TEST> alter table scott.emp modify empno number(4);
alter table scott.emp modify empno number(4)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: ALTER is not allowed on SCOTT.EMP table  <=====
ORA-06512: at line 3

Regards
Michel
Re: Database Field size [message #571623 is a reply to message #571614] Wed, 28 November 2012 04:15 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
A check constraint could validate the length of input like LENGTH(X)=6 (safer would be a regular expressionn REGEXP_LIKE, as you would have to check for filler space).
Michael posted a way to prevent modifying the structure of EMP-tabel (can be dropped too)
Re: Database Field size [message #571624 is a reply to message #571623] Wed, 28 November 2012 04:33 Go to previous messageGo to next message
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
Senior Member
Thanx a lot for response.
but actually we have a user who has full admin rights he can access the database through Toad or SQL Plus 8.0. so he will disable the trigger or check constraint. with admin rights, we want him to prevent to enter the value in employee_no field which is greater 6 characters.

is there any way.


thanx regards.
Re: Database Field size [message #571625 is a reply to message #571624] Wed, 28 November 2012 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so he will disable the trigger or check constraint.


No you can add the same thing for the trigger than for the table and so forbid the account to disable the trigger.

Quote:
we want him to prevent to enter the value in employee_no field which is greater 6 characters.


Add a check constraint as Flyby said, or better modify the column as VARCHAR2(6 CHAR) so no one can enter bigger data.

In the end, if you don't trust the user, fire him/her! Someone that disables constraints to bypass verifications is a danger for your entreprise and its clients.

Regards
Michel
Re: Database Field size [message #571627 is a reply to message #571624] Wed, 28 November 2012 05:09 Go to previous message
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
Senior Member
thanx to all for reply
Previous Topic: Database Vault
Next Topic: History of privileges changes done for a DB user.
Goto Forum:
  


Current Time: Thu Mar 28 10:38:33 CDT 2024