Hello everyone I'm trying to solve a little problem that I have everytime I connect as another user on the database.
First of all let me explain what I want to do.
When the user connect on the database I want to know his role and just show on output a message to saying this is your role.
For doing this I created a
SYSTEM trigger for simply do a
select ROLE from SESSION_ROLES and check the user what role he has.
Here is my system trigger
CREATE OR REPLACE TRIGGER SONO_COLLEGATO
AFTER LOGON ON DATABASE
DECLARE
USER_ROLE VARCHAR(30);
BEGIN
SELECT SR.ROLE INTO USER_ROLE FROM SESSION_ROLES SR;
IF USER_ROLE = 'R_CLIENTE' THEN
DBMS_OUTPUT.PUT_LINE('I am a CLIENT');
-- SET LINESIZE 120
ELSIF USER_ROLE = 'R_DIPENDENTE' THEN
DBMS_OUTPUT.PUT_LINE('I am a DIPENDENT');
-- SET LINESIZE 250
ELSIF USER_ROLE = 'R_FORNITORE' THEN
DBMS_OUTPUT.PUT_LINE('I am a supplier');
-- SET LINESIZE 200
ELSE
DBMS_OUTPUT.PUT_LINE('UNKNOWN USER');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'ERROR: ' || RUOLO_UTENTE);
END;
/
When I connect as SYSTEM or an User administrator I don't get this error, but when I connect as a user who has the role of
R_CLIENTE, for example, I get always the error
NO_DATA_FOUND.
Why this happen?
When I created the user I also gived the role but still I get this error always when I'm trying to connect on the database.
Can someone help me?
Roles are never enabled during the execution of a procedure except in the special case of Invokers Rights which is a new feature in Oracle8i, release 8.1.
This fact is documented application developers guide:
Privileges Required to Create Procedures and Functions
To create a stand-alone procedure or function, or package specification or
body, you must meet the following prerequisites:
• You must have the CREATE PROCEDURE system privilege to create a
procedure or package in your schema, or the CREATE ANY
PROCEDURE system privilege to create a procedure or package in
another user’s schema.
Attention: To create without errors, that is, to compile the procedure
or package successfully, requires the following additional privileges:
The owner of the procedure or package must have been explicitly
granted the necessary object privileges for all objects referenced within
the body of the code; the owner cannot have obtained required
privileges through roles.
If the privileges of a procedure’s or package’s owner change, the procedure
must be reauthenticated before it is executed. If a necessary privilege to a
referenced object is revoked from the owner of the procedure (or package), the
procedure cannot be executed.
</quote>
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure (unless you are using Invokers rights in Oracle8i. See the PLSQL documentation for more information on this feature and make sure you understand the ramifications). To be able to perform that operation in a typical procedure, you need to have that privelege granted directly to you.