SQL & PL/SQL :: Which Privileges Required To Create A Trigger Like After Logon On Database
Mar 17, 2011
Which Privileges required to create a trigger like after logon on database. I have grant Create any trigger privilege to user but still not able to create and give me an error insufficient Privilege.
I'm looking to create an after logon trigger... my question is - can this trigger be created by any user (i.e. other than sys) and - if so - what's required to ensure it fires for EVERY other user that log's on to the database?
i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"
how can user "A" create trigger(etc.) on tables of user "B".
For procedures, we need to grant EXECUTE privilege.
For example:
grant execute on scott.process_salary to john;Lets say I have a function SCOTT.GET_EMPIDS and I want another user john to be able to invoke this fuction. So, what is the privilege that needs to be granted to John.
I have one question regarding privileges required for export.I read in a document that
Quote: If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you created a synonym for it.
what are the system privileges required in EXP_FULL_DATABASE role to perform export objects contained in another users schema?
why my logon trigger always creates 2 records with different timestamp. Also is this the proper way of excepting records to insert?
Script.
CREATE OR REPLACE TRIGGER TR_LOGON_AUDIT AFTER LOGON ON DATABASE BEGIN If user<> 'DBSNMP' then -- don't want to insert this if user <> 'SYS' then -- don't want to insert this
[code]....
Result when select.
OS_USERNAME LOGON_TIMESTAMP ----------- ---------------- GAN 2010-08-05 14:27:52 GAN 2010-08-05 14:27:55
I am working in the company which is using PL/SQL to create web interface. Of course we are using mod_plsql. I was using OWA_UTIL get_cgi_env ('REMOTE_ADDR') in the packages and it was always properly returning the IP address of the client. But Now I want to use it in after logon on schema trigger and it is never initialized.
Maybe some other way to obtain the IP of the client machine just before calling the package which is showing the interface?
Here is the simplified code of the trigger:
CREATE OR REPLACE TRIGGER dev_debug_trg AFTER LOGON ON SCHEMA DECLARE l_ip VARCHAR2(200); BEGIN
I want to create a database trigger which will test the database link, if it is ok then it will use dblink and do its work.
If it fails then it will send the data into its own server logfile.
I Wrote:
CREATE OR REPLACE TRIGGER PERMIT.TESTTRG AFTER INSERT OR UPDATE ON PERMIT.TR_LP_M_H_COMPANY_25072012 REFERENCING NEW AS New OLD AS Old FOR EACH ROW Declare l number; nIgn PLS_INTEGER; nRows PLS_INTEGER := 0;
[code]....
When I execute it it is giving error:
LINE/COL ERROR -------- ----------------------------------------------------------------- 4/11 PLS-00201: identifier 'EXEC_SQL.CONNTYPE' must be declared 4/11 PL/SQL: Item ignored 8/2 PLS-00320: the declaration of the type of this expression is incomplete or malformed
I have a table that has three columns. UserId - DateStamp - Action
An action contains user activities such as "logon". A user may logon multiple times in a day.I'm trying to create a query that displays the number of unique logons for each user per day.
By doing the following:
select userid, to_char(creationtimestamp,'YYYY-MM-DD'), count(*) from useraction where action = 'logon' group by to_char(creationtimestamp,'YYYY-MM-DD'), userid order by to_char(creationtimestamp,'YYYY-MM-DD') desc;
I can get the following results userid - date - count 1 - 2/21/2010 - 8 1 - 2/22/2010 - 3 2 - 2/22/2010 - 2 1 - 2/23/2010 - 1 4 - 2/23/2010 - 6 6 - 2/23/2010 - 1
What I'm trying to get is 2/21/2010 - 1 2/22/2010 - 2 2/22/2010 - 3
we have a production database 'X'. Now i have created a test database 'T' and did'nt configured another listener to it! The issue is when i cam connecting to oracle through sqlplus i am directly connecting to Test database 'T' but not the production database 'X'----ofcourse i can login to production DB afterwards. but initially i want to access the production database 'X'.
I have created a user "user1" who has dba role.i wanted to create a another user "user2" with same privileges which user1 has. so i granted dba role to user2 but i dont want user2 to have "drop" privilege on any objects except his own objects.
from sysdba i grant to my user hospital creat any synonym but give me this error why ?
SQL> conn sys as sysdba Enter password: Connected. SQL> grant create any synonym to hospital 2 ;
Grant succeeded.
SQL> conn hospital Enter password: Connected. SQL> create public synonym Sur 2 for surgeries; create public synonym Sur * ERROR at line 1: ORA-01031: insufficient privileges
CREATE MATERIALIZED VIEW Matview1 NOLOGGING NOCACHE NOPARALLEL REFRESH COMPLETE ON DEMAND START WITH sysdate NEXT sysdate + 1 WITH ROWID ENABLE QUERY REWRITE AS select Query;
if i run select query it works fine .. also the user has create materialized view and query rewrite privs .. not sure why i am getting insufficient privileges error still ..
create view TodaySurgeries as select s.surgery "Surgery", p.full_name"Patient Name",e.full_name"Doctor Name", f.floor_id"FloorID",r.room_id"RoomID", to_char(s.surgery_date,'dd-mm-yyyy hh24:mi:ss')"Surgery Date" from floors f,rooms r,employees e ,patients p, surgeries s where f.floor_id=r.floor_id and p.patient_id= s.patient_id and r.room_id= s.room_id and s.doctor_id= e.employee_id
I had been granted to the user grant session and resource
but the error is
create view TodaySurgeries * ERROR at line 1: ORA-01031: insufficient privileges
CREATE OR REPLACE TRIGGER TRIGGER1 BEFORE INSERT ON table1 FOR EACH ROW
[code]......
Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist
I will be installing SOA Suite with OSB in Exalogic for testing. It is not for the production environment and very simple application will be used for testing. Database admin asked me what size database would be required but I did not find clear answer in the documentation anywhere.
We have 2 Unix SunOS server, named as SUNOS01 and SUNOS02, there is some space need to add in SUNOS02 and for that I have to bring Database down on SUNOS02. When I checked I found that the DB is pointing to SUNOS01, i.e. there is no DB on SUNOS02, So, Do I required to shutdown the DB on SUNOS01 or it is not required to do anything from Database side. I mean to say should I ask Unix team to bring down the unix server and add the space.Also, if DB shutdown is not required then what I have to do afetr they have added space and bring the server up from DB side.
I am connected as System. It was the only user I set-up a password when installed the database on personal computer.
SQL> alter user sys identified by mypass007 2 / User altered. SQL> connect sys/mypass007 ERROR: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
how to create the trigger that count the last 7 day about from sale table if the last 7 day amount is greater then 10000 then update the commission 2% other wise do nothing
I cant create a trigger for the following problem "write a trigger to change the "hiredate"(in emp tale) in the default format even if the user enters it in some other format, say year/mon/date..."