| need help with roles/synonyms [message #467472] |
Sat, 24 July 2010 12:38  |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
Hi,
I have a user A to whom i have granted few roles. A superuser B(not dba) has similar roles. When i login through user B, i can access certain screens in my application, whereas when i login through user A and access same screens,i get the following error:
FRM-40735: POST QUERY trigger raised unhandled exception ORA-00942
Does it has something to do with granting roles/synonyms?
Best Regards,
|
|
|
|
|
|
| Re: need help with roles/synonyms [message #467474 is a reply to message #467472] |
Sat, 24 July 2010 12:45  |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
I ran the same script to create the users with different parameters.
SET LINESIZE 132
SET PAGESIZE 999
SET HEAD OFF
SET UNDERLINE OFF
SET VERIFY OFF
SET FEEDBACK OFF
REM
spool cre_usr.lg1
ACCEPT DBAUSER CHAR PROMPT 'Enter DBA User Name ultgnp: '
ACCEPT DBAUSER_PSWD CHAR PROMPT 'Enter current user Password ultgnp: '
ACCEPT DBName CHAR PROMPT 'Enter DB Connect String Name: '
prompt
prompt
ACCEPT TBLOWNER CHAR PROMPT 'Enter ULTIMA TABLE OWNER User Name ultgnp:'
ACCEPT TBLOWNER_PSWD CHAR PROMPT 'Enter ULTIMA TABLE OWNER Password : '
prompt
prompt
ACCEPT ULTUSER CHAR PROMPT 'Enter NEW User Name: '
ACCEPT ULTPSWD CHAR PROMPT 'Enter NEW Password : '
ACCEPT DEFLT_TABLESPACE CHAR PROMPT 'Enter Default tablespace ultgnp : '
ACCEPT TEMP_TABLESPACE CHAR PROMPT 'Enter TEMP tablespace ULT_TEMP_DATA : '
ACCEPT GNPROLE CHAR PROMPT 'Enter USER ROLE: '
SET TERMOUT ON
prompt
prompt ****** Creating user &&ULTUSER *****
prompt
SET TERMOUT OFF
CONNECT &&DBAUSER/&&DBAUSER_PSWD@&&DBName
show user
spool cre_usr.ls0
select 'define DBAUSER='||user from dual;
spool off
start cre_usr.ls0
create user &&ULTUSER IDENTIFIED BY &&ULTPSWD
QUOTA 100K ON &&DEFLT_TABLESPACE ;
alter user &&ULTUSER default tablespace &&DEFLT_TABLESPACE;
alter user &&ULTUSER temporary tablespace &&TEMP_TABLESPACE;
--GRANT ROLE TO NEW USER
grant &&GNPROLE to &&ULTUSER;
SET TERMOUT OFF
CONNECT &&TBLOWNER/&&TBLOWNER_PSWD@&&DBName
show user
SET TERMOUT ON
SET ECHO OFF
SET FEED OFF
SET VER OFF
SET HEAD OFF
SET UND OFF
SET PAGESIZE 0
prompt
prompt ****** Building create synonyms script *****
prompt
SET TERMOUT OFF
spool cre_usr.ls2
select distinct
'CREATE SYNONYM '||TABLE_NAME||' FOR '||USER||'.'||TABLE_NAME||';'
FROM DBA_TAB_PRIVS
WHERE GRANTEE = '&&GNPROLE';
prompt spool off
SPOOL OFF
spool cre_usr.lg2
SET TERMOUT ON
prompt
prompt ****** Building grants at this time *****
prompt
SET TERMOUT OFF
show user
CONNECT &&ULTUSER/&&ULTPSWD@&&DBName
SET TERMOUT ON
prompt
prompt ****** Building Synonyms at this Time *****
prompt
SET TERMOUT OFF
show user
set echo on
START cre_usr.ls2
CONNECT &&DBAUSER/&&DBAUSER_PSWD@&&DBName
show user
SET ECHO OFF
|
|
|
|
|
|
|
|
| Re: need help with roles/synonyms [message #467510 is a reply to message #467480] |
Sun, 25 July 2010 01:43 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
CREATE USER G01281
IDENTIFIED BY VALUES password
DEFAULT TABLESPACE ULTGNP
TEMPORARY TABLESPACE ULT_TEMP_DATA
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT ULTGNP_SYNM_GRANTS TO G01281;
GRANT ULT_CASHIER TO G01281;
GRANT ULT_IT_SUPPORT_LVL1 TO G01281;
GRANT ULT_IT_SUPPORT_LVL2 TO G01281;
GRANT ULT_RECEPTION TO G01281;
ALTER USER G01281 DEFAULT ROLE ALL;
ALTER USER G01281
QUOTA 100K ON ULTGNP;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON ULTGNP.A_ULT_CSH_BANK_SETUP TO G01281;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON ULTGNP.SOURCE_CODE_MANAGEMENT TO G01281;
All the below roles has connect,resource included in them.
ULTGNP_SYNM_GRANTS;
ULT_CASHIER;
ULT_IT_SUPPORT_LVL1;
ULT_IT_SUPPORT_LVL2;
ULT_RECEPTION;
Is this one of the reason this user cannot open certain forms and get error?
|
|
|
|
| Re: need help with roles/synonyms [message #467520 is a reply to message #467510] |
Sun, 25 July 2010 03:50 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I don't see how the above refers to the error you mentioned.
From what I can tell, POST-QUERY is (usually) used to populate non-database items after the query was executed. This is done in a SELECT statement. For example:SELECT d.department_name
INTO :dpt.dept_name
FROM departments d
WHERE d.department_id = :dpt.dept_id;
If user doesn't have the "departments" table in his schema, he'll get the ORA-00942 error. So, if he's not an owner, he has to acquire privileges differently. Table owner can do that, such asGRANT SELECT ON departments TO you; CREATE SYNONYM departments FOR owner.departments;
So, where's that in your case, exactly? CONNECT and RESOURCE roles (you mentioned) don't do that. I don't know what all those UTL_ roles do.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
