如何为用户账户授予数据表权限？授权更新后查询用户数据表报错‘table or view does not exist’的解决方法咨询

Granting table permissions to a user is straightforward once you grasp the basics, though exact syntax varies slightly across database systems. Here’s what you need to know:

• Common Permission Types: You can grant specific access like SELECT, INSERT, UPDATE, DELETE, or use ALL to give full access to the table.
• Core Syntax: The standard structure for granting permissions looks like this:

  GRANT [PERMISSION_TYPE] ON [TABLE_NAME] TO [USER_NAME];
  

  Examples:
  • Give admin1 select access to the stuff table:

    GRANT SELECT ON stuff TO admin1;
    

  • Grant all available permissions to admin1 for stuff:

    GRANT ALL ON stuff TO admin1;
    

• Schema Note: Some databases (like PostgreSQL) require specifying the schema explicitly, e.g., GRANT SELECT ON schema.stuff TO admin1;.

Let’s break down why your query is failing and how to fix it:

Why SELECT * FROM admin1.stuff Throws an Error #

When you ran GRANT UPDATE ON stuff TO admin1;, you gave admin1 permission to update the stuff table owned by your current user (not admin1 themselves). admin1 doesn’t have their own stuff table—they only have update access to your copy of it. So when you try to query admin1.stuff, you’re looking for a table that doesn’t exist in admin1’s schema, hence the error.

Fixes to Access the Table #

1. Use the Fully Qualified Table Name: Have admin1 query the table using the original owner’s username (the user who created stuff):

   SELECT * FROM [original_owner_username].stuff;
   

2. Create a Synonym (Oracle/PostgreSQL/etc.): If you want admin1 to use just stuff instead of the full name, create a synonym for them:

   -- Run this as the original owner or a user with synonym creation rights
   CREATE SYNONYM admin1.stuff FOR [original_owner_username].stuff;
   

   Now admin1 can run SELECT * FROM stuff; without errors.

How to View Tables Under a User Account #

Commands differ by database, but here are the most common ones:

• MySQL/MariaDB:
  • Log in as the user and run:

    SHOW TABLES;
    

  • Or query the information schema for a structured list:

    SELECT table_name FROM information_schema.tables WHERE table_schema = 'admin1';
    

• Oracle:
  • See tables the user owns:

    SELECT table_name FROM user_tables;
    

  • See tables the user has access to (including others’ tables they can query):

    SELECT table_name FROM all_tables;
    

• PostgreSQL:
  • Log in as the user and run this command-line shortcut:

    \dt
    

  • Or use SQL to query the schema:

    SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; -- Replace 'public' with your target schema
    

内容的提问来源于stack exchange，提问作者nanatua