it is  grant select on  rev_address to Accountant;

pyaradeshbasiharu · Oct 11, 2009 8:36 PM

ya thanks.. it worked.. another problem is that: GRANT ALL on author to Accountant; GRANT Accountant to AMARTIN; everything suceeds with no error but when i log in as amartin, i dont see any tables under him. select * from author; returns nothing..  thanks for help

the_hareeb · Oct 11, 2009 9:24 PM

SELECT* FROM ROLE_SYS_PRIVS WHERE role = 'Accountant'; from SYSTEM also gives: Unknown Command Error starting at line 3 in command: WHERE role = 'Accountant'; Error report: Unknown Command

the_hareeb · Oct 11, 2009 9:29 PM

GRANT all  on author to Accountant; GRANT Accountant to AMARTIN; SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'AMARTIN'; GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE  ------------------------------ ------------------------------ ------------ ------------  AMARTIN                        CEO                            NO           YES           AMARTIN                        ACCOUNTANT                     NO           YES           AMARTIN                        CONNECT                        NO           YES           but when i do  SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'Accountant'; it returns nothing .. could this be the reason? it is not showing what tables Accountant role has privledges to.

the_hareeb · Oct 11, 2009 9:41 PM

use , Create role all_test;-Create a Role called all_test GRANT ALL on author to all_test; -Grant all Privileges to this Role i.e all_test GRANT all_test to AMARTIN;-Assign all_test role to amartin if this doesn't work try  grant all on author to all_test with grant option; or grant all on author to all_test with admin option;

pyaradeshbasiharu · Oct 11, 2009 9:51 PM

GRANT all  on author to Accountant with grant option; Error starting at line 1 in command: GRANT all  on author to Accountant with grant option Error report: SQL Error: ORA-01926: cannot GRANT to a role WITH GRANT OPTION 01926. 00000 -  "cannot GRANT to a role WITH GRANT OPTION" *Cause:    Role cannot have a privilege with the grant option. *Action:   Perform the grant without the grant option. ---------------------- This is what i did.. GRANT all  on author to Accountant; GRANT Accountant to AMARTIN;  SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'Accountant'; returns: ROLE                           OWNER                          TABLE_NAME                     COLUMN_NAME                    PRIVILEGE                                GRANTABLE  ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---------  0 rows selected

the_hareeb · Oct 11, 2009 11:06 PM

Bro , where clauses varchar are case sensetive .   SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'ACCOUNTANT';   <<select *from author>> You are logged in as AMARTIN so it is looking under AMARTIN schema for an object names author which doe not exists . You could solve the following by 2 ways .   1. select * from schema_name.table_name ;   in your case I believe author is a table but you need to know the schema name.   or 2. Create public or private synonym author for schema_name.author;   and try your statement select * from author;   Hope this works   Good Luck

???? · Oct 12, 2009 9:46 AM

pyaradeshbasiharu and baire.. thanks for your help.... I really appriciate it

the_hareeb · Oct 12, 2009 3:55 PM

Anytime Bro..

pyaradeshbasiharu · Oct 12, 2009 3:59 PM

btw is there a good DBA resource site I should be familiar off. Good documents. I am giving an OCA exam soon, need some good tutorial, any forums that I can ask questions..

the_hareeb · Oct 12, 2009 4:28 PM