Home » RDBMS Server » Security » database security thru roles
database security thru roles [message #115721] Wed, 13 April 2005 21:23 Go to next message
Hina
Messages: 51
Registered: April 2004
Member
I am trying to implement a role base security for an application, where each user will be granted through different roles.

As far as I know all privileges on DML will be assigned to PKG and packages will be granted to roles.
Is this correct ? or please explain me with some real examples

no base table permissions
No single User ID or Role will have any permissions to any underlying data tables for
data modification. All data manipulation (DML) operations will be performed only
using Oracle packages
Re: database security thru roles [message #115802 is a reply to message #115721] Thu, 14 April 2005 09:25 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Sounds like an excellent plan to me.
Re: database security thru roles [message #115803 is a reply to message #115721] Thu, 14 April 2005 09:26 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

Well...you can implement this functionality this way I think.

SQL> CREATE USER abc IDENTIFIED BY abc;

User created.

SQL> GRANT DBA, resource, CREATE SESSION TO abc;

Grant succeeded.

SQL> conn abc/abc
Connected.
SQL> CREATE TABLE test
  2  (a NUMBER);

Table created.

SQL> CREATE TABLE test1
  2  (a NUMBER);

Table created.

SQL> CREATE OR REPLACE PACKAGE pkg_insert AS
  2   PROCEDURE ins_test;
  3   PROCEDURE ins_test1;
  4  END;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg_insert AS
  2   PROCEDURE ins_test IS
  3    BEGIN
  4    INSERT INTO test
  5    VALUES(1);
  6    END;
  7   PROCEDURE ins_test1 IS
  8    BEGIN
  9    INSERT INTO test1
 10    VALUES(2);
 11   END;
 12  END;
 13  /

Package body created.

SQL> CREATE ROLA manager;
CREATE ROLA manager
       *
ERROR at line 1:
ORA-00901: invalid CREATE command


SQL> CREATE ROLE manager;

Role created.

SQL> GRANT EXECUTE ON pkg_insert TO manager;

Grant succeeded.

SQL> CREATE USER xyz IDENTIFIED BY xyz;

User created.

SQL> GRANT CREATE SESSION TO xyz;

Grant succeeded.

SQL> GRANT manager TO xyz;

Grant succeeded.

SQL> SELECT * FROM test;

no rows selected

SQL> SELECT * FROM test1;

no rows selected

SQL> conn xyz/xyz
Connected.
SQL> EXEC abc.pkg_insert.ins_test

PL/SQL procedure successfully completed.

SQL> EXEC abc.pkg_insert.ins_test1

PL/SQL procedure successfully completed.

SQL> SELECT * FROM abc.test
  2  ;
SELECT * FROM abc.test
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> conn abc/abc
Connected.
SQL> SELECT * FROM test;

        A
---------
        1

SQL> SELECT * FROM test1;

        A
---------
        2

SQL> conn xyz/xyz
Connected.
SQL> INSERT INTO abc.test
  2  VALUES (2);
INSERT INTO abc.test
                *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn system
Enter password: ******
Connected.
SQL> DROP USER abc CASCADE;

User dropped.

SQL> conn xyz/xyz
Connected.
SQL> EXEC abc.pkg_insert.ins_test1
BEGIN abc.pkg_insert.ins_test1; END;

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


SQL> conn system
Enter password: ******
Connected.
SQL> DROP USER xyz CASCADE;

User dropped.
Re: database security thru roles [message #115886 is a reply to message #115803] Thu, 14 April 2005 16:23 Go to previous messageGo to next message
Hina
Messages: 51
Registered: April 2004
Member
Thanks a lot.

But, What about DML privileges ?

On what levelI have to granted INSERT/UPDATE/DELETE privileges, I mean on User XYZ, PKG_insert, or ROLE

Thanks
Re: database security thru roles [message #116069 is a reply to message #115886] Sun, 17 April 2005 07:57 Go to previous message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

Hi,
You explained before that you want every DML operation only from Packages and no direct access is aasigned to any user. So you can't assign privileges to user xyz.
as the user abc will create the packages, abc will have any privilege to make DML operations and abc will assign privileges for only executing the packages thorough a role to any number of users.
You'll have to perform any DML operation in the packages.
Previous Topic: How to add a user for oracle ?
Next Topic: Adding a user
Goto Forum:
  


Current Time: Mon Aug 03 16:15:34 CDT 2020