Questions tagged [permissions]
Privileges granted to an account or role through the security mechanism of an operating system, database manager or other system.
1,610 questions
4
votes
1
answer
2k
views
How to revoke SA permissions that seem to be "stuck" to a login?
Apologies in advance if I don't use some terminology correctly here. I'm not a DBA but I've been temporarily forced into the role.
I've inherited an application that runs on SQL Server 2016 ...
- 43
1
vote
1
answer
2k
views
MariaDB Read-Only user can't see database, why?
I have 2 users, one with ALL grants, another with SELECT only.
User 1 can see the database, not user 2, why?
Read-only user:
MariaDB [(none)]> SHOW GRANTS FOR 'readonly'@'%';
+----------------------...
- 111
1
vote
1
answer
2k
views
Proper roles for a backup-only user on PostgreSQL
I am working on new processes and policies for my PostgreSQL databases when they are first created, and I am trying to define what role attributes are necessary for a role specifically used for ...
- 47
0
votes
1
answer
2k
views
SQL Server on Linux, an issue with filesystem privileges for a backup creation
I'm trying to backup SQL Server to a directory that different users should have access to. To do this, I create a user group, and include the mssql user there - but when I try to create a backup, an &...
- 584
1
vote
1
answer
102
views
Cannot Retrieve User Permissions for All Users
I have the following database and users:
I would like to be able to check what permissions users have to certain tables. I can run the following command and get the permissions for tgrover:
USE ...
- 115
0
votes
0
answers
1k
views
Linked Server Error Msg 7321, Level 16, State 2, Line 1
I am getting the following error when I execute a simple linked server query.
OLE DB provider "MSDASQL" for linked server "name" returned message "HTTP request returned status ...
- 83
0
votes
1
answer
2k
views
How to create a Postgres schema, let one role create tables in it, and let another role read from those tables
I'm trying to set up a Postgres database using a process along the following lines:
A script is run, authenticated to role postgres that:
Creates a new, empty schema schema0.
Creates a role role0 ...
- 113
0
votes
0
answers
1k
views
Read Only user's access getting removed automatically in PostgreSQL
I have created a read only user per below commands:
CREATE ROLE test_dw WITH
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOINHERIT
NOREPLICATION
CONNECTION LIMIT -1
...
0
votes
1
answer
2k
views
PostgreSQL granted all privileges on schema, but user still cannot insert a record
This is maddening.
I'm running PostgreSQL 14 in a Docker container using a standard image.
# docker-compose.yml
version: "3.7"
services:
pg:
image: postgres
environment:
- ...
- 341
0
votes
3
answers
2k
views
Unable to grant membership to public role
I'm trying to refresh my dev db with a snapshot from prod. I've restored the production db from the prod server to my dev server just named as development. I'm trying to grant access to the dev db to ...
- 111
7
votes
3
answers
748
views
Preventing stored procedures from writing to databases
I am a database developer mostly writing SSRS reports. I would like to create stored procedures and assure the DBAs that those stored procedures cannot write to the production databases, without the ...
- 83
0
votes
1
answer
35
views
Drop view from package
I have a user called DEPLOY defined with DBA role.
When logged on via SQL Developer the user can drop a view as follows:
DROP VIEW APP_USER.VIEW_NAME_VW CASCADE CONSTRAINTS
If I put the same ...
- 125
0
votes
0
answers
113
views
When root user changes a table name, how to ensure end user permissions also gets updated to the new table in MySQL?
I have a table - user1_db.table1
user1 has the following permission -
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER ON `user1_db`.`table1` TO `user1`@`localhost`
Now the root user changes the ...
- 123
0
votes
0
answers
333
views
DBA role is not applied
This worked yesterday, but now it won't.
We've defined a user 'DEPLOY' on 'Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0' which will be used for deployments into each environment - DEV, ...
- 125
2
votes
2
answers
3k
views
How can I grant permission to multiple databases / tables to a user in a single query in MySQL?
I am trying the query -
GRANT SELECT ON company.*, school.*, onlineshop.* TO user1@localhost;
To which I am getting a syntax error.
I can grant permissions one by one using -
GRANT SELECT ON company.*...
- 123
0
votes
1
answer
4k
views
Create readonly user in postgresql
Using pg 13.4, created a readonly user reader for database abc, via:
CREATE ROLE reader WITH LOGIN PASSWORD 'psssswwwd'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'...
- 253
0
votes
1
answer
46
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 ...
- 29
1
vote
1
answer
2k
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=> ...
- 211
1
vote
1
answer
310
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 ...
- 111
0
votes
1
answer
1k
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 ...
- 111
0
votes
2
answers
1k
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,083
2
votes
1
answer
565
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?
- 263
0
votes
1
answer
3k
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,...
- 137
1
vote
0
answers
30
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 ...
- 13
0
votes
1
answer
245
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
1
answer
924
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 ...
- 205
4
votes
1
answer
2k
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,815
-1
votes
1
answer
577
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 ...
- 401
0
votes
1
answer
694
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
1
answer
371
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
1
answer
354
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 ...
- 11
3
votes
2
answers
2k
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 ...
- 7,484
1
vote
0
answers
107
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. ...
- 113
1
vote
0
answers
87
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 ...
- 167
1
vote
1
answer
243
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
1
answer
180
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 ...
- 101
1
vote
2
answers
3k
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 ...
- 141
0
votes
1
answer
338
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 ...
- 3,489
-1
votes
1
answer
4k
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
- 3,489
1
vote
1
answer
138
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://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/determining-effective-database-engine-permissions?view=sql-server-ver15#principals
Remember that a ...
- 3,489
1
vote
1
answer
394
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 ...
- 3,489
0
votes
1
answer
134
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, ...
- 3,489
0
votes
1
answer
628
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
1
answer
391
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 ...
- 7
0
votes
0
answers
564
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&...
- 101
0
votes
1
answer
206
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 ...
- 1
0
votes
0
answers
365
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 ...
- 101
2
votes
2
answers
125
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, ...
- 33
0
votes
1
answer
2k
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 ...
- 21
3
votes
3
answers
10k
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 ...
- 377