Questions tagged [permissions]
Privileges granted to an account or role through the security mechanism of an operating system, database manager or other system.
1,463
questions
0
votes
1answer
12 views
Oracle 12cR2 User Can Select Table Without Privilege
I noticed an interesting thing today. Consider a system where the production and test databases are equipped with the same privileges. In the production database, a user can select the table under ...
0
votes
1answer
14 views
Grant read only access to postgresql schema
I want to grant user foo_user read only access to schema bar_schema.
I executed the following commands
postgres=> GRANT SELECT ON ALL TABLES IN SCHEMA bar_schema TO foo_user;
GRANT
postgres=> ...
1
vote
1answer
73 views
Unable to restrict user from built in SQL Server Functions
We have a SQL Server (Standard) database with a user whom we are attempting to restrict permissions to only CRUD Operations to certain tables (CREATE, SELECT, UPDATE, DELETE) and only allow connect ...
0
votes
0answers
5 views
Which rights does a user need to backup procedures in MySQL 8.x?
I am performing a backup on MySQL 8.0.26 which needs to include all routines, triggers, events etc. in order to be fully recoverable.
The command I am using:
/usr/bin/mysqldump -u $MUSER -p$MPASS ...
-1
votes
1answer
15 views
ALTER DEFAULT PRIVILEGES IN SCHEMA vs ALTER DEFAULT PRIVILEGES FOR ROLE, what is the difference?
What is the functional difference between these two commands?
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name REVOKE ALL ON FUNCTIONS FROM old_role;
ALTER DEFAULT PRIVILEGES FOR ROLE old_role IN ...
0
votes
0answers
11 views
how can I grant permissions by table name, for all schemas, with postgres?
If I have a database called 'A' and multiple schemas but they all have a table called 't',
Schemas get added per account, but they all look the same inside and have the same tables.
I'd like some ...
0
votes
1answer
17 views
ALTER permission on database allows creation of security principals
It appears the ALTER permission granted at the database level allows the principal to which it is granted, to create security principals in the database.
Consider the below example:
/* setup database *...
2
votes
1answer
21 views
What locks does a REVOKE ALL PRIVILEGES ON a PostgreSQL table require?
What locks does a REVOKE ALL PRIVILEGES ON a PostgreSQL table require?
Specifically, could it get blocked by a long running ALTER TABLE which adds a column to the table?
0
votes
1answer
25 views
Oracle - EXECUTE on package without explicit privileges
I'm trying to understand package permissions in Oracle.
If user A is granted EXECUTE on a package owned/created by user B, and that package makes various inserts and updates to a table owned by user B,...
0
votes
0answers
22 views
Granting privileges on a view
I'm reading Database System Concept, in a chapter about authorization, the book first says that:
A user who creates a view does not necessarily receive all privileges
on that view. She receives only ...
0
votes
1answer
34 views
Insufficient privilege for read only transaction
I am using Oracle and SQL Developer and here is a problem I ran into. I have created a table called T_TEST and I am trying to create a read only transaction to it from another user. This is my ...
0
votes
1answer
28 views
I have permission to the table, but not to a view that uses the table
I am having a behaviour I cannot understand, and I have no idea where to look for solutions.
I have a view called wms_view_orderline that gets information from the wms_orderline table. When I SELECT ...
0
votes
1answer
43 views
PostgreSQL denies change in table ownership
I am trying to change the ownership of a table for which I am already the owner.
I am also the owner of the database and have the CREATE and USAGE privileges on the PUBLIC schema.
I get the following ...
-1
votes
1answer
20 views
How to assign minimal privileges for create view
In Db2 v11.1 on Linux we have a request to create a new user with access only to the "create view" privilege.
What are minimal permissions I have to set in order for the user to be able ...
0
votes
1answer
24 views
Data from other user not visible
I am using Oracle and SQL Developer to create a database. I am fairly new to SQL, so this may be a simple question. I have my SYSTEM user, who owns several tables, for example this one:
CREATE TABLE ...
0
votes
1answer
38 views
For my RDS SQL server master user, I granted denyreader and denywriter to a database. Now I cannot revoke it
For my RDS SQL Server master user, I have granted denyreader and denywriter to the master user on a database. Now I cannot revoke it.
There are no other db owners to the database that can change ...
0
votes
1answer
14 views
SQL Developer: View only permissions
I am using SQL Developer, generally to assist in writing reports against various databases but there is one application that is run on an annual basis and at the end of that processing (about a 3 hour ...
2
votes
2answers
83 views
INSERT denied on tables. but login has permission for it
I'm running a procedure from database X to database Y.
On database Y there should be an insert from the user.
In this picture , the first column is the Login, the second one is the role that have all ...
1
vote
0answers
16 views
Can you synchronize user accounts with specific DB permissions with records found in a table?
Background / Current Setup
I have a database in a SQL server that uses row level security to determine what records a user has access to.
Users will have two methods to interact with the database. ...
0
votes
0answers
18 views
Is granting sysadmin for service accounts used for SQL Server Database Engine and Agent a norm?
I assumed that granting minimum permissions for SQL Server database engine and agent service accounts was a norm. Recently, vendor came back and told us that we needed to grant those service account a ...
0
votes
0answers
18 views
Showing a different set of fields for different rows
In my Postgres database I have a table of employees:
CREATE TABLE employee (
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY
name text,
email text,
phone text,
department text
)...
0
votes
0answers
15 views
Azure SQL database make AD user see more databases in SSMS
I'm using Azure SQL databases and want to add a user from my company using Active Directory auth, so adding the user from external provider.
The problem - by design Azure SQL creates AD users as ...
1
vote
1answer
27 views
set a lower privilege permission on a specific database when a GRANT ALL exists on a pattern matching this database on MySQL
I have a user which has access (GRANT ALL) to a set of databases matching a wildcard pattern, now I would like to remove one specific database matching this wildcard from it's access.
mysql> select ...
0
votes
1answer
22 views
Postgres preventing update on "events" table but insert "warning" event
I'm facing a bit of a problem on some restrictions I'm trying to implement on my postgresql database.
My dilemma is as follows: I have an event table that logs basically everything that happens on my ...
1
vote
2answers
147 views
SQL Server 2019 - using MSA as a proxy account
I'm thinking about using MSA (or gMSA) as proxies inside my SQL Server instances. This proxies would be used for example for running SSIS packages or xp_cmdshell command.
But based on what I've ...
0
votes
0answers
19 views
Give SQL Server DB Login permission
I have SQL Server 2017 instance with many databases, as an example, I have a database on this instant called DB_A, and I want to create a SQL Server user account to log in to this database (I will ...
0
votes
1answer
65 views
What is the permission that makes the user a SQL Server administrator?
From the documentation: Determining Effective Database Engine Permissions
SQL Server administrators can see information about all logins and
users. Less privileged users usually see information about ...
0
votes
1answer
17 views
How to query the effective permission of sql login (windows and/or sql user)?
How to query the SQL server to fetch the effective permission of the following SQL logins under the Security/Logins:
SQL user
Windows user
Windows group
1
vote
1answer
24 views
What is effective permission if windows user is added as login as well as via group membership; with only public permission given via the user login?
Link: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/determining-effective-database-engine-permissions?view=sql-server-ver15#principals
Remember that a ...
1
vote
1answer
30 views
How to remove user from login (user is already part of group) and keep the jobs?
I have a sql login for windows group.
One of the members of ths group was also added as a sql login. This user has 1 job in which he is registered as the owner.
I want to remove this user login ...
0
votes
1answer
19 views
Does deleting the login retain the permissions given via group membership?
I have a windows group account added as login in sql server. The group contains around 100 members.
The above windows group login is given specific database level permissions like db_datareader, ...
0
votes
1answer
22 views
Explicitly revoke privileges from a user who is a member of a role that is granted those privileges in PostgreSQL
Let's say I have a user on my PostgreSQL instance called "myuser", and I add it to a new group called "update_all" like this:
CREATE ROLE update_all WITH NOLOGIN ROLE myuser;
I ...
0
votes
1answer
37 views
Postgres Role access privileges
I am trying to assign select privs via a role for current and future tables, and i cant see to figure this out. Please advise.
create role dev_role;
grant usage on schema address to dev_role
grant ...
0
votes
0answers
17 views
MongoDB - Grant permission over one collection that exists on all databases
I have several databases on my MongoDB, all of them have or will have a collection called _schema. Currently, I'm granting permissions like this:
db.grantRolesToUser("userA",[
{"role&...
0
votes
1answer
23 views
Dropping everything in a database owned by a certain role -- not working
I want to delete everything in a certain database owned by a certain role. I've tried to do and nothing has been deleted.
sudo -u postgres psql
postgres=# \l
List ...
0
votes
0answers
27 views
Remote Login Failure for User - Error No 18456
I setup a test user in a SQL Instance.
I tested logging in from within the Windows Server and it worked fine. So password is fine and permissions are fine.
From my PC I attempted to connect to SQL ...
1
vote
1answer
41 views
PostgreSQL 11 - Can I turn a schema into the wild west?
I have been given a task to set up a development database where all users able to access a given project schema (each project would have its own schema) would be able to do anything to any table, ...
0
votes
1answer
64 views
How to restrict Users to see only their databases where has access? [duplicate]
I need to restrict the list of database for our users in a SQL Instance
In order to move forward this
I revoked the view any database permission to the user desired.
But I figured out that only ...
2
votes
3answers
261 views
how to script out database users and permissions in all user databases in the instance [closed]
Can anyone help, please?
I would like to use Steve Kusen's script at Script DB Level Permissions v4.3
to loop through all the user databases automatically, using Aaron Bertrand's sp_ineachdb. Has ...
1
vote
0answers
183 views
Create multi-user database schema with roles, permissions, and services
I am in the process of designing a user administration database and I am stuck in the creation of the tables and their relationships, in order to explain myself below I expose the requirements of the ...
1
vote
0answers
199 views
Change database owner of Azure SQL Database
I want to change the database owner of an Azure SQL Database. I can do this via the server administration user. However I want to do it using a different user. Why? Because I have a process which ...
0
votes
1answer
28 views
PSQL asks for password for a specific database
I am hosting two different databases (manage_invite and manage_invite_v49) on the same server.
As you can see, when I try to connect them using the CLI, it demands the password for the second ...
5
votes
1answer
339 views
How do I create an insert-only user for a Postgres table with an index?
Here's my proposed schema:
CREATE TABLE Surveys (
id serial primary key,
user_email citext,
survey_data jsonb,
created_at timestamp default current_timestamp
);
CREATE INDEX ...
0
votes
1answer
17 views
Creating specific permisions for DB user
I need to give access to CREATE QUEUE and DROP QUEUE queries. Can`t find that information over the internet, only found that db_ddladmin or db_owner fixed database roles, or the sysadmin have that ...
0
votes
0answers
21 views
The user privileges I have assigned don't work when someone imports my .sql database file on mySQL
I'm new to SQLs and mySQL, I and my colleagues who are also new to SQLs are working on a database project and I have successfully queried its structures and inserted data into them.
I then created ...
1
vote
2answers
80 views
Unable to delete server_principal due to granted permissions
I want to delete a server principal from my server because the member is not working at our company anymore. Unfortunately this principal is owner of objects / grantor of permissions because he ...
0
votes
0answers
37 views
The function fn_my_permissions is not showing ALTER permission even when appears assigned to the user
I have a DB user in SQL Server (2012) that I have granted ALTER permission for the DB (via the UI: DB name > Secuity > Users > affected user > Properties > Securables wherein I can see ...
0
votes
0answers
147 views
How can I DENY a SQL Server Database User from seeing tables in a specific schema? VIEW DEFINITION doesn't seem to work
This is somewhat of an X/Y Problem: my original problem is that I want to use SSDT to maintain my database design, with the catch that my database design uses two schemas:
The dbo schema that ...
0
votes
3answers
55 views
why is granting ALL permissions and privileges to a new role/user for a new database difficult with postgresql?
With mysql and am sure countless other database softwares, it is pretty easy to create a new database, create a new user and grant that user to own and be able to do everything on that database
this ...
-1
votes
2answers
38 views
Extremely safe Postgres table permissions
Let's say that I have four tables in my Postgres database, two of which are private and contain highly sensitive information (private1 and private2), and two that contain information I want to allow ...
