DBA question

Posts 11 · Viewed 5890 · Go to Last Post

the_hareeb

· Snapshot

Like · Likedby · 0

I am logged in as SYSTEM.. and do the following query:

grant select rev_address to Accountant;

i get the following error:

Error starting at line 1 in command:

grant select rev_address to Accountant

Error report:

SQL Error: ORA-00990: missing or invalid privilege

00990. 00000 - "missing or invalid privilege"

*Cause:

*Action:

Because I am logged in as SYSTEM, shouldn't I have grant privledge? If I dont, how do i grant SYSTEM a grant privledge. Thanks.

pyaradeshbasiharu

· Snapshot

Like · Liked by · 0

it is

grant select on rev_address to Accountant;

the_hareeb

· Snapshot

Like · Liked by · 0

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

· Snapshot

Like · Liked by · 0

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

· Snapshot

Like · Liked by · 0

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.

pyaradeshbasiharu

· Snapshot

Like · Liked by · 0

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;

grant all on author to all_test with admin option;

the_hareeb

· Snapshot

Like · Liked by · 0

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

बैरे

· Snapshot

Like · Liked by · 0

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.

2. Create public or private synonym author for schema_name.author;

and try your statement select * from author;

Hope this works

Good Luck

the_hareeb

· Snapshot

Like · Liked by · 0

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

pyaradeshbasiharu

· Snapshot