Home » RDBMS Server » Security » Hide SQL Statements
Hide SQL Statements [message #175649] Sun, 04 June 2006 01:59 Go to next message
AlinaC
Messages: 45
Registered: November 2005
Location: India
Member


I need to hide SQL Statements of procedures. I want to include those procedures in a package and also don’t want to give the package name to users also, is it possible to do so using synonym or any other way. Please suggest and give me an example.

Regards,

Alina

Re: Hide SQL Statements [message #175664 is a reply to message #175649] Sun, 04 June 2006 07:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You can wrap the pl/sql code.
By wrapping your .sql file is converted into .plb file. You can compile this plb file and create the procedure/package. The source code will not be exposed.
But, make sure you have the original .sql file secured somewhere.
Because once you have wrapped the sql file you cannot unwrapp it.
But still, you cannot hide the name of procedure from a knowledgeable user
http://www.databasejournal.com/features/oracle/article.php/3382331
Re: Hide SQL Statements [message #175849 is a reply to message #175649] Mon, 05 June 2006 13:31 Go to previous messageGo to next message
AlinaC
Messages: 45
Registered: November 2005
Location: India
Member

Thanks for your reply.
Is it possible to create a synonym for a package procedure?

For example, if I am having a package test_pkg with a procedure proc1, I would like to be able to call it as proc1, not test_pkg.proc1.

I've tried it using the normal "create synonym proc1 for
test_pkg.proc1", however that does not work.

Regards,

Alina
Re: Hide SQL Statements [message #175870 is a reply to message #175849] Mon, 05 June 2006 15:35 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is your database version? It works fine on 10.2.0.1.0:
SQL> create synonym pkg_synonym for pkg_view_employee;

Synonym created.

SQL>

[Updated on: Mon, 05 June 2006 15:36]

Report message to a moderator

Re: Hide SQL Statements [message #175899 is a reply to message #175649] Mon, 05 June 2006 23:22 Go to previous messageGo to next message
AlinaC
Messages: 45
Registered: November 2005
Location: India
Member

Thanks, I tried on DB Version 10.2.0.1.0 and I'm able to create synonym using
create synonym proc1 for test_pkg.proc1;

However when trying to execute the procedure using the synonym proc1

Exec proc1;
getting the following error

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier ‘PROC1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.

Regards,
Alina
Re: Hide SQL Statements [message #175908 is a reply to message #175899] Tue, 06 June 2006 01:12 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You should have created a synonym for the whole package, not only one procedure within:
SQL> CREATE OR REPLACE PACKAGE pkg_employee
  2  IS
  3    FUNCTION fun_emp_sal (p_empno IN EMP.empno%TYPE)
  4    RETURN EMP.sal%TYPE;
  5  END;
  6  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg_employee
  2  IS
  3    FUNCTION fun_emp_sal (p_empno IN EMP.empno%TYPE)
  4    RETURN EMP.sal%TYPE
  5    IS
  6       retval EMP.sal%TYPE;
  7    BEGIN
  8       SELECT sal INTO retval FROM EMP
  9      WHERE empno = p_empno;
 10      RETURN (retval);
 11    END;
 12
 13  END;
 14  /

Package body created.

SQL> SELECT pkg_employee.fun_emp_sal(7369) FROM dual;

PKG_EMPLOYEE.FUN_EMP_SAL(7369)
------------------------------
                           800

SQL> CREATE SYNONYM syn_pkg_employee FOR pkg_employee;

Synonym created.

SQL> SELECT syn_pkg_employee.fun_emp_sal(7369) FROM dual;

SYN_PKG_EMPLOYEE.FUN_EMP_SAL(7369)
----------------------------------
                               800

SQL>
Re: Hide SQL Statements [message #176035 is a reply to message #175649] Tue, 06 June 2006 08:23 Go to previous messageGo to next message
AlinaC
Messages: 45
Registered: November 2005
Location: India
Member

Thanks a lot for all your help. Lastly I want to know is it possible to execute a procedure from a package without specifying package_name.procedure_name. I wanted to create a synonym such a way that, using a single procedure or function name it can be executed. Is it possible?

For example, instead of saying

SELECT syn_pkg_employee.fun_emp_sal(7369) FROM dual;

Is it possible to say like the following?

SELECT syn_pkg_emp_sal(7369) FROM dual;

Thanks,
Alina

Re: Hide SQL Statements [message #176070 is a reply to message #176035] Tue, 06 June 2006 15:08 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No, it isn't possible (as far as I know). Moreover, if procedures are private (declared in package body and not in package specification), they can only be executed within the package (called from another package procedures).
Re: Hide SQL Statements [message #176329 is a reply to message #175649] Wed, 07 June 2006 21:44 Go to previous message
AlinaC
Messages: 45
Registered: November 2005
Location: India
Member

Thank you so very musch for all your help. Using wrap I'm able to hide source code of procedures, functions and packages. Is it possible to hide source code of triggers also, if so how? Is it possible to hide other SQL statements like views, tables etc?



Regards,

Alina
Previous Topic: Tracking All users SQL stmts as SYS and SYSTEM user
Next Topic: object privileges
Goto Forum:
  


Current Time: Fri Mar 29 01:03:04 CDT 2024