September 23rd, 2003, 01:59 PM
newbie: can't select *
I've migrated a db over from Access; i can log in with isql and "show tables;" lists all tables including "foo"; when I look at the db from an admin tool I can see data in the tables, but when I try "select * from foo;" it tells me:
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1 char xx
Where can I start to troubleshoot this?
Thanks for any help.
September 24th, 2003, 01:04 AM
You must have SELECT privilege granted:
GRANT SELECT ON foo TO <user>,
or you have to login as SYSDBA user.
September 24th, 2003, 09:36 AM
.An answer and one further question:
This db was created by a script generated by InterBase DataPump; because the table names were specified in quotes during the creation, it turns out that they must be referred to with quotes:
SELECT * from "foo";
How would I do this on IB/Firebird:
GRANT SELECT ON * TO <user>; ? (grant privileges to all tables in a db)
Last edited by sporb; September 24th, 2003 at 12:10 PM.
September 25th, 2003, 01:27 AM
Re: .An answer and one further question:
1). you can create a stored procedure to grant all privileges (SIUDR) to an user on all tables/views in database:
CREATE PROCEDURE SP_GRANT_ALL (
DECLARE VARIABLE M_TABLE CHAR(31);
for select RDB$RELATION_NAME as myTable from rdb$relations WHERE SUBSTRING(RDB$RELATION_NAME from 1 for 4) <> 'RDB$' INTO :m_table
do begin execute statement 'grant all on ' || :m_table || ' to ' || :m_user; end
(to execute SP_GRANT_ALL(<cUser>) you must have EXECUTE ON PROCEDURE privilege)
2).or GRANT ALL on <table> to PUBLIC;
* ALL = select + insert + update + delete + reference