Forum Stats

  • 3,826,580 Users
  • 2,260,667 Discussions
  • 7,897,010 Comments

Discussions

Trigger to prevent edit:keywords being used as Column Names

4218587
4218587 Member Posts: 6
edited Apr 2, 2020 9:29AM in SQL & PL/SQL

I am trying to create a trigger with Regex to prevent tables being created with a column name (quoted or unquoted) that is a reserved word (e.g. ACCOUNT).

I have the trigger part worked out but I am struggling with the regex part. I have the following so far SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(PARTIAL|LIST|OF|RESERVED|WORDS)\s*"', 1, 1, 0, 'i') into l_ret2 from dual; Which works fine for quoted words.

If I try with unquoted words, for some reason it blocks when I try to add a primary key SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*?|\(.+?,\s+)(PRIMARY)\s', 1, 1, 0, 'i') into l_ret2 from dual; I added a negative lookahead but all that does is allow the name PRIMARY as a column name SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*?|\(.+?,\s+)\s*(PRIMARY(?!\s+KEY))', 1, 1, 0, 'i') into l_ret2 from dual;

So my question is what is wrong with my regex, or even better has someone already made a trigger to do what I want?

Tagged:
«1

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,472 Red Diamond
    edited Mar 26, 2020 7:48AM

    You can't. Syntax is checked before trigger fires.

    SY.

  • 4218587
    4218587 Member Posts: 6
    edited Mar 26, 2020 7:51AM

    Are you sure? I've gotten this to work in almost all cases, it's just struggling with PRIMARY KEY and CONSTRANT <name> CHECK.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,472 Red Diamond
    edited Mar 26, 2020 7:52AM

    Actually your post is confusing. You say "Trigger to prevent reserved words being used as Column Names" but then using column name account which is keyword but is not reserved.

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,472 Red Diamond
    edited Mar 26, 2020 7:58AM

    As I said, you are confusing keyword with reserved word. For example:

    SQL> create or replace

      2    trigger before_create

      3    before create

      4    on database

      5    begin

      6        dbms_output.put_line('Trigger fired');

      7  end;

      8  /

    Trigger created.

    SQL> set serveroutput on

    SQL> create table tbl(column number)

      2  /

    create table tbl(column number)

                     *

    ERROR at line 1:

    ORA-00904: : invalid identifier

    SQL> create table tbl(account number)

      2  /

    Trigger fired

    Table created.

    SQL>

    As you can see error is raised before trigger can fire if column name is reserved word and trigger doesn't fire. It will fire if column name is a keyword but isn't reserved. So please clarify what are you trying to achieve.

    SY.

  • 4218587
    4218587 Member Posts: 6
    edited Mar 26, 2020 8:20AM

    Sorry, a mistake on my part. I do indeed want to prevent Keywords being used as column names, not reserved words.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,472 Red Diamond
    edited Mar 26, 2020 8:10AM
    af497e6c-eb39-4a40-8947-65fc200a1bfc wrote:Sorry, a mistake on my part. I do indeed want to prevent Keywords being used as column names, not reserved words.

    Post trigger code.

    SY.

  • 4218587
    4218587 Member Posts: 6
    edited Mar 26, 2020 8:20AM

    create or replace trigger no_key_in_name

    before create on database

    DECLARE

      sql_text ora_name_list_t;

      n PLS_INTEGER;

      v_stmt CLOB;

      v_stmt2 CLOB;

      l_ret2 PLS_INTEGER;

    BEGIN

      n := ora_sql_txt(sql_text);

      FOR i IN 1..n LOOP

        v_stmt := v_stmt || sql_text(i);

    END LOOP;

    v_stmt2 := replace(v_stmt,chr(13),' ');

    v_stmt2 := replace(v_stmt2,chr(10),' ');

    l_ret2 := 0;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(ACCESS|ACCOUNT|ACTIVATE|ADD|ADMIN|ADVISE|AFTER|ALL|ALL_ROWS|ALLOCATE|ALTER|ANALYZE|AND|ANY|ARCHIVE|ARCHIVELOG|ARRAY|AS|ASC|AT|AUDIT|AUTHENTICATED|AUTHORIZATION|AUTOEXTEND|AUTOMATIC|BACKUP|BECOME|BEFORE|BEGIN|BETWEEN|BFILE|BITMAP|BLOB|BLOCK|BODY|BY|CACHE|CACHE_INSTANCES|CANCEL|CASCADE|CAST|CFILE|CHAINED|CHANGE|CHAR|CHAR_CS)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(CHARACTER|CHECK|CHECKPOINT|CHOOSE|CHUNK|CLEAR|CLOB|CLONE|CLOSE|CLOSE_CACHED_OPEN_CURSORS|CLUSTER|COALESCE|COLUMN|COLUMNS|COMMENT|COMMIT|COMMITTED|COMPATIBILITY|COMPILE|COMPLETE|COMPOSITE_LIMIT|COMPRESS|COMPUTE|CONNECT|CONNECT_TIME|CONSTRAINT|CONSTRAINTS|CONTENTS|CONTINUE|CONTROLFILE|CONVERT|COST|CPU_PER_CALL|CPU_PER_SESSION|CREATE|CURRENT|CURRENT_SCHEMA|CURREN_USER|CURSOR|CYCLE)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(DANGLING|DATABASE|DATAFILE|DATAFILES|DATAOBJNO|DATE|DBA|DBHIGH|DBLOW|DBMAC|DEALLOCATE|DEBUG|DEC|DECIMAL|DECLARE|DEFAULT|DEFERRABLE|DEFERRED|DEGREE|DELETE|DEREF|DESC|DIRECTORY|DISABLE|DISCONNECT|DISMOUNT|DISTINCT|DISTRIBUTED|DML|DOUBLE|DROP|DUMP|EACH|ELSE|ENABLE|END|ENFORCE|ENTRY|ESCAPE|EXCEPT|EXCEPTIONS|EXCHANGE|EXCLUDING)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(EXCLUSIVE|EXECUTE|EXISTS|EXPIRE|EXPLAIN|EXTENT|EXTENTS|EXTERNALLY|FAILED_LOGIN_ATTEMPTS|FALSE|FAST|FILE|FIRST_ROWS|FLAGGER|FLOAT|FLOB|FLUSH|FOR|FORCE|FOREIGN|FREELIST|FREELISTS|FROM|FULL|FUNCTION|GLOBAL|GLOBALLY|GLOBAL_NAME|GRANT|GROUP|GROUPS|HASH|HASHKEYS|HAVING|HEADER|HEAP|ID|IDENTIFIED|IDGENERATORS|IDLE_TIME|IF|IMMEDIATE|IN|INCLUDING|INCREMENT|INDEX|INDEXED|INDEXES|INDICATOR)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(IND_PARTITION|INITIAL|INITIALLY|INITRANS|INSERT|INSTANCE|INSTANCES|INSTEAD|INT|INTEGER|INTERMEDIATE|INTERSECT|INTO|IS|ISOLATION|ISOLATION_LEVEL|KEEP|KEY|KILL|LABEL|LAYER|LESS|LEVEL|LIBRARY|LIKE|LIMIT|LINK|LIST|LOB|LOCAL|LOCK|LOCKED|LOG|LOGFILE|LOGGING|LOGICAL_READS_PER_CALL|LOGICAL_READS_PER_SESSION|LONG|MANAGE|MASTER|MAX)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(MAXARCHLOGS|MAXDATAFILES|MAXEXTENTS|MAXINSTANCES|MAXLOGFILES|MAXLOGHISTORY|MAXLOGMEMBERS|MAXSIZE|MAXTRANS|MAXVALUE|MIN|MEMBER|MINIMUM|MINEXTENTS|MINUS|MINVALUE|MLSLABEL|MLS_LABEL_FORMAT|MODE|MODIFY|MOUNT|MOVE|MTS_DISPATCHERS|MULTISET|NATIONAL|NCHAR|NCHAR_CS|NCLOB|NEEDED|NESTED|NETWORK|NEW|NEXT|NOARCHIVELOG|NOAUDIT|NOCACHE|NOCOMPRESS|NOCYCLE|NOFORCE|NOLOGGING|NOMAXVALUE)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(NOMINVALUE|NONE|NOORDER|NOOVERRIDE|NOPARALLEL|NOPARALLEL|NOREVERSE|NORMAL|NOSORT|NOT|NOTHING|NOWAIT|NULL|NUMBER|NUMERIC|NVARCHAR2|OBJECT|OBJNO|OBJNO_REUSE|OF|OFF|OFFLINE|OID|OIDINDEX|OLD|ON|ONLINE|ONLY|OPCODE|OPEN|OPTIMAL|OPTIMIZER_GOAL|OPTION|OR|ORDER|ORGANIZATION|OSLABEL|OVERFLOW|OWN|PACKAGE|PARALLEL|PARTITION|PASSWORD)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(PASSWORD_GRACE_TIME|PASSWORD_LIFE_TIME|PASSWORD_LOCK_TIME|PASSWORD_REUSE_MAX|PASSWORD_REUSE_TIME|PASSWORD_VERIFY_FUNCTION|PCTFREE|PCTINCREASE|PCTTHRESHOLD|PCTUSED|PCTVERSION|PERCENT|PERMANENT|PLAN|PLSQL_DEBUG|POST_TRANSACTION|PRECISION|PRESERVE|PRIMARY|PRIOR|PRIVATE|PRIVATE_SGA|PRIVILEGE|PRIVILEGES|PROCEDURE|PROFILE|PUBLIC|PURGE|QUEUE|QUOTA|RANGE|RAW|RBA|READ|READUP|REAL)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(REBUILD|RECOVER|RECOVERABLE|RECOVERY|REF|REFERENCES|REFERENCING|REFRESH|RENAME|REPLACE|RESET|RESETLOGS|RESIZE|RESOURCE|RESTRICTED|RETURN|RETURNING|REUSE|REVERSE|REVOKE|ROLE|ROLES|ROLLBACK|ROW|ROWID|ROWNUM|ROWS|RULE|SAMPLE|SAVEPOINT|SB4|SCAN_INSTANCES|SCHEMA|SCN|SCOPE|SD_ALL|SD_INHIBIT|SD_SHOW|SEGMENT|SEG_BLOCK|SEG_FILE)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(SELECT|SEQUENCE|SERIALIZABLE|SESSION|SESSION_CACHED_CURSORS|SESSIONS_PER_USER|SET|SHARE|SHARED|SHARED_POOL|SHRINK|SIZE|SKIP|SKIP_UNUSABLE_INDEXES|SMALLINT|SNAPSHOT|SOME|SORT|SPECIFICATION|SPLIT|SQL_TRACE|STANDBY|START|STATEMENT_ID|STATISTICS|STOP|STORAGE|STORE|STRUCTURE|SUCCESSFUL|SWITCH|SYS_OP_ENFORCE_NOT_NULL$|SYS_OP_NTCIMG$|SYNONYM|SYSDATE|SYSDBA|SYSOPER|SYSTEM|TABLE|TABLES)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(TABLESPACE|TABLESPACE_NO|TABNO|TEMPORARY|THAN|THE|THEN|THREAD|TIMESTAMP|TIME|TO|TOPLEVEL|TRACE|TRACING|TRANSACTION|TRANSITIONAL|TRIGGER|TRIGGERS|TRUE|TRUNCATE|TX|TYPE|UB2|UBA|UID|UNARCHIVED)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(UNDO|UNION|UNIQUE|UNLIMITED|UNLOCK|UNRECOVERABLE|UNTIL|UNUSABLE|UNUSED|UPDATABLE|UPDATE|USAGE|USE|USER|USING|VALIDATE|VALIDATION|VALUE|VALUES|VARCHAR|VARCHAR2|VARYING|VIEW|WHEN|WHENEVER|WHERE|WITH|WITHOUT|WORK|WRITE|WRITEDOWN|WRITEUP|XID|YEAR)\s*"', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

    IF l_ret2 < 1 THEN

    SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*?|\(.+?,\s*)(CONSTRAINT|ID|ACCOUNT|ACCESS|PRIMARY\s+([^K]|K[^E]|KE[^Y]))\s+', 1, 1, 0, 'i') into l_ret2 from dual;

    END IF;

          IF l_ret2 > 0 THEN

              RAISE_APPLICATION_ERROR(-20999, 'Stall the Ball. You are trying to create a new table '||ora_dict_obj_owner||'.'||ora_dict_obj_name

              ||chr(10)||'

              with a forbidden column name. '

              ||chr(10)||'           Contact your DBA please if you need help!');

          END IF;

    END IF;

    END;

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,472 Red Diamond
    edited Mar 26, 2020 9:14AM

    It is virtually impossible to parse CREATE TABLE statement for keywords in general. What if someone issues CTAS

    CREATE TABLE TBL AS SELECT expression list ...

    Now we have to parse expression list looking for column aliases (explicit or implicit). And even if it is plain

    CREATE TABLE(....)

    it can be something like:

    create table tbl(col1 number generated always as (account()),col2 date);

    where account is function name. You should use AFTER CREATE:

    create or replace

      trigger no_key_in_name

        after create

        on database

        declare

            cursor v_cur

              is

                select  column_name

                  from  dba_tab_cols c,

                        v$reserved_words w

                  where c.owner = ora_dict_obj_owner

                    and c.table_name = ora_dict_obj_name

                    and upper(c.column_name) = w.keyword

                    and w.reserved = 'N';

        begin

            -- We are interested in CREATE TABLE only

            if ora_dict_obj_type = 'TABLE'

              then

              for v_rec in v_cur loop

                dbms_output.put_line('Column name ' || v_rec.column_name || ' is a keyword.');

              end loop;

            end if;

    end;

    /

    Now:

    set serveroutput on

    create table tbl(

                    active    varchar2(1),

                    account_id number generated always as (account()),

                    dt        date

                    )

    /

    Column name ACTIVE is a keyword.

    Table created.

    SQL>

    SY.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 26, 2020 11:36AM

    Seems to me that it might be easier to create a daily job that simply scans DBA_TAB_COLS for any column name that is a key word.  If any hits are found, email the DBA team, and they can take corrective action -- like introducing the devs to Billy's infamous lead pipe.

  • 4218587
    4218587 Member Posts: 6
    edited Mar 30, 2020 5:50AM

    I am getting the following error. https://imgur.com/a/78JmksM