5

I use oracle 12c. I try to grant all privileges of all tables to a user. And in future I don't need to add any privileges to that user. I need to do this. I don't want to grant system tables to that user. I wrote pl/sql command. but it gives error.

BEGIN
  2  FOR t IN (select * from dba_tables where owner not like '%SYS%' AND owner not like '%ADMIN%' AND iot_type IS NULL)
  3  LOOP
  4  EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES ON '|| t.owner ||'.' || t.table_name ||' TO GGSTEST';
  5  END LOOP;
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 4

What is the mistake? thanks.

I use dbms_print

GRANT ALL ON OUTLN.OL$ TO GGSTEST
GRANT ALL ON OUTLN.OL$HINTS TO GGSTEST
GRANT ALL ON OUTLN.OL$NODES TO GGSTEST
GRANT ALL ON DBSNMP.BSLN_BASELINES TO GGSTEST
GRANT ALL ON APEX_040200.WWV_FLOW_LOG_HISTORY TO GGSTEST
GRANT ALL ON DBSNMP.BSLN_METRIC_DEFAULTS TO GGSTEST
GRANT ALL ON XDB.XDB$ROOT_INFO TO GGSTEST
GRANT ALL ON XDB.XDB$XDB_READY TO GGSTEST
GRANT ALL ON XDB.XDB$CDBPORTS TO GGSTEST
GRANT ALL ON XDB.XDB$IMPORT_TT_INFO TO GGSTEST
GRANT ALL ON XDB.XDB$TTSET TO GGSTEST
GRANT ALL ON XDB.XDB$H_INDEX TO GGSTEST
GRANT ALL ON XDB.XDB$D_LINK TO GGSTEST
GRANT ALL ON XDB.XDB$COLUMN_INFO TO GGSTEST
GRANT ALL ON XDB.XDB$PATH_INDEX_PARAMS TO GGSTEST
GRANT ALL ON XDB.X$NM7UJB7VOLFE92KV0GUML7K0LVSF TO GGSTEST
GRANT ALL ON XDB.X$QN7UJB7VOLFE92KV0GUML7K0LVSF TO GGSTEST
GRANT ALL ON XDB.X$PT7UJB7VOLFE92KV0GUML7K0LVSF TO GGSTEST
GRANT ALL ON XDB.APP_USERS_AND_ROLES TO GGSTEST
GRANT ALL ON XDB.APP_ROLE_MEMBERSHIP TO GGSTEST
GRANT ALL ON XDB.XDB$DBFS_VIRTUAL_FOLDER TO GGSTEST
BEGIN
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 4
Improve this question
6
  • Try adding double quotes to the owner and table name. EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES ON "'|| t.owner ||'"."' || t.table_name ||'" TO GGSTEST';
    – Jefferson B. Elias
    Commented Nov 13, 2015 at 9:34
  • GRANT ALL ON IX.AQ$_STREAMS_QUEUE_TABLE_L TO GGSTEST; it is not error. But pl/sql, loop; it gives error.
    – Melih
    Commented Nov 13, 2015 at 10:07
  • then try this : GRANT ALL ON IX."AQ$_STREAMS_QUEUE_TABLE_L" TO GGSTEST If it doesn't work, it may be something with the Advanced Queue feature...
    – Jefferson B. Elias
    Commented Nov 13, 2015 at 10:27
  • 1
    What's the type of the object? select OBJECT_TYPE, STATUS From dba_objects where owner = 'IX' and object_name = 'AQ$_STREAMS_QUEUE_TABLE_L'
    – Jefferson B. Elias
    Commented Nov 13, 2015 at 10:33
  • I tried it's worked. Status type = TABLE and status = VALID
    – Melih
    Commented Nov 13, 2015 at 10:40

4 Answers 4

Reset to default
3

GRANT ALL PRIVILEGES ON .... is MySQL privs syntax.

You need to do:

GRANT ALL ON SCHEMA.TABLE TO USER

See the documentation.

Improve this answer
6
  • Hi, I taught it was a MySQL Syntax too, but : check this
    – Jefferson B. Elias
    Commented Nov 13, 2015 at 9:33
  • grant all privileges to USERNAME is completely different, and is a db-wide thing.
    – Philᵀᴹ
    Commented Nov 13, 2015 at 9:35
  • There is a section entitled "Granting Object Privileges on a Table to a User: Example"
    – Jefferson B. Elias
    Commented Nov 13, 2015 at 9:37
  • Actually, god knows. Don't have a DB handy to test :/
    – Philᵀᴹ
    Commented Nov 13, 2015 at 9:37
  • It gives same error..
    – Melih
    Commented Nov 13, 2015 at 10:05
2

It is the right command. Previously, it give error because of oracle's installation tables.

BEGIN
    FOR t IN (select * from dba_tables where owner!='GGSTEST' AND owner not like '%SYS%' AND owner not like '%ORDDATA%' AND owner not like '%ADMIN%' AND owner not like '%APEX%' AND owner not like '%XDB%' AND owner not like '%FLOW_FILES%' AND owner not like '%OE%' AND iot_type IS NULL)
    LOOP
    EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES ON '|| t.owner ||'.' || t.table_name ||' TO GGSTEST';
    END LOOP;
    END;
    /
Improve this answer
1

the problem is due to characters being interpreted by PL/SQL, like the dollar sign or the hash tag. By adding double quotes, you won't get the error anymore.

Regards.

Improve this answer
2
  • maybe hash. it doesn't give error with Dollar sign.
    – Melih
    Commented Nov 13, 2015 at 10:50
  • I exclude APEX, XDB, FLOW_FILES, ORDDATA schemas and it's worked.
    – Melih
    Commented Nov 13, 2015 at 10:51
0

I try to grant all privileges of all tables to a user.

Why?

And in future I don't need to add any privileges to that user.

Yes, you will.
As soon as you add another table, you have to repeat the whole process and you have to do so for each and every User. I would suggest that this is not an efficient use of your time.

I need to do this.

We'll agree to disagree.

The vast majority of Users don't need (or want) "all privileges" on any table; select, insert, delete and update is all they need and all they should be given.
How are you going to explain to your Management how a Production system went down (for however long it takes you to recover it) because some User, somewhere, decided to drop a table, just because they discovered that they could? That's not a User's responsibility, so they shouldn't have the permission to it in the first place.

Read up on Roles.
Grant object-level privileges to a Role, then grant that Role to [many] Users. When you modify tables, you only have to [re-]grant privileges to the relevant Role(s); Oracle will take care of "cascading" those privileges to the relevant Users. If you need different "levels" of privilege for different groups of Users, create multiple Roles.

Improve this answer
1
  • While these may be true things, he never gives information on if the schema is changing, if the env is dev or production, and if it's for a personal project or his company. You never address his actual syntax related problem.
    – LowlyDBA - John M
    Commented Nov 13, 2015 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.