This is a common problem in database application development, with a few valid solutions.
First, just to say it, a single SQL account for the application, with permissions to do anything the program is capable of, is bad. Doubly bad when this account's credentials are hard-coded into your app to allow the connection. The reason why is exactly the reason you posit; someone could feed the credentials into a SQL console and run
delete * from User to blow away your security system.
The first possible solution is to not grant any non-admin account CRUD rights to tables, and of course never hard-code or give an admin account to client software. Instead, Read rights can be granted with SELECT permissions on views, and C_UD rights through EXECUTE permissions on stored procedures. This is an old and widely-accepted solution, and is still used even though best practices for most languages and environments now encourage other patterns.
The second solution is similar in theory but different in implementation; implement a service layer endpoint that defines the possible data operations. This is the new best practice, especially for situations involving "anonymous" usage over public networks (web services). Instead of the client connecting to the DB, the client establishes a service channel to the endpoint and from that point can only call methods the service layer allows, and may also be required to pass in authentication data such as a token in order to make any data call other than the initial login.
The downsides to both of the above approaches are that you will likely have to make deep sweeping changes to your client-side data access to call stored procedures or service methods instead of direct table-based operations. You now also have business logic on the server side and even in the data layer, relating to ensuring users can only do what they're supposed to and are not trying to do what they shouldn't. Lastly, by necessity, you narrow the scope of valid data retrieval operations by exposing specific access methods, which is a step backward in DAL design for most modern languages like .NET and Java (which have ORMs like Hibernate and Entity Framework to provide rich querying ability without "magic strings").
The last solution requires the least implementation but can be more vulnerable; the user account hard-coded into the client has the minimum possible permissions needed to verify the credentials of a user (ideally by being given EXECUTE rights to a single stored procedure "PerformLogin"). As part of user authentication, after verifying credentials, the routine will give back the SQL credentials of a DB user that has the data-related permissions correlating to what the authenticated user account is allowed to do. The client software then re-connects to the database after authentication using the provided credentials. The advantage over other solutions is that only minor changes to your persistence layer (and a reconnect after authentication is successful) are required to implement it in your current system of a direct DB connection and table operations, instead of deeper changes to how you get data by invoking stored procs or a service.
The downsides are that the SQL users available through authentication have to represent useful cross sections of functionality, typically lending the overall structure more toward "tiers" of user permissions instead of a more modular "a la carte" scheme. Means to mitigate this (such as dynamically creating and setting permissions on a user after authentication) are typically their own can o' worms from a security standpoint. You'll will however need many "built-in" users, one for each tier, for this two-step connection process to have much real value (if Johnny User has the lowest user level, but just by authenticating he gets a near-administrator SQL user account for the rest of his session, your client is now the only layer of security, and as we have discussed it can be easily bypassed). Lastly, even with well-tailored SQL user accounts, the permissions needed can still be rather broad; UPDATE rights on a table are needed for the client to update one record, but can be used maliciously by the attacker from a console to blank out all of them. The other solutions very specifically limit the allowable data operations to both approaches.